Description:
When the 'mysql' system database is resynced on servers where the "master-info-repository" value set to TABLE, the resync causes the mysql.slave_master_info table to get copied from the master to the slave. If there is any information in that table from the master (i.e. if a row exists in it), that row continues to be persistent on the slave. If a "change master statement" is issued on the slave, instead of that single row being updated, a NEW ADDITIONAL row is inserted into the table, causing 2 rows to now exist in the table. The 2nd (new) row is active in memory and is used for the replication, but once mysqld is restarted, the FIRST row in the table becomes active, thus breaking replication.
How to repeat:
1. Set up 2 servers with the "master-info-repository" value set to TABLE.
2. On the master server, issue this command:
change master to master_host='IDoNotExist', master_port=3306, master_user='blah', master_password='blah', master_log_file='IDoNotExist', master_log_pos=1;
3. Perform a full dump of the databases on the master, using the "master-data=2" switch.
4. On the slave server, reset the slave (reset slave;).
5. Restore the mysql dump file onto the slave.
6. On the slave, issue the correct change master statement to get the server in sync with the master (using the info from the head of the dump file).
7. Replication will now be running and in sync, but there will be TWO rows in the mysql.slave_master_info table.
8. Restart the mysqld process on the slave.
9. Replication will now be broken because the master_host will be set to 'IDoNotExist' on the slave.
Additional note: Although the mysql.slave_master_info table on master servers is normally empty, it oftentimes can be populated where master/master replication is used, or when chained replication is in effect.
