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
owners.

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)