| Bug #71527 | With GTID_MODE=ON, anonymous transactions are assigned local GTIDs on slaves. | ||
|---|---|---|---|
| Submitted: | 30 Jan 2014 15:19 | Modified: | 6 Feb 2014 11:51 |
| Reporter: | Jean-François Gagné | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.6.15 & 5.7.3 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | GTID | ||
[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:51]
Sven Sandberg
It's good to have an error message in this case rather than corrupting the GTID state. This has been fixed and will appear in 5.7.4. Here is the changelog entry:
Documented fix as follows in the 5.7.4 changelog:
When the master and the slave both had gtid_mode=OFF initially,
and the slave SQL thread was stopped while there remained
anonymous transactions in the relay log, if the slave was then
restarted with gtid_mode=ON, then the slave assigned GTIDs such
transactions. This could cause problems when the slave was later
promoted to a master, as the transactions would be played again
on the promoted master's slaves, leading quickly to
inconsistencies on those slaves.
Closed.
Description: When a slave is run with GTID_MODE=ON, if/when the SQL thread executes an anonymous transaction, GTID with the slave UUID will be assigned to the transaction (an anonymous transaction will be assigned different GTID on different slaves). If this happen on slaves B and C bellow, changing the master of C to B will either replay the transaction (if the transaction is still in B's binary logs) or fail (if B purged the transaction from its binary log). ----- ----- | A |--+--->| B | ----- | ----- \ \ ----- \->| C | ----- How to repeat: I identified 3 ways to execute anonymous transactions on a GTID_MODE=ON slave. The 1st and 2nd ways involve error in migration to GTID_MODE=ON and the 3rd one involves restarting the master in the wrong GTID mode. The 3 will use the master/slave architecture bellow (A is the master of B). ----- ----- | A |----->| B | ----- ----- 1. Anonymous transaction in the master binary logs not sent to slave before migrating to GTID_MODE=ON: a. Have A and B in GTID_MODE=OFF, b. Make sure B is in sync with A, c. On B: STOP SLAVE;, d. On A: execute a transaction, e. Restart mysqld on A and B with GTID_MODE=ON. 2. Unexecuted anonymous transaction in a slave binary logs before migrating to GTID_MODE=ON: a. Have A and B in GTID_MODE=OFF, b. Make sure B is in sync with A, c. On B: STOP SLAVE SQL_THREAD;, d. On A: execute a transaction, e. Wait for the transaction to reach the relay logs of B, f. Restart mysqld on A and B with GTID_MODE=ON. 3. Restart a master in GTID_MODE=OFF and then in GTID_MODE=ON: a. Have A and B in GTID_MODE=ON, b. Make sure B is in sync with A, c. On B: STOP SLAVE;CHANGE MASTER TO MASTER_AUTO_POSITION=0;, d. Restart mysqld on A with GTID_MODE=OFF, e. On A: execute a transaction, f. Restart mysqld on A with GTID_MODE=ON, g. On B: START SLAVE;.