Saturday, November 10, 2012

MySQL 5.6.8 - Broken compatibility ahead

Users are safer

MySQL 5.6.8 was announced a few days ago. You can download it from the MySQL downloads page

This is the second release candidate of MySQL 5.6, and it is strange. My understanding of a release candidate is something that is stable, its features committed long time ago, and the release will just attempt to fix bugs. Instead, there are features that were not in the first release candidate. This one strikes me as really odd (quoting from the announcement) :

On Unix platforms, mysql_install_db supports a new option, --random-passwords, that provides for more secure MySQL installation. Invoking mysql_install_db with this option causes it to perform the following actions in addition to its normal operation:
  • Create a random password, assign it to the initial root accounts, and set the "password expired" flag for those accounts.
  • Write the initial password file to the .mysql.secret file in the directory named by the $HOME environment variable. Depending on operating system, using a command such as sudo may cause the value of $HOME to refer to the home directory of the root system user.
  • Remove the anonymous-user accounts.

As a result of these actions, it is necessary after installation to start the server, connect as root using the password written to the .mysql.secret file, and assign a new root password. Until this is done, root cannot do anything else. This must be done for each root account you intend to use. To change the password, you can use the SET PASSWORD statement (for example, with the mysql client). You can also use mysqladmin or mysql_secure_installation.

For new RPM install operations (not upgrades), mysql_install_db is invoked with the --random-passwords option. (Install operations using RPMs for Unbreakabkle Linux Network are unaffected and do not use this option.)

On one hand, this is a very commendable feature, one that many users (including me) have been asking for years. It is especially pleasing to see that this feature removes the anonymous-user accounts, which have been source of chagrin and gotchas for as long as I can recall.

Some users are safer, whether they want it or not

On the other hand, this feature is a potential disaster. Making the new feature mandatory for new RPMs means that people used to the old behavior and relying to a MySQL RPM to perform a repeating task will have to rethink the job. Existing scripts and provisioning recipes will be broken, and the way this feature is created makes it unnecessarily hard to script it appropriately. You will need to find where the password was saved, depending on how $HOME was intended by the application, and parse the file to retrieve the password.

Here is what the .mysql_secret file looks like:

cat ~/.mysql_secret 
# The random password set for the root user at Sat Nov 10 10:30:19 2012 (local time): 0aGUREOO

BTW, if you are not aware of the new feature, it is likely that you will never notice. As noted already in MySQL 5.6 is too verbose when creating data directory, the amount of unwanted data that is shoved to your screen is so big that the tiny message about the new random password being saved in a location near you or far far away gets lost in the flood.

Another consequence of this feature is that it is sandbox-unfriendly. Tying the secret password file to $HOME without the possibility of telling where we want it (I found already a workaround, but it is not pretty) means that the file can be overwritten and I can end up with one or more servers that are locked in.

Users of Oracle Linux have the right not to be safer

The oddest thing of all is the last sentence in the quoted text. While everyone else using a RPM will be struggling with this last minute addition, Oracle's own Linux distribution won't be affected. I can only speculate why that is happening, and my imagination conjures scenes of product managers for MySQL breaking the news to product managers for Unbreakable Linux, being told that this new feature is too much hassle, and in the end deciding that Oracle customers can be spared the aggravation. But this is just my unruly mind working overtime.

More to come

Ready to upgrade yet?

Better wait a few more releases. The next release, which could be another release candidate or the GA, is not out yet, but the release notes show that there are at least some more incompatibilities waiting for you.

If you were trying out the global transaction ID, be aware that the odd-looking --disable-gtid-unsafe-statements server option will be renamed --enforce-gtid-consistency. That sounds much better. However, the manual page of how to set up GTID uses the new variable name, and if you try it with MySQL 5.6.8, it will fail.

More changes will come. Currently, if you use GTID you can't restore a backup made with mysqldump, and there is a partial solution to that in the next release, which requires setting some global variables that are read-only now and will become writable. And again for GTID,

the server allows single statements on nontransactional tables.

Whatever that mean is not clear. I will report back after testing the next release.

Until then, have fun with MySQL 5.6.8!