Monday, August 03, 2015

MySQL 5.7.8 : features, bugs and rumors

I’ve had a look at a preview release of MySQL 5.7.8, some time before it became available to the general public (perks and duties of an Oracle ACE) and I found a few interesting things among the release notes and the tarball itself:

  • There is a new tool named mysqlpump, which is intended as a replacement for mysqldump, with parallel processing, compression, progress watch, the long awaited ability of excluding databases or tables, and more.
  • The json functionality has been fished out from the labs and added to the main distribution.

I was initially pleased with this preview when it installed without errors in MySQL Sandbox, but my pleasure didn’t last long, as I soon stumbled upon a bug that affects replication. Until MySQL 5.7.7, a user with just REPLICATION SLAVE privileges can activate replication. In MySQL 5.7.8, though, replication fails with a baffling error:

slave1 [localhost] {msandbox} ((none)) > show slave status\G
            Last_IO_Errno: 1142
            Last_IO_Error: The slave I/O thread stops because a fatal error is encountered when it try to get the value of SERVER_ID variable from master. Error: SELECT command denied to user 'rsandbox'@'localhost' for table 'global_variables'

This error is baffling for three reasons:

  1. It has never been an issue for a user with this privilege to connect to the master;
  2. the user rsandbox can actually see information_schema.global_variables, which I originally thought was the table been denied;
  3. the above table is empty, because – see below – it was replaced by an homonymous table in performance_schema.

While I don’t understand how this bug could have been missed in the test suite, I believe the fix should not be too hard, and I hope the bug will be closed in the next release.
For the inquisitive readers, the reason for the bug is a changed default value for a compatibility variable. As of MySQL 5.7.6, system and status variables are available through performance_schema tables. There is a variable that controls the compatibility with previous behavior: show_compatibility_56. Up until MySQL 5.7.7, this switch was ON. In the latest version, the switch is OFF. This means that users that don’t have SELECT access to the global_variables table can’t run ‘SHOW VARIABLES …’ commands. This seems logical, but the restriction clashes with the basic requirements for replication, where the only privilege needed for the IO thread user is REPLICATION SLAVE.

So, something has to give. Either we change the requirements or we soften the restrictions. The current workaround, keeping show_compatibility_56=NO in the master would make the adoption of performance_schema features more difficult. Perhaps the best solution would be to change the IO thread user behavior, which should run ‘SELECT @@SERVER_ID’ instead of ‘SHOW VARIABLES LIKE “SERVER_ID”’. This is feasible, assuming that ‘SELECT @@VARIABLE_NAME’ will not be tied to the performance table as well.
And thinking about this workaround, the restriction on SHOW VARIABLES makes little sense, as it can be circumvented by running ‘SELECT @@VARIABLE_NAME’, thus making the restriction itself quite ineffective.

If you are planning to give MySQL 5.7 a try, be aware that there are lots of deprecated features, and at least one that should break lots of existing procedures but is not in the official list, as it is not, strictly speaking, a deprecation. The "password" column in mysql.user is gone. Instead, you will find an "authentication_string" column. Good luck!

And now come the rumors: I was reading the comments on the bug progress through the verification process, and I noticed that the bug has been confirmed for at least two new versions: MySQL 5.7.9 (no surprises here, as this is already mentioned in the release notes page) and MySQL 5.8.0-m17-enterprise-commercial-advanced-log of which, until this bug, we did not know anything about.
The latest milestone release, before the RC, was m16, so 5.8.0 seems to be the next incarnation right after 5.7 will be GA (it should be 5.7.9 or 5.7.10.) But this is only a rumor, so the next version could just as well be called MySQL 12g, to align it with its older stepbrother. (And this is really just a wild rumor!)

In some related news, MySQL Sandbox was updated with a workaround for the bug mentioned above. Moreover, it uses a different way of installing MySQL: instead of the now deprecated mysql_install_db, it uses mysqld --initialize.
If you want to use MySQL 5.7.8, please download the latest version (MySQL::Sandbox 3.0.62.)


Shlomi Noach said...

Another word of caution: with 5.7 you should "SELECT @@global.server_id" instead of "SELECT @@server_id". Though right now I'm unable to find the doc for that, I'll do so once at the office.

Shlomi Noach said...

Nope; I'm wrong on the above. For SET operations you must include the @@general directive, but for SELECT this works with or without the @@general directive.

However I'm banging my head on various other issues with INFORMATION_SCHEMA->PERFORMANCE_SCHEMA variables migration; I'll write a post on my thoughts.

Shlomi Noach said...

Please read my further thoughts in