MySQL Bugs: #70167: Impossible to disable MASTER_AUTO_POSITION with gtid

Bug #70167	Impossible to disable MASTER_AUTO_POSITION with gtid_mode=OFF
Submitted:	27 Aug 2013 19:06	Modified:	23 Sep 2013 10:36
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GTID

Description:
If a slave has been previously configured to use GTID replication with MASTER_AUTO_POSITION, and that slave is restarted with gtid_mode=OFF, it is not possible to get replication working again on the instance without re-enabling GTID mode.

Crucially, it is not possible to do CHANGE MASTER TO MASTER_AUTO_POSITION=0 if the instance is running with gtid_mode=OFF.

How to repeat:
mysqld_safe --server-id=1 --log-bin=mysql-bin --log-slave-updates --enforce-gtid-consistency=on --gtid-mode=on &

mysql 5.6.13-log (root) [test]> change master to master_host='test', master_port=1234, master_user='root', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysqladmin shutdown
mysqld_safe --server-id=1 --log-bin=mysql-bin --log-slave-updates &

mysql 5.6.13-log (root) [test]> start slave;
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when @@GLOBAL.GTID_MODE = ON.
mysql 5.6.13-log (root) [test]> change master to master_host='test', master_port=1234, master_user='root', master_log_file='first', master_log_pos=4;
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when @@GLOBAL.GTID_MODE = ON.
mysql 5.6.13-log (root) [test]> change master to master_auto_position=0;
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when @@GLOBAL.GTID_MODE = ON.

Suggested fix:
It should be possible to set MASTER_AUTO_POSITION=0 even if the server is running with gtid_mode=OFF>

Hello Kolbe,

Thank you for the bug report.
Verified as described.

Regards,
Umesh

[20 Sep 2013 6:37] Jon Stephens

Documented fix in the 5.6.15 and 5.7.3 changelogs, as follows:

        Trying to set CHANGE MASTER TO ... MASTER_AUTO_POSITION = 0
        failed with error 1777 (ER_AUTO_POSITION_REQUIRES_GTID_MODE_ON).

Closed.