2010-11-18

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).

2010-11-09

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