Friday, May 02, 2014

MySQL defaults evolution

MySQL, the original brand, the one developed by the MySQL team at Oracle, is steadily evolving. You can feel it if you try every new release that comes out of the milestone release cycle. Or even if you don’t try all of them, just testing a release once in a while gives you something to think about.

The engineers at Oracle are trying hard to improve the defaults. If you are the out-of-the-box type, and just install the new version on top of the previous one, leaving the same setup in place, you may be up for a for a few surprises. It’s the marketing, see? They tell you that just by replacing your old MySQL (5.1 or 5.5) with MySQL 5.6 you get 30% to 70% performance improvement. Which happens to be true, not only because the server is better, but also because they have changed the defaults. However, this change in defaults may come with some serious consequences for the ones who skip the release notes.

An annoying consequence of the MySQL team focusing on security is that in MySQL 5.6 you get a mandatory warning if you use a password in the command line. On one hand, it’s a good thing, because they force you to use better security practices. On the other hand, it’s a royal PITA, because many applications are broken because of this warning, just by replacing MySQL 5.1 or 5.5 with 5.6. There are solutions. For example, you can adopt the new mysql_config_editor to handle your password, but that would break compatibility with previous MySQL versions. Rewriting complex procedures to use configuration files instead of username and passwords is tricky, especially if you are testing exactly the behavior of using a password on the command line to override the contents of an options file.

INTERMISSION: this is a point of contention with the MySQL team. They have started a trend of introducing features that will prevent working smoothly with previous versions of MySQL. Up to MySQL 5.5, installing a server and staring using it was a set of steps that would work in the same way regardless of the version. With MySQL 5.6, all bets are over. When you install a new server, you get a long list of unwanted messages to the error output (which is fortunately fixed in MySQL 5.7), and then you get the warning if you use a password on the command line. For me, and for many developers who build software related to MySQL, the ability of writing a piece of software that works well with any version is paramount. The MySQL team seems to think that users will be happy to throw everything to the wind and start writing new code for MySQL 5.6 only instead of reusing what was working until the month before. And let’s be clear: I fully understand the need of moving forward, but I don’t understand the need of trapping users in the new behavior without remedy.

Back to the defaults. What else is new? One good improvement in MySQL 5.6 is a change in the default value for SQL_MODE. Up to MySQL 5.5, it was an empty string. In MySQL 5.6.6 and later it is ‘NO_ENGINE_SUBSTITUTION.’ Can this change have side effects? Yes. Code that worked before may break. IMO, it’s a good thing, because getting an error when trying to create a table of a non-existing engine is better than having the table created silently with the default engine. I can, however, think of at least one case where a silent substitution is desirable, as I have seen in action at a customer’s deployment. That aside, one wonders why they did not go the extra mile and add STRICT_TRANS_TABLES (or even STRICT_ALL_TABLES) to the default. It turned out that they did it… and didn’t. When you install a new data directory using mysql_install_db, the procedure creates a my.cnf file in the $BASEDIR (the place where your mysql tarball was expanded), containing the line

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

However, if you have skipped the release notes, you will be left wondering where does this additional directive come from, since the manual mentions only one of them, and SHOW VARIABLES tells you that SQL_MODE contains two values.

MySQL 5.7 has also changed something. There is a lot of cleanup going on. Options and variables that were deprecated long ago suddenly disappear. Did you ever use ‘key-buffer’ as a short for ‘key-buffer-size’? If you did, that directive in the my.cnf won’t work anymore. (I could not find it in the release notes, but the test suite for MySQL Sandbox suddenly stopped working when I tried MySQL 5.7 and then I realized what was happening.) More to the point, though, is the installation procedure. In MySQL 5.6 there is a –random-passwords option that generates a random password for the server, and you can’t do anything with root until you use such random password to access the server and change the password to something else. This is an improvement over the old and despicable root without password, which has been the default since the very beginning of MySQL, and it’s been the source of many security nightmares and interesting bugs. In MySQL 5.7.4, this behavior, i.e. the generation of a random password during the installation, is now the default. It is good news, because the old behavior was a disaster, but if you have an automated way of dealing with installation, there will be more hard work in front of you to handle the changes. The implementation is not script friendly, and definitely nightmarish if you want to install several instances of the server in the same host. What happens when you install MySQL 5.7.4? The installation software generates a random password, and writes it to a file named .mysql_secret in your $HOME directory. A sample file that was created by the installer would be:

# The random password set for the root user at Mon Mar 31 10:16:54 2014 (local time):
2X7,S4PGkIg=H(lJ


EOF

If you wanted a script to read the password generated by this procedure, it would be a mistake to look for the second line. In fact, if you repeat the installation on the same host, you get something like this:

# The random password set for the root user at Mon Mar 31 10:16:54 2014 (local time):
2X7,S4PGkIg=H(lJ


# The random password set for the root user at Tue Apr 29 09:35:07 2014 (local time):
_S07zDt7dQ=,sxw9


# The random password set for the root user at Tue Apr 29 09:42:19 2014 (local time):
r[yn4ND$-5p,4q}5


EOF

Then the correct approach would be looking for the last non empty line in the file. However, if you were installing several instances in the same host (such as MySQL Sandbox does) you wouldn’t be able to find which password belongs to which server. Admittedly, multiple instances of the same server is not what most users do, but since it breaks MySQL Sandbox, which is used by many, I mention it here. BTW, MySQL Sandbox 3.0.44 has a temporary fix for this behavior. If you install MySQL 5.7.4 or later, it will include –skip-random-passwords, and defaults to the old password-less installation. There is a hackish workaround for the above uncompromising design and I will add it to MySQL Sandbox unless the next MySQL version introduces an optional path for the .mysql_secret file.

Summing up, there are mostly good improvements from the MySQL team, although the attention to usability is still low. They are whipping users into better security. A gentler approach would be appreciated. Sometimes I see a post from the community team asking for feedback on some feature being deprecated or modified. I would welcome such requests on matters that affect the default behavior of everyday tools.

8 comments:

Anonymous said...

The logging changes removed too much that is useful so we'll probably adjust 5.7 again. Anything that comes back should have been reviewed by me, as a support engineer who knows what is used and why. That probably won't be perfect for you but it will be better than what you saw originally. I'm definitely not going to say "the plugin start/stop and InnoDB settings messages are always needed". We want to give you total control at the individual message level but that won't happen in 5.7. Hopefully the one after that, but no guarantee, just desire.

Not sure what we'll do about the "error log" that is really "status log" and stderr mixing but I agree that we should do something about that so that stderr gets errors while the log file gets useful non-errors as well. It's something that just grew and is one of many things that merits cleanup.

Unique option prefixes are no longer supported in the 5.7 server. Todd Farmer blogged about this at http://mysqlblog.fivefarmers.com/2013/08/02/option-prefixes-deprecated/ and hopefully the deprecation warnings starting in 5.6.13 will get people to change them before upgrading to 5.7. As well as the 5.6.13 release note it's mentioned in the 5.7.2 release note at http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html .

We did STRICT_TRANS_TABLES in the default config file because that would only affect new installations of the server. STRICT_TRANS_TABLES could cause data loss and other breakage in applications that don't understand it so we wanted to give one more version's time to get applications or settings updated before changing the compiled-in default. It's described at http://dev.mysql.com/doc/refman/5.6/en/server-default-configuration-file.html . With 5.7 the compiled-in defaults for this are heading more in the direction of what is normally recommended and desired by us and less in the direction of backwards compatibility by default. You should also read http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes that is mentioned in the 5.7.4 release note for other changes.

See the high level architecture part of http://dev.mysql.com/worklog/task/?id=6962 for more on skip-random-password, it's what we did to try to make this easier to script. If it's not enough please blog about why or file a bug report on how it fails so we can take a look. As well as for what you do, it's important for OEMs and maybe some distros that we have a good option for this.

Yes, we are trying to make the server more secure by default. One of the nice things about Oracle is that Oracle really likes secure defaults. Doing that neatly and in a way that's backwards compatible can be very fiddly sometimes, though.

I'm glad you like the community blogging about planned changes. Much of the time I'm the person who requests those posts. Please always take that opportunity to give feedback on them if you can.

Please do keep up the feedback in blog entries bugs or however else makes sense.

James Day, MySQL Senior Principal Support Engineer, Oracle

Giuseppe Maxia said...

James,
Thanks for your updates.

One recent example of a script unfriendly action is in the MySQL 5.7.4 release notes:
Because mysql_install_db deployment now is secure by default, the --random-passwords option is unnecessary and has been removed. It has been replaced by the --skip-random-passwords option.
This means that an automated procedure to handle random passwords for both MySQL 5.6 and 5.7 should check the version and change the behavior depending on the version number. Leaving "--random-passwords" as a DUD, instead of removing it would have made the script easier.

Daniƫl van Eeden said...

With Bug #71600 it's even more difficult to find where the sql_mode is set as a /usr/my.cnf is generated and most folks don't expect a config to be placed there.

Entry 23 Eco of Kristofer said...

Giuseppe, the m_i_db script really isn't fit for using as a stable API to MySQL deployment. It is and has always been a helper tool for the MySQL deployment procedures which you get by reading the manual or executing a package manager with a software package (RPM, DEB etc). If you really want to have API stability here for your custom solutions I think you should consider that bootstrapping MySQL isn't very difficult. You just point at the lc_messages_dir and the datadir and include --bootstrap. Then you pipe the SQL you want into the process. Done. Given that you already written the entire sandbox-suite i don't understand why you continue to rely on some old helper-script for your needs.
The current m_i_db script is somewhat inconvenient even for our needs so I would expect that this is replaced with something more robust in the future. Possibly with more API change though ;-)

Giuseppe Maxia said...

@Kristofer
the only thing I found about --bootstrap in the manual is "This option is used by the mysql_install_db script to create the MySQL privilege tables without having to start a full MySQL server."

MySQL Sandbox uses mysql_install_db because this is the recommended procedure .

Is there another place where the usage of --bootstrap is explained and/or recommended?

Entry 23 Eco of Kristofer said...

You can try something like this:
cat mysql_system_tables_n_users.sql | mysqld --no-defaults --bootstrap --datadir=/MrGs_1eet_data/ --lc-messages-dir=/usr/share/mysql --lc-messages=en_US

It isn't pretty and we can do better, but I think it might be a better choice for you.

Giuseppe Maxia said...

@Kristofer,
I know how to use mysqld --bootstrap (BTW, where does mysql_system_tables_n_users.sql come from? It's not in my 5.7.4 tarball)
What I meant is that I did not find in the documentation any indication that "mysqld --bootstrap" is recommended instead of mysql_install_db.
From what I see, mysql_install_db is still the official way.
Besides, using 'mysqld --bootstrap" would not completely solve my problems. I will need to try the installation for every new version (as I do now) and deal with trivial events like the disappearance of the 'host' table, the appearance of the performance_schema, the sudden noise in the installation output, and so on.

Entry 23 Eco of Kristofer said...
This comment has been removed by the author.