mysql_upgrade should default to --skip-write-binlog | Replication breaks with "You cannot 'ALTER' a log table if logging is enabled" on upgrade, even when logging to files

[Marcus Bointon]

I get this error every time I upgrade MySQL on my servers.

This error is described in various MySQL bug reports dating back several years, for example http://bugs.mysql.com/bug.php?id=56155. That one was fixed 2 years ago, yet is tagged for release in 5.7! They're all marked as fixed, yet I'm still seeing this problem whenever I upgrade my Percona server setup. I've never had this not happen; it last happened when I upgraded to 5.5.27-28 and I expect it to happen again with 5.5.27-29.

I generally work around it by telling slaves to skip that query and carry on, but it does mean that upgrades break replication.

To be clear, I am not logging to tables and never have!

[Marcus Bointon (marcus-synchromedia)]

Hm, and of course on the upgrade to 5.5.27-29, it didn't happen after all...

I guess you can close this one now!

[Laurynas Biveinis (laurynas-biveinis)]

Please re-open if the problem resurfaces, thanks!

[lpad (spameden)]

same error just happened for me while upgrading from 5.1.65-rel14.0-475.squeeze to 5.1.66-rel14.1-495.squeeze

didn't test on 5.5 as of yet

the bug is still here

[Marcus Bointon (marcus-synchromedia)]

Well it's hapened again...

I did this upgrade:

replace percona-server-server 5.5.27-rel29.0-315.lucid (using .../percona-server-server_5.5.28-rel29.1-334.lucid_am
d64.deb)

On the server itself this worked perfectly, however on the slave (it's a master-master mmm config), it stops replication with this error:

Last_SQL_Errno: 1580
Last_SQL_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log MODIFY start_time TIMESTAMP NOT NULL,
   MODIFY user_host MEDIUMTEXT NOT NULL,
   MODIFY query_time TIME NOT NULL,
   MODIFY lock_time TIME NOT NULL,
   MODIFY rows_sent INTEGER NOT NULL,
   MODIFY rows_examined INTEGER NOT NULL,
   MODIFY db VARCHAR(512) NOT NULL,
   MODIFY last_insert_id INTEGER NOT NULL,
   MODIFY insert_id INTEGER NOT NULL,
   MODIFY server_id INTEGER UNSIGNED NOT NULL,
   MODIFY sql_text MEDIUMTEXT NOT NULL'

Logging is not using tables on either server, and never has.

I told the slave to skip that statement and continue, and all worked again.

When I upgraded the other master, I had exactly the same problem in the opposite direction.

[Miguel Angel Nieto (miguelangelnieto)]

I've tested the bug on the following environment (Debian Squeeze):

Master PS 5.5.27 (binlog and slowlog enabled)
Slave PS 5.5.7 (binlog and slowlog enabled)

After upgrading the master to 5.5.28 I get the following error on the slave status:

               Last_SQL_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log
   MODIFY start_time TIMESTAMP NOT NULL,
   MODIFY user_host MEDIUMTEXT NOT NULL,
   MODIFY query_time TIME NOT NULL,
   MODIFY lock_time TIME NOT NULL,
   MODIFY rows_sent INTEGER NOT NULL,
   MODIFY rows_examined INTEGER NOT NULL,
   MODIFY db VARCHAR(512) NOT NULL,
   MODIFY last_insert_id INTEGER NOT NULL,
   MODIFY insert_id INTEGER NOT NULL,
   MODIFY server_id INTEGER UNSIGNED NOT NULL,
   MODIFY sql_text MEDIUMTEXT NOT NULL'

So confirmed, it breaks the replication.

[Miguel Angel Nieto (miguelangelnieto)]

Just tested the same process with 5.1.

Master PS 5.1.65 (binlog and slowlog enabled)
Slave PS 5.1.65 (binlog and slowlog enabled)

After upgrade the master to 5.1.66 I get the same error on the slave server.

Last_SQL_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log
[...]

As Marcus said, the problem also happens on MySQL (56155) but is tagged for release 5.7.0

[Stewart Smith (stewart)]

Setting to high as this breaks upgrading, which isn't that good.

[Marcus Bointon (marcus-synchromedia)]

Happened again on upgrading from 5.5.28-rel29.3-388.lucid to 5.5.29-rel29.4-401.lucid.

[mig5 (mig5)]

This occurred to me upgrading to percona-server-server-5.5 1:5.5.31-rel30.3-520.precise

[Marcus Bointon (marcus-synchromedia)]

Yes, broke again for me too with that upgrade.

[Tim Gokcen (hexetic)]

The issue seems to be that the debian startscript for MariaDB server runs mysql_upgrade *without* specifying --skip-write-binlog. As a result, although the slow-query log and the general log are disabled by mysql_system_tables_fix.sql, these SET statements are not replicated to the slave, so, when the slave tries to run the ALTER TABLE slow_query or ALTER TABLE general_log, it runs up against the log still being enabled.

The correct solution seems to be to modify debian/addtiions/debian-start, changing:
MYUPGRADE="/usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf"
to
MYUPGRADE="/usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf --skip-write-binlog"

[Tim Gokcen (hexetic)]

I'm not sure who is upstream of whom -- Percona server vs MariaDB -- but I've also opened a MariaDB bug for this issue:

https://mariadb.atlassian.net/browse/MDEV-5116

[Timur Bakeyev (timur.bakeyev)]

I also get those on slaves while upgrading percona-server-5.5. Common fix I use to address stopped slave:

SET GLOBAL slow_query_log="OFF"; START SLAVE; SET GLOBAL slow_query_log="ON";

[mig5 (mig5)]

After 4 years of this bug in Percona, 7 years of it being *fixed* in upstream MySQL 5.7, and 3 years of it having been fixed in MariaDB [2]... could we get these Pull Requests pushed through?

https://github.com/percona/percona-server/pull/1006
https://github.com/percona/percona-server/pull/1007

Having slave replication break on every. single. percona. upgrade. starting to get a bit old, or I am :)

[1] http://bugs.mysql.com/bug.php?id=43579
[2] https://mariadb.atlassian.net/browse/MDEV-5116

[Laurynas Biveinis (laurynas-biveinis)]

Note that the 5.7 fix does not prevent mysql_upgrade on master from binlogging its actions. I think all versions 5.5/5.6/5.7 should be fixed the same way MariaDB did: flipping the default of --write-binlog for mysql_upgrade.

[Laurynas Biveinis (laurynas-biveinis)]

5.6 and 5.7 already have the mysql_upgrade default of --skip-write-binlog. Thus only 5.5 remains to be fixed.

[Laurynas Biveinis (laurynas-biveinis)]

    https://github.com/percona/percona-server/pull/1017
    https://github.com/percona/percona-server/pull/1018
    https://github.com/percona/percona-server/pull/1019

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-595