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).
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).