MySQL Bugs: #82384: 5.7.14 complains about wrong slave_master

Bug #82384	5.7.14 complains about wrong slave_master_info after upgrade from 5.7.13
Submitted:	29 Jul 2016 21:23	Modified:	14 Nov 2016 18:22
Reporter:	Yves Trudeau (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S1 (Critical)
Version:	5.7.14	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	replication

Description:
The fix for bug BUG#23180202 enforces the column order in mysql.slave_master_info but the mysql_upgrade tool doesn't change the order if it was already wrong.

How to repeat:
In my case:

- install 5.6.21
- upgrade to 5.7.13 and run mysql_upgrade
- upgrade to 5.7.14 and run mysql_upgrade

Suggested fix:
modify mysql_upgrade to change the order of the columns in mysql.slave_master_info if the column order is found to be wrong.

The actual error message:

2016-07-29T20:37:12.041830Z 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.

Hello Trudeau,

Thank you for the report.
Could you please tell us whether you are performing binary in place upgrades or logical upgrades? I tried binary tarball in place upgrade from 5.6.21->5.7.13->5.7.14 but not seeing the message(i.e slave_master_info  but seen some p_s things reported observed but were no longer seen after running mysql_upgrade) even before/after upgrade. Is this the slave instance? Please upload conf file used.

Thanks,
Umesh

pls ignore my previous requested details.
Verified as described.

2016-08-01T12:43:21.630865Z 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-01T12:43:21.637790Z 0 [Note] Event Scheduler: Loaded 0 events
2016-08-01T12:43:21.638005Z 0 [Note] bin/mysqld: ready for connections.
Version: '5.7.14-enterprise-commercial-advanced-log'  socket: '/tmp/mysql_ushastry.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition (Commercial)

Thanks,
Umesh

test results

Attachment: 82384.log (application/octet-stream, text), 57.26 KiB.

Bug #82765 marked as duplicate of this

Manually executing the following statement appears to be a workaround:

ALTER TABLE mysql.slave_master_info MODIFY COLUMN Tls_version TEXT AFTER Channel_name;

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

I've upgraded from 5.7.13 -> 5.7.15 and am now experiencing the same error after having performed a mysql_upgrade.

Replication is now dead on this box.

for me it helps:
mysql_upgrade --upgrade-system-tables
service mysql restart

Posted by developer:
 
Noted in 5.7.17 changelog.

Upgrading from MySQL 5.6 to 5.7.13 and then to 5.7.14 resulted in an
incorrect column order in the mysql.slave_master_info system table.

I just upgraded from Mysql 5.7.11 to mysql 5.7.16 and I'm experiencing the same issue. Our slave is now unconfigured and now we have to recreate the slave instance.

I've upgraded from Mysql 5.7.12 to mysql 5.7.16 and now I'm experiencing the same issue.
Any solution of workaround, please ?

The workaround suggested by Miguel Angel Nieto (see the link below) worked for me vey fine, without any additional errors/warnings:
https://bugs.launchpad.net/percona-server/+bug/1617715