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!


Chuck said...

What is broken using mysqldump for restore? I have done this without a problem. Do you mean the replication specific information or something else?

Giuseppe Maxia said...

Here is what does not work (with mysql 5.6.8):

$ mysqldump --all-databases > all_dbs.sql

$ mysql < all_dbs.sql
ERROR 1785 (HY000) at line 218: Updates to non-transactional tables are forbidden when DISABLE_GTID_UNSAFE_STATEMENTS = 1.

Simon Mudd said...

I've reported various GTID problems to Oracle which I've noticed in 5.6.5 .. 5.6.7 and I guess some of these have triggered incompatible changes in 5.6.8, probably as the only solution to the issues I've reported.

That said the GTID feature sounds "good" and is something which I think we all would like. I have the feeling that the designers of this functionality did not look at the usage in a real environment, and therefore not consider all the cases where it needs to work and where at least in 5.6.6 .. 5.6.7 it broke in silent and silly ways.

I also agree, now we're at a 5.6-RC level it does seem sensible that the environment starts to stay stable and if Oracle has to make changes it would be helpful if they explain the reasoning behind these changes and the issues which have triggered them.

Sheeri K. Cabral said...

This is really annoying considering the official MySQL way to upgrade major versions (e.g. from MySQL 5.5 to MySQL 5.6) is to do a mysqldump export, move/delete the files, remove the package (if using one), install the newer version, and import.

If people actually do that, they will find a surprise when they upgrade because they would expect the password to be empty on a new db. And this is the official MySQL way to upgrade.....