Bug #71529 GTID automatic positioning skips anonymous transactions.
Submitted: 30 Jan 2014 15:25 Modified: 6 Feb 2014 11:46
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.6.15 & 5.7.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: GTID

[30 Jan 2014 15:25] Jean-François Gagné
Description:
When a slave connects with its master with MASTER_AUTO_POSITION=1 and that the last transaction executed on the slave is followed by anonymous transactions in the master's binary logs, the anonymous transactions do not reach the slave and are skipped silently.

How to repeat:
With the architecture bellow (A is the master of B):

 -----      -----
 | A |----->| B |
 -----      -----

a. Have A and B in GTID_MODE=ON,
b. Make sure B is in sync with A,
c. On A, execute the following statements:

CREATE DATABASE test_gtid;
CREATE TABLE test_gtid.t1 (username varchar(50));
INSERT INTO test_gtid.t1 VALUES ("user_1");

d. Wait for B to be in sync with A,
e. On B: STOP SLAVE;CHANGE MASTER TO MASTER_AUTO_POSITION=1;,
f. Restart mysqld on A with GTID_MODE=OFF,
g. On A, execute the following statement:

INSERT INTO test_gtid.t1 VALUES ("user_2");

i. Restart mysqld on A with GTID_MODE=ON,
j. On A, execute the following statement:

INSERT INTO test_gtid.t1 VALUES ("user_3");

k. On B: START SLAVE;.

When B catches up with A, user_1 and 3 exist on B but not user_2 (SELECT * from test_gtid.t1;).

Note that if we run a GTID transaction before restarting A in GTID_MODE=OFF (between e and f above), automatic positioning will fail on the slave (step k) and we will need to revert to traditional file/position positioning to start the slave (STOP SLAVE;CHANGE MASTER TO MASTER_AUTO_POSITION=0;START SLAVE;).  Once started, the slave runs the GTID transaction and fails on the anonymous transaction.  If we restart the slave without setting  MASTER_AUTO_POSITION to 1, the anonymous transaction will be assigned a local GTID (BUG #71527: http://bugs.mysql.com/bug.php?id=71527).
[30 Jan 2014 17:38] Sveta Smirnova
Thank you for the report.

At http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html our manual explicitly says that both master and slave must be in read-only state before you change gtid-mode to ON. Mixing ON and OFF settings of gtid mode while updates are running is not supported.
[6 Feb 2014 11:46] Sven Sandberg
Reopening - it would make sense to have an error message in this case.