Tuesday, March 10, 2015

MySQL 5.7.6 is out. Be prepared for big changes

Today Oracle released MySQL 5.7.6 milestone 16. With this, MySQL 5.7 has been in development for over 2 years.
Compared to MySQL 5.6, the changes are quite extensive. The main effort of the team has been focused on speed, with performance reportedly improved from 2 to 3 times compared to previous releases.
A full list of what is new would take too much space here, but I would like to mention some key points:

  • Oracle has spent a considerable amount of energy in the improvement of MySQL security and safety. You will see many new features, but even more old features that were deprecated and more that were removed after deprecation in 5.6.
  • The installation process has been changing in every milestone release of MySQL 5.7, always towards the goal of making MySQL more robust. The effort, though, will break compatibility with installation practices used for previous versions.

In this article, I will cover the biggest changes during installation.
In MySQL 5.6, mysql_install_db has an option for the generation of a random password during database creation. The process is unfriendly to scripting but it is a step in the direction of ending the long time practice of creating a root user without a password.
In MySQL 5.7.4, there was a further change, with the random password generation becoming the default, with the possibility of skipping the creation with the option –skip-random-password.
In MySQL 5.7.5, the default was confirmed, but the option was changed to –insecure.

And now, in MySQL 5.7.6, the crackdown on old practices continues: mysql_install_db is deprecated, and replaced with mysqld –initialize (formerly known as “mysqld –bootstrap,” now deprecated.)
Here’s a test run:

$ ~/opt/mysql/5.7.6/bin/mysqld --no-defaults --basedir=~/opt/mysql/5.7.6/ --datadir=$PWD/data --initialize
2015-03-09T05:06:37.159659Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-09T05:06:37.355155Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-03-09T05:06:37.410118Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-03-09T05:06:37.467002Z 0 [Warning] Failed to setup SSL
2015-03-09T05:06:37.467029Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2015-03-09T05:06:37.468142Z 1 [Warning] A temporary password is generated for root@localhost: f<jqhdJ(A5p#

Compared to the previous versions, the most notable difference is that there is no .mysql_secret file, but a simple line on the screen mentioning the temporary password.
But there is one, more important behavioral difference: this command works only once. When using mysql_install_db, you could run the same command even if the data directory existed, where the script would re-apply the data creation commands. Using mysqld –initialize, you can only run on a non-existing data directory.

$ ~/opt/mysql/5.7.6/bin/mysqld --no-defaults --basedir=~/opt/mysql/5.7.6/ --datadir=$PWD/data --initialize
2015-03-09T05:49:12.504413Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-09T05:49:12.505398Z 0 [ERROR] --initialize specified but the data directory exists. Aborting.
2015-03-09T05:49:12.505422Z 0 [ERROR] Aborting

Using the newly created database is a bit more trickier than before:

$ ~/opt/mysql/5.7.6/bin/mysql --no-defaults   -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.6-m16

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password=password('test');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password('test')' at line 1

Uh? What’s this? This command used to work until recently. The reason is a change in the syntax of SET PASSWORD, which now accepts a plain text argument:

mysql> set password='test';
Query OK, 0 rows affected (0.00 sec)

The old syntax was meant to be only deprecated, but it was accidentally completely removed. This will be hopefully fixed in MySQL 5.7.7.

More changes involve the use of GRANT, REVOKE, CREATE USER, and ALTER USER, which now are more strict. You get warnings if you try to create users with the GRANT command, or when mixing granting of privileges with authentication options.

mysql> grant all on test.* to testuser identified by 'test';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1287): Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.

mysql> grant all on *.* to testuser identified by 'test';

Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1287): Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.

In short, if you have automated scripts that deal with installing and administering MySQL, you should test them with warnings enabled, and be prepared for broken compatibility with your old practice.

One such ‘old practice’ scripts that is broken by the new syntax changes is MySQL-Sandbox. I have just released an updated version (MySQL Sandbox 3.0.48) with a workaround for MySQL 5.7.6 changed SET PASSWORD syntax.

BTW, did I mention that the password field in the mysql.user table was removed? That’s another surprise that may break quite a lot of existing tests:

mysql> use mysql
Database changed
mysql> select host,user,password from user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

mysql> select host, user, authentication_string from user;
| host      | user        | authentication_string                     |
| localhost | root        | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| 127.%     | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| localhost | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| localhost | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| 127.%     | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| 127.%     | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| localhost | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| 127.%     | rsandbox    | *B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD |
| %         | testuser    | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
9 rows in set (0.00 sec)


Fadi El-Eter (itoctopus) said...

Hi Giuseppe,

Do you think that the current version of phpMyAdmin will suffer from compatibility issues with 5.7.6?

Also, do you have any references asserting that the performance of 5.7.6 is 2 to 3 times better than the predecessor (usually, these claims are substantiated but only under certain conditions).

Giuseppe Maxia said...

I don't know about phpMyAdmin. If it runs operations that deal with passwords, it may be affected.

I haven't tested the performance. I am sure someone at Percona would do it sooner or later.

Justin Swanhart said...

removing password field does not just break tests/scritps:
"You cannot use mysqldump to dump the user table from a version of MySQL older than 5.7.6 and reload the dump file into MySQL 5.7.6 or later. Instead, perform a binary (in-place) upgrade to MySQL 5.7.6 or later and run mysql_upgrade to migrate the Password column contents to the authentication_string column."

Don said...

"The old syntax was meant to be only deprecated, but it was accidentally completely removed."

How could Oracle have possibly "accidentally completely remove" the old syntax for setting a password even though it "was meant to be only deprecated", without being completely incompetent?

I can not in my wildest imagination come up with a scenario in which a competent developer could possibly "accidentally remove" something like that. If they made such a huge "accident" with that security feature, what other terrible negligent "accidents" lurk just beneath the surface of this new release of MySQL?

How can you "LOL" away such an incompetent "accident" like that?

Giuseppe Maxia said...

"LOL away?" What gave you the impression that I was making fun of it? I am the one who found the bug and reported it, and this is the explanation I got. Since the person I talked to is someone I trust, I don't have reason to think otherwise.
I can elaborate further and tell you why this particular error was likely to happen: it's because since the beginning MySQL was built with root user without password, and most of the tests were running in such conditions. That was the culture of rapid growth that gave MySQL its popularity and the developers are paying now the technical debt that has left so many features with poor testing behind. What Oracle is doing now is trying to redress that situation by strengthening the defaults, for which I commend them. Being a QA developer, I am less pleased with the quality of tests that let such mistake pass, but I know that such things happen, and it does not necessarily mean that there is a can of worms behind this bug.

Don said...

It's not my impression that you're making fun of it, but that you're trying to excuse it as an inconsequential "accident" instead of incompetence. Accidents like that don't happen without causes like incompetence or negligence or malice. Accidents like that are a symptom that something is deeply wrong.

Giuseppe Maxia said...

I am not excusing anything. I am, actually, quite annoyed by this bug. But since there is little I can do except spreading the news, I can only speculate on the cause.
If you want to get deep conclusions from this fact, be my guest.
I am satisfied by having found the issue, having got it fixed (in the next version), and having a workaround in my own project.

Chris said...

can't seem to find the ~/.mysql_secret file (this is a clean install). Anyway to login as root? Or should I just re-install?

Giuseppe Maxia said...

Quoting myself from above:

Compared to the previous versions, the most notable difference is that there is no .mysql_secret file, but a simple line on the screen mentioning the temporary password.

If you missed the output on screen, I don't know how to recover from that.

Chris said...

Hm, it never printed any password to the screen. However I was able to login with skip grant tables. Then ran "update user set authentication_string=password('test') where user='root';". Then "set password='test123';" once logged in.


Ben Krug said...

Hi Giuseppe -

Nice writeup, as always! :) I noticed Joro also posted about some of these bootstrap changes, giving a little background on the reasoning; don't know whether you've already seen it. Just in case you're interested -


Thank you (and thanks for
continuing to maintain Sandbox)

Ben Krug

Giuseppe Maxia said...

Thanks for the link. Joro's post is very enlightening.

Anonymous said...

About phpMyAdmin, work is in progress for the upcoming 4.4.1 version to fully support MySQL 5.7.6.

Anonymous said...


Very interesting article. so can i re-post your article to translate korean ?

It is really useful information, I want to share for korean people.

Thank you.

Giuseppe Maxia said...

Feel free to translate. Please post a link to the translation when done.