While setting up MySQL Enterprise Monitor 2.2 (Merlin) on a system which had been running version 2.1 I thought I’d try and see what difference the change from using normal innodb tables to using the compressed table format available in the innodb plugin.

I’ve been using a separate db backend for merlin because for me it’s easier to manage and also the database backend has been put on a dedicated server. I’ve also been trying the innodb_plugin on another busier server as I had performance problems with the normal 5.1.42 built-in innodb engine which the plugin managed to solve.

So given that I was using a separate db server I upgraded it to 5.1.47, configured the server to use the plugin (1.0.8) rather than to use the built-in innodb engine and then decided to …

MySQL 5.1.47

In addition to the security update, MySQL 5.1.47 is also very important for an additional reason. The InnoDB plugin that ships with this version has been updated to 1.0.8, which is …

MySQL 5.1.47

In addition to the security update, MySQL 5.1.47 is also very important for an additional reason. The InnoDB plugin that ships with this version has been updated to 1.0.8, which is …

MySQL 5.1.47

In addition to the security update, MySQL 5.1.47 is also very important for an additional reason. The InnoDB plugin that ships with this version has been updated to 1.0.8, which is …

TO_DAYS() prunes two partitions instead of one
If you are partitioning by date, chances are that you are using TO_DAYS(). And depending on how you have partitioned your table, your queries are as fast as you expect them to be. However, there are cases where your query takes twice as long as it should, and of course this will not make you happy.

For example, in a table partitioned by month, when …

I have been upgrading more MySQL database instances recently and have found a few more potential gotchas, which if you are not careful, can potentially be rather nasty. These are not documented explicitly by MySQL, so it may be handy for you to know if you have not come across this type of thing before.

Most of the issues are those related to upgrading MySQL instances which are replicated, either the master servers or the slaves. Some seem specific to the rpm packages I am using (MySQL enterprise or MySQL advanced rpms), though others are not.

Take care upgrading a 5.0 master when you have 5.1 slaves

It is not a good idea to run a mixed major version of mysql in a replicated environment so why would I be doing this? If you work in a replicated environment and have several slaves then it is recommended that you upgrade the slaves first. I work with quite a few slaves so the process of upgrading them all takes longer …

UPDATE This matter was more tricky than it appeared at first sight. As Bug#49861 shows, several MySQL engineers were …

As part of reviewing storage engines for my work on the upcoming Expert PHP and MySQL book, I finally had an excuse to try out the InnoDB Plugin for MySQL which is now conveniently included with MySQL 5.1 since 5.1.38.

Following the MySQL 5.1 Reference Manual instructions at 13.6. The InnoDB Storage Engine I included the bare minimum as documented to my my.cnf.

[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so ;innodb_trx=ha_innodb_plugin.so ;innodb_locks=ha_innodb_plugin.so ;innodb_cmp=ha_innodb_plugin.so ;innodb_cmp_reset=ha_innodb_plugin.so ;innodb_cmpmem=ha_innodb_plugin.so ;innodb_cmpmem_reset=ha_innodb_plugin.so

However to my misfortune the following error occured.

091212 17:45:14 [ERROR] Can't open shared library …

According to the official lifecycle calendar at http://www.mysql.com/about/legal/lifecycle/#calendar, active support for MySQL 5.0 (including regular binary updates) will end on December 31st, 2009, which is about 3 weeks away.

Many folks are still using MySQL 5.0.45, as until October that was the package that came with RedHat. That was released in July 2007, over 2 years ago!

Upgrading to MySQL 5.1 is not difficult, though it requires more steps than just upgrading the packages.

There is a list with all the changes made that might affect the upgrade process at http://www.pythian.com/news/1414/new-in-mysql-51-sheeris-presentation/. This includes which variable names have been deprecated and changed, as well as how to upgrade stored …

It’s been a while since I’ve posted anything new. In the meantime I’ve been pretty busy. Working on production systems often means that you are not running bleading edge. That’s fine but sometimes you need to look at doing the upgrade and to do that you have to do quite a few checks to see how well newer versions of the software you use will work.

I’ve been in that situation with MySQL. I have quite a few boxes most of which are 5.0 and have been working fine. MySQL-5.1 has been GA now for some time, and I have started to look at it as support for 5.0 is about to end.  I’ve already found quite a few 5.1 bugs, good enough to crash the server, but again that’s to be expected. Heavy loads and odd usage of the server means that MySQL can never be able to test all things.  Replication problems caused a few issues with the first versions of 5.1 but they all seem to have been ironed out now.  The other …