Slave replication fails on master upgrade 'InnoDB does not support system tables'

[mig5]

Hi,

Upgrading servers from 5.5.48-rel37.8-1.wheezy to 5.5.49-rel37.9-1 on Debian Wheezy.

2-node master-master setup (slaves of each other).

Upgraded secondary master. Replication broke on the primary master as per known bug, which is fine: https://bugs.launchpad.net/percona-server/+bug/1065841

Fixed and then upgraded primary master. This time replication broke on the secondary master, with a new error I've not seen before (didn't happen when upgrading the secondary master):

160708 0:43:49 [ERROR] Slave SQL: Error 'Storage engine 'InnoDB' does not support system tables. [mysql.servers]' on query. Default database: 'mysql'. Query: 'CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table'', Error_code: 1726

160708 0:43:49 [Warning] Slave: Storage engine 'InnoDB' does not support system tables. [mysql.servers] Error_code: 1726

160708 0:43:49 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.001200' position 7180

I had to skip this statement, and *then* the usual one about 'You cannot 'ALTER' a log table if logging is enabled'

Why would this have affected only the second master?

[mig5 (mig5)]

I also confirm that the 'system' table exists on both servers, storage engine MyISAM.

[mig5 (mig5)]

This is in mysqlbinlog:

CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table'

Perhaps this should have engine=MyISAM, as it does for the 'func' and 'user' tables? But it's still not clear to me why it didn't happen on the other master when replicating the binlogs from its master.

[mig5 (mig5)]

Happened again today, on upgrade to 5.5.50-38.0, on a *different* pair of multi-master servers running Debian Wheezy. The secondary master was patched first, the primary master's slave replication broke because of the above arriving to the replication logs.

Seems new as of 5.5.49-rel37.9-1 - we have been running these clusters for several years now, and patching them in exactly the same process. First time this error has occurred.

[Roel Van de Paar (roel11)]

https://groups.google.com/forum/#!topic/percona-discussion/2eZtWaqTMPM

[mig5 (mig5)]

I made a pull request which might 'fix' the issue (the 'system' table is the only one in the script that doesn't explicitly set engine=MyISAM)

https://github.com/percona/percona-server/pull/998

[Laurynas Biveinis (laurynas-biveinis)]

    https://github.com/percona/percona-server/pull/1014
    https://github.com/percona/percona-server/pull/1015
    https://github.com/percona/percona-server/pull/1016

[Shahriyar Rzayev (rzayev-sehriyar)]

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