MySQL Bugs: #82765: mysql_upgrade doesn't really upgrade slave_master

Bug #82765	mysql_upgrade doesn't really upgrade slave_master_info
Submitted:	28 Aug 2016 17:30	Modified:	28 Aug 2016 17:42
Reporter:	Miguel Angel Nieto	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S1 (Critical)
Version:	5.7.14	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In 5.7.14 there is this bug:

Replication: After upgrading from MySQL 5.6, MySQL 5.7 slaves were not handling replication channels correctly. This was due to the upgrade procedure inverting the order of two fields of the mysql.slave_master_info table (Channel_Name and Tls_version). The fix avoids this inversion and adds a check to ensure that the Channel_name is correctly used as the primary key. (Bug #23180202)

But after you upgrade to 5.7.14 mysql_upgrade doesn't actually update the schema. This could break the replication after the upgrade is done with the following error message:

2016-08-28T05:58:37.920065Z 0 [ERROR] Info table has a problem with its key field(s). Table 'mysql.slave_master_info' expected field #23 to be 'Channel_name' but found 'Tls_version' instead.
2016-08-28T05:58:37.920086Z 0 [ERROR] Slave: Failed to create a channel from master info table repository.
2016-08-28T05:58:37.920114Z 0 [ERROR] Slave: Could not create channel list
2016-08-28T05:58:37.920126Z 0 [ERROR] Failed to create or recover replication info repositories.

How to repeat:
On Debian, install latest 5.6. Just download the tar bundle and dpkg -i the packages.

Remove the packages leaving the datadir there. Install 5.7.13 following the same procedure. Then mysql_upgrade. It will upgrade slave_master_info but with the incorrect order:

`Tls_version` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Tls version',
`Channel_name` char(64) NOT NULL DEFAULT '' COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication',

Ok, known bug. No problem. Follow the same procedure, remove packages and leave there the datadir. Install 5.7.14 and then run mysql_upgrade. It will update nothing on that table. The order of the columns will remain the same and replication if you have it could just fail.

You have to install a fresh 5.7.14 somewhere else, get the schema of the table and do the changes manually. After that, replication will start to work.

Suggested fix:
Make mysql_upgrade to really upgrade the table.

host and Channel_name columns also need to be updated.

Hello Miguel,

Thank you for the report.
This is most likely duplicate of Bug #82384, please see Bug #82384.

Thanks,
Umesh

The work around does get replication up and running again however on examination of the logs there are further schema issues that need to be addressed. Which are shown in the logs... 

2016-09-21T19:15:58.899683Z 0 [ERROR] Incorrect definition of table performance_schema.replication_connection_status: expected column 'RECEIVED_TRANSACTION_SET' at position 7 to have type longtext, found type text.
2016-09-21T19:15:58.899948Z 0 [ERROR] Incorrect definition of table performance_schema.replication_group_member_stats: expected column 'COUNT_TRANSACTIONS_ROWS_VALIDATING' at position 6, found 'COUNT_TRANSACTIONS_VALIDATING'.
2016-09-21T19:15:58.900711Z 0 [Note] Event Scheduler: Loaded 0 events
2016-09-21T19:15:58.900850Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.14-8-log'  socket: '/mysql/data004/mysql.sock'  port: 3310  Percona Server (GPL), Release 8, Revision 1f84ccd
2016-09-21T19:16:20.315077Z 6 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

Suggest mysql_upgrade needs to consider these tables and also consider taking into account any users that need to have applied the work around to repair replication