Bug #1870 CHANGE MASTER makes SQL thread restart from coordinates of I/O thread
Submitted: 18 Nov 2003 6:36 Modified: 18 Nov 2003 8:54
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.0 OS:Any (all)
Assigned to: Guilhem Bichot CPU Architecture:Any

[18 Nov 2003 6:36] Guilhem Bichot 
Description:
CHANGE MASTER, as said in the manual, deletes the relay logs, keeps the coordinates of the I/O thread unchanged. So when later replication restarts, it will fetch the master's binlog from the coordinates of the I/O thread.
Thus it will have skipped all events between 'the coordinates of the SQL thread just before CHANGE MASTER was issued' and 'the coordinates of the I/O thread just before CHANGE MASTER was issued'.
This behaviour is documented in the manual but has been classified as a bug so should be fixed.

How to repeat:
Set up replication, with intense load so that the SQL thread is late compared to the I/O thread (i.e. there are events from the master's binary log, which have been written to the slave's relay log by the I/O thread but not executed by the SQL thread yet).
Then on slave do 
STOP SLAVE;
CHANGE MASTER TO MASTER_CONNECT_RETRY=10; -- an apparently harmless change
START SLAVE;
then you will notice that the slave permanently lacks some table changes from the master, so replication is broken.
[18 Nov 2003 8:54] Guilhem Bichot 
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

ChangeSet@1.1610.1.2, 2003-11-18 17:31:17+01:00, guilhem@mysql.com
will be integrated into 4.0.17 and 4.1.1.
CHANGE MASTER will use coordinates of the *SQL* thread.