I've been trying to circle back and clean up things I left for later in several chapters of High Performance MySQL, second edition. This includes a lot of material in chapter 4, Schema Optimization and Indexing. At some point I'll write more about the process of writing this book, and what we've done well and what we've learned to do better, but for right now I wanted to complete the picture of what material we have on schema, index, and query optimization. The last two chapters I've written about (Query Performance Optimization and Advanced MySQL Features) have generated lots of feed back along the lines …

I was just searching for something and found this MySQL bug report: Other JDBC drivers I have used make toast for breakfast... MySQL Connector/J doesn't make toast, it can only pour a bowl of froot loops... ...Thank you for taking the time to write to us, but this is not a bug... I believe you should look into a device called a "toaster" to make your toast.

The new release of MySQL Toolkit (version 1051) updates MySQL Parallel Dump in minor ways, but more importantly, it adds MySQL Parallel Restore. MySQL Parallel Restore is the reverse of MySQL Parallel Dump. You give it one or more files and/or directories, and it discovers all the files contained within them and loads them in parallel. It understands how to load SQL and/or TXT/CSV files. If you give it some of both, it loads the SQL first and then loads the TXT/CSV as delimited files with LOAD DATA INFILE.

Have you ever needed a random timestamp in MySQL? For example to create demo data programmatically? Here’s my solution:

SELECT FROM_UNIXTIME(
  FLOOR(
    UNIX_TIMESTAMP('2007-01-01') +
        RAND() *
        (UNIX_TIMESTAMP('2007-01-03')-UNIX_TIMESTAMP('2007-01-01'))
    )
) as random_timestamp;

Or if you prefer a function:

CREATE FUNCTION random_timestamp (start TIMESTAMP, end TIMESTAMP)
RETURNS TIMESTAMP NOT DETERMINISTIC
RETURN FROM_UNIXTIME(
  FLOOR(
    UNIX_TIMESTAMP(start) +
    RAND() *
    (UNIX_TIMESTAMP(end)-UNIX_TIMESTAMP(start))
  )
);

mysql> select random_timestamp('2007-10-01', NOW());
+---------------------------------------+
| random_timestamp('2007-10-01', NOW()) |
+---------------------------------------+
| 2007-10-05 23:07:11                   |
+---------------------------------------+
1 row in set (0.00 sec)

I've been doing a few experiments with ENUMs and SQL_MODE.

Not a lot of people realise that in MySQL, an ENUM can actually contain one other value as well as the specified ones - no, not NULL, but ANOTHER value, specifically, the empty string ''.

This can lead to trouble. The empty string is entered by MySQL when it tries to insert an invalid value into the column. It does also give a warning, but nobody takes any notice of those right?

Demo schema:

CREATE TABLE enumtest (
 id int not null auto_increment,
 name varchar(100) NOT NULL,
 status ENUM('ok','broken','decommissioned','narnia'),
 PRIMARY KEY(id),
 KEY(name),
 KEY(status)
);

Now let's try some inserts...

INSERT INTO enumtest (name,status) VALUES
 ('Mark','ok'),
 ('Fred','broken'),
 ('Bob','decommissioned'); …

This was the title of an article back in 2002 by Shelley Doll and other articles with more or less the same concerns including the secretary of the ANSI database committee. In my recent blog post I made the point that I do hope that MySQL AB tries to follow the standards as much as possible (while retaining the freedom to add things as deemed necessary .. LIMIT and friends). In a chat conversation with Jan, he asked me what my thoughts are in regards to the SQL standard in particular. Most people will for example agree that standards compliant SQL routines are no fun to write. Unfortunately I had to agree with him that SQL today isn't what it should be.

Since I am not sure if everybody is aware of this so just let …

mysqlsla v1.6 is ready. It has four new options to allow better isolation/filtering of queries: –only-databases, –only-users, –only-hosts, –only-ids. Each option limits the queries under analysis to their respective properties (i.e., “–only-users root” only analyzes queries executed by root). See the documentation for which options work with which kinds of logs.

One of MMM users reported that they’re experiencing really weird memory leaks in checker processes used by MMM. After a deep investigation I’ve found out that Perl part of the checker and checker modules does not leak (at least I didn’t found these leaks), so I think it could be caused by some problems in MySQL DBD module (client uses Ubuntu server).

So, I’d like to ask all users to check if their checker processes use more memory than expected and if yes, what OS, MySQL libraries versions and Perl version used on their servers.

Thanks in advance for any help.

Some small (but meaningful) updates in HEAD at http://consoleninja.net/code/dpm/dpm.git :

- You can now actually specify a username/password when connecting to a server. A few other similar spots were fixed.
- You can get the remote connection ID from a connection object.
- New myp.close() command for closing connections ;)

I've tested kill -9'ing clients, servers, in various states of transit with no obvious bugs. It's probably still unstable but these changes make a big difference.

I'm not an API designer by any stretch. I'm stuck wondering:

Should connobj:remote_id() (or just remote()) return the object id, or the full object?

On that same line, all callbacks have the affected connection ID as an argument. Should that just be a reference to the actual connection object, or continue to …

Some small (but meaningful) updates in HEAD at http://consoleninja.net/code/dpm/dpm.git :

- You can now actually specify a username/password when connecting to a server. A few other similar spots were fixed.
- You can get the remote connection ID from a connection object.
- New myp.close() command for closing connections ;)

I've tested kill -9'ing clients, servers, in various states of transit with no obvious bugs. It's probably still unstable but these changes make a big difference.

I'm not an API designer by any stretch. I'm stuck wondering:

Should connobj:remote_id() (or just remote()) return the object id, or the full object?

On that same line, all callbacks have the affected connection ID as an argument. Should that just be a reference to the actual connection object, or continue to …