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.