Bug #72680 Make changing MASTER_AUTO_POSITION = 0 user-friendly and document behaviour
Submitted: 19 May 2014 12:39 Modified: 20 May 2014 8:36
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: gtid_mode, master_auto_position, replication

[19 May 2014 12:39] Simon Mudd
When testing GTID you may want to disable it (gtid_mode = OFF) for example to compare performance metrics and see if the i/o load is lower on a slave simply because right now you avoid writing to binlogs and the overhead associated with that.

Several things are needed in 5.6 to disable GTID behaviour but one thing is to change MASTER_AUTO_POSITION to 0.

The documentation at http://dev.mysql.com/doc/refman/5.6/en/change-master-to.html gives a few examples with setting the value to 1 and also tells you that you can not also set MASTER_LOG_FILE and MASTER_LOG_POS if you do this which makes sense.

However, there is no description of the correct / best way to go the other way (changing MASTER_AUTO_POSITION = 0) and if replication is working in with gtid_mode = ON then ideally it should not be necessary to configure the binlog file and position values explicitly as they are already known to the server.

It also seems that in gtid_mode a CHANGE MASTER clears the relay logs and they are resynced to the slave from the master which is fine if there is no replication delay between master and slave but may be fatal if the slave is running behind the master (as was the situation on a server I was working on) and not all the required binlogs are not available on the master.

How to repeat:
Look at the documentation and see how to use CHANGE MASTER TO ... with the MASTER_AUTO_POSITION = 0 setting. There's no documentation on this specific use case.

Suggested fix:
I think that it would be good, if possible that:
(1) STOP SLAVE; CHANGE MASTER TO MASTER_AUTO_POSITION = 0; START SLAVE would change behaviour and not _require_ the DBA to have to configure the appropriate binlog file and positions.
(2) under these circumstances the existing relay logs SHOULD NOT be removed (there's no need and with a replication delay removing them may break replication if the slave is a long way behind)
(3) the documentation should be more explicit about how this setting should be used.  The case (1) above is an ideal situation and I can imagine that it may be necessary to provide binlog and position parameters under some circumstances but in preparation for switch OFF gtid_mode the behaviour outlined above would be most helpful and avoid any errors at a critical moment when you are changing server configuration settings.
(4) the whole documentation on exactly how the master and slaves negotiate binlog / GTID positions is rather vague and while it normally works fine if it breaks it can leave you with a lot of pieces to pick up which is not ideal.  So improving this aspect of the documentation would also be useful.
[20 May 2014 8:36] MySQL Verification Team
Hello Simon,

Thank you for the report/feature request.