2

I recently upgraded from mysql-community 5.7.4 to 5.7.8. All packages show the newer version is correctly installed. I reinitialized my database using mysqld --initialize and corresponding steps. I then restored from a full dump using the standard method (ie, zcat dumpfile.gz | mysql). The server started up fine; I was able to start slave and catch up to the master, still running on 5.7.4. Now the weirdness begins. The user tables don't match. Even if I stop slave and restart my mysqld process, the following (and similar) commands do something strange:

create user 'test'@'%' identified by 'test334455' ;

It puts the hash into the "plugin" field of the user table:

mysql> select user,host,password,plugin,authentication_string from user where user='test';
+------+------+----------+-------------------------------------------+-----------------------+
| user | host | password | plugin                                    | authentication_string |
+------+------+----------+-------------------------------------------+-----------------------+
| test | %    |          | *443C443E4789A416587C0A5892C7D345B73B9B3B | NULL                  |
+------+------+----------+-------------------------------------------+-----------------------+

To make sure this isn't some weird package-specific bug, I initialized a new database and with a separate mysqld process, verifies the command works as expected. Somehow restoring from a dump or connecting as a slave to an older version causes the problem.

However, existing user entries (ones restored from the dump) are correct and work just fine.

UPDATE: It appears the password field was removed between 5.7.5 and 5.7.6, but the restore-from-dump (not replication!) recreated the column; but the current version's create user commands still assume a particular order to the columns (** facepalm **).

So the Question now is: How do I safely update the master database on 5.7.4 to 5.7.8, preferably without dump/restore?

UPDATE: The Mysql documentation does indeed cover this particular quirk, more or less, on a page well-worth bookmarking (Because really, MySQL, you have no shame in making incompatible changes to sub-minor versions.) https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

  • Which databases are you replicating? If you replicate the mysql you'll probably run into problems. – dwjv Oct 8 '15 at 10:45
  • Updated question: it wasn't the replication. It was restoring from dump without doing the mysql_upgrade. – Otheus Oct 8 '15 at 11:33
  • 1
    Wow, some bad coding from MySQL there then. Column names should be explicit. – dwjv Oct 8 '15 at 11:43
  • 1
    File a bug at bugs.mysql.com – Rick James Oct 23 '15 at 1:46
1

mysql_upgrade is your friend

mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

  • This might be the answer I needed. testing – Otheus Oct 8 '15 at 11:31
  • Hello @Otheus, were you able to use mysql_upgrade? – Craig Efrein Oct 12 '15 at 9:27
  • For operational purposes, I had to back out of the upgrade completely, revert to the prior version of MySQL, and do a full rsync from the master. I get to try again soon. Upvoting for now. :) – Otheus Oct 13 '15 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.