More Optimizer Bugs in MySQL

I'm proud to report of two bugs I've submitted to the MySQL bug tracker.
Both are seemingly simple and vaguely related, but have grave consequences when combined with large amounts of data.

The first one, Bug #50212 is a regression performance problem in 5.1, dealing with MySQL not using a primary key when it can, in order to avoid a file sort. The problem is made worse by the fact that even using a FORCE INDEX hint does not help things. The bug requires a join condition to be present to reproduce.
So if you have a large table, and you're expecting MySQL to select data from it ordered by the primary key and avoid a file sort, you're out of luck.
Of course, you can remove the ORDER BY and hope it's going to return in order, but it's not guaranteed.
There is no workaround, and this bug is in a verified state for a year now.

A more recent bug, Bug #59557 deals with another optimization issue, that takes place when MySQL is not willing to perform an index scan to complete a GROUP BY statement, when you reverse the order of the fields compared the their order in the index.

Since a GROUP BY statement does not care about the order of fields, it should not matter: the fields in the GROUP BY clause are essentially a set, and not a ordered list, as they are in ORDER BY.

An example to demonstrate the problem (taken from the bug report itself):
For this table:

CREATE TABLE `t` (
`a` INT(11) NOT NULL,
`b` INT(11) NOT NULL,
PRIMARY KEY (`a`,`b`)
) ENGINE=INNODB;

Run this:

mysql> EXPLAIN SELECT COUNT(*) FROM t GROUP BY b,a\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra: Using index; Using temporary; Using filesort

What you get is: Using index; Using temporary; Using filesort
If you're a MySQL veteran, you know that any GROUP BY is implicitly a ORDER BY as well - and you'd guess that causes the issue.
Unfortunately, even if we add a ORDER BY NULL clause, the optimizer would still choose a wrong plan, like so:

mysql> EXPLAIN SELECT COUNT(*) FROM t GROUP BY b,a ORDER BY NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra: Using index; Using temporary

What you get now is: Using index; Using temporary
A bit less wrong, but still... wrong. If someone knows what the database tried to accomplish by using a temporary table here, I'd be happy to hear an explanation... :)

As I've said, I am proud to submit bugs and help the community, but I would be a bit more proud if bugs I've submitted a year ago would have been fixed by now. Just saying.

Which SQL Mode Should I Use with MySQL?

Dealing with sql_mode can be a tricky business, and many people have blogged about it before (here and here). My intent is not to explain the details of what each option means (since it's covered in the manual), but rather to answer this simple and common question:
Which SQL mode should I use with MySQL? / What is the recommended SQL mode for MySQL?

My general purpose recommendation is this one:

sql-mode="TRADITIONAL,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

This "traditional" setting includes most of what you'd want your database to behave like, namely:

STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER

This has a nice benefit of returning an error instead of a warning on truncating a varchar column, not let you inserts completely broken date values, and give out division by zero errors. That's pretty traditional as I see it.

The other two settings make sure you don't create InnoDB tables as MyISAM by mistake, and that you include all the columns in the GROUP BY clause, like in other databases.

That's my choice for a reasonable all-around setting - for different applications you might need different ones (see excellent comment by Roland Bouman below).

How to Install innotop 1.8.0 on CentOS 5

innotop is a fine tool that every MySQL DBA should be familiar with. Although it takes a while to get used to, it's worth it. It's less than intuitive for most people, unless you grew up in Linux.
The other problematic part except the learning curve is the installation. It took me a while last time I had to install it, and this time re-installing it I've decided to write down the steps needed.

Most of the trouble I went through the first time around were with CPAN, and finding out that those Perl modules were available in RPM form saved me lots of time. Avoid CPAN if you can for this purpose.
Also, these steps should work on RedHat Linux as well, of course, but I didn't test it.

1. Download the compatibility version of the shared MySQL libraries. The compatibility package will save you a lot of trouble due to dependencies. The rest won't go smoothly if you don't get this one.
   It should be named something like:

   MySQL-shared-compat-5.1.xx.distribution.platform.rpm

2. Install it:
   

   rpm -Uvh MySQL-shared-compat-5.1.52.rhel5.x86_64.rpm

3. Install these packages:
   

   yum install perl-DBI perl-DBD-mysql perl-TermReadKey

4. Download latest Innotop, and extract it:
   

   wget http://innotop.googlecode.com/files/innotop-1.8.0.tar.gz
   tar zfx innotop-1.8.0.tar.gz

5. We're done with the prerequisites. Go into the directory and install innotop (from now on it's the same as in the INSTALL file):
   

   perl Makefile.pl
   make install

That's it. We're done.
Hope this saves someone some time.

Update:
On Debian, this is also simple due to this little trick (if you have the right sources defined):

sudo aptitude install libdbd-mysql-perl

Recommended Google Chrome Extensions

Not all great Chrome Extensions get to the Featured or Popular lists on the Google Chrome Extensions website. These are 3 extensions I really like, and are not as widely known.

• Chromey Calculator - super useful for quick money/distance conversions
• JoinTabs - unites all the open chrome windows into one window as tabs
• Facebook Photo Zoom - does exactly what the name says, highly recommended

UNHEX for Integers in MySQL

The following is a very, very specific subject matter, but I wish this blog post had existed when I was looking for the solution to a problem I was having. Most likely it has no interest to anyone who is not specifically looking for it, so feel free to stop reading...

MySQL has a few built-in functions for handling binary data. One of them is HEX which converts any data into hexadecimal representation. The function which you would expect to do the opposite (as the manual states) is UNHEX which takes a hexadecimal representation and turns it into characters.

So, if you try to do:

SELECT UNHEX(HEX('a'));

You get an "a" back. But if you try that on a integer:

SELECT UNHEX(HEX(1));

You get back the char corresponding to the ASCII value of 1, which is not what was intended.
The correct way to do it, and the real opposite of HEX is the CONV function which converts betweens bases:

SELECT CONV(HEX(1), 16, 10);

This time the result is the number "1" as expected.

Emulating The Missing RENAME DATABASE Command in MySQL

I'll approach this topic from another direction.

I had to perform a task which required moving tables between schemas on the same MySQL server.

One way of doing this would be:

1. Dumping the original table to disk.
2. Creating new empty table on the target schema.
3. Importing table from disk into the target schema.

But, a simpler way exists!

The manual entry for RENAME TABLE casually describes this feature:

As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

Which works almost instantaneously and does what it is supposed to do. This command could also be called MOVE TABLE.

Using this command, it is easy to emulate the missing RENAME DATABASE command.

1. Create the new schema.
2. Perform a query to generate the move commands from the source to the target schema:
   

   SELECT CONCAT('RENAME TABLE ',table_schema,'.',table_name,
   ' TO ','new_schema.',table_name,';')
   FROM information_schema.TABLES
   WHERE table_schema LIKE 'old_schema';

   Run those by copying them from your editor, or whatnot. If you're using SQLyog, CTRL-L switches the results tab to text mode for easy copying.
3. Drop the old schema

Some notes:

If you're using InnoDB foreign keys, worry not. The constraints are kept across databases, and moving the tables does not affect them - no need to move the tables in any specific order.

The move command also moves the .idb files between the database directories, if you're using innodb_file_per_table.

Hope this helps.

A Free Text Editor for Very Large / Huge Files on Windows

Every once in a while a programmer finds himself in need of a tool that allows him to edit very large text files. By large, I mean several gigabytes. For DBAs it is common, especially if you’re using MySQL dumps a lot. What do you do if you’re doing this on Windows?
If you’re using Notepad++ or any other Scintilla derivatives, you’re out of luck - those editors are not cut out for this kind of work. Using Visual Studio also won’t work. There are some partial solutions just for viewing, like LTFViewer – but it cannot handle large files without line breaks, something common in MySQL dumps. So what do you do?
The answer is simple and somewhat unexpected – use Vim for Windows. It opens a file of any size effortlessly and almost instantly – then you can browse through the file like it was a small script - it just works.
Few things to notice:
For files with very long lines, like dumps, consider using “set nowrap”.
Another thing is the fact that Vim keeps a temporary copy of the file for backup when saving an edited file, so not only it takes a while to save since it writes the entire buffer to the disk, but you also need to delete the temp file afterwards. To disable this behavior, use “set nobackup”.