Description:
If 'xa commit' is blocked by FTWRL, after lock timeout, the transaction branch is in inconsistent states within the master node, and in slave nodes:
1. the prepared transaction branch is rolled back in innodb
2. 'xa recover' no longer lists this prepared transaction branch on master.
3. in binlog it's not binlogged as 'aborted', so still in 'prepared' state, and this prepared transaction is replicated to slaves and becomes prepared on slaves.
4. A slave node replicating the binlog has a prepared transaction branch and it can be listed by 'xa recover'. and you can do 'xa commit' to commit the transaction branch on the slave, so that the slave and the master have inconsistent data.
How to repeat:
On master:
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.03 sec)
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> xa start '123';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> xa end '123';
Query OK, 0 rows affected (0.00 sec)
mysql> xa prepare '123';
Query OK, 0 rows affected (0.00 sec)
-- Here start another mysql session to connect to the same DB instance, and run -- 'flush table with read lock'.
mysql> xa commit '123';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> xa recover;
Empty set (0.00 sec)
mysql> show binary logs;
+---------------------------+-----------+
| Log_name | File_size |
+---------------------------+-----------+
| mysql-instance-dzw.000001 | 1351 |
+---------------------------+-----------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-instance-dzw.000001';
-- some omitted rows ...
| mysql-instance-dzw.000001 | 398 | Gtid | 19 | 463 | SET @@SESSION.GTID_NEXT= '7c792bd1-d95d-11e6-8a5e-6d4619a90727:2' |
| mysql-instance-dzw.000001 | 463 | Query | 19 | 557 | create database test |
| mysql-instance-dzw.000001 | 557 | Gtid | 19 | 622 | SET @@SESSION.GTID_NEXT= '7c792bd1-d95d-11e6-8a5e-6d4619a90727:3' |
| mysql-instance-dzw.000001 | 622 | Query | 19 | 719 | use `test`; create table t1 (a int) |
| mysql-instance-dzw.000001 | 719 | Gtid | 19 | 784 | SET @@SESSION.GTID_NEXT= '7c792bd1-d95d-11e6-8a5e-6d4619a90727:4' |
| mysql-instance-dzw.000001 | 784 | Query | 19 | 875 | XA START X'313233',X'',1 |
| mysql-instance-dzw.000001 | 875 | Table_map | 19 | 920 | table_id: 108 (test.t1) |
| mysql-instance-dzw.000001 | 920 | Write_rows | 19 | 965 | table_id: 108 flags: STMT_END_F |
| mysql-instance-dzw.000001 | 965 | Query | 19 | 1054 | XA END X'313233',X'',1 |
| mysql-instance-dzw.000001 | 1054 | XA_prepare | 19 | 1093 | XA PREPARE X'313233',X'',1
On slave:
mysql> select*from t1;
Empty set (0.00 sec)
mysql> xa recover;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
| 1 | 3 | 0 | 123 |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)
mysql> xa commit '123';
Query OK, 0 rows affected (0.01 sec)
mysql> select*from t1;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)