diff options
Diffstat (limited to 'mysql-test')
49 files changed, 7567 insertions, 20 deletions
diff --git a/mysql-test/include/kill_and_restart_mysqld.inc b/mysql-test/include/kill_and_restart_mysqld.inc new file mode 100644 index 00000000000..b67fb7350b4 --- /dev/null +++ b/mysql-test/include/kill_and_restart_mysqld.inc @@ -0,0 +1,15 @@ +if (!$restart_parameters) +{ + let $restart_parameters = restart; +} + +--let $_server_id= `SELECT @@server_id` +--let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect + +--echo # Kill and $restart_parameters +--exec echo "$restart_parameters" > $_expect_file_name +--shutdown_server 0 +--source include/wait_until_disconnected.inc +--enable_reconnect +--source include/wait_until_connected_again.inc +--disable_reconnect diff --git a/mysql-test/main/flush_read_lock.result b/mysql-test/main/flush_read_lock.result index 0f8c2ce9fb9..be710050139 100644 --- a/mysql-test/main/flush_read_lock.result +++ b/mysql-test/main/flush_read_lock.result @@ -1310,6 +1310,8 @@ unlock tables; # Check that XA non-COMMIT statements are not and COMMIT is # blocked by active FTWRL in another connection # +# XA COMMIT, XA ROLLBACK and XA PREPARE does take COMMIT lock to ensure +# that nothing is written to bin log and redo log under FTWRL mode. connection con1; flush tables with read lock; connection default; @@ -1322,11 +1324,25 @@ connection con1; flush tables with read lock; connection default; xa end 'test1'; -xa prepare 'test1'; +xa prepare 'test1';; +connection con1; +unlock tables; +# Switching to connection 'default'. +connection default; +# Reap XA PREPARE. +# Switching to connection 'con1'. +connection con1; +flush tables with read lock; +# Switching to connection 'default'. +connection default; +# Send XA ROLLBACK 'test1' xa rollback 'test1'; +# Switching to connection 'con1'. connection con1; +# Wait until XA ROLLBACK is blocked. unlock tables; connection default; +# Reap XA ROLLBACK xa start 'test1'; insert into t3_trans values (1); connection con1; @@ -1334,7 +1350,20 @@ flush tables with read lock; connection default; connection default; xa end 'test1'; +# Send XA PREPARE 'test1' xa prepare 'test1'; +# Switching to connection 'con1'. +connection con1; +# Wait until XA PREPARE is blocked. +unlock tables; +# Switching to connection 'default'. +connection default; +# Reap XA PREPARE. +# Switching to connection 'con1'. +connection con1; +flush tables with read lock; +# Switching to connection 'default'. +connection default; # Send: xa commit 'test1';; connection con1; @@ -1344,6 +1373,51 @@ connection default; # Reap XA COMMIT. delete from t3_trans; # +# Check that XA COMMIT / ROLLBACK for prepared transaction from a +# disconnected session is blocked by active FTWRL in another connection. +# +# Create temporary connection for XA transaction. +connect con_tmp,localhost,root,,; +xa start 'test1'; +insert into t3_trans values (1); +xa end 'test1'; +xa prepare 'test1'; +# Disconnect temporary connection +disconnect con_tmp; +# Create temporary connection for XA transaction. +connect con_tmp,localhost,root,,; +xa start 'test2'; +insert into t3_trans values (2); +xa end 'test2'; +xa prepare 'test2'; +# Disconnect temporary connection +disconnect con_tmp; +# Switching to connection 'con1'. +connection con1; +flush tables with read lock; +# Switching to connection 'default'. +connection default; +# Send XA ROLLBACK 'test1' +xa rollback 'test1'; +# Switching to connection 'con1'. +connection con1; +# Wait until XA ROLLBACK is blocked. +unlock tables; +flush tables with read lock; +# Switching to connection 'default'. +connection default; +# Reap XA ROLLBACK +# Send XA COMMIT +xa commit 'test2';; +# Switching to connection 'con1'. +connection con1; +# Wait until XA COMMIT is blocked. +unlock tables; +# Switching to connection 'default'. +connection default; +# Reap XA COMMIT. +delete from t3_trans; +# # Check that XA COMMIT blocks FTWRL in another connection. xa start 'test1'; insert into t3_trans values (1); diff --git a/mysql-test/main/flush_read_lock.test b/mysql-test/main/flush_read_lock.test index 80512deac4e..4283358770c 100644 --- a/mysql-test/main/flush_read_lock.test +++ b/mysql-test/main/flush_read_lock.test @@ -1592,6 +1592,8 @@ unlock tables; --echo # Check that XA non-COMMIT statements are not and COMMIT is --echo # blocked by active FTWRL in another connection --echo # +--echo # XA COMMIT, XA ROLLBACK and XA PREPARE does take COMMIT lock to ensure +--echo # that nothing is written to bin log and redo log under FTWRL mode. connection $con_aux1; flush tables with read lock; connection default; @@ -1604,11 +1606,37 @@ connection $con_aux1; flush tables with read lock; connection default; xa end 'test1'; -xa prepare 'test1'; -xa rollback 'test1'; +--send xa prepare 'test1'; connection $con_aux1; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "xa prepare 'test1'"; +--source include/wait_condition.inc unlock tables; +--echo # Switching to connection 'default'. +connection default; +--echo # Reap XA PREPARE. +--reap +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +flush tables with read lock; +--echo # Switching to connection 'default'. connection default; +--echo # Send XA ROLLBACK 'test1' +--send xa rollback 'test1' +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +--echo # Wait until XA ROLLBACK is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "xa rollback 'test1'"; +--source include/wait_condition.inc +unlock tables; +connection default; +--echo # Reap XA ROLLBACK +--reap xa start 'test1'; insert into t3_trans values (1); connection $con_aux1; @@ -1616,7 +1644,27 @@ flush tables with read lock; connection default; connection default; xa end 'test1'; -xa prepare 'test1'; +--echo # Send XA PREPARE 'test1' +--send xa prepare 'test1' +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +--echo # Wait until XA PREPARE is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "xa prepare 'test1'"; +--source include/wait_condition.inc +unlock tables; +--echo # Switching to connection 'default'. +connection default; +--echo # Reap XA PREPARE. +--reap +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +flush tables with read lock; +--echo # Switching to connection 'default'. +connection default; + --echo # Send: --send xa commit 'test1'; connection $con_aux1; @@ -1632,6 +1680,64 @@ connection default; --reap delete from t3_trans; --echo # +--echo # Check that XA COMMIT / ROLLBACK for prepared transaction from a +--echo # disconnected session is blocked by active FTWRL in another connection. +--echo # +--echo # Create temporary connection for XA transaction. +connect (con_tmp,localhost,root,,); +xa start 'test1'; +insert into t3_trans values (1); +xa end 'test1'; +xa prepare 'test1'; +--echo # Disconnect temporary connection +disconnect con_tmp; +--echo # Create temporary connection for XA transaction. +connect (con_tmp,localhost,root,,); +xa start 'test2'; +insert into t3_trans values (2); +xa end 'test2'; +xa prepare 'test2'; +--echo # Disconnect temporary connection +disconnect con_tmp; +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +flush tables with read lock; +--echo # Switching to connection 'default'. +connection default; +--echo # Send XA ROLLBACK 'test1' +--send xa rollback 'test1' +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +--echo # Wait until XA ROLLBACK is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "xa rollback 'test1'"; +--source include/wait_condition.inc +unlock tables; +flush tables with read lock; +--echo # Switching to connection 'default'. +connection default; +--echo # Reap XA ROLLBACK +--reap +--echo # Send XA COMMIT +--send xa commit 'test2'; +--echo # Switching to connection '$con_aux1'. +connection $con_aux1; +--echo # Wait until XA COMMIT is blocked. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" and + info = "xa commit 'test2'"; +--source include/wait_condition.inc +unlock tables; +--echo # Switching to connection 'default'. +connection default; +--echo # Reap XA COMMIT. +--reap +delete from t3_trans; + +--echo # --echo # Check that XA COMMIT blocks FTWRL in another connection. xa start 'test1'; insert into t3_trans values (1); diff --git a/mysql-test/main/xa.result b/mysql-test/main/xa.result index 5e03c8f75dc..e2a103a7a77 100644 --- a/mysql-test/main/xa.result +++ b/mysql-test/main/xa.result @@ -59,6 +59,9 @@ select * from t1; a 20 disconnect con1; +xa rollback 'testb',0x2030405060,11; +xa recover; +formatID gtrid_length bqual_length data connection default; xa start 'tr1'; insert t1 values (40); @@ -400,3 +403,61 @@ XA ROLLBACK 'xid1'; # XA START 'gtrid', 'bqual', 0x80000000; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0x80000000' at line 1 +# MDEV-7974 related +# Check XA state when lock_wait_timeout happens +# More tests added to flush_read_lock.test +connect con_tmp,localhost,root,,; +set session lock_wait_timeout=1; +create table asd (a int) engine=innodb; +xa start 'test1'; +insert into asd values(1); +xa end 'test1'; +connection default; +flush table with read lock; +connection con_tmp; +# PREPARE error will do auto rollback. +xa prepare 'test1'; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +show errors; +Level Code Message +Error 1205 Lock wait timeout exceeded; try restarting transaction +Error 1402 XA_RBROLLBACK: Transaction branch was rolled back +connection default; +unlock tables; +connection con_tmp; +xa start 'test1'; +insert into asd values(1); +xa end 'test1'; +xa prepare 'test1'; +connection default; +flush tables with read lock; +connection con_tmp; +# LOCK error during ROLLBACK will not alter transaction state. +xa rollback 'test1'; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +show errors; +Level Code Message +Error 1205 Lock wait timeout exceeded; try restarting transaction +Error 1401 XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency +xa recover; +formatID gtrid_length bqual_length data +1 5 0 test1 +# LOCK error during COMMIT will not alter transaction state. +xa commit 'test1'; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +show errors; +Level Code Message +Error 1205 Lock wait timeout exceeded; try restarting transaction +Error 1401 XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency +xa recover; +formatID gtrid_length bqual_length data +1 5 0 test1 +connection default; +unlock tables; +connection con_tmp; +xa rollback 'test1'; +xa recover; +formatID gtrid_length bqual_length data +drop table asd; +disconnect con_tmp; +connection default; diff --git a/mysql-test/main/xa.test b/mysql-test/main/xa.test index e23e3c6a428..7aaa72b1645 100644 --- a/mysql-test/main/xa.test +++ b/mysql-test/main/xa.test @@ -82,6 +82,8 @@ xa start 'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz'; select * from t1; disconnect con1; --source include/wait_until_count_sessions.inc +xa rollback 'testb',0x2030405060,11; +xa recover; connection default; @@ -541,3 +543,50 @@ XA ROLLBACK 'xid1'; XA START 'gtrid', 'bqual', 0x80000000; --source include/wait_until_count_sessions.inc + +--echo # MDEV-7974 related +--echo # Check XA state when lock_wait_timeout happens +--echo # More tests added to flush_read_lock.test +connect (con_tmp,localhost,root,,); +set session lock_wait_timeout=1; +create table asd (a int) engine=innodb; +xa start 'test1'; +insert into asd values(1); +xa end 'test1'; +connection default; +flush table with read lock; +connection con_tmp; +--echo # PREPARE error will do auto rollback. +--ERROR ER_LOCK_WAIT_TIMEOUT +xa prepare 'test1'; +show errors; +connection default; +unlock tables; + +connection con_tmp; +xa start 'test1'; +insert into asd values(1); +xa end 'test1'; +xa prepare 'test1'; +connection default; +flush tables with read lock; +connection con_tmp; +--echo # LOCK error during ROLLBACK will not alter transaction state. +--ERROR ER_LOCK_WAIT_TIMEOUT +xa rollback 'test1'; +show errors; +xa recover; +--echo # LOCK error during COMMIT will not alter transaction state. +--ERROR ER_LOCK_WAIT_TIMEOUT +xa commit 'test1'; +show errors; +xa recover; +connection default; +unlock tables; +connection con_tmp; +xa rollback 'test1'; +xa recover; +drop table asd; +disconnect con_tmp; +--source include/wait_until_disconnected.inc +connection default; diff --git a/mysql-test/main/xa_binlog.result b/mysql-test/main/xa_binlog.result index 619a6e08b20..c45749d500f 100644 --- a/mysql-test/main/xa_binlog.result +++ b/mysql-test/main/xa_binlog.result @@ -18,14 +18,17 @@ a 1 2 3 -SHOW BINLOG EVENTS LIMIT 3,9; +SHOW BINLOG EVENTS LIMIT 3,12; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Gtid 1 # BEGIN GTID #-#-# +master-bin.000001 # Gtid 1 # XA START X'786174657374',X'',1 GTID #-#-# master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES (1) -master-bin.000001 # Query 1 # COMMIT +master-bin.000001 # Query 1 # XA END X'786174657374',X'',1 +master-bin.000001 # XA_prepare 1 # XA PREPARE X'786174657374',X'',1 +master-bin.000001 # Gtid 1 # GTID #-#-# +master-bin.000001 # Query 1 # XA COMMIT X'786174657374',X'',1 master-bin.000001 # Gtid 1 # BEGIN GTID #-#-# master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES (2) -master-bin.000001 # Query 1 # COMMIT +master-bin.000001 # Xid 1 # COMMIT /* xid=XX */ master-bin.000001 # Gtid 1 # BEGIN GTID #-#-# master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES (3) master-bin.000001 # Xid 1 # COMMIT /* xid=XX */ diff --git a/mysql-test/main/xa_binlog.test b/mysql-test/main/xa_binlog.test index ecbf1f4f066..91bca2ac8cb 100644 --- a/mysql-test/main/xa_binlog.test +++ b/mysql-test/main/xa_binlog.test @@ -27,6 +27,6 @@ SELECT * FROM t1 ORDER BY a; --replace_column 2 # 5 # --replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ -SHOW BINLOG EVENTS LIMIT 3,9; +SHOW BINLOG EVENTS LIMIT 3,12; DROP TABLE t1; diff --git a/mysql-test/main/xa_prepared_binlog_off-master.opt b/mysql-test/main/xa_prepared_binlog_off-master.opt new file mode 100644 index 00000000000..789275fa25e --- /dev/null +++ b/mysql-test/main/xa_prepared_binlog_off-master.opt @@ -0,0 +1 @@ +--skip-log-bin diff --git a/mysql-test/main/xa_prepared_binlog_off.result b/mysql-test/main/xa_prepared_binlog_off.result new file mode 100644 index 00000000000..ca19f6cdfaf --- /dev/null +++ b/mysql-test/main/xa_prepared_binlog_off.result @@ -0,0 +1,1044 @@ +call mtr.add_suppression("You need to use --log-bin to make --log-slave-updates work."); +connection default; +CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND'; +call mtr.add_suppression("Found 10 prepared XA transactions"); +CREATE TABLE t (a INT) ENGINE=innodb; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx1tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx1tmp'; +XA PREPARE 'trx1tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx2tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx2tmp'; +XA PREPARE 'trx2tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx3tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx3tmp'; +XA PREPARE 'trx3tmp'; +connection default; +XA COMMIT 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA ROLLBACK 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA START 'trx1tmp'; +ERROR XAE08: XAER_DUPID: The XID already exists +connection default; +*** 3 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1tmp; +disconnect conn1tmp; +connection default; +XA COMMIT 'trx1tmp'; +KILL connection CONN_ID; +XA COMMIT 'trx3tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1ro'; +SELECT * from t ORDER BY a; +a +XA END 'trx1ro'; +XA PREPARE 'trx1ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2ro'; +SELECT * from t ORDER BY a; +a +XA END 'trx2ro'; +XA PREPARE 'trx2ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3ro'; +SELECT * from t ORDER BY a; +a +XA END 'trx3ro'; +XA PREPARE 'trx3ro'; +connection default; +*** 4 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1ro; +disconnect conn1ro; +connection default; +XA ROLLBACK 'trx1ro'; +KILL connection CONN_ID; +XA ROLLBACK 'trx3ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1empty'; +XA END 'trx1empty'; +XA PREPARE 'trx1empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2empty'; +XA END 'trx2empty'; +XA PREPARE 'trx2empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3empty'; +XA END 'trx3empty'; +XA PREPARE 'trx3empty'; +connection default; +*** 5 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1empty; +disconnect conn1empty; +connection default; +XA COMMIT 'trx1empty'; +KILL connection CONN_ID; +XA COMMIT 'trx3empty'; +connect conn1$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1unprepared'; +INSERT INTO t set a=0; +XA END 'trx1unprepared'; +INSERT INTO t set a=0; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +disconnect conn1unprepared; +connection default; +XA COMMIT 'trx1unprepared'; +ERROR XAE04: XAER_NOTA: Unknown XID +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_0'; +INSERT INTO t SET a=0; +XA END 'trx_0'; +XA PREPARE 'trx_0'; +disconnect conn0; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_1'; +INSERT INTO t SET a=1; +XA END 'trx_1'; +XA PREPARE 'trx_1'; +disconnect conn1; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_2'; +INSERT INTO t SET a=2; +XA END 'trx_2'; +XA PREPARE 'trx_2'; +disconnect conn2; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_3'; +INSERT INTO t SET a=3; +XA END 'trx_3'; +XA PREPARE 'trx_3'; +disconnect conn3; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_4'; +INSERT INTO t SET a=4; +XA END 'trx_4'; +XA PREPARE 'trx_4'; +disconnect conn4; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_5'; +INSERT INTO t SET a=5; +XA END 'trx_5'; +XA PREPARE 'trx_5'; +disconnect conn5; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_6'; +INSERT INTO t SET a=6; +XA END 'trx_6'; +XA PREPARE 'trx_6'; +disconnect conn6; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_7'; +INSERT INTO t SET a=7; +XA END 'trx_7'; +XA PREPARE 'trx_7'; +disconnect conn7; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_8'; +INSERT INTO t SET a=8; +XA END 'trx_8'; +XA PREPARE 'trx_8'; +disconnect conn8; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_9'; +INSERT INTO t SET a=9; +XA END 'trx_9'; +XA PREPARE 'trx_9'; +disconnect conn9; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_10'; +INSERT INTO t SET a=10; +XA END 'trx_10'; +XA PREPARE 'trx_10'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_11'; +INSERT INTO t SET a=11; +XA END 'trx_11'; +XA PREPARE 'trx_11'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_12'; +INSERT INTO t SET a=12; +XA END 'trx_12'; +XA PREPARE 'trx_12'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_13'; +INSERT INTO t SET a=13; +XA END 'trx_13'; +XA PREPARE 'trx_13'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_14'; +INSERT INTO t SET a=14; +XA END 'trx_14'; +XA PREPARE 'trx_14'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_15'; +INSERT INTO t SET a=15; +XA END 'trx_15'; +XA PREPARE 'trx_15'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_16'; +INSERT INTO t SET a=16; +XA END 'trx_16'; +XA PREPARE 'trx_16'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_17'; +INSERT INTO t SET a=17; +XA END 'trx_17'; +XA PREPARE 'trx_17'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_18'; +INSERT INTO t SET a=18; +XA END 'trx_18'; +XA PREPARE 'trx_18'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_19'; +INSERT INTO t SET a=19; +XA END 'trx_19'; +XA PREPARE 'trx_19'; +connection default; +KILL CONNECTION CONN_ID; +connection default; +XA ROLLBACK 'trx_0'; +XA ROLLBACK 'trx_1'; +XA ROLLBACK 'trx_2'; +XA ROLLBACK 'trx_3'; +XA ROLLBACK 'trx_4'; +XA COMMIT 'trx_5'; +XA COMMIT 'trx_6'; +XA COMMIT 'trx_7'; +XA COMMIT 'trx_8'; +XA COMMIT 'trx_9'; +# restart +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_0'; +INSERT INTO t SET a=0; +XA END 'new_trx_0'; +XA PREPARE 'new_trx_0'; +disconnect conn_restart_0; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_1'; +INSERT INTO t SET a=1; +XA END 'new_trx_1'; +XA PREPARE 'new_trx_1'; +disconnect conn_restart_1; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_2'; +INSERT INTO t SET a=2; +XA END 'new_trx_2'; +XA PREPARE 'new_trx_2'; +disconnect conn_restart_2; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_3'; +INSERT INTO t SET a=3; +XA END 'new_trx_3'; +XA PREPARE 'new_trx_3'; +disconnect conn_restart_3; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_4'; +INSERT INTO t SET a=4; +XA END 'new_trx_4'; +XA PREPARE 'new_trx_4'; +disconnect conn_restart_4; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_5'; +INSERT INTO t SET a=5; +XA END 'new_trx_5'; +XA PREPARE 'new_trx_5'; +disconnect conn_restart_5; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_6'; +INSERT INTO t SET a=6; +XA END 'new_trx_6'; +XA PREPARE 'new_trx_6'; +disconnect conn_restart_6; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_7'; +INSERT INTO t SET a=7; +XA END 'new_trx_7'; +XA PREPARE 'new_trx_7'; +disconnect conn_restart_7; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_8'; +INSERT INTO t SET a=8; +XA END 'new_trx_8'; +XA PREPARE 'new_trx_8'; +disconnect conn_restart_8; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_9'; +INSERT INTO t SET a=9; +XA END 'new_trx_9'; +XA PREPARE 'new_trx_9'; +disconnect conn_restart_9; +connection default; +connection default; +XA COMMIT 'new_trx_0'; +XA COMMIT 'new_trx_1'; +XA COMMIT 'new_trx_2'; +XA COMMIT 'new_trx_3'; +XA COMMIT 'new_trx_4'; +XA COMMIT 'new_trx_5'; +XA COMMIT 'new_trx_6'; +XA COMMIT 'new_trx_7'; +XA COMMIT 'new_trx_8'; +XA COMMIT 'new_trx_9'; +XA START 'trx_10'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_10'; +XA START 'trx_11'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_11'; +XA START 'trx_12'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_12'; +XA START 'trx_13'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_13'; +XA START 'trx_14'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_14'; +XA START 'trx_15'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_15'; +XA START 'trx_16'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_16'; +XA START 'trx_17'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_17'; +XA START 'trx_18'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_18'; +XA START 'trx_19'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_19'; +SELECT * FROM t; +a +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +disconnect conn2tmp; +disconnect conn3tmp; +disconnect conn2ro; +disconnect conn3ro; +disconnect conn2empty; +disconnect conn3empty; +connection default; +XA ROLLBACK 'trx_20'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn19; +connection default; +XA ROLLBACK 'trx_19'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn18; +connection default; +XA ROLLBACK 'trx_18'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn17; +connection default; +XA ROLLBACK 'trx_17'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn16; +connection default; +XA ROLLBACK 'trx_16'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn15; +connection default; +XA ROLLBACK 'trx_15'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn14; +connection default; +XA ROLLBACK 'trx_14'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn13; +connection default; +XA ROLLBACK 'trx_13'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn12; +connection default; +XA ROLLBACK 'trx_12'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn11; +connection default; +XA ROLLBACK 'trx_11'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn10; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx1tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx1tmp'; +XA PREPARE 'trx1tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx2tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx2tmp'; +XA PREPARE 'trx2tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx3tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx3tmp'; +XA PREPARE 'trx3tmp'; +connection default; +XA COMMIT 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA ROLLBACK 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA START 'trx1tmp'; +ERROR XAE08: XAER_DUPID: The XID already exists +connection default; +*** 3 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1tmp; +disconnect conn1tmp; +connection default; +XA COMMIT 'trx1tmp'; +KILL connection CONN_ID; +XA COMMIT 'trx3tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1ro'; +SELECT * from t ORDER BY a; +a +0 +1 +2 +3 +4 +5 +5 +6 +6 +7 +7 +8 +8 +9 +9 +10 +11 +12 +13 +14 +XA END 'trx1ro'; +XA PREPARE 'trx1ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2ro'; +SELECT * from t ORDER BY a; +a +0 +1 +2 +3 +4 +5 +5 +6 +6 +7 +7 +8 +8 +9 +9 +10 +11 +12 +13 +14 +XA END 'trx2ro'; +XA PREPARE 'trx2ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3ro'; +SELECT * from t ORDER BY a; +a +0 +1 +2 +3 +4 +5 +5 +6 +6 +7 +7 +8 +8 +9 +9 +10 +11 +12 +13 +14 +XA END 'trx3ro'; +XA PREPARE 'trx3ro'; +connection default; +*** 4 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1ro; +disconnect conn1ro; +connection default; +XA ROLLBACK 'trx1ro'; +KILL connection CONN_ID; +XA ROLLBACK 'trx3ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1empty'; +XA END 'trx1empty'; +XA PREPARE 'trx1empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2empty'; +XA END 'trx2empty'; +XA PREPARE 'trx2empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3empty'; +XA END 'trx3empty'; +XA PREPARE 'trx3empty'; +connection default; +*** 5 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1empty; +disconnect conn1empty; +connection default; +XA COMMIT 'trx1empty'; +KILL connection CONN_ID; +XA COMMIT 'trx3empty'; +connect conn1$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1unprepared'; +INSERT INTO t set a=0; +XA END 'trx1unprepared'; +INSERT INTO t set a=0; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +disconnect conn1unprepared; +connection default; +XA COMMIT 'trx1unprepared'; +ERROR XAE04: XAER_NOTA: Unknown XID +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_0'; +INSERT INTO t SET a=0; +XA END 'trx_0'; +XA PREPARE 'trx_0'; +disconnect conn0; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_1'; +INSERT INTO t SET a=1; +XA END 'trx_1'; +XA PREPARE 'trx_1'; +disconnect conn1; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_2'; +INSERT INTO t SET a=2; +XA END 'trx_2'; +XA PREPARE 'trx_2'; +disconnect conn2; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_3'; +INSERT INTO t SET a=3; +XA END 'trx_3'; +XA PREPARE 'trx_3'; +disconnect conn3; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_4'; +INSERT INTO t SET a=4; +XA END 'trx_4'; +XA PREPARE 'trx_4'; +disconnect conn4; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_5'; +INSERT INTO t SET a=5; +XA END 'trx_5'; +XA PREPARE 'trx_5'; +disconnect conn5; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_6'; +INSERT INTO t SET a=6; +XA END 'trx_6'; +XA PREPARE 'trx_6'; +disconnect conn6; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_7'; +INSERT INTO t SET a=7; +XA END 'trx_7'; +XA PREPARE 'trx_7'; +disconnect conn7; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_8'; +INSERT INTO t SET a=8; +XA END 'trx_8'; +XA PREPARE 'trx_8'; +disconnect conn8; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_9'; +INSERT INTO t SET a=9; +XA END 'trx_9'; +XA PREPARE 'trx_9'; +disconnect conn9; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_10'; +INSERT INTO t SET a=10; +XA END 'trx_10'; +XA PREPARE 'trx_10'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_11'; +INSERT INTO t SET a=11; +XA END 'trx_11'; +XA PREPARE 'trx_11'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_12'; +INSERT INTO t SET a=12; +XA END 'trx_12'; +XA PREPARE 'trx_12'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_13'; +INSERT INTO t SET a=13; +XA END 'trx_13'; +XA PREPARE 'trx_13'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_14'; +INSERT INTO t SET a=14; +XA END 'trx_14'; +XA PREPARE 'trx_14'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_15'; +INSERT INTO t SET a=15; +XA END 'trx_15'; +XA PREPARE 'trx_15'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_16'; +INSERT INTO t SET a=16; +XA END 'trx_16'; +XA PREPARE 'trx_16'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_17'; +INSERT INTO t SET a=17; +XA END 'trx_17'; +XA PREPARE 'trx_17'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_18'; +INSERT INTO t SET a=18; +XA END 'trx_18'; +XA PREPARE 'trx_18'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_19'; +INSERT INTO t SET a=19; +XA END 'trx_19'; +XA PREPARE 'trx_19'; +connection default; +KILL CONNECTION CONN_ID; +connection default; +XA ROLLBACK 'trx_0'; +XA ROLLBACK 'trx_1'; +XA ROLLBACK 'trx_2'; +XA ROLLBACK 'trx_3'; +XA ROLLBACK 'trx_4'; +XA COMMIT 'trx_5'; +XA COMMIT 'trx_6'; +XA COMMIT 'trx_7'; +XA COMMIT 'trx_8'; +XA COMMIT 'trx_9'; +# Kill and restart +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_0'; +INSERT INTO t SET a=0; +XA END 'new_trx_0'; +XA PREPARE 'new_trx_0'; +disconnect conn_restart_0; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_1'; +INSERT INTO t SET a=1; +XA END 'new_trx_1'; +XA PREPARE 'new_trx_1'; +disconnect conn_restart_1; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_2'; +INSERT INTO t SET a=2; +XA END 'new_trx_2'; +XA PREPARE 'new_trx_2'; +disconnect conn_restart_2; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_3'; +INSERT INTO t SET a=3; +XA END 'new_trx_3'; +XA PREPARE 'new_trx_3'; +disconnect conn_restart_3; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_4'; +INSERT INTO t SET a=4; +XA END 'new_trx_4'; +XA PREPARE 'new_trx_4'; +disconnect conn_restart_4; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_5'; +INSERT INTO t SET a=5; +XA END 'new_trx_5'; +XA PREPARE 'new_trx_5'; +disconnect conn_restart_5; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_6'; +INSERT INTO t SET a=6; +XA END 'new_trx_6'; +XA PREPARE 'new_trx_6'; +disconnect conn_restart_6; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_7'; +INSERT INTO t SET a=7; +XA END 'new_trx_7'; +XA PREPARE 'new_trx_7'; +disconnect conn_restart_7; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_8'; +INSERT INTO t SET a=8; +XA END 'new_trx_8'; +XA PREPARE 'new_trx_8'; +disconnect conn_restart_8; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_9'; +INSERT INTO t SET a=9; +XA END 'new_trx_9'; +XA PREPARE 'new_trx_9'; +disconnect conn_restart_9; +connection default; +connection default; +XA COMMIT 'new_trx_0'; +XA COMMIT 'new_trx_1'; +XA COMMIT 'new_trx_2'; +XA COMMIT 'new_trx_3'; +XA COMMIT 'new_trx_4'; +XA COMMIT 'new_trx_5'; +XA COMMIT 'new_trx_6'; +XA COMMIT 'new_trx_7'; +XA COMMIT 'new_trx_8'; +XA COMMIT 'new_trx_9'; +XA START 'trx_10'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_10'; +XA START 'trx_11'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_11'; +XA START 'trx_12'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_12'; +XA START 'trx_13'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_13'; +XA START 'trx_14'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_14'; +XA START 'trx_15'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_15'; +XA START 'trx_16'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_16'; +XA START 'trx_17'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_17'; +XA START 'trx_18'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_18'; +XA START 'trx_19'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_19'; +SELECT * FROM t; +a +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +disconnect conn2tmp; +disconnect conn3tmp; +disconnect conn2ro; +disconnect conn3ro; +disconnect conn2empty; +disconnect conn3empty; +connection default; +XA ROLLBACK 'trx_20'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn19; +connection default; +XA ROLLBACK 'trx_19'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn18; +connection default; +XA ROLLBACK 'trx_18'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn17; +connection default; +XA ROLLBACK 'trx_17'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn16; +connection default; +XA ROLLBACK 'trx_16'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn15; +connection default; +XA ROLLBACK 'trx_15'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn14; +connection default; +XA ROLLBACK 'trx_14'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn13; +connection default; +XA ROLLBACK 'trx_13'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn12; +connection default; +XA ROLLBACK 'trx_12'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn11; +connection default; +XA ROLLBACK 'trx_11'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn10; +connection default; +XA START 'one_phase_trx_0'; +INSERT INTO t SET a=0; +XA END 'one_phase_trx_0'; +XA COMMIT 'one_phase_trx_0' ONE PHASE; +XA START 'one_phase_trx_1'; +INSERT INTO t SET a=1; +XA END 'one_phase_trx_1'; +XA COMMIT 'one_phase_trx_1' ONE PHASE; +XA START 'one_phase_trx_2'; +INSERT INTO t SET a=2; +XA END 'one_phase_trx_2'; +XA COMMIT 'one_phase_trx_2' ONE PHASE; +XA START 'one_phase_trx_3'; +INSERT INTO t SET a=3; +XA END 'one_phase_trx_3'; +XA COMMIT 'one_phase_trx_3' ONE PHASE; +XA START 'one_phase_trx_4'; +INSERT INTO t SET a=4; +XA END 'one_phase_trx_4'; +XA COMMIT 'one_phase_trx_4' ONE PHASE; +SELECT SUM(a) FROM t; +SUM(a) +290 +DROP TABLE t; +DROP VIEW v_processlist; +All transactions must be completed, to empty-list the following: +XA RECOVER; +formatID gtrid_length bqual_length data diff --git a/mysql-test/main/xa_prepared_binlog_off.test b/mysql-test/main/xa_prepared_binlog_off.test new file mode 100644 index 00000000000..edbfa7c2825 --- /dev/null +++ b/mysql-test/main/xa_prepared_binlog_off.test @@ -0,0 +1,11 @@ +############################################################################### +# MDEV-7974 (bug#12161 Xa recovery and client disconnection) +# Testing XA behaviour with binlog turned off. +############################################################################### + +--source include/not_valgrind.inc +--source include/not_embedded.inc + +# Common part with XA binlogging testing +call mtr.add_suppression("You need to use --log-bin to make --log-slave-updates work."); +--source suite/binlog/t/binlog_xa_prepared.inc diff --git a/mysql-test/main/xa_sync.result b/mysql-test/main/xa_sync.result index 1482ff5cacf..e7dd9b02847 100644 --- a/mysql-test/main/xa_sync.result +++ b/mysql-test/main/xa_sync.result @@ -18,6 +18,11 @@ disconnect con1; SET debug_sync='now SIGNAL go'; connection con2; ERROR XAE04: XAER_NOTA: Unknown XID +*** Must have 'xatest' in the list +XA RECOVER; +formatID gtrid_length bqual_length data +1 6 0 xatest +XA COMMIT 'xatest'; disconnect con2; connection default; SET debug_sync='RESET'; @@ -37,6 +42,11 @@ disconnect con1; SET debug_sync='now SIGNAL go'; connection con2; ERROR XAE04: XAER_NOTA: Unknown XID +*** Must have 'xatest' in the list +XA RECOVER; +formatID gtrid_length bqual_length data +1 6 0 xatest +XA ROLLBACK 'xatest'; disconnect con2; connection default; SET debug_sync='RESET'; diff --git a/mysql-test/main/xa_sync.test b/mysql-test/main/xa_sync.test index bb95af7c0ba..ad1243ce6f8 100644 --- a/mysql-test/main/xa_sync.test +++ b/mysql-test/main/xa_sync.test @@ -35,6 +35,11 @@ while ($i) connection con2; --error ER_XAER_NOTA reap; + --echo *** Must have 'xatest' in the list + XA RECOVER; + # second time yields no error + --error 0,1402 + --eval $op disconnect con2; connection default; diff --git a/mysql-test/suite/binlog/include/binlog_xa_prepare_connection.inc b/mysql-test/suite/binlog/include/binlog_xa_prepare_connection.inc new file mode 100644 index 00000000000..c0041af1e7f --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_xa_prepare_connection.inc @@ -0,0 +1,31 @@ +# +# This file initiate connections to run XA transactions up to +# their prepare. +# Connection name, transaction name and its content depends on +# supplied parameters. +# +# param $type type of transaction +# param $index index identifies the connection with those of type $type +# param $sql_init1 a query to execute once connection is established +# param $sql_init2 a query to execute once connection is established +# param $sql_doit a query to execute inside transaction +# Note, the query may depend on tables created by caller +# + +--connect (conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,) +if ($sql_init1) +{ + --eval $sql_init1 +} +if ($sql_init2) +{ + --eval $sql_init2 +} + +--eval XA START 'trx$index$type' +if ($sql_doit) +{ + --eval $sql_doit +} +--eval XA END 'trx$index$type' +--eval XA PREPARE 'trx$index$type' diff --git a/mysql-test/suite/binlog/include/binlog_xa_prepare_disconnect.inc b/mysql-test/suite/binlog/include/binlog_xa_prepare_disconnect.inc new file mode 100644 index 00000000000..4a83aa5c282 --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_xa_prepare_disconnect.inc @@ -0,0 +1,37 @@ +# +# This file disconnects two connections. One actively and one through +# kill. It is included by binlog_xa_prepared_do_and_restart. +# +# param $type type of transaction +# param $terminate_with how to conclude actively disconnecte: +# XA COMMIT or XA ROLLBACK +# param $conn3_id connection id of the being killed. +# param $num_trx_prepared number of transactions prepared so far +# +--connection default + +--echo *** $num_trx_prepared prepared transactions must be in the list *** +--replace_column 2 LEN1 3 LEN2 4 TRX_N +XA RECOVER; + +--connection conn1$type +--let $conn1_id=`SELECT connection_id()` +--disconnect conn1$type + +--connection default +--let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn1_id +--source include/wait_condition.inc + +# It will conclude now +--error 0,1402 +--eval $terminate_with 'trx1$type' + +--replace_result $conn3_id CONN_ID +--eval KILL connection $conn3_id + +--let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn3_id +--source include/wait_condition.inc + +# It will conclude now +--error 0,1402 +--eval $terminate_with 'trx3$type' diff --git a/mysql-test/suite/binlog/include/binlog_xa_prepared_do_and_restart.inc b/mysql-test/suite/binlog/include/binlog_xa_prepared_do_and_restart.inc new file mode 100644 index 00000000000..cbd740fdae4 --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_xa_prepared_do_and_restart.inc @@ -0,0 +1,323 @@ +# +# This file creates various kinds of prepared XA transactions, +# manipulates their connection state and examines how their prepared +# status behave while the transaction is disconnected, killed or +# the server kisses it shutdown. +# The file can be sourced multiple times +# param $restart_number (as the number of inclusion) adjusts +# verification logics. +# +# param [in] $conn_number Total number of connection each performing +# one insert into table. +# param [in] $commit_number Number of commits from either. +# side of the server restart. +# param [in] $rollback_number The same as the above just for rollback. +# param [in] $term_number Number of transaction that are terminated +# before server restarts +# param [in] $killed_number Instead of disconnect make some +# connections killed when their +# transactions got prepared. +# param [in] $server_disconn_number Make some connections disconnected +# by shutdown rather than actively +# param [in] $post_restart_conn_number Number a "warmup" connection +# after server restart, they all commit +# param [out] restart_number Counter to be incremented at the end of the test +# + +# The test consists of three sections: +# I. Corner cases check +# II. Regular case check +# III. Post server-restart verification + + +# +# I. Corner cases of +# +# A. XA with an update to a temp table +# B. XA with SELECT +# C. XA empty +# Demonstrate their XA status upon prepare and how they react on disconnect and +# shutdown. +# In each of A,B,C three prepared transactions are set up. +# trx1 is for disconnection, trx2 for shutdown, trx3 for being killed. +# The A case additionally contains some XA prohibited state transaction check. +# +# D. Prove that not prepared XA remains to be cleared out by disconnection. +# + +# +# A. The temp table only prepared XA recovers only formally to +# let post recovery XA COMMIT or XA ROLLBACK with no effect. + +--let $type = tmp +--let $index = 1 +--let $sql_init1 = SET @@sql_log_bin = OFF +--let $sql_init2 = CREATE TEMPORARY TABLE tmp$index (a int) ENGINE=innodb +--let $sql_doit = INSERT INTO tmp$index SET a=$index +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 2 +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 3 +--source suite/binlog/include/binlog_xa_prepare_connection.inc +--let $conn3_id=`SELECT connection_id()` + +# +# Various prohibited XA state changes to test here: +# + +--connection default +# Stealing is not allowed +--error ER_XAER_NOTA +--eval XA COMMIT 'trx1$type' +--error ER_XAER_NOTA +--eval XA ROLLBACK 'trx1$type' + +# Before disconnect: creating a duplicate is not allowed +--error ER_XAER_DUPID +--eval XA START 'trx1$type' + +# Manipulate now the prepared transactions. +# Two to terminate, one to leave out. +--let $terminate_with = XA COMMIT +--let $num_trx_prepared = $index +--source suite/binlog/include/binlog_xa_prepare_disconnect.inc + +# +# B. "Read-only" (select) prepared XA recovers only formally to +# let post recovery XA COMMIT or XA ROLLBACK with no effect. +# +--let $type=ro +--let $index = 1 +--let $sql_init1 = +--let $sql_init2 = +--let $sql_doit = SELECT * from t ORDER BY a +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 2 +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 3 +--source suite/binlog/include/binlog_xa_prepare_connection.inc +--let $conn3_id=`SELECT connection_id()` + +--let $terminate_with = XA ROLLBACK +# two three above section prepared transaction were terminated. +--inc $num_trx_prepared +--source suite/binlog/include/binlog_xa_prepare_disconnect.inc + +# +# C. Empty prepared XA recovers only formally to +# let post recovery XA COMMIT or XA ROLLBACK with no effect. +# +--let $type=empty +--let $index = 1 +--let $sql_init1 = +--let $sql_init2 = +--let $sql_doit = +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 2 +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 3 +--source suite/binlog/include/binlog_xa_prepare_connection.inc +--let $conn3_id=`SELECT connection_id()` + +--let $terminate_with = XA COMMIT +--inc $num_trx_prepared +--source suite/binlog/include/binlog_xa_prepare_disconnect.inc + +# +# D. Not prepared XA disconnects to be cleared out, +# no effect on data left as well. +# Few more prohibited XA state transactions is checked out. +# +--let $type=unprepared +--let $prev_count=`SELECT count(*) from t` + +--connect(conn1$type, 127.0.0.1,root,,test,$MASTER_MYPORT,) +--eval XA START 'trx1$type' +INSERT INTO t set a=0; +--eval XA END 'trx1$type' + +--error ER_XAER_RMFAIL +INSERT INTO t set a=0; +--error ER_XAER_RMFAIL +--eval XA START 'trx1$type' +--error ER_XAER_RMFAIL +--eval XA START 'trx1$type' + +--disconnect conn1$type + +--connection default +# No such transactions +--error ER_XAER_NOTA +--eval XA COMMIT 'trx1$type' +if (`SELECT count(*) > $prev_count from t`) +{ + --echo *** Unexpected commit to the table. *** + --die +} + +# +# II. Regular case. +# +# Prepared transactions get disconnected in three ways: +# actively, being killed and by the server shutdown. +# +--let $i=0 +while ($i < $conn_number) +{ + --connect (conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,) + --let $conn_id=`SELECT connection_id()` + --disable_reconnect + SET @@binlog_format = STATEMENT; + if (`SELECT $i % 2`) + { + SET @@binlog_format = ROW; + } + --eval XA START 'trx_$i' + --eval INSERT INTO t SET a=$i + --eval XA END 'trx_$i' + --eval XA PREPARE 'trx_$i' + + --let $disc_via_kill=`SELECT $conn_number - $i <= $killed_number` + if (!$disc_via_kill) + { + --let $disc_via_shutdown=`SELECT $conn_number - $i <= $killed_number + $server_disconn_number` + if (!$disc_via_shutdown) + { + --disconnect conn$i + } + } + if ($disc_via_kill) + { + --connection default + --replace_result $conn_id CONN_ID + --eval KILL CONNECTION $conn_id + } + + if (!$disc_via_shutdown) + { + --connection default + --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id + --source include/wait_condition.inc + } + --inc $i +} + +# [0, $rollback_number - 1] are rolled back now +--connection default + +--let $i=0 +while ($i < $rollback_number) +{ + --eval XA ROLLBACK 'trx_$i' + + --inc $i +} + +# [$rollback_number, $rollback_number + $commit_number - 1] get committed +while ($i < $term_number) +{ + --eval XA COMMIT 'trx_$i' + + --inc $i +} + +--source include/$how_to_restart + +# +# III. Post server-restart verification. +# It concludes survived XA:s with a number of commits and rollbacks +# as configured in the 1st part to check expected results in the end. +# Cleanup section consists of explicit disconnect (for killed, or +# not disconnected before shutdown). +# + +# New XA can be prepared and committed +--let $k = 0 +while ($k < $post_restart_conn_number) +{ + --connect (conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,) + --let $conn_id=`SELECT connection_id()` + --eval XA START 'new_trx_$k' + --eval INSERT INTO t SET a=$k + --eval XA END 'new_trx_$k' + --eval XA PREPARE 'new_trx_$k' + + --disconnect conn_restart_$k + + --connection default + --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id + --source include/wait_condition.inc + + --inc $k +} + +--connection default +--let $k = 0 +while ($k < $post_restart_conn_number) +{ + --eval XA COMMIT 'new_trx_$k' + --inc $k +} + +# +# Symmetrically to the pre-restart, the resurrected trx:s are committed +# [$term_number, $term_number + $commit_number - 1] +# and the rest is rolled back. +# +--let $i = $term_number + +while ($i < `SELECT $term_number + $commit_number`) +{ + # Expected to fail + --error ER_XAER_DUPID + --eval XA START 'trx_$i' + --eval XA COMMIT 'trx_$i' + --inc $i +} + +while ($i < $conn_number) +{ + # Expected to fail + --error ER_XAER_DUPID + --eval XA START 'trx_$i' + --eval XA ROLLBACK 'trx_$i' + --inc $i +} + +# +# Verification of correct results of recovered XA transaction handling: +# +SELECT * FROM t; + +--let $type=tmp +--disconnect conn2$type +--disconnect conn3$type +--let $type=ro +--disconnect conn2$type +--disconnect conn3$type +--let $type=empty +--disconnect conn2$type +--disconnect conn3$type + +--let $i= $conn_number +--let $k= 0 +--let $expl_disconn_number = `SELECT $killed_number + $server_disconn_number` +while ($k < $expl_disconn_number) +{ + --connection default + --error ER_XAER_NOTA + --eval XA ROLLBACK 'trx_$i' + + --dec $i + --disconnect conn$i + + --inc $k +} + +--inc $restart_number diff --git a/mysql-test/suite/binlog/r/binlog_xa_checkpoint.result b/mysql-test/suite/binlog/r/binlog_xa_checkpoint.result new file mode 100644 index 00000000000..d8a5818674f --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_xa_checkpoint.result @@ -0,0 +1,33 @@ +RESET MASTER; +CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb; +connect con1,localhost,root,,; +SET DEBUG_SYNC= "at_unlog_xa_prepare SIGNAL con1_ready WAIT_FOR con1_go"; +XA START '1'; +INSERT INTO t1 SET a=1; +XA END '1'; +XA PREPARE '1';; +connection default; +SET DEBUG_SYNC= "now WAIT_FOR con1_ready"; +FLUSH LOGS; +FLUSH LOGS; +FLUSH LOGS; +show binary logs; +Log_name File_size +master-bin.000001 # +master-bin.000002 # +master-bin.000003 # +master-bin.000004 # +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000004 # Format_desc # # SERVER_VERSION, BINLOG_VERSION +master-bin.000004 # Gtid_list # # [#-#-#] +master-bin.000004 # Binlog_checkpoint # # master-bin.000001 +SET DEBUG_SYNC= "now SIGNAL con1_go"; +connection con1; +*** master-bin.000004 checkpoint must show up now *** +connection con1; +XA ROLLBACK '1'; +SET debug_sync = 'reset'; +connection default; +DROP TABLE t1; +SET debug_sync = 'reset'; diff --git a/mysql-test/suite/binlog/r/binlog_xa_prepared.result b/mysql-test/suite/binlog/r/binlog_xa_prepared.result new file mode 100644 index 00000000000..9fda8ab3143 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_xa_prepared.result @@ -0,0 +1,1176 @@ +connection default; +RESET MASTER; +CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND'; +call mtr.add_suppression("Found 10 prepared XA transactions"); +CREATE TABLE t (a INT) ENGINE=innodb; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx1tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx1tmp'; +XA PREPARE 'trx1tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx2tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx2tmp'; +XA PREPARE 'trx2tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx3tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx3tmp'; +XA PREPARE 'trx3tmp'; +connection default; +XA COMMIT 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA ROLLBACK 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA START 'trx1tmp'; +ERROR XAE08: XAER_DUPID: The XID already exists +connection default; +*** 3 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1tmp; +disconnect conn1tmp; +connection default; +XA COMMIT 'trx1tmp'; +KILL connection CONN_ID; +XA COMMIT 'trx3tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1ro'; +SELECT * from t ORDER BY a; +a +XA END 'trx1ro'; +XA PREPARE 'trx1ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2ro'; +SELECT * from t ORDER BY a; +a +XA END 'trx2ro'; +XA PREPARE 'trx2ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3ro'; +SELECT * from t ORDER BY a; +a +XA END 'trx3ro'; +XA PREPARE 'trx3ro'; +connection default; +*** 4 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1ro; +disconnect conn1ro; +connection default; +XA ROLLBACK 'trx1ro'; +KILL connection CONN_ID; +XA ROLLBACK 'trx3ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1empty'; +XA END 'trx1empty'; +XA PREPARE 'trx1empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2empty'; +XA END 'trx2empty'; +XA PREPARE 'trx2empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3empty'; +XA END 'trx3empty'; +XA PREPARE 'trx3empty'; +connection default; +*** 5 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1empty; +disconnect conn1empty; +connection default; +XA COMMIT 'trx1empty'; +KILL connection CONN_ID; +XA COMMIT 'trx3empty'; +connect conn1$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1unprepared'; +INSERT INTO t set a=0; +XA END 'trx1unprepared'; +INSERT INTO t set a=0; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +disconnect conn1unprepared; +connection default; +XA COMMIT 'trx1unprepared'; +ERROR XAE04: XAER_NOTA: Unknown XID +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_0'; +INSERT INTO t SET a=0; +XA END 'trx_0'; +XA PREPARE 'trx_0'; +disconnect conn0; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_1'; +INSERT INTO t SET a=1; +XA END 'trx_1'; +XA PREPARE 'trx_1'; +disconnect conn1; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_2'; +INSERT INTO t SET a=2; +XA END 'trx_2'; +XA PREPARE 'trx_2'; +disconnect conn2; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_3'; +INSERT INTO t SET a=3; +XA END 'trx_3'; +XA PREPARE 'trx_3'; +disconnect conn3; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_4'; +INSERT INTO t SET a=4; +XA END 'trx_4'; +XA PREPARE 'trx_4'; +disconnect conn4; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_5'; +INSERT INTO t SET a=5; +XA END 'trx_5'; +XA PREPARE 'trx_5'; +disconnect conn5; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_6'; +INSERT INTO t SET a=6; +XA END 'trx_6'; +XA PREPARE 'trx_6'; +disconnect conn6; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_7'; +INSERT INTO t SET a=7; +XA END 'trx_7'; +XA PREPARE 'trx_7'; +disconnect conn7; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_8'; +INSERT INTO t SET a=8; +XA END 'trx_8'; +XA PREPARE 'trx_8'; +disconnect conn8; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_9'; +INSERT INTO t SET a=9; +XA END 'trx_9'; +XA PREPARE 'trx_9'; +disconnect conn9; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_10'; +INSERT INTO t SET a=10; +XA END 'trx_10'; +XA PREPARE 'trx_10'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_11'; +INSERT INTO t SET a=11; +XA END 'trx_11'; +XA PREPARE 'trx_11'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_12'; +INSERT INTO t SET a=12; +XA END 'trx_12'; +XA PREPARE 'trx_12'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_13'; +INSERT INTO t SET a=13; +XA END 'trx_13'; +XA PREPARE 'trx_13'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_14'; +INSERT INTO t SET a=14; +XA END 'trx_14'; +XA PREPARE 'trx_14'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_15'; +INSERT INTO t SET a=15; +XA END 'trx_15'; +XA PREPARE 'trx_15'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_16'; +INSERT INTO t SET a=16; +XA END 'trx_16'; +XA PREPARE 'trx_16'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_17'; +INSERT INTO t SET a=17; +XA END 'trx_17'; +XA PREPARE 'trx_17'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_18'; +INSERT INTO t SET a=18; +XA END 'trx_18'; +XA PREPARE 'trx_18'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_19'; +INSERT INTO t SET a=19; +XA END 'trx_19'; +XA PREPARE 'trx_19'; +connection default; +KILL CONNECTION CONN_ID; +connection default; +XA ROLLBACK 'trx_0'; +XA ROLLBACK 'trx_1'; +XA ROLLBACK 'trx_2'; +XA ROLLBACK 'trx_3'; +XA ROLLBACK 'trx_4'; +XA COMMIT 'trx_5'; +XA COMMIT 'trx_6'; +XA COMMIT 'trx_7'; +XA COMMIT 'trx_8'; +XA COMMIT 'trx_9'; +# restart +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_0'; +INSERT INTO t SET a=0; +XA END 'new_trx_0'; +XA PREPARE 'new_trx_0'; +disconnect conn_restart_0; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_1'; +INSERT INTO t SET a=1; +XA END 'new_trx_1'; +XA PREPARE 'new_trx_1'; +disconnect conn_restart_1; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_2'; +INSERT INTO t SET a=2; +XA END 'new_trx_2'; +XA PREPARE 'new_trx_2'; +disconnect conn_restart_2; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_3'; +INSERT INTO t SET a=3; +XA END 'new_trx_3'; +XA PREPARE 'new_trx_3'; +disconnect conn_restart_3; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_4'; +INSERT INTO t SET a=4; +XA END 'new_trx_4'; +XA PREPARE 'new_trx_4'; +disconnect conn_restart_4; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_5'; +INSERT INTO t SET a=5; +XA END 'new_trx_5'; +XA PREPARE 'new_trx_5'; +disconnect conn_restart_5; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_6'; +INSERT INTO t SET a=6; +XA END 'new_trx_6'; +XA PREPARE 'new_trx_6'; +disconnect conn_restart_6; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_7'; +INSERT INTO t SET a=7; +XA END 'new_trx_7'; +XA PREPARE 'new_trx_7'; +disconnect conn_restart_7; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_8'; +INSERT INTO t SET a=8; +XA END 'new_trx_8'; +XA PREPARE 'new_trx_8'; +disconnect conn_restart_8; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_9'; +INSERT INTO t SET a=9; +XA END 'new_trx_9'; +XA PREPARE 'new_trx_9'; +disconnect conn_restart_9; +connection default; +connection default; +XA COMMIT 'new_trx_0'; +XA COMMIT 'new_trx_1'; +XA COMMIT 'new_trx_2'; +XA COMMIT 'new_trx_3'; +XA COMMIT 'new_trx_4'; +XA COMMIT 'new_trx_5'; +XA COMMIT 'new_trx_6'; +XA COMMIT 'new_trx_7'; +XA COMMIT 'new_trx_8'; +XA COMMIT 'new_trx_9'; +XA START 'trx_10'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_10'; +XA START 'trx_11'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_11'; +XA START 'trx_12'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_12'; +XA START 'trx_13'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_13'; +XA START 'trx_14'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_14'; +XA START 'trx_15'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_15'; +XA START 'trx_16'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_16'; +XA START 'trx_17'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_17'; +XA START 'trx_18'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_18'; +XA START 'trx_19'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_19'; +SELECT * FROM t; +a +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +disconnect conn2tmp; +disconnect conn3tmp; +disconnect conn2ro; +disconnect conn3ro; +disconnect conn2empty; +disconnect conn3empty; +connection default; +XA ROLLBACK 'trx_20'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn19; +connection default; +XA ROLLBACK 'trx_19'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn18; +connection default; +XA ROLLBACK 'trx_18'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn17; +connection default; +XA ROLLBACK 'trx_17'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn16; +connection default; +XA ROLLBACK 'trx_16'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn15; +connection default; +XA ROLLBACK 'trx_15'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn14; +connection default; +XA ROLLBACK 'trx_14'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn13; +connection default; +XA ROLLBACK 'trx_13'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn12; +connection default; +XA ROLLBACK 'trx_12'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn11; +connection default; +XA ROLLBACK 'trx_11'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn10; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx1tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx1tmp'; +XA PREPARE 'trx1tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx2tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx2tmp'; +XA PREPARE 'trx2tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx3tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx3tmp'; +XA PREPARE 'trx3tmp'; +connection default; +XA COMMIT 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA ROLLBACK 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA START 'trx1tmp'; +ERROR XAE08: XAER_DUPID: The XID already exists +connection default; +*** 3 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1tmp; +disconnect conn1tmp; +connection default; +XA COMMIT 'trx1tmp'; +KILL connection CONN_ID; +XA COMMIT 'trx3tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1ro'; +SELECT * from t ORDER BY a; +a +0 +1 +2 +3 +4 +5 +5 +6 +6 +7 +7 +8 +8 +9 +9 +10 +11 +12 +13 +14 +XA END 'trx1ro'; +XA PREPARE 'trx1ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2ro'; +SELECT * from t ORDER BY a; +a +0 +1 +2 +3 +4 +5 +5 +6 +6 +7 +7 +8 +8 +9 +9 +10 +11 +12 +13 +14 +XA END 'trx2ro'; +XA PREPARE 'trx2ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3ro'; +SELECT * from t ORDER BY a; +a +0 +1 +2 +3 +4 +5 +5 +6 +6 +7 +7 +8 +8 +9 +9 +10 +11 +12 +13 +14 +XA END 'trx3ro'; +XA PREPARE 'trx3ro'; +connection default; +*** 4 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1ro; +disconnect conn1ro; +connection default; +XA ROLLBACK 'trx1ro'; +KILL connection CONN_ID; +XA ROLLBACK 'trx3ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1empty'; +XA END 'trx1empty'; +XA PREPARE 'trx1empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2empty'; +XA END 'trx2empty'; +XA PREPARE 'trx2empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3empty'; +XA END 'trx3empty'; +XA PREPARE 'trx3empty'; +connection default; +*** 5 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1empty; +disconnect conn1empty; +connection default; +XA COMMIT 'trx1empty'; +KILL connection CONN_ID; +XA COMMIT 'trx3empty'; +connect conn1$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1unprepared'; +INSERT INTO t set a=0; +XA END 'trx1unprepared'; +INSERT INTO t set a=0; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +disconnect conn1unprepared; +connection default; +XA COMMIT 'trx1unprepared'; +ERROR XAE04: XAER_NOTA: Unknown XID +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_0'; +INSERT INTO t SET a=0; +XA END 'trx_0'; +XA PREPARE 'trx_0'; +disconnect conn0; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_1'; +INSERT INTO t SET a=1; +XA END 'trx_1'; +XA PREPARE 'trx_1'; +disconnect conn1; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_2'; +INSERT INTO t SET a=2; +XA END 'trx_2'; +XA PREPARE 'trx_2'; +disconnect conn2; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_3'; +INSERT INTO t SET a=3; +XA END 'trx_3'; +XA PREPARE 'trx_3'; +disconnect conn3; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_4'; +INSERT INTO t SET a=4; +XA END 'trx_4'; +XA PREPARE 'trx_4'; +disconnect conn4; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_5'; +INSERT INTO t SET a=5; +XA END 'trx_5'; +XA PREPARE 'trx_5'; +disconnect conn5; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_6'; +INSERT INTO t SET a=6; +XA END 'trx_6'; +XA PREPARE 'trx_6'; +disconnect conn6; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_7'; +INSERT INTO t SET a=7; +XA END 'trx_7'; +XA PREPARE 'trx_7'; +disconnect conn7; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_8'; +INSERT INTO t SET a=8; +XA END 'trx_8'; +XA PREPARE 'trx_8'; +disconnect conn8; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_9'; +INSERT INTO t SET a=9; +XA END 'trx_9'; +XA PREPARE 'trx_9'; +disconnect conn9; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_10'; +INSERT INTO t SET a=10; +XA END 'trx_10'; +XA PREPARE 'trx_10'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_11'; +INSERT INTO t SET a=11; +XA END 'trx_11'; +XA PREPARE 'trx_11'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_12'; +INSERT INTO t SET a=12; +XA END 'trx_12'; +XA PREPARE 'trx_12'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_13'; +INSERT INTO t SET a=13; +XA END 'trx_13'; +XA PREPARE 'trx_13'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_14'; +INSERT INTO t SET a=14; +XA END 'trx_14'; +XA PREPARE 'trx_14'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_15'; +INSERT INTO t SET a=15; +XA END 'trx_15'; +XA PREPARE 'trx_15'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_16'; +INSERT INTO t SET a=16; +XA END 'trx_16'; +XA PREPARE 'trx_16'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_17'; +INSERT INTO t SET a=17; +XA END 'trx_17'; +XA PREPARE 'trx_17'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_18'; +INSERT INTO t SET a=18; +XA END 'trx_18'; +XA PREPARE 'trx_18'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_19'; +INSERT INTO t SET a=19; +XA END 'trx_19'; +XA PREPARE 'trx_19'; +connection default; +KILL CONNECTION CONN_ID; +connection default; +XA ROLLBACK 'trx_0'; +XA ROLLBACK 'trx_1'; +XA ROLLBACK 'trx_2'; +XA ROLLBACK 'trx_3'; +XA ROLLBACK 'trx_4'; +XA COMMIT 'trx_5'; +XA COMMIT 'trx_6'; +XA COMMIT 'trx_7'; +XA COMMIT 'trx_8'; +XA COMMIT 'trx_9'; +# Kill and restart +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_0'; +INSERT INTO t SET a=0; +XA END 'new_trx_0'; +XA PREPARE 'new_trx_0'; +disconnect conn_restart_0; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_1'; +INSERT INTO t SET a=1; +XA END 'new_trx_1'; +XA PREPARE 'new_trx_1'; +disconnect conn_restart_1; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_2'; +INSERT INTO t SET a=2; +XA END 'new_trx_2'; +XA PREPARE 'new_trx_2'; +disconnect conn_restart_2; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_3'; +INSERT INTO t SET a=3; +XA END 'new_trx_3'; +XA PREPARE 'new_trx_3'; +disconnect conn_restart_3; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_4'; +INSERT INTO t SET a=4; +XA END 'new_trx_4'; +XA PREPARE 'new_trx_4'; +disconnect conn_restart_4; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_5'; +INSERT INTO t SET a=5; +XA END 'new_trx_5'; +XA PREPARE 'new_trx_5'; +disconnect conn_restart_5; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_6'; +INSERT INTO t SET a=6; +XA END 'new_trx_6'; +XA PREPARE 'new_trx_6'; +disconnect conn_restart_6; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_7'; +INSERT INTO t SET a=7; +XA END 'new_trx_7'; +XA PREPARE 'new_trx_7'; +disconnect conn_restart_7; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_8'; +INSERT INTO t SET a=8; +XA END 'new_trx_8'; +XA PREPARE 'new_trx_8'; +disconnect conn_restart_8; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_9'; +INSERT INTO t SET a=9; +XA END 'new_trx_9'; +XA PREPARE 'new_trx_9'; +disconnect conn_restart_9; +connection default; +connection default; +XA COMMIT 'new_trx_0'; +XA COMMIT 'new_trx_1'; +XA COMMIT 'new_trx_2'; +XA COMMIT 'new_trx_3'; +XA COMMIT 'new_trx_4'; +XA COMMIT 'new_trx_5'; +XA COMMIT 'new_trx_6'; +XA COMMIT 'new_trx_7'; +XA COMMIT 'new_trx_8'; +XA COMMIT 'new_trx_9'; +XA START 'trx_10'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_10'; +XA START 'trx_11'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_11'; +XA START 'trx_12'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_12'; +XA START 'trx_13'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_13'; +XA START 'trx_14'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_14'; +XA START 'trx_15'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_15'; +XA START 'trx_16'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_16'; +XA START 'trx_17'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_17'; +XA START 'trx_18'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_18'; +XA START 'trx_19'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_19'; +SELECT * FROM t; +a +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +disconnect conn2tmp; +disconnect conn3tmp; +disconnect conn2ro; +disconnect conn3ro; +disconnect conn2empty; +disconnect conn3empty; +connection default; +XA ROLLBACK 'trx_20'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn19; +connection default; +XA ROLLBACK 'trx_19'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn18; +connection default; +XA ROLLBACK 'trx_18'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn17; +connection default; +XA ROLLBACK 'trx_17'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn16; +connection default; +XA ROLLBACK 'trx_16'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn15; +connection default; +XA ROLLBACK 'trx_15'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn14; +connection default; +XA ROLLBACK 'trx_14'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn13; +connection default; +XA ROLLBACK 'trx_13'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn12; +connection default; +XA ROLLBACK 'trx_12'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn11; +connection default; +XA ROLLBACK 'trx_11'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn10; +connection default; +XA START 'one_phase_trx_0'; +INSERT INTO t SET a=0; +XA END 'one_phase_trx_0'; +XA COMMIT 'one_phase_trx_0' ONE PHASE; +XA START 'one_phase_trx_1'; +INSERT INTO t SET a=1; +XA END 'one_phase_trx_1'; +XA COMMIT 'one_phase_trx_1' ONE PHASE; +XA START 'one_phase_trx_2'; +INSERT INTO t SET a=2; +XA END 'one_phase_trx_2'; +XA COMMIT 'one_phase_trx_2' ONE PHASE; +XA START 'one_phase_trx_3'; +INSERT INTO t SET a=3; +XA END 'one_phase_trx_3'; +XA COMMIT 'one_phase_trx_3' ONE PHASE; +XA START 'one_phase_trx_4'; +INSERT INTO t SET a=4; +XA END 'one_phase_trx_4'; +XA COMMIT 'one_phase_trx_4' ONE PHASE; +SELECT SUM(a) FROM t; +SUM(a) +290 +DROP TABLE t; +DROP VIEW v_processlist; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_processlist` AS SELECT * FROM performance_schema.threads where type = 'FOREGROUND' +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'Found 10 prepared XA transactions' COLLATE 'latin1_swedish_ci')) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t (a INT) ENGINE=innodb +master-bin.000001 # Gtid # # XA START X'7472785f30',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=0 +master-bin.000001 # Query # # XA END X'7472785f30',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f30',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f31',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=1 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f31',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f31',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f32',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=2 +master-bin.000001 # Query # # XA END X'7472785f32',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f32',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f33',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=3 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f33',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f33',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f34',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=4 +master-bin.000001 # Query # # XA END X'7472785f34',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f34',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f35',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=5 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f35',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f35',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f36',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=6 +master-bin.000001 # Query # # XA END X'7472785f36',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f36',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f37',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=7 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f37',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f37',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f38',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=8 +master-bin.000001 # Query # # XA END X'7472785f38',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f38',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f39',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=9 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f39',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f39',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3130',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=10 +master-bin.000001 # Query # # XA END X'7472785f3130',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3130',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3131',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=11 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f3131',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3131',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3132',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=12 +master-bin.000001 # Query # # XA END X'7472785f3132',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3132',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3133',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=13 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f3133',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3133',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3134',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=14 +master-bin.000001 # Query # # XA END X'7472785f3134',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3134',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3135',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=15 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f3135',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3135',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3136',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=16 +master-bin.000001 # Query # # XA END X'7472785f3136',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3136',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3137',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=17 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f3137',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3137',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3138',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=18 +master-bin.000001 # Query # # XA END X'7472785f3138',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3138',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3139',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=19 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f3139',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3139',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA ROLLBACK X'7472785f30',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA ROLLBACK X'7472785f31',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA ROLLBACK X'7472785f32',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA ROLLBACK X'7472785f33',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA ROLLBACK X'7472785f34',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'7472785f35',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'7472785f36',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'7472785f37',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'7472785f38',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'7472785f39',X'',1 +master-bin.000001 # Stop # # +All transactions must be completed, to empty-list the following: +XA RECOVER; +formatID gtrid_length bqual_length data +XA RECOVER; +formatID gtrid_length bqual_length data diff --git a/mysql-test/suite/binlog/r/binlog_xa_prepared_disconnect.result b/mysql-test/suite/binlog/r/binlog_xa_prepared_disconnect.result new file mode 100644 index 00000000000..9fda8ab3143 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_xa_prepared_disconnect.result @@ -0,0 +1,1176 @@ +connection default; +RESET MASTER; +CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND'; +call mtr.add_suppression("Found 10 prepared XA transactions"); +CREATE TABLE t (a INT) ENGINE=innodb; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx1tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx1tmp'; +XA PREPARE 'trx1tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx2tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx2tmp'; +XA PREPARE 'trx2tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx3tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx3tmp'; +XA PREPARE 'trx3tmp'; +connection default; +XA COMMIT 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA ROLLBACK 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA START 'trx1tmp'; +ERROR XAE08: XAER_DUPID: The XID already exists +connection default; +*** 3 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1tmp; +disconnect conn1tmp; +connection default; +XA COMMIT 'trx1tmp'; +KILL connection CONN_ID; +XA COMMIT 'trx3tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1ro'; +SELECT * from t ORDER BY a; +a +XA END 'trx1ro'; +XA PREPARE 'trx1ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2ro'; +SELECT * from t ORDER BY a; +a +XA END 'trx2ro'; +XA PREPARE 'trx2ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3ro'; +SELECT * from t ORDER BY a; +a +XA END 'trx3ro'; +XA PREPARE 'trx3ro'; +connection default; +*** 4 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1ro; +disconnect conn1ro; +connection default; +XA ROLLBACK 'trx1ro'; +KILL connection CONN_ID; +XA ROLLBACK 'trx3ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1empty'; +XA END 'trx1empty'; +XA PREPARE 'trx1empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2empty'; +XA END 'trx2empty'; +XA PREPARE 'trx2empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3empty'; +XA END 'trx3empty'; +XA PREPARE 'trx3empty'; +connection default; +*** 5 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1empty; +disconnect conn1empty; +connection default; +XA COMMIT 'trx1empty'; +KILL connection CONN_ID; +XA COMMIT 'trx3empty'; +connect conn1$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1unprepared'; +INSERT INTO t set a=0; +XA END 'trx1unprepared'; +INSERT INTO t set a=0; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +disconnect conn1unprepared; +connection default; +XA COMMIT 'trx1unprepared'; +ERROR XAE04: XAER_NOTA: Unknown XID +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_0'; +INSERT INTO t SET a=0; +XA END 'trx_0'; +XA PREPARE 'trx_0'; +disconnect conn0; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_1'; +INSERT INTO t SET a=1; +XA END 'trx_1'; +XA PREPARE 'trx_1'; +disconnect conn1; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_2'; +INSERT INTO t SET a=2; +XA END 'trx_2'; +XA PREPARE 'trx_2'; +disconnect conn2; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_3'; +INSERT INTO t SET a=3; +XA END 'trx_3'; +XA PREPARE 'trx_3'; +disconnect conn3; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_4'; +INSERT INTO t SET a=4; +XA END 'trx_4'; +XA PREPARE 'trx_4'; +disconnect conn4; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_5'; +INSERT INTO t SET a=5; +XA END 'trx_5'; +XA PREPARE 'trx_5'; +disconnect conn5; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_6'; +INSERT INTO t SET a=6; +XA END 'trx_6'; +XA PREPARE 'trx_6'; +disconnect conn6; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_7'; +INSERT INTO t SET a=7; +XA END 'trx_7'; +XA PREPARE 'trx_7'; +disconnect conn7; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_8'; +INSERT INTO t SET a=8; +XA END 'trx_8'; +XA PREPARE 'trx_8'; +disconnect conn8; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_9'; +INSERT INTO t SET a=9; +XA END 'trx_9'; +XA PREPARE 'trx_9'; +disconnect conn9; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_10'; +INSERT INTO t SET a=10; +XA END 'trx_10'; +XA PREPARE 'trx_10'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_11'; +INSERT INTO t SET a=11; +XA END 'trx_11'; +XA PREPARE 'trx_11'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_12'; +INSERT INTO t SET a=12; +XA END 'trx_12'; +XA PREPARE 'trx_12'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_13'; +INSERT INTO t SET a=13; +XA END 'trx_13'; +XA PREPARE 'trx_13'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_14'; +INSERT INTO t SET a=14; +XA END 'trx_14'; +XA PREPARE 'trx_14'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_15'; +INSERT INTO t SET a=15; +XA END 'trx_15'; +XA PREPARE 'trx_15'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_16'; +INSERT INTO t SET a=16; +XA END 'trx_16'; +XA PREPARE 'trx_16'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_17'; +INSERT INTO t SET a=17; +XA END 'trx_17'; +XA PREPARE 'trx_17'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_18'; +INSERT INTO t SET a=18; +XA END 'trx_18'; +XA PREPARE 'trx_18'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_19'; +INSERT INTO t SET a=19; +XA END 'trx_19'; +XA PREPARE 'trx_19'; +connection default; +KILL CONNECTION CONN_ID; +connection default; +XA ROLLBACK 'trx_0'; +XA ROLLBACK 'trx_1'; +XA ROLLBACK 'trx_2'; +XA ROLLBACK 'trx_3'; +XA ROLLBACK 'trx_4'; +XA COMMIT 'trx_5'; +XA COMMIT 'trx_6'; +XA COMMIT 'trx_7'; +XA COMMIT 'trx_8'; +XA COMMIT 'trx_9'; +# restart +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_0'; +INSERT INTO t SET a=0; +XA END 'new_trx_0'; +XA PREPARE 'new_trx_0'; +disconnect conn_restart_0; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_1'; +INSERT INTO t SET a=1; +XA END 'new_trx_1'; +XA PREPARE 'new_trx_1'; +disconnect conn_restart_1; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_2'; +INSERT INTO t SET a=2; +XA END 'new_trx_2'; +XA PREPARE 'new_trx_2'; +disconnect conn_restart_2; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_3'; +INSERT INTO t SET a=3; +XA END 'new_trx_3'; +XA PREPARE 'new_trx_3'; +disconnect conn_restart_3; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_4'; +INSERT INTO t SET a=4; +XA END 'new_trx_4'; +XA PREPARE 'new_trx_4'; +disconnect conn_restart_4; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_5'; +INSERT INTO t SET a=5; +XA END 'new_trx_5'; +XA PREPARE 'new_trx_5'; +disconnect conn_restart_5; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_6'; +INSERT INTO t SET a=6; +XA END 'new_trx_6'; +XA PREPARE 'new_trx_6'; +disconnect conn_restart_6; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_7'; +INSERT INTO t SET a=7; +XA END 'new_trx_7'; +XA PREPARE 'new_trx_7'; +disconnect conn_restart_7; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_8'; +INSERT INTO t SET a=8; +XA END 'new_trx_8'; +XA PREPARE 'new_trx_8'; +disconnect conn_restart_8; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_9'; +INSERT INTO t SET a=9; +XA END 'new_trx_9'; +XA PREPARE 'new_trx_9'; +disconnect conn_restart_9; +connection default; +connection default; +XA COMMIT 'new_trx_0'; +XA COMMIT 'new_trx_1'; +XA COMMIT 'new_trx_2'; +XA COMMIT 'new_trx_3'; +XA COMMIT 'new_trx_4'; +XA COMMIT 'new_trx_5'; +XA COMMIT 'new_trx_6'; +XA COMMIT 'new_trx_7'; +XA COMMIT 'new_trx_8'; +XA COMMIT 'new_trx_9'; +XA START 'trx_10'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_10'; +XA START 'trx_11'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_11'; +XA START 'trx_12'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_12'; +XA START 'trx_13'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_13'; +XA START 'trx_14'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_14'; +XA START 'trx_15'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_15'; +XA START 'trx_16'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_16'; +XA START 'trx_17'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_17'; +XA START 'trx_18'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_18'; +XA START 'trx_19'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_19'; +SELECT * FROM t; +a +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +disconnect conn2tmp; +disconnect conn3tmp; +disconnect conn2ro; +disconnect conn3ro; +disconnect conn2empty; +disconnect conn3empty; +connection default; +XA ROLLBACK 'trx_20'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn19; +connection default; +XA ROLLBACK 'trx_19'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn18; +connection default; +XA ROLLBACK 'trx_18'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn17; +connection default; +XA ROLLBACK 'trx_17'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn16; +connection default; +XA ROLLBACK 'trx_16'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn15; +connection default; +XA ROLLBACK 'trx_15'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn14; +connection default; +XA ROLLBACK 'trx_14'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn13; +connection default; +XA ROLLBACK 'trx_13'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn12; +connection default; +XA ROLLBACK 'trx_12'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn11; +connection default; +XA ROLLBACK 'trx_11'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn10; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx1tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx1tmp'; +XA PREPARE 'trx1tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx2tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx2tmp'; +XA PREPARE 'trx2tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@sql_log_bin = OFF; +CREATE TEMPORARY TABLE tmp1 (a int) ENGINE=innodb; +XA START 'trx3tmp'; +INSERT INTO tmp1 SET a=1; +XA END 'trx3tmp'; +XA PREPARE 'trx3tmp'; +connection default; +XA COMMIT 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA ROLLBACK 'trx1tmp'; +ERROR XAE04: XAER_NOTA: Unknown XID +XA START 'trx1tmp'; +ERROR XAE08: XAER_DUPID: The XID already exists +connection default; +*** 3 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1tmp; +disconnect conn1tmp; +connection default; +XA COMMIT 'trx1tmp'; +KILL connection CONN_ID; +XA COMMIT 'trx3tmp'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1ro'; +SELECT * from t ORDER BY a; +a +0 +1 +2 +3 +4 +5 +5 +6 +6 +7 +7 +8 +8 +9 +9 +10 +11 +12 +13 +14 +XA END 'trx1ro'; +XA PREPARE 'trx1ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2ro'; +SELECT * from t ORDER BY a; +a +0 +1 +2 +3 +4 +5 +5 +6 +6 +7 +7 +8 +8 +9 +9 +10 +11 +12 +13 +14 +XA END 'trx2ro'; +XA PREPARE 'trx2ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3ro'; +SELECT * from t ORDER BY a; +a +0 +1 +2 +3 +4 +5 +5 +6 +6 +7 +7 +8 +8 +9 +9 +10 +11 +12 +13 +14 +XA END 'trx3ro'; +XA PREPARE 'trx3ro'; +connection default; +*** 4 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1ro; +disconnect conn1ro; +connection default; +XA ROLLBACK 'trx1ro'; +KILL connection CONN_ID; +XA ROLLBACK 'trx3ro'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1empty'; +XA END 'trx1empty'; +XA PREPARE 'trx1empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx2empty'; +XA END 'trx2empty'; +XA PREPARE 'trx2empty'; +connect conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx3empty'; +XA END 'trx3empty'; +XA PREPARE 'trx3empty'; +connection default; +*** 5 prepared transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +1 LEN1 LEN2 TRX_N +connection conn1empty; +disconnect conn1empty; +connection default; +XA COMMIT 'trx1empty'; +KILL connection CONN_ID; +XA COMMIT 'trx3empty'; +connect conn1$type, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'trx1unprepared'; +INSERT INTO t set a=0; +XA END 'trx1unprepared'; +INSERT INTO t set a=0; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +XA START 'trx1unprepared'; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +disconnect conn1unprepared; +connection default; +XA COMMIT 'trx1unprepared'; +ERROR XAE04: XAER_NOTA: Unknown XID +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_0'; +INSERT INTO t SET a=0; +XA END 'trx_0'; +XA PREPARE 'trx_0'; +disconnect conn0; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_1'; +INSERT INTO t SET a=1; +XA END 'trx_1'; +XA PREPARE 'trx_1'; +disconnect conn1; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_2'; +INSERT INTO t SET a=2; +XA END 'trx_2'; +XA PREPARE 'trx_2'; +disconnect conn2; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_3'; +INSERT INTO t SET a=3; +XA END 'trx_3'; +XA PREPARE 'trx_3'; +disconnect conn3; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_4'; +INSERT INTO t SET a=4; +XA END 'trx_4'; +XA PREPARE 'trx_4'; +disconnect conn4; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_5'; +INSERT INTO t SET a=5; +XA END 'trx_5'; +XA PREPARE 'trx_5'; +disconnect conn5; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_6'; +INSERT INTO t SET a=6; +XA END 'trx_6'; +XA PREPARE 'trx_6'; +disconnect conn6; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_7'; +INSERT INTO t SET a=7; +XA END 'trx_7'; +XA PREPARE 'trx_7'; +disconnect conn7; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_8'; +INSERT INTO t SET a=8; +XA END 'trx_8'; +XA PREPARE 'trx_8'; +disconnect conn8; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_9'; +INSERT INTO t SET a=9; +XA END 'trx_9'; +XA PREPARE 'trx_9'; +disconnect conn9; +connection default; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_10'; +INSERT INTO t SET a=10; +XA END 'trx_10'; +XA PREPARE 'trx_10'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_11'; +INSERT INTO t SET a=11; +XA END 'trx_11'; +XA PREPARE 'trx_11'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_12'; +INSERT INTO t SET a=12; +XA END 'trx_12'; +XA PREPARE 'trx_12'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_13'; +INSERT INTO t SET a=13; +XA END 'trx_13'; +XA PREPARE 'trx_13'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_14'; +INSERT INTO t SET a=14; +XA END 'trx_14'; +XA PREPARE 'trx_14'; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_15'; +INSERT INTO t SET a=15; +XA END 'trx_15'; +XA PREPARE 'trx_15'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_16'; +INSERT INTO t SET a=16; +XA END 'trx_16'; +XA PREPARE 'trx_16'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_17'; +INSERT INTO t SET a=17; +XA END 'trx_17'; +XA PREPARE 'trx_17'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +XA START 'trx_18'; +INSERT INTO t SET a=18; +XA END 'trx_18'; +XA PREPARE 'trx_18'; +connection default; +KILL CONNECTION CONN_ID; +connect conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@binlog_format = STATEMENT; +SET @@binlog_format = ROW; +XA START 'trx_19'; +INSERT INTO t SET a=19; +XA END 'trx_19'; +XA PREPARE 'trx_19'; +connection default; +KILL CONNECTION CONN_ID; +connection default; +XA ROLLBACK 'trx_0'; +XA ROLLBACK 'trx_1'; +XA ROLLBACK 'trx_2'; +XA ROLLBACK 'trx_3'; +XA ROLLBACK 'trx_4'; +XA COMMIT 'trx_5'; +XA COMMIT 'trx_6'; +XA COMMIT 'trx_7'; +XA COMMIT 'trx_8'; +XA COMMIT 'trx_9'; +# Kill and restart +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_0'; +INSERT INTO t SET a=0; +XA END 'new_trx_0'; +XA PREPARE 'new_trx_0'; +disconnect conn_restart_0; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_1'; +INSERT INTO t SET a=1; +XA END 'new_trx_1'; +XA PREPARE 'new_trx_1'; +disconnect conn_restart_1; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_2'; +INSERT INTO t SET a=2; +XA END 'new_trx_2'; +XA PREPARE 'new_trx_2'; +disconnect conn_restart_2; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_3'; +INSERT INTO t SET a=3; +XA END 'new_trx_3'; +XA PREPARE 'new_trx_3'; +disconnect conn_restart_3; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_4'; +INSERT INTO t SET a=4; +XA END 'new_trx_4'; +XA PREPARE 'new_trx_4'; +disconnect conn_restart_4; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_5'; +INSERT INTO t SET a=5; +XA END 'new_trx_5'; +XA PREPARE 'new_trx_5'; +disconnect conn_restart_5; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_6'; +INSERT INTO t SET a=6; +XA END 'new_trx_6'; +XA PREPARE 'new_trx_6'; +disconnect conn_restart_6; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_7'; +INSERT INTO t SET a=7; +XA END 'new_trx_7'; +XA PREPARE 'new_trx_7'; +disconnect conn_restart_7; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_8'; +INSERT INTO t SET a=8; +XA END 'new_trx_8'; +XA PREPARE 'new_trx_8'; +disconnect conn_restart_8; +connection default; +connect conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'new_trx_9'; +INSERT INTO t SET a=9; +XA END 'new_trx_9'; +XA PREPARE 'new_trx_9'; +disconnect conn_restart_9; +connection default; +connection default; +XA COMMIT 'new_trx_0'; +XA COMMIT 'new_trx_1'; +XA COMMIT 'new_trx_2'; +XA COMMIT 'new_trx_3'; +XA COMMIT 'new_trx_4'; +XA COMMIT 'new_trx_5'; +XA COMMIT 'new_trx_6'; +XA COMMIT 'new_trx_7'; +XA COMMIT 'new_trx_8'; +XA COMMIT 'new_trx_9'; +XA START 'trx_10'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_10'; +XA START 'trx_11'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_11'; +XA START 'trx_12'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_12'; +XA START 'trx_13'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_13'; +XA START 'trx_14'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA COMMIT 'trx_14'; +XA START 'trx_15'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_15'; +XA START 'trx_16'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_16'; +XA START 'trx_17'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_17'; +XA START 'trx_18'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_18'; +XA START 'trx_19'; +ERROR XAE08: XAER_DUPID: The XID already exists +XA ROLLBACK 'trx_19'; +SELECT * FROM t; +a +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +disconnect conn2tmp; +disconnect conn3tmp; +disconnect conn2ro; +disconnect conn3ro; +disconnect conn2empty; +disconnect conn3empty; +connection default; +XA ROLLBACK 'trx_20'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn19; +connection default; +XA ROLLBACK 'trx_19'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn18; +connection default; +XA ROLLBACK 'trx_18'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn17; +connection default; +XA ROLLBACK 'trx_17'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn16; +connection default; +XA ROLLBACK 'trx_16'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn15; +connection default; +XA ROLLBACK 'trx_15'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn14; +connection default; +XA ROLLBACK 'trx_14'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn13; +connection default; +XA ROLLBACK 'trx_13'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn12; +connection default; +XA ROLLBACK 'trx_12'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn11; +connection default; +XA ROLLBACK 'trx_11'; +ERROR XAE04: XAER_NOTA: Unknown XID +disconnect conn10; +connection default; +XA START 'one_phase_trx_0'; +INSERT INTO t SET a=0; +XA END 'one_phase_trx_0'; +XA COMMIT 'one_phase_trx_0' ONE PHASE; +XA START 'one_phase_trx_1'; +INSERT INTO t SET a=1; +XA END 'one_phase_trx_1'; +XA COMMIT 'one_phase_trx_1' ONE PHASE; +XA START 'one_phase_trx_2'; +INSERT INTO t SET a=2; +XA END 'one_phase_trx_2'; +XA COMMIT 'one_phase_trx_2' ONE PHASE; +XA START 'one_phase_trx_3'; +INSERT INTO t SET a=3; +XA END 'one_phase_trx_3'; +XA COMMIT 'one_phase_trx_3' ONE PHASE; +XA START 'one_phase_trx_4'; +INSERT INTO t SET a=4; +XA END 'one_phase_trx_4'; +XA COMMIT 'one_phase_trx_4' ONE PHASE; +SELECT SUM(a) FROM t; +SUM(a) +290 +DROP TABLE t; +DROP VIEW v_processlist; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_processlist` AS SELECT * FROM performance_schema.threads where type = 'FOREGROUND' +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'Found 10 prepared XA transactions' COLLATE 'latin1_swedish_ci')) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t (a INT) ENGINE=innodb +master-bin.000001 # Gtid # # XA START X'7472785f30',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=0 +master-bin.000001 # Query # # XA END X'7472785f30',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f30',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f31',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=1 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f31',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f31',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f32',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=2 +master-bin.000001 # Query # # XA END X'7472785f32',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f32',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f33',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=3 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f33',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f33',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f34',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=4 +master-bin.000001 # Query # # XA END X'7472785f34',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f34',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f35',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=5 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f35',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f35',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f36',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=6 +master-bin.000001 # Query # # XA END X'7472785f36',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f36',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f37',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=7 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f37',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f37',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f38',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=8 +master-bin.000001 # Query # # XA END X'7472785f38',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f38',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f39',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=9 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f39',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f39',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3130',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=10 +master-bin.000001 # Query # # XA END X'7472785f3130',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3130',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3131',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=11 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f3131',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3131',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3132',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=12 +master-bin.000001 # Query # # XA END X'7472785f3132',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3132',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3133',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=13 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f3133',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3133',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3134',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=14 +master-bin.000001 # Query # # XA END X'7472785f3134',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3134',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3135',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=15 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f3135',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3135',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3136',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=16 +master-bin.000001 # Query # # XA END X'7472785f3136',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3136',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3137',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=17 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f3137',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3137',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3138',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t SET a=18 +master-bin.000001 # Query # # XA END X'7472785f3138',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3138',X'',1 +master-bin.000001 # Gtid # # XA START X'7472785f3139',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t SET a=19 +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'7472785f3139',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'7472785f3139',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA ROLLBACK X'7472785f30',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA ROLLBACK X'7472785f31',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA ROLLBACK X'7472785f32',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA ROLLBACK X'7472785f33',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA ROLLBACK X'7472785f34',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'7472785f35',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'7472785f36',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'7472785f37',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'7472785f38',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'7472785f39',X'',1 +master-bin.000001 # Stop # # +All transactions must be completed, to empty-list the following: +XA RECOVER; +formatID gtrid_length bqual_length data +XA RECOVER; +formatID gtrid_length bqual_length data diff --git a/mysql-test/suite/binlog/t/binlog_xa_checkpoint.test b/mysql-test/suite/binlog/t/binlog_xa_checkpoint.test new file mode 100644 index 00000000000..b208d02cf2a --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_xa_checkpoint.test @@ -0,0 +1,57 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_binlog_format_row.inc + +RESET MASTER; + +CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb; + +# Test that +# 1. XA PREPARE is binlogged before the XA has been prepared in Engine +# 2. While XA PREPARE already binlogged in an old binlog file which has been rotated, +# Binlog checkpoint is not generated for the latest log until +# XA PREPARE returns, e.g OK to the client. + + +# con1 will hang before doing commit checkpoint, blocking RESET MASTER. +connect(con1,localhost,root,,); +SET DEBUG_SYNC= "at_unlog_xa_prepare SIGNAL con1_ready WAIT_FOR con1_go"; +XA START '1'; +INSERT INTO t1 SET a=1; +XA END '1'; +--send XA PREPARE '1'; + + +connection default; +SET DEBUG_SYNC= "now WAIT_FOR con1_ready"; +FLUSH LOGS; +FLUSH LOGS; +FLUSH LOGS; + +--source include/show_binary_logs.inc +--let $binlog_file= master-bin.000004 +--let $binlog_start= 4 +--source include/show_binlog_events.inc + +SET DEBUG_SYNC= "now SIGNAL con1_go"; + +connection con1; +reap; +--echo *** master-bin.000004 checkpoint must show up now *** +--source include/wait_for_binlog_checkpoint.inc + +# Todo: think about the error code returned, move to an appropriate test, or remove +# connection default; +#--error 1399 +# DROP TABLE t1; + +connection con1; +XA ROLLBACK '1'; +SET debug_sync = 'reset'; + +# Clean up. +connection default; + +DROP TABLE t1; +SET debug_sync = 'reset'; diff --git a/mysql-test/suite/binlog/t/binlog_xa_prepared.inc b/mysql-test/suite/binlog/t/binlog_xa_prepared.inc new file mode 100644 index 00000000000..b6306791cf4 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_xa_prepared.inc @@ -0,0 +1,102 @@ +--source include/have_innodb.inc +--source include/have_perfschema.inc +# +# The test verifies binlogging of XA transaction and state of prepared XA +# as far as binlog is concerned. +# +# The prepared XA transactions can be disconnected from the client, +# discovered from another connection and commited or rolled back +# later. They also survive the server restart. The test runs two +# loops each consisting of prepared XA:s generation, their +# manipulation and a server restart followed with survived XA:s +# completion. +# +# Prepared XA can't get available to an external connection +# until connection that either leaves actively or is killed +# has completed a necessary part of its cleanup. +# Selecting from P_S.threads provides a method to learn that. +# +# Total number of connection each performing one insert into table +--let $conn_number=20 +# Number of rollbacks and commits from either side of the server restart +--let $rollback_number=5 +--let $commit_number=5 +# Number of transactions that are terminated before server restarts +--let $term_number=`SELECT $rollback_number + $commit_number` +# Instead of disconnect make some connections killed when their +# transactions got prepared. +--let $killed_number=5 +# make some connections disconnected by shutdown rather than actively +--let $server_disconn_number=5 +--let $prepared_at_server_restart = `SELECT $conn_number - $term_number` +# number a "warmup" connection after server restart, they all commit +--let $post_restart_conn_number=10 + +# Counter to be used in GTID consistency check. +# It's incremented per each non-XA transaction commit. +# Local to this file variable to control one-phase commit loop +--let $one_phase_number = 5 + +--connection default + +# Remove possibly preceeding binlogs and clear initialization time +# GTID executed info. In the following all transactions are counted +# to conduct verification at the end of the test. +if (`SELECT @@global.log_bin`) +{ + RESET MASTER; +} + +# Disconected and follower threads need synchronization +CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND'; + +--eval call mtr.add_suppression("Found $prepared_at_server_restart prepared XA transactions") + +CREATE TABLE t (a INT) ENGINE=innodb; + +# Counter is incremented at the end of post restart to +# reflect number of loops done in correctness computation. +--let $restart_number = 0 +--let $how_to_restart=restart_mysqld.inc +--source suite/binlog/include/binlog_xa_prepared_do_and_restart.inc + +--let $how_to_restart=kill_and_restart_mysqld.inc +--source suite/binlog/include/binlog_xa_prepared_do_and_restart.inc + +--connection default + +# Few xs that commit in one phase, not subject to the server restart +# nor reconnect. +# This piece of test is related to mysqlbinlog recovery examine below. +--let $k = 0 +while ($k < $one_phase_number) +{ + --eval XA START 'one_phase_trx_$k' + --eval INSERT INTO t SET a=$k + --eval XA END 'one_phase_trx_$k' + --eval XA COMMIT 'one_phase_trx_$k' ONE PHASE + + --inc $k +} + +SELECT SUM(a) FROM t; +DROP TABLE t; +DROP VIEW v_processlist; + +let $outfile= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql; +if (`SELECT @@global.log_bin`) +{ + # Recording proper samples of binlogged prepared XA:s + --source include/show_binlog_events.inc + --exec $MYSQL_BINLOG -R --to-last-log master-bin.000001 > $outfile +} + +--echo All transactions must be completed, to empty-list the following: +XA RECOVER; + +if (`SELECT @@global.log_bin`) +{ + --exec $MYSQL test < $outfile + --remove_file $outfile + XA RECOVER; +} diff --git a/mysql-test/suite/binlog/t/binlog_xa_prepared_disconnect.test b/mysql-test/suite/binlog/t/binlog_xa_prepared_disconnect.test new file mode 100644 index 00000000000..2a3184030cf --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_xa_prepared_disconnect.test @@ -0,0 +1,11 @@ +############################################################################### +# Bug#12161 Xa recovery and client disconnection +# Testing new server options and binary logging prepared XA transaction. +############################################################################### + +# +# MIXED mode is chosen because formats are varied inside the sourced tests. +# +--source include/have_binlog_format_mixed.inc + +--source suite/binlog/t/binlog_xa_prepared.inc diff --git a/mysql-test/suite/rpl/include/rpl_xa_mixed_engines.inc b/mysql-test/suite/rpl/include/rpl_xa_mixed_engines.inc new file mode 100644 index 00000000000..0707a04090a --- /dev/null +++ b/mysql-test/suite/rpl/include/rpl_xa_mixed_engines.inc @@ -0,0 +1,183 @@ +# +# The test file is invoked from rpl.rpl_xa_survive_disconnect_mixed_engines +# +# The test file is orginized as three sections: setup, run and cleanup. +# The main logics is resided in the run section which generates +# three types of XA transaction: two kinds of mixed and one on non-transactional +# table. +# +# param $command one of three of: 'setup', 'run' or 'cleanup' +# param $xa_terminate how to conclude: 'XA COMMIT' or 'XA ROLLBACK' +# param $one_phase 'one_phase' can be opted with XA COMMIT above +# param $xa_prepare_opt '1' or empty can be opted to test with and without XA PREPARE +# param $xid arbitrary name for xa trx, defaults to 'xa_trx' +# Note '' is merely to underline, not a part of the value. +# + +if ($command == setup) +{ + # Test randomizes the following variable's value: + SET @@session.binlog_direct_non_transactional_updates := if(floor(rand()*10)%2,'ON','OFF'); + CREATE TABLE t (a INT) ENGINE=innodb; + CREATE TABLE tm (a INT) ENGINE=myisam; +} +if (!$xid) +{ + --let $xid=xa_trx +} +if ($command == run) +{ + ## Non-temporary table cases + # Non transactional table goes first + --eval XA START '$xid' + --disable_warnings + INSERT INTO tm VALUES (1); + INSERT INTO t VALUES (1); + --enable_warnings + --eval XA END '$xid' + if ($xa_prepare_opt) + { + --eval XA PREPARE '$xid' + } + --eval $xa_terminate '$xid' $one_phase + + # Transactional table goes first + --eval XA START '$xid' + --disable_warnings + INSERT INTO t VALUES (2); + INSERT INTO tm VALUES (2); + --enable_warnings + --eval XA END '$xid' + if ($xa_prepare_opt) + { + --eval XA PREPARE '$xid' + } + --eval $xa_terminate '$xid' $one_phase + + # The pure non-transactional table + --eval XA START '$xid' + --disable_warnings + INSERT INTO tm VALUES (3); + --enable_warnings + --eval XA END '$xid' + if ($xa_prepare_opt) + { + --eval XA PREPARE '$xid' + } + --eval $xa_terminate '$xid' $one_phase + + ## Temporary tables + # create outside xa use at the tail + CREATE TEMPORARY TABLE tmp_i LIKE t; + CREATE TEMPORARY TABLE tmp_m LIKE tm; + --eval XA START '$xid' + --disable_warnings + INSERT INTO t VALUES (4); + INSERT INTO tm VALUES (4); + INSERT INTO tmp_i VALUES (4); + INSERT INTO tmp_m VALUES (4); + INSERT INTO t SELECT * FROM tmp_i; + INSERT INTO tm SELECT * FROM tmp_m; + --enable_warnings + --eval XA END '$xid' + if ($xa_prepare_opt) + { + --eval XA PREPARE '$xid' + } + --eval $xa_terminate '$xid' $one_phase + + # temporary tables at the head + --eval XA START '$xid' + --disable_warnings + INSERT INTO tmp_i VALUES (5); + INSERT INTO tmp_m VALUES (5); + INSERT INTO t SELECT * FROM tmp_i; + INSERT INTO tm SELECT * FROM tmp_m; + INSERT INTO t VALUES (5); + INSERT INTO tm VALUES (5); + --enable_warnings + --eval XA END '$xid' + if ($xa_prepare_opt) + { + --eval XA PREPARE '$xid' + } + --eval $xa_terminate '$xid' $one_phase + + # create inside xa use at the tail + DROP TEMPORARY TABLE tmp_i; + DROP TEMPORARY TABLE tmp_m; + + --eval XA START '$xid' + --disable_warnings + INSERT INTO t VALUES (6); + INSERT INTO tm VALUES (6); + CREATE TEMPORARY TABLE tmp_i LIKE t; + CREATE TEMPORARY TABLE tmp_m LIKE tm; + INSERT INTO tmp_i VALUES (6); + INSERT INTO tmp_m VALUES (6); + INSERT INTO t SELECT * FROM tmp_i; + INSERT INTO tm SELECT * FROM tmp_m; + --enable_warnings + --eval XA END '$xid' + if ($xa_prepare_opt) + { + --eval XA PREPARE '$xid' + } + --eval $xa_terminate '$xid' $one_phase + + # use at the head + DROP TEMPORARY TABLE tmp_i; + DROP TEMPORARY TABLE tmp_m; + --eval XA START '$xid' + --disable_warnings + CREATE TEMPORARY TABLE tmp_i LIKE t; + CREATE TEMPORARY TABLE tmp_m LIKE tm; + INSERT INTO tmp_i VALUES (7); + INSERT INTO tmp_m VALUES (7); + INSERT INTO t SELECT * FROM tmp_i; + INSERT INTO tm SELECT * FROM tmp_m; + INSERT INTO t VALUES (7); + INSERT INTO tm VALUES (7); + --enable_warnings + --eval XA END '$xid' + if ($xa_prepare_opt) + { + --eval XA PREPARE '$xid' + } + --eval $xa_terminate '$xid' $one_phase + + # use at the tail and drop + --eval XA START '$xid' + --disable_warnings + INSERT INTO t VALUES (8); + INSERT INTO tm VALUES (8); + INSERT INTO tmp_i VALUES (8); + INSERT INTO tmp_m VALUES (8); + INSERT INTO t SELECT * FROM tmp_i; + INSERT INTO tm SELECT * FROM tmp_m; + DROP TEMPORARY TABLE tmp_i; + DROP TEMPORARY TABLE tmp_m; + --enable_warnings + --eval XA END '$xid' + if ($xa_prepare_opt) + { + --eval XA PREPARE '$xid' + } + --eval $xa_terminate '$xid' $one_phase + + ## Ineffective transactional table operation case + + --eval XA START '$xid' + UPDATE t SET a = 99 where a = -1; + --eval XA END '$xid' + if ($xa_prepare_opt) + { + --eval XA PREPARE '$xid' + } + --eval $xa_terminate '$xid' $one_phase +} + +if ($command == cleanup) +{ + DROP TABLE t, tm; +} diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic_xa.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic_xa.result new file mode 100644 index 00000000000..4136f1885db --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic_xa.result @@ -0,0 +1,51 @@ +include/master-slave.inc +[connection master] +call mtr.add_suppression("Deadlock found when trying to get lock; try restarting transaction"); +call mtr.add_suppression("WSREP: handlerton rollback failed"); +CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND'; +connection master; +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; +connection slave; +include/stop_slave.inc +SET @old_parallel_threads = @@GLOBAL.slave_parallel_threads; +SET @@global.slave_parallel_threads = 7; +SET @old_parallel_mode = @@GLOBAL.slave_parallel_mode; +SET @@global.slave_parallel_mode ='optimistic'; +SET @old_gtid_cleanup_batch_size = @@GLOBAL.gtid_cleanup_batch_size; +SET @@global.gtid_cleanup_batch_size = 1000000; +CHANGE MASTER TO master_use_gtid=slave_pos; +connection master; +CREATE TABLE t0 (a int, b INT) ENGINE=InnoDB; +CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 0); +include/save_master_gtid.inc +connection slave; +include/start_slave.inc +include/sync_with_master_gtid.inc +include/stop_slave.inc +connection master; +include/save_master_gtid.inc +connection slave; +include/start_slave.inc +include/sync_with_master_gtid.inc +include/diff_tables.inc [master:t0, slave:t0] +include/diff_tables.inc [master:t1, slave:t1] +connection slave; +include/stop_slave.inc +set global log_warnings=default; +SET GLOBAL slave_parallel_mode=@old_parallel_mode; +SET GLOBAL slave_parallel_threads=@old_parallel_threads; +include/start_slave.inc +connection master; +DROP VIEW v_processlist; +DROP TABLE t0, t1; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +SELECT COUNT(*) <= 5*@@GLOBAL.gtid_cleanup_batch_size +FROM mysql.gtid_slave_pos; +COUNT(*) <= 5*@@GLOBAL.gtid_cleanup_batch_size +1 +SET GLOBAL gtid_cleanup_batch_size= @old_gtid_cleanup_batch_size; +connection master; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic_xa_lsu_off.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic_xa_lsu_off.result new file mode 100644 index 00000000000..4136f1885db --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic_xa_lsu_off.result @@ -0,0 +1,51 @@ +include/master-slave.inc +[connection master] +call mtr.add_suppression("Deadlock found when trying to get lock; try restarting transaction"); +call mtr.add_suppression("WSREP: handlerton rollback failed"); +CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND'; +connection master; +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; +connection slave; +include/stop_slave.inc +SET @old_parallel_threads = @@GLOBAL.slave_parallel_threads; +SET @@global.slave_parallel_threads = 7; +SET @old_parallel_mode = @@GLOBAL.slave_parallel_mode; +SET @@global.slave_parallel_mode ='optimistic'; +SET @old_gtid_cleanup_batch_size = @@GLOBAL.gtid_cleanup_batch_size; +SET @@global.gtid_cleanup_batch_size = 1000000; +CHANGE MASTER TO master_use_gtid=slave_pos; +connection master; +CREATE TABLE t0 (a int, b INT) ENGINE=InnoDB; +CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 0); +include/save_master_gtid.inc +connection slave; +include/start_slave.inc +include/sync_with_master_gtid.inc +include/stop_slave.inc +connection master; +include/save_master_gtid.inc +connection slave; +include/start_slave.inc +include/sync_with_master_gtid.inc +include/diff_tables.inc [master:t0, slave:t0] +include/diff_tables.inc [master:t1, slave:t1] +connection slave; +include/stop_slave.inc +set global log_warnings=default; +SET GLOBAL slave_parallel_mode=@old_parallel_mode; +SET GLOBAL slave_parallel_threads=@old_parallel_threads; +include/start_slave.inc +connection master; +DROP VIEW v_processlist; +DROP TABLE t0, t1; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +SELECT COUNT(*) <= 5*@@GLOBAL.gtid_cleanup_batch_size +FROM mysql.gtid_slave_pos; +COUNT(*) <= 5*@@GLOBAL.gtid_cleanup_batch_size +1 +SET GLOBAL gtid_cleanup_batch_size= @old_gtid_cleanup_batch_size; +connection master; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_parallel_xa_same_xid.result b/mysql-test/suite/rpl/r/rpl_parallel_xa_same_xid.result new file mode 100644 index 00000000000..03fe5157623 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_parallel_xa_same_xid.result @@ -0,0 +1,23 @@ +include/master-slave.inc +[connection master] +connection slave; +call mtr.add_suppression("WSREP: handlerton rollback failed"); +include/stop_slave.inc +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; +SET @old_parallel_threads = @@GLOBAL.slave_parallel_threads; +SET @old_parallel_mode = @@GLOBAL.slave_parallel_mode; +SET @@global.slave_parallel_mode ='optimistic'; +include/start_slave.inc +connection master; +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; +CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b INT) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +include/diff_tables.inc [master:t1, slave:t1] +connection slave; +include/stop_slave.inc +SET GLOBAL slave_parallel_threads=@old_parallel_threads; +SET GLOBAL slave_parallel_mode=@old_parallel_mode; +include/start_slave.inc +connection master; +DROP TABLE t1, t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_temporary_errors.result b/mysql-test/suite/rpl/r/rpl_temporary_errors.result index 8654fe218dc..c126871e460 100644 --- a/mysql-test/suite/rpl/r/rpl_temporary_errors.result +++ b/mysql-test/suite/rpl/r/rpl_temporary_errors.result @@ -3,7 +3,7 @@ include/master-slave.inc call mtr.add_suppression("Deadlock found"); call mtr.add_suppression("Can't find record in 't.'"); connection master; -CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=innodb; INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); connection slave; SHOW STATUS LIKE 'Slave_retried_transactions'; @@ -11,34 +11,67 @@ Variable_name Value Slave_retried_transactions 0 set @@global.slave_exec_mode= 'IDEMPOTENT'; UPDATE t1 SET a = 5, b = 47 WHERE a = 1; -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b -5 47 2 2 3 3 4 4 +5 47 connection master; UPDATE t1 SET a = 5, b = 5 WHERE a = 1; -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b -5 5 2 2 3 3 4 4 +5 5 connection slave; set @@global.slave_exec_mode= default; SHOW STATUS LIKE 'Slave_retried_transactions'; Variable_name Value Slave_retried_transactions 0 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b -5 47 2 2 3 3 4 4 +5 47 include/check_slave_is_running.inc connection slave; call mtr.add_suppression("Slave SQL.*Could not execute Update_rows event on table test.t1"); +call mtr.add_suppression("Slave SQL for channel '': worker thread retried transaction"); +call mtr.add_suppression("The slave coordinator and worker threads are stopped"); +connection slave; +set @save_innodb_lock_wait_timeout=@@global.innodb_lock_wait_timeout; +set @save_slave_transaction_retries=@@global.slave_transaction_retries; +set @@global.innodb_lock_wait_timeout=1; +set @@global.slave_transaction_retries=2; +include/restart_slave.inc +connection slave1; +BEGIN; +INSERT INTO t1 SET a = 6, b = 7; +connection master; +INSERT INTO t1 SET a = 99, b = 99; +XA START 'xa1'; +INSERT INTO t1 SET a = 6, b = 6; +XA END 'xa1'; +XA PREPARE 'xa1'; +connection slave; +include/wait_for_slave_sql_error.inc [errno=1213,1205] +set @@global.innodb_lock_wait_timeout=1; +set @@global.slave_transaction_retries=100; +include/restart_slave.inc +Warnings: +Note 1255 Slave already has been stopped +connection slave1; +ROLLBACK; +connection master; +XA COMMIT 'xa1'; +include/sync_slave_sql_with_master.inc +connection slave; +include/assert.inc [XA transaction record must be in the table] +set @@global.innodb_lock_wait_timeout=@save_innodb_lock_wait_timeout; +set @@global.slave_transaction_retries= @save_slave_transaction_retries; connection master; DROP TABLE t1; connection slave; diff --git a/mysql-test/suite/rpl/r/rpl_xa.result b/mysql-test/suite/rpl/r/rpl_xa.result new file mode 100644 index 00000000000..ed07edd8fb5 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_xa.result @@ -0,0 +1,224 @@ +include/master-slave.inc +[connection master] +connection master; +create table t1 (a int, b int) engine=InnoDB; +insert into t1 values(0, 0); +xa start 't'; +insert into t1 values(1, 2); +xa end 't'; +xa prepare 't'; +xa commit 't'; +connection slave; +include/diff_tables.inc [master:t1, slave:t1] +connection master; +xa start 't'; +insert into t1 values(3, 4); +xa end 't'; +xa prepare 't'; +xa rollback 't'; +connection slave; +include/diff_tables.inc [master:t1, slave:t1] +connection master; +SET pseudo_slave_mode=1; +create table t2 (a int) engine=InnoDB; +xa start 't'; +insert into t1 values (5, 6); +xa end 't'; +xa prepare 't'; +xa start 's'; +insert into t2 values (0); +xa end 's'; +xa prepare 's'; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +xa recover; +formatID gtrid_length bqual_length data +1 1 0 t +1 1 0 s +connection master; +xa commit 't'; +xa commit 's'; +SET pseudo_slave_mode=0; +connection slave; +include/diff_tables.inc [master:t1, slave:t1] +include/diff_tables.inc [master:t2, slave:t2] +connection master; +*** At the start of read-only section gtid list is: +flush logs; +show binlog events in 'master-bin.000002' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000002 # Gtid_list 1 # [0-1-11] +set @query1="select 1"; +set @query2="select count(*) into @s2 from t1"; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'ro_2'; +select count(*) into @s2 from t1; +xa end 'ro_2'; +xa prepare 'ro_2';; +disconnect master_ro_2; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'ro_1'; +select 1; +1 +1 +xa end 'ro_1'; +xa prepare 'ro_1';; +disconnect master_ro_1; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'ro_2'; +select count(*) into @s2 from t1; +xa end 'ro_2'; +xa prepare 'ro_2';; +disconnect master_ro_2; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'ro_1'; +select 1; +1 +1 +xa end 'ro_1'; +xa prepare 'ro_1';; +disconnect master_ro_1; +*** 2 prepared xa:s must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +1 4 0 ro_2 +1 4 0 ro_1 +*** Zero prepared xa:s must be in the list: +xa recover; +formatID gtrid_length bqual_length data +*** At the end of read-only section gtid list has 0 more compare with previous check: +flush logs; +show binlog events in 'master-bin.000003' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000003 # Gtid_list 1 # [0-1-11] +create database test_ign; +set @@sql_log_bin = 0; +create table test_ign.t (a int) engine=InnoDB; +set @@sql_log_bin = 1; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'rw_no_binlog'; +insert into test_ign.t set a=1; +xa end 'rw_no_binlog'; +xa prepare 'rw_no_binlog';; +disconnect master_rw_no_binlog; +*** rw_no_binlog must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +1 12 0 rw_no_binlog +*** Zero must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +*** At the end of --binlog-ignore-db section gtid list has 2 more: +flush logs; +show binlog events in 'master-bin.000004' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000004 # Gtid_list 1 # [0-1-13] +connection master; +create table t3 (a int) engine=innodb; +*** the disconnected prepare case +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +set @@binlog_format=statement; +xa start 'rw_binlog_only'; +delete from t3; +xa end 'rw_binlog_only'; +xa prepare 'rw_binlog_only'; +disconnect master_rw_binlog_only; +connection master; +*** rw_binlog_only must be in the list: +xa recover; +formatID gtrid_length bqual_length data +1 14 0 rw_binlog_only +*** Zero must be in the list: +xa recover; +formatID gtrid_length bqual_length data +*** the same connection complete case. +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +set @@binlog_format=statement; +xa start 'rw_binlog_only'; +delete from t3; +xa end 'rw_binlog_only'; +xa prepare 'rw_binlog_only'; +*** rw_binlog_only must be in the list: +xa recover; +formatID gtrid_length bqual_length data +1 14 0 rw_binlog_only +disconnect master_rw_binlog_only; +*** Zero must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +*** At the end of ineffective in engine section gtid list has 5 more: +flush logs; +show binlog events in 'master-bin.000005' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000005 # Gtid_list 1 # [0-1-18] +create table tm (a int) engine=myisam; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'rw_myisam'; +insert into tm set a=1; +xa end 'rw_myisam'; +xa prepare 'rw_myisam';; +disconnect master_rw_myisam; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'rw_myisam'; +insert into tm set a=1; +xa end 'rw_myisam'; +xa prepare 'rw_myisam';; +disconnect master_rw_myisam; +*** rw_myisam prepared must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +1 9 0 rw_myisam +*** Zero prepared xa:s must be in the list: +xa recover; +formatID gtrid_length bqual_length data +*** At the end of MyISAM "xa" section gtid list has 7 more compare with previous check: +flush logs; +show binlog events in 'master-bin.000006' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000006 # Gtid_list 1 # [0-1-25] +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +set @@session.sql_log_bin = OFF; +xa start 'skip_binlog'; +insert into t2 values(1); +xa end 'skip_binlog'; +xa prepare 'skip_binlog'; +disconnect master_skip_binlog; +*** skip_binlog must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +1 11 0 skip_binlog +connection master; +call mtr.add_suppression("Slave: XAER_NOTA: Unknown XID"); +xa rollback 'skip_binlog'; +*** Zero must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +*** At the end of --binlog-ignore-db section gtid list has 2 more: +flush logs; +show binlog events in 'master-bin.000007' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000007 # Gtid_list 1 # [0-1-27] +include/save_master_gtid.inc +connection slave; +include/wait_for_slave_sql_error.inc [errno=1397] +set @@global.sql_slave_skip_counter= 1; +include/start_slave.inc +connection master; +drop database test_ign; +drop table t1, t2, t3, tm; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_xa_gap_lock.result b/mysql-test/suite/rpl/r/rpl_xa_gap_lock.result new file mode 100644 index 00000000000..cb760abe2d2 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_xa_gap_lock.result @@ -0,0 +1,44 @@ +include/master-slave.inc +[connection master] +connection slave; +SET @saved_innodb_limit_optimistic_insert_debug = @@GLOBAL.innodb_limit_optimistic_insert_debug; +SET @@GLOBAL.innodb_limit_optimistic_insert_debug = 2; +connection master; +CREATE TABLE t1 ( +c1 INT NOT NULL, +KEY(c1) +) ENGINE=InnoDB; +CREATE TABLE t2 ( +c1 INT NOT NULL, +FOREIGN KEY(c1) REFERENCES t1(c1) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (3), (4); +connection master1; +XA START 'XA1'; +INSERT INTO t1 values(2); +XA END 'XA1'; +connection master; +XA START 'XA2'; +INSERT INTO t2 values(3); +XA END 'XA2'; +XA PREPARE 'XA2'; +connection master1; +XA PREPARE 'XA1'; +XA COMMIT 'XA1'; +connection master; +XA COMMIT 'XA2'; +include/sync_slave_sql_with_master.inc +include/stop_slave.inc +DROP TABLE t2, t1; +RESET SLAVE; +RESET MASTER; +connection master; +Restore binary log from the master into the slave +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +DROP TABLE t2, t1; +connection slave; +CHANGE MASTER TO MASTER_LOG_FILE='LOG_FILE', MASTER_LOG_POS=LOG_POS; +SET @@GLOBAL.innodb_limit_optimistic_insert_debug = @saved_innodb_limit_optimistic_insert_debug; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result b/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result new file mode 100644 index 00000000000..3826bf32f32 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result @@ -0,0 +1,240 @@ +include/master-slave.inc +[connection master] +connection slave; +call mtr.add_suppression("The automatically created table.*name may not be entirely in lowercase"); +include/stop_slave.inc +CHANGE MASTER TO master_use_gtid=slave_pos; +SET @@global.gtid_pos_auto_engines="innodb"; +include/start_slave.inc +connection master; +create table t1 (a int, b int) engine=InnoDB; +insert into t1 values(0, 0); +xa start 't'; +insert into t1 values(1, 2); +xa end 't'; +xa prepare 't'; +xa commit 't'; +connection slave; +include/diff_tables.inc [master:t1, slave:t1] +connection master; +xa start 't'; +insert into t1 values(3, 4); +xa end 't'; +xa prepare 't'; +xa rollback 't'; +connection slave; +include/diff_tables.inc [master:t1, slave:t1] +connection master; +SET pseudo_slave_mode=1; +create table t2 (a int) engine=InnoDB; +xa start 't'; +insert into t1 values (5, 6); +xa end 't'; +xa prepare 't'; +xa start 's'; +insert into t2 values (0); +xa end 's'; +xa prepare 's'; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +SELECT @@global.gtid_slave_pos = CONCAT(domain_id,"-",server_id,"-",seq_no) FROM mysql.gtid_slave_pos WHERE seq_no = (SELECT DISTINCT max(seq_no) FROM mysql.gtid_slave_pos); +@@global.gtid_slave_pos = CONCAT(domain_id,"-",server_id,"-",seq_no) +1 +xa recover; +formatID gtrid_length bqual_length data +1 1 0 t +1 1 0 s +connection master; +xa commit 't'; +xa commit 's'; +SET pseudo_slave_mode=0; +connection slave; +include/diff_tables.inc [master:t1, slave:t1] +include/diff_tables.inc [master:t2, slave:t2] +connection master; +*** At the start of read-only section gtid list is: +flush logs; +show binlog events in 'master-bin.000002' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000002 # Gtid_list 1 # [0-1-11] +set @query1="select 1"; +set @query2="select count(*) into @s2 from t1"; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'ro_2'; +select count(*) into @s2 from t1; +xa end 'ro_2'; +xa prepare 'ro_2';; +disconnect master_ro_2; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'ro_1'; +select 1; +1 +1 +xa end 'ro_1'; +xa prepare 'ro_1';; +disconnect master_ro_1; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'ro_2'; +select count(*) into @s2 from t1; +xa end 'ro_2'; +xa prepare 'ro_2';; +disconnect master_ro_2; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'ro_1'; +select 1; +1 +1 +xa end 'ro_1'; +xa prepare 'ro_1';; +disconnect master_ro_1; +*** 2 prepared xa:s must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +1 4 0 ro_2 +1 4 0 ro_1 +*** Zero prepared xa:s must be in the list: +xa recover; +formatID gtrid_length bqual_length data +*** At the end of read-only section gtid list has 0 more compare with previous check: +flush logs; +show binlog events in 'master-bin.000003' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000003 # Gtid_list 1 # [0-1-11] +create database test_ign; +set @@sql_log_bin = 0; +create table test_ign.t (a int) engine=InnoDB; +set @@sql_log_bin = 1; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'rw_no_binlog'; +insert into test_ign.t set a=1; +xa end 'rw_no_binlog'; +xa prepare 'rw_no_binlog';; +disconnect master_rw_no_binlog; +*** rw_no_binlog must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +1 12 0 rw_no_binlog +*** Zero must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +*** At the end of --binlog-ignore-db section gtid list has 2 more: +flush logs; +show binlog events in 'master-bin.000004' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000004 # Gtid_list 1 # [0-1-13] +connection master; +create table t3 (a int) engine=innodb; +*** the disconnected prepare case +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +set @@binlog_format=statement; +xa start 'rw_binlog_only'; +delete from t3; +xa end 'rw_binlog_only'; +xa prepare 'rw_binlog_only'; +disconnect master_rw_binlog_only; +connection master; +*** rw_binlog_only must be in the list: +xa recover; +formatID gtrid_length bqual_length data +1 14 0 rw_binlog_only +*** Zero must be in the list: +xa recover; +formatID gtrid_length bqual_length data +*** the same connection complete case. +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +set @@binlog_format=statement; +xa start 'rw_binlog_only'; +delete from t3; +xa end 'rw_binlog_only'; +xa prepare 'rw_binlog_only'; +*** rw_binlog_only must be in the list: +xa recover; +formatID gtrid_length bqual_length data +1 14 0 rw_binlog_only +disconnect master_rw_binlog_only; +*** Zero must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +*** At the end of ineffective in engine section gtid list has 5 more: +flush logs; +show binlog events in 'master-bin.000005' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000005 # Gtid_list 1 # [0-1-18] +create table tm (a int) engine=myisam; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'rw_myisam'; +insert into tm set a=1; +xa end 'rw_myisam'; +xa prepare 'rw_myisam';; +disconnect master_rw_myisam; +connection master; +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +xa start 'rw_myisam'; +insert into tm set a=1; +xa end 'rw_myisam'; +xa prepare 'rw_myisam';; +disconnect master_rw_myisam; +*** rw_myisam prepared must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +1 9 0 rw_myisam +*** Zero prepared xa:s must be in the list: +xa recover; +formatID gtrid_length bqual_length data +*** At the end of MyISAM "xa" section gtid list has 7 more compare with previous check: +flush logs; +show binlog events in 'master-bin.000006' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000006 # Gtid_list 1 # [0-1-25] +connect master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,; +set @@session.sql_log_bin = OFF; +xa start 'skip_binlog'; +insert into t2 values(1); +xa end 'skip_binlog'; +xa prepare 'skip_binlog'; +disconnect master_skip_binlog; +*** skip_binlog must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +1 11 0 skip_binlog +connection master; +call mtr.add_suppression("Slave: XAER_NOTA: Unknown XID"); +xa rollback 'skip_binlog'; +*** Zero must be in the list: +connection master; +xa recover; +formatID gtrid_length bqual_length data +*** At the end of --binlog-ignore-db section gtid list has 2 more: +flush logs; +show binlog events in 'master-bin.000007' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000007 # Gtid_list 1 # [0-1-27] +include/save_master_gtid.inc +connection slave; +include/wait_for_slave_sql_error.inc [errno=1397] +set @@global.sql_slave_skip_counter= 1; +include/start_slave.inc +connection master; +drop database test_ign; +drop table t1, t2, t3, tm; +connection slave; +include/stop_slave.inc +SET @@global.gtid_pos_auto_engines=""; +SET @@session.sql_log_bin=0; +DROP TABLE mysql.gtid_slave_pos_InnoDB; +SET @@session.sql_log_bin=1; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_xa_survive_disconnect.result b/mysql-test/suite/rpl/r/rpl_xa_survive_disconnect.result new file mode 100644 index 00000000000..0ee7b497077 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_xa_survive_disconnect.result @@ -0,0 +1,319 @@ +include/master-slave.inc +[connection master] +connection master; +call mtr.add_suppression("Found 2 prepared XA transactions"); +CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND'; +CREATE DATABASE d1; +CREATE DATABASE d2; +CREATE TABLE d1.t (a INT) ENGINE=innodb; +CREATE TABLE d2.t (a INT) ENGINE=innodb; +connect master_conn1, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@session.binlog_format= statement; +XA START '1-stmt'; +INSERT INTO d1.t VALUES (1); +XA END '1-stmt'; +XA PREPARE '1-stmt'; +disconnect master_conn1; +connection master; +connect master_conn2, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@session.binlog_format= row; +XA START '1-row'; +INSERT INTO d2.t VALUES (1); +XA END '1-row'; +XA PREPARE '1-row'; +disconnect master_conn2; +connection master; +XA START '2'; +INSERT INTO d1.t VALUES (2); +XA END '2'; +XA PREPARE '2'; +XA COMMIT '2'; +XA COMMIT '1-row'; +XA COMMIT '1-stmt'; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'Found 2 prepared XA transactions' COLLATE 'latin1_swedish_ci')) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_processlist` AS SELECT * FROM performance_schema.threads where type = 'FOREGROUND' +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # CREATE DATABASE d1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # CREATE DATABASE d2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE d1.t (a INT) ENGINE=innodb +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE d2.t (a INT) ENGINE=innodb +master-bin.000001 # Gtid # # XA START X'312d73746d74',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO d1.t VALUES (1) +master-bin.000001 # Query # # XA END X'312d73746d74',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'312d73746d74',X'',1 +master-bin.000001 # Gtid # # XA START X'312d726f77',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO d2.t VALUES (1) +master-bin.000001 # Table_map # # table_id: # (d2.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'312d726f77',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'312d726f77',X'',1 +master-bin.000001 # Gtid # # XA START X'32',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO d1.t VALUES (2) +master-bin.000001 # Query # # XA END X'32',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'32',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'32',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'312d726f77',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'312d73746d74',X'',1 +include/sync_slave_sql_with_master.inc +include/stop_slave.inc +connection master; +connect master2, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master2; +SET @@session.binlog_format= statement; +XA START '3-stmt'; +INSERT INTO d1.t VALUES (3); +XA END '3-stmt'; +XA PREPARE '3-stmt'; +disconnect master2; +connect master2, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master2; +SET @@session.binlog_format= row; +XA START '3-row'; +INSERT INTO d2.t VALUES (4); +XA END '3-row'; +XA PREPARE '3-row'; +disconnect master2; +connection master; +connect master2, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master2; +XA START '4'; +SELECT * FROM d1.t; +a +1 +2 +XA END '4'; +XA PREPARE '4'; +disconnect master2; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_10'; +INSERT INTO d1.t VALUES (10); +INSERT INTO d2.t VALUES (10); +XA END 'bulk_trx_10'; +XA PREPARE 'bulk_trx_10'; +disconnect master_bulk_conn10; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_9'; +INSERT INTO d1.t VALUES (9); +INSERT INTO d2.t VALUES (9); +XA END 'bulk_trx_9'; +XA PREPARE 'bulk_trx_9'; +disconnect master_bulk_conn9; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_8'; +INSERT INTO d1.t VALUES (8); +INSERT INTO d2.t VALUES (8); +XA END 'bulk_trx_8'; +XA PREPARE 'bulk_trx_8'; +disconnect master_bulk_conn8; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_7'; +INSERT INTO d1.t VALUES (7); +INSERT INTO d2.t VALUES (7); +XA END 'bulk_trx_7'; +XA PREPARE 'bulk_trx_7'; +disconnect master_bulk_conn7; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_6'; +INSERT INTO d1.t VALUES (6); +INSERT INTO d2.t VALUES (6); +XA END 'bulk_trx_6'; +XA PREPARE 'bulk_trx_6'; +disconnect master_bulk_conn6; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_5'; +INSERT INTO d1.t VALUES (5); +INSERT INTO d2.t VALUES (5); +XA END 'bulk_trx_5'; +XA PREPARE 'bulk_trx_5'; +disconnect master_bulk_conn5; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_4'; +INSERT INTO d1.t VALUES (4); +INSERT INTO d2.t VALUES (4); +XA END 'bulk_trx_4'; +XA PREPARE 'bulk_trx_4'; +disconnect master_bulk_conn4; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_3'; +INSERT INTO d1.t VALUES (3); +INSERT INTO d2.t VALUES (3); +XA END 'bulk_trx_3'; +XA PREPARE 'bulk_trx_3'; +disconnect master_bulk_conn3; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_2'; +INSERT INTO d1.t VALUES (2); +INSERT INTO d2.t VALUES (2); +XA END 'bulk_trx_2'; +XA PREPARE 'bulk_trx_2'; +disconnect master_bulk_conn2; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_1'; +INSERT INTO d1.t VALUES (1); +INSERT INTO d2.t VALUES (1); +XA END 'bulk_trx_1'; +XA PREPARE 'bulk_trx_1'; +disconnect master_bulk_conn1; +connection master; +connection slave; +include/start_slave.inc +connection master; +include/sync_slave_sql_with_master.inc +include/stop_slave.inc +connection master; +XA COMMIT 'bulk_trx_10'; +XA ROLLBACK 'bulk_trx_9'; +XA COMMIT 'bulk_trx_8'; +XA ROLLBACK 'bulk_trx_7'; +XA COMMIT 'bulk_trx_6'; +XA ROLLBACK 'bulk_trx_5'; +XA COMMIT 'bulk_trx_4'; +XA ROLLBACK 'bulk_trx_3'; +XA COMMIT 'bulk_trx_2'; +XA ROLLBACK 'bulk_trx_1'; +include/rpl_restart_server.inc [server_number=1] +connection slave; +include/start_slave.inc +connection master; +*** '3-stmt','3-row' xa-transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 6 0 3-stmt +1 5 0 3-row +XA COMMIT '3-stmt'; +XA ROLLBACK '3-row'; +include/sync_slave_sql_with_master.inc +connection master; +connect master_conn2, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START '0123456789012345678901234567890123456789012345678901234567890124','0123456789012345678901234567890123456789012345678901234567890124',2147483647; +INSERT INTO d1.t VALUES (64); +XA END '0123456789012345678901234567890123456789012345678901234567890124','0123456789012345678901234567890123456789012345678901234567890124',2147483647; +XA PREPARE '0123456789012345678901234567890123456789012345678901234567890124','0123456789012345678901234567890123456789012345678901234567890124',2147483647; +disconnect master_conn2; +connection master; +connect master_conn3, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF',X'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',0; +INSERT INTO d1.t VALUES (0); +XA END X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF',X'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',0; +XA PREPARE X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF',X'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',0; +disconnect master_conn3; +connection master; +disconnect master_conn4; +connection master; +XA COMMIT '0123456789012345678901234567890123456789012345678901234567890124','0123456789012345678901234567890123456789012345678901234567890124',2147483647; +XA COMMIT X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF',X'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',0; +XA COMMIT 'RANDOM XID' +include/sync_slave_sql_with_master.inc +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn10; +XA START 'one_phase_10'; +INSERT INTO d1.t VALUES (10); +INSERT INTO d2.t VALUES (10); +XA END 'one_phase_10'; +XA COMMIT 'one_phase_10' ONE PHASE; +disconnect master_bulk_conn10; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn9; +XA START 'one_phase_9'; +INSERT INTO d1.t VALUES (9); +INSERT INTO d2.t VALUES (9); +XA END 'one_phase_9'; +XA COMMIT 'one_phase_9' ONE PHASE; +disconnect master_bulk_conn9; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn8; +XA START 'one_phase_8'; +INSERT INTO d1.t VALUES (8); +INSERT INTO d2.t VALUES (8); +XA END 'one_phase_8'; +XA COMMIT 'one_phase_8' ONE PHASE; +disconnect master_bulk_conn8; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn7; +XA START 'one_phase_7'; +INSERT INTO d1.t VALUES (7); +INSERT INTO d2.t VALUES (7); +XA END 'one_phase_7'; +XA COMMIT 'one_phase_7' ONE PHASE; +disconnect master_bulk_conn7; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn6; +XA START 'one_phase_6'; +INSERT INTO d1.t VALUES (6); +INSERT INTO d2.t VALUES (6); +XA END 'one_phase_6'; +XA COMMIT 'one_phase_6' ONE PHASE; +disconnect master_bulk_conn6; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn5; +XA START 'one_phase_5'; +INSERT INTO d1.t VALUES (5); +INSERT INTO d2.t VALUES (5); +XA END 'one_phase_5'; +XA COMMIT 'one_phase_5' ONE PHASE; +disconnect master_bulk_conn5; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn4; +XA START 'one_phase_4'; +INSERT INTO d1.t VALUES (4); +INSERT INTO d2.t VALUES (4); +XA END 'one_phase_4'; +XA COMMIT 'one_phase_4' ONE PHASE; +disconnect master_bulk_conn4; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn3; +XA START 'one_phase_3'; +INSERT INTO d1.t VALUES (3); +INSERT INTO d2.t VALUES (3); +XA END 'one_phase_3'; +XA COMMIT 'one_phase_3' ONE PHASE; +disconnect master_bulk_conn3; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn2; +XA START 'one_phase_2'; +INSERT INTO d1.t VALUES (2); +INSERT INTO d2.t VALUES (2); +XA END 'one_phase_2'; +XA COMMIT 'one_phase_2' ONE PHASE; +disconnect master_bulk_conn2; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn1; +XA START 'one_phase_1'; +INSERT INTO d1.t VALUES (1); +INSERT INTO d2.t VALUES (1); +XA END 'one_phase_1'; +XA COMMIT 'one_phase_1' ONE PHASE; +disconnect master_bulk_conn1; +connection master; +include/sync_slave_sql_with_master.inc +include/diff_tables.inc [master:d1.t, slave:d1.t] +include/diff_tables.inc [master:d2.t, slave:d2.t] +connection master; +DELETE FROM d1.t; +DELETE FROM d2.t; +DROP TABLE d1.t, d2.t; +DROP DATABASE d1; +DROP DATABASE d2; +DROP VIEW v_processlist; +include/sync_slave_sql_with_master.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_xa_survive_disconnect_lsu_off.result b/mysql-test/suite/rpl/r/rpl_xa_survive_disconnect_lsu_off.result new file mode 100644 index 00000000000..0ee7b497077 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_xa_survive_disconnect_lsu_off.result @@ -0,0 +1,319 @@ +include/master-slave.inc +[connection master] +connection master; +call mtr.add_suppression("Found 2 prepared XA transactions"); +CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND'; +CREATE DATABASE d1; +CREATE DATABASE d2; +CREATE TABLE d1.t (a INT) ENGINE=innodb; +CREATE TABLE d2.t (a INT) ENGINE=innodb; +connect master_conn1, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@session.binlog_format= statement; +XA START '1-stmt'; +INSERT INTO d1.t VALUES (1); +XA END '1-stmt'; +XA PREPARE '1-stmt'; +disconnect master_conn1; +connection master; +connect master_conn2, 127.0.0.1,root,,test,$MASTER_MYPORT,; +SET @@session.binlog_format= row; +XA START '1-row'; +INSERT INTO d2.t VALUES (1); +XA END '1-row'; +XA PREPARE '1-row'; +disconnect master_conn2; +connection master; +XA START '2'; +INSERT INTO d1.t VALUES (2); +XA END '2'; +XA PREPARE '2'; +XA COMMIT '2'; +XA COMMIT '1-row'; +XA COMMIT '1-stmt'; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'Found 2 prepared XA transactions' COLLATE 'latin1_swedish_ci')) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_processlist` AS SELECT * FROM performance_schema.threads where type = 'FOREGROUND' +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # CREATE DATABASE d1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # CREATE DATABASE d2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE d1.t (a INT) ENGINE=innodb +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE d2.t (a INT) ENGINE=innodb +master-bin.000001 # Gtid # # XA START X'312d73746d74',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO d1.t VALUES (1) +master-bin.000001 # Query # # XA END X'312d73746d74',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'312d73746d74',X'',1 +master-bin.000001 # Gtid # # XA START X'312d726f77',X'',1 GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO d2.t VALUES (1) +master-bin.000001 # Table_map # # table_id: # (d2.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # XA END X'312d726f77',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'312d726f77',X'',1 +master-bin.000001 # Gtid # # XA START X'32',X'',1 GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO d1.t VALUES (2) +master-bin.000001 # Query # # XA END X'32',X'',1 +master-bin.000001 # XA_prepare # # XA PREPARE X'32',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'32',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'312d726f77',X'',1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # XA COMMIT X'312d73746d74',X'',1 +include/sync_slave_sql_with_master.inc +include/stop_slave.inc +connection master; +connect master2, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master2; +SET @@session.binlog_format= statement; +XA START '3-stmt'; +INSERT INTO d1.t VALUES (3); +XA END '3-stmt'; +XA PREPARE '3-stmt'; +disconnect master2; +connect master2, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master2; +SET @@session.binlog_format= row; +XA START '3-row'; +INSERT INTO d2.t VALUES (4); +XA END '3-row'; +XA PREPARE '3-row'; +disconnect master2; +connection master; +connect master2, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master2; +XA START '4'; +SELECT * FROM d1.t; +a +1 +2 +XA END '4'; +XA PREPARE '4'; +disconnect master2; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_10'; +INSERT INTO d1.t VALUES (10); +INSERT INTO d2.t VALUES (10); +XA END 'bulk_trx_10'; +XA PREPARE 'bulk_trx_10'; +disconnect master_bulk_conn10; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_9'; +INSERT INTO d1.t VALUES (9); +INSERT INTO d2.t VALUES (9); +XA END 'bulk_trx_9'; +XA PREPARE 'bulk_trx_9'; +disconnect master_bulk_conn9; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_8'; +INSERT INTO d1.t VALUES (8); +INSERT INTO d2.t VALUES (8); +XA END 'bulk_trx_8'; +XA PREPARE 'bulk_trx_8'; +disconnect master_bulk_conn8; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_7'; +INSERT INTO d1.t VALUES (7); +INSERT INTO d2.t VALUES (7); +XA END 'bulk_trx_7'; +XA PREPARE 'bulk_trx_7'; +disconnect master_bulk_conn7; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_6'; +INSERT INTO d1.t VALUES (6); +INSERT INTO d2.t VALUES (6); +XA END 'bulk_trx_6'; +XA PREPARE 'bulk_trx_6'; +disconnect master_bulk_conn6; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_5'; +INSERT INTO d1.t VALUES (5); +INSERT INTO d2.t VALUES (5); +XA END 'bulk_trx_5'; +XA PREPARE 'bulk_trx_5'; +disconnect master_bulk_conn5; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_4'; +INSERT INTO d1.t VALUES (4); +INSERT INTO d2.t VALUES (4); +XA END 'bulk_trx_4'; +XA PREPARE 'bulk_trx_4'; +disconnect master_bulk_conn4; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_3'; +INSERT INTO d1.t VALUES (3); +INSERT INTO d2.t VALUES (3); +XA END 'bulk_trx_3'; +XA PREPARE 'bulk_trx_3'; +disconnect master_bulk_conn3; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_2'; +INSERT INTO d1.t VALUES (2); +INSERT INTO d2.t VALUES (2); +XA END 'bulk_trx_2'; +XA PREPARE 'bulk_trx_2'; +disconnect master_bulk_conn2; +connection master; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START 'bulk_trx_1'; +INSERT INTO d1.t VALUES (1); +INSERT INTO d2.t VALUES (1); +XA END 'bulk_trx_1'; +XA PREPARE 'bulk_trx_1'; +disconnect master_bulk_conn1; +connection master; +connection slave; +include/start_slave.inc +connection master; +include/sync_slave_sql_with_master.inc +include/stop_slave.inc +connection master; +XA COMMIT 'bulk_trx_10'; +XA ROLLBACK 'bulk_trx_9'; +XA COMMIT 'bulk_trx_8'; +XA ROLLBACK 'bulk_trx_7'; +XA COMMIT 'bulk_trx_6'; +XA ROLLBACK 'bulk_trx_5'; +XA COMMIT 'bulk_trx_4'; +XA ROLLBACK 'bulk_trx_3'; +XA COMMIT 'bulk_trx_2'; +XA ROLLBACK 'bulk_trx_1'; +include/rpl_restart_server.inc [server_number=1] +connection slave; +include/start_slave.inc +connection master; +*** '3-stmt','3-row' xa-transactions must be in the list *** +XA RECOVER; +formatID gtrid_length bqual_length data +1 6 0 3-stmt +1 5 0 3-row +XA COMMIT '3-stmt'; +XA ROLLBACK '3-row'; +include/sync_slave_sql_with_master.inc +connection master; +connect master_conn2, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START '0123456789012345678901234567890123456789012345678901234567890124','0123456789012345678901234567890123456789012345678901234567890124',2147483647; +INSERT INTO d1.t VALUES (64); +XA END '0123456789012345678901234567890123456789012345678901234567890124','0123456789012345678901234567890123456789012345678901234567890124',2147483647; +XA PREPARE '0123456789012345678901234567890123456789012345678901234567890124','0123456789012345678901234567890123456789012345678901234567890124',2147483647; +disconnect master_conn2; +connection master; +connect master_conn3, 127.0.0.1,root,,test,$MASTER_MYPORT,; +XA START X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF',X'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',0; +INSERT INTO d1.t VALUES (0); +XA END X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF',X'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',0; +XA PREPARE X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF',X'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',0; +disconnect master_conn3; +connection master; +disconnect master_conn4; +connection master; +XA COMMIT '0123456789012345678901234567890123456789012345678901234567890124','0123456789012345678901234567890123456789012345678901234567890124',2147483647; +XA COMMIT X'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF',X'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',0; +XA COMMIT 'RANDOM XID' +include/sync_slave_sql_with_master.inc +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn10; +XA START 'one_phase_10'; +INSERT INTO d1.t VALUES (10); +INSERT INTO d2.t VALUES (10); +XA END 'one_phase_10'; +XA COMMIT 'one_phase_10' ONE PHASE; +disconnect master_bulk_conn10; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn9; +XA START 'one_phase_9'; +INSERT INTO d1.t VALUES (9); +INSERT INTO d2.t VALUES (9); +XA END 'one_phase_9'; +XA COMMIT 'one_phase_9' ONE PHASE; +disconnect master_bulk_conn9; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn8; +XA START 'one_phase_8'; +INSERT INTO d1.t VALUES (8); +INSERT INTO d2.t VALUES (8); +XA END 'one_phase_8'; +XA COMMIT 'one_phase_8' ONE PHASE; +disconnect master_bulk_conn8; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn7; +XA START 'one_phase_7'; +INSERT INTO d1.t VALUES (7); +INSERT INTO d2.t VALUES (7); +XA END 'one_phase_7'; +XA COMMIT 'one_phase_7' ONE PHASE; +disconnect master_bulk_conn7; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn6; +XA START 'one_phase_6'; +INSERT INTO d1.t VALUES (6); +INSERT INTO d2.t VALUES (6); +XA END 'one_phase_6'; +XA COMMIT 'one_phase_6' ONE PHASE; +disconnect master_bulk_conn6; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn5; +XA START 'one_phase_5'; +INSERT INTO d1.t VALUES (5); +INSERT INTO d2.t VALUES (5); +XA END 'one_phase_5'; +XA COMMIT 'one_phase_5' ONE PHASE; +disconnect master_bulk_conn5; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn4; +XA START 'one_phase_4'; +INSERT INTO d1.t VALUES (4); +INSERT INTO d2.t VALUES (4); +XA END 'one_phase_4'; +XA COMMIT 'one_phase_4' ONE PHASE; +disconnect master_bulk_conn4; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn3; +XA START 'one_phase_3'; +INSERT INTO d1.t VALUES (3); +INSERT INTO d2.t VALUES (3); +XA END 'one_phase_3'; +XA COMMIT 'one_phase_3' ONE PHASE; +disconnect master_bulk_conn3; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn2; +XA START 'one_phase_2'; +INSERT INTO d1.t VALUES (2); +INSERT INTO d2.t VALUES (2); +XA END 'one_phase_2'; +XA COMMIT 'one_phase_2' ONE PHASE; +disconnect master_bulk_conn2; +connect master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,; +connection master_bulk_conn1; +XA START 'one_phase_1'; +INSERT INTO d1.t VALUES (1); +INSERT INTO d2.t VALUES (1); +XA END 'one_phase_1'; +XA COMMIT 'one_phase_1' ONE PHASE; +disconnect master_bulk_conn1; +connection master; +include/sync_slave_sql_with_master.inc +include/diff_tables.inc [master:d1.t, slave:d1.t] +include/diff_tables.inc [master:d2.t, slave:d2.t] +connection master; +DELETE FROM d1.t; +DELETE FROM d2.t; +DROP TABLE d1.t, d2.t; +DROP DATABASE d1; +DROP DATABASE d2; +DROP VIEW v_processlist; +include/sync_slave_sql_with_master.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_xa_survive_disconnect_mixed_engines.result b/mysql-test/suite/rpl/r/rpl_xa_survive_disconnect_mixed_engines.result new file mode 100644 index 00000000000..09bfffc0da4 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_xa_survive_disconnect_mixed_engines.result @@ -0,0 +1,373 @@ +include/master-slave.inc +[connection master] +connection master; +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +SET @@session.binlog_direct_non_transactional_updates := if(floor(rand()*10)%2,'ON','OFF'); +CREATE TABLE t (a INT) ENGINE=innodb; +CREATE TABLE tm (a INT) ENGINE=myisam; +=== COMMIT === +XA START 'xa_trx'; +INSERT INTO tm VALUES (1); +INSERT INTO t VALUES (1); +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +XA COMMIT 'xa_trx' ; +XA START 'xa_trx'; +INSERT INTO t VALUES (2); +INSERT INTO tm VALUES (2); +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +XA COMMIT 'xa_trx' ; +XA START 'xa_trx'; +INSERT INTO tm VALUES (3); +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +XA COMMIT 'xa_trx' ; +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +XA START 'xa_trx'; +INSERT INTO t VALUES (4); +INSERT INTO tm VALUES (4); +INSERT INTO tmp_i VALUES (4); +INSERT INTO tmp_m VALUES (4); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +XA COMMIT 'xa_trx' ; +XA START 'xa_trx'; +INSERT INTO tmp_i VALUES (5); +INSERT INTO tmp_m VALUES (5); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +INSERT INTO t VALUES (5); +INSERT INTO tm VALUES (5); +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +XA COMMIT 'xa_trx' ; +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA START 'xa_trx'; +INSERT INTO t VALUES (6); +INSERT INTO tm VALUES (6); +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +INSERT INTO tmp_i VALUES (6); +INSERT INTO tmp_m VALUES (6); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +XA COMMIT 'xa_trx' ; +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA START 'xa_trx'; +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +INSERT INTO tmp_i VALUES (7); +INSERT INTO tmp_m VALUES (7); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +INSERT INTO t VALUES (7); +INSERT INTO tm VALUES (7); +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +XA COMMIT 'xa_trx' ; +XA START 'xa_trx'; +INSERT INTO t VALUES (8); +INSERT INTO tm VALUES (8); +INSERT INTO tmp_i VALUES (8); +INSERT INTO tmp_m VALUES (8); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +XA COMMIT 'xa_trx' ; +XA START 'xa_trx'; +UPDATE t SET a = 99 where a = -1; +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +XA COMMIT 'xa_trx' ; +include/sync_slave_sql_with_master.inc +connection master; +=== COMMIT ONE PHASE === +XA START 'xa_trx'; +INSERT INTO tm VALUES (1); +INSERT INTO t VALUES (1); +XA END 'xa_trx'; +XA COMMIT 'xa_trx' ONE PHASE; +XA START 'xa_trx'; +INSERT INTO t VALUES (2); +INSERT INTO tm VALUES (2); +XA END 'xa_trx'; +XA COMMIT 'xa_trx' ONE PHASE; +XA START 'xa_trx'; +INSERT INTO tm VALUES (3); +XA END 'xa_trx'; +XA COMMIT 'xa_trx' ONE PHASE; +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +XA START 'xa_trx'; +INSERT INTO t VALUES (4); +INSERT INTO tm VALUES (4); +INSERT INTO tmp_i VALUES (4); +INSERT INTO tmp_m VALUES (4); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +XA END 'xa_trx'; +XA COMMIT 'xa_trx' ONE PHASE; +XA START 'xa_trx'; +INSERT INTO tmp_i VALUES (5); +INSERT INTO tmp_m VALUES (5); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +INSERT INTO t VALUES (5); +INSERT INTO tm VALUES (5); +XA END 'xa_trx'; +XA COMMIT 'xa_trx' ONE PHASE; +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA START 'xa_trx'; +INSERT INTO t VALUES (6); +INSERT INTO tm VALUES (6); +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +INSERT INTO tmp_i VALUES (6); +INSERT INTO tmp_m VALUES (6); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +XA END 'xa_trx'; +XA COMMIT 'xa_trx' ONE PHASE; +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA START 'xa_trx'; +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +INSERT INTO tmp_i VALUES (7); +INSERT INTO tmp_m VALUES (7); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +INSERT INTO t VALUES (7); +INSERT INTO tm VALUES (7); +XA END 'xa_trx'; +XA COMMIT 'xa_trx' ONE PHASE; +XA START 'xa_trx'; +INSERT INTO t VALUES (8); +INSERT INTO tm VALUES (8); +INSERT INTO tmp_i VALUES (8); +INSERT INTO tmp_m VALUES (8); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA END 'xa_trx'; +XA COMMIT 'xa_trx' ONE PHASE; +XA START 'xa_trx'; +UPDATE t SET a = 99 where a = -1; +XA END 'xa_trx'; +XA COMMIT 'xa_trx' ONE PHASE; +include/sync_slave_sql_with_master.inc +connection master; +=== ROLLBACK with PREPARE === +XA START 'xa_trx'; +INSERT INTO tm VALUES (1); +INSERT INTO t VALUES (1); +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +XA START 'xa_trx'; +INSERT INTO t VALUES (2); +INSERT INTO tm VALUES (2); +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +XA START 'xa_trx'; +INSERT INTO tm VALUES (3); +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +XA START 'xa_trx'; +INSERT INTO t VALUES (4); +INSERT INTO tm VALUES (4); +INSERT INTO tmp_i VALUES (4); +INSERT INTO tmp_m VALUES (4); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +XA START 'xa_trx'; +INSERT INTO tmp_i VALUES (5); +INSERT INTO tmp_m VALUES (5); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +INSERT INTO t VALUES (5); +INSERT INTO tm VALUES (5); +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA START 'xa_trx'; +INSERT INTO t VALUES (6); +INSERT INTO tm VALUES (6); +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +INSERT INTO tmp_i VALUES (6); +INSERT INTO tmp_m VALUES (6); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA START 'xa_trx'; +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +INSERT INTO tmp_i VALUES (7); +INSERT INTO tmp_m VALUES (7); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +INSERT INTO t VALUES (7); +INSERT INTO tm VALUES (7); +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +XA START 'xa_trx'; +INSERT INTO t VALUES (8); +INSERT INTO tm VALUES (8); +INSERT INTO tmp_i VALUES (8); +INSERT INTO tmp_m VALUES (8); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +XA START 'xa_trx'; +UPDATE t SET a = 99 where a = -1; +XA END 'xa_trx'; +XA PREPARE 'xa_trx'; +xa rollback 'xa_trx' ; +include/sync_slave_sql_with_master.inc +connection master; +=== ROLLBACK with no PREPARE === +XA START 'xa_trx'; +INSERT INTO tm VALUES (1); +INSERT INTO t VALUES (1); +XA END 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +XA START 'xa_trx'; +INSERT INTO t VALUES (2); +INSERT INTO tm VALUES (2); +XA END 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +XA START 'xa_trx'; +INSERT INTO tm VALUES (3); +XA END 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +XA START 'xa_trx'; +INSERT INTO t VALUES (4); +INSERT INTO tm VALUES (4); +INSERT INTO tmp_i VALUES (4); +INSERT INTO tmp_m VALUES (4); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +XA END 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +XA START 'xa_trx'; +INSERT INTO tmp_i VALUES (5); +INSERT INTO tmp_m VALUES (5); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +INSERT INTO t VALUES (5); +INSERT INTO tm VALUES (5); +XA END 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA START 'xa_trx'; +INSERT INTO t VALUES (6); +INSERT INTO tm VALUES (6); +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +INSERT INTO tmp_i VALUES (6); +INSERT INTO tmp_m VALUES (6); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +XA END 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA START 'xa_trx'; +CREATE TEMPORARY TABLE tmp_i LIKE t; +CREATE TEMPORARY TABLE tmp_m LIKE tm; +INSERT INTO tmp_i VALUES (7); +INSERT INTO tmp_m VALUES (7); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +INSERT INTO t VALUES (7); +INSERT INTO tm VALUES (7); +XA END 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +XA START 'xa_trx'; +INSERT INTO t VALUES (8); +INSERT INTO tm VALUES (8); +INSERT INTO tmp_i VALUES (8); +INSERT INTO tmp_m VALUES (8); +INSERT INTO t SELECT * FROM tmp_i; +INSERT INTO tm SELECT * FROM tmp_m; +DROP TEMPORARY TABLE tmp_i; +DROP TEMPORARY TABLE tmp_m; +XA END 'xa_trx'; +xa rollback 'xa_trx' ; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +XA START 'xa_trx'; +UPDATE t SET a = 99 where a = -1; +XA END 'xa_trx'; +xa rollback 'xa_trx' ; +include/sync_slave_sql_with_master.inc +include/diff_tables.inc [master:tm, slave:tm] +connection master; +DROP TABLE t, tm; +include/sync_slave_sql_with_master.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_create_xa_prepared.inc b/mysql-test/suite/rpl/t/rpl_create_xa_prepared.inc new file mode 100644 index 00000000000..b823ebf62ee --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_create_xa_prepared.inc @@ -0,0 +1,9 @@ +# param $xid to name xa and take part in the connection name +# param $query to execute as the xa body +# param $db_ign the default database + +--connect (master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,) +--eval xa start '$xid' +--eval $query +--eval xa end '$xid' +--eval xa prepare '$xid'; diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic_xa.test b/mysql-test/suite/rpl/t/rpl_parallel_optimistic_xa.test new file mode 100644 index 00000000000..35c22d1e92e --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic_xa.test @@ -0,0 +1,235 @@ +# The tests verify concurrent execution of replicated (MDEV-742) +# XA transactions in the parallel optimistic mode. + +--source include/have_innodb.inc +--source include/have_perfschema.inc +--source include/master-slave.inc + +# Tests' global declarations +--let $trx = _trx_ + +call mtr.add_suppression("Deadlock found when trying to get lock; try restarting transaction"); +call mtr.add_suppression("WSREP: handlerton rollback failed"); +#call mtr.add_suppression("Can't find record in 't1'"); +CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND'; + +--connection master +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; +--save_master_pos + +# Prepare to restart slave into optimistic parallel mode +--connection slave +--sync_with_master +--source include/stop_slave.inc +SET @old_parallel_threads = @@GLOBAL.slave_parallel_threads; +SET @@global.slave_parallel_threads = 7; +SET @old_parallel_mode = @@GLOBAL.slave_parallel_mode; +SET @@global.slave_parallel_mode ='optimistic'; +# Run the first part of the test with high batch size and see that +# old rows remain in the table. +SET @old_gtid_cleanup_batch_size = @@GLOBAL.gtid_cleanup_batch_size; +SET @@global.gtid_cleanup_batch_size = 1000000; + +CHANGE MASTER TO master_use_gtid=slave_pos; + +# LOAD GENERATOR creates XA:s interleaved in binlog when they are from +# different connections. All the following block XA:s of the same connection +# update the same data which challenges slave optimistic scheduler's correctness. +# Slave must eventually apply such load, and correctly (checked). + +--connection master +CREATE TABLE t0 (a int, b INT) ENGINE=InnoDB; +CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 0); + + +# I. Logging some sequence of XA:s by one connection. +# +# The slave applier's task is to successfully execute a series of +# Prepare and Complete parts of a sequence of XA:s + +--let $trx_num = 300 +--let $i = $trx_num +--let $conn = master +--disable_query_log +while($i > 0) +{ + # 'decision' to commit 0, or rollback 1 + --let $decision = `SELECT $i % 2` + --eval XA START '$conn$trx$i' + --eval UPDATE t1 SET b = 1 - 2 * $decision WHERE a = 1 + --eval XA END '$conn$trx$i' + --let $one_phase = `SELECT IF(floor(rand()*10)%2, "ONE PHASE", 0)` + if (!$one_phase) + { + --eval XA PREPARE '$conn$trx$i' + --let $one_phase = + } + + --let $term = COMMIT + if ($decision) + { + --let $term = ROLLBACK + --let $one_phase = + } + --eval XA $term '$conn$trx$i' $one_phase + + --dec $i +} +--enable_query_log +--source include/save_master_gtid.inc + +--connection slave +--source include/start_slave.inc +--source include/sync_with_master_gtid.inc +--source include/stop_slave.inc + + +# II. Logging XS:s from multiple connections in random interweaving manner: +# +# in a loop ($i) per connection +# arrange an inner ($k) loop where +# start and prepare an XA; +# decide whether to terminate it and then continue to loop innerly +# OR disconnect to break the inner loop; +# the disconnected one's XA is taken care by 'master' connection +# +# Effectively binlog must collect a well mixed XA- prepared and terminated +# groups for slave to handle. + +--connection master +# Total # of connections +--let $conn_num=53 + +--let $i = $conn_num +--disable_query_log +while($i > 0) +{ + --connect (master_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,) +--dec $i +} +--enable_query_log + +--let $i = $conn_num +while($i > 0) +{ + --let $conn_i = conn$i + # $i2 indexes the current connection's "own" row + --let $i2 = `SELECT $i + 2` +--disable_query_log + --connection master_conn$i +--enable_query_log + --disable_query_log + --let $i_conn_id = `SELECT connection_id()` + + --let $decision = 0 + # the row id of the last connection that committed its XA + --let $c_max = 1 + --let $k = 0 + while ($decision < 3) + { + --inc $k + --eval XA START '$conn_i$trx$k' + # UPDATE depends on previously *committed* transactions + --eval UPDATE t1 SET b = b + $k + 1 WHERE a = $c_max + if (`SELECT $k % 2 = 1`) + { + --eval REPLACE INTO t1 VALUES ($i2, $k) + } + if (`SELECT $k % 2 = 0`) + { + --eval DELETE FROM t1 WHERE a = $i2 + } + CREATE TEMPORARY TABLE tmp LIKE t0; + --eval INSERT INTO tmp SET a=$i, b= $k + INSERT INTO t0 SELECT * FROM tmp; + DROP TEMPORARY TABLE tmp; + --eval XA END '$conn_i$trx$k' + + --let $term = COMMIT + --let $decision = `SELECT (floor(rand()*10 % 10) + ($i+$k)) % 4` + if ($decision == 1) + { + --let $term = ROLLBACK + } + if ($decision < 2) + { + --eval XA PREPARE '$conn_i$trx$k' + --eval XA $term '$conn_i$trx$k' + # Iteration counter is taken care *now* + } + if ($decision == 2) + { + --eval XA COMMIT '$conn_i$trx$k' ONE PHASE + } + } + + # $decision = 3 + --eval XA PREPARE '$conn_i$trx$k' + # disconnect now + --disconnect master_conn$i + --connection master + + --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $i_conn_id + --source include/wait_condition.inc + + --disable_query_log + --let $decision = `SELECT ($i+$k) % 2` + --let $term = COMMIT + if ($decision == 1) + { + --let $term = ROLLBACK + } + --eval XA $term '$conn_i$trx$k' + --let $c_max = $i2 + +--dec $i +} +--enable_query_log +--source include/save_master_gtid.inc + +--connection slave +--source include/start_slave.inc +--source include/sync_with_master_gtid.inc + +# +# Overall consistency check +# +--let $diff_tables= master:t0, slave:t0 +--source include/diff_tables.inc +--let $diff_tables= master:t1, slave:t1 +--source include/diff_tables.inc + + +# +# Clean up. +# +--connection slave +--source include/stop_slave.inc +set global log_warnings=default; +SET GLOBAL slave_parallel_mode=@old_parallel_mode; +SET GLOBAL slave_parallel_threads=@old_parallel_threads; +--source include/start_slave.inc + +--connection master +DROP VIEW v_processlist; +DROP TABLE t0, t1; +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc +# Check that old rows are deleted from mysql.gtid_slave_pos. +# Deletion is asynchronous, so use wait_condition.inc. +# Also, there is a small amount of non-determinism in the deletion of old +# rows, so it is not guaranteed that there can never be more than +# @@gtid_cleanup_batch_size rows in the table; so allow a bit of slack +# here. +let $wait_condition= + SELECT COUNT(*) <= 5*@@GLOBAL.gtid_cleanup_batch_size + FROM mysql.gtid_slave_pos; +--source include/wait_condition.inc +eval $wait_condition; +SET GLOBAL gtid_cleanup_batch_size= @old_gtid_cleanup_batch_size; + +--connection master +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic_xa_lsu_off-slave.opt b/mysql-test/suite/rpl/t/rpl_parallel_optimistic_xa_lsu_off-slave.opt new file mode 100644 index 00000000000..88cf77fd281 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic_xa_lsu_off-slave.opt @@ -0,0 +1 @@ +--log-slave-updates=OFF diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic_xa_lsu_off.test b/mysql-test/suite/rpl/t/rpl_parallel_optimistic_xa_lsu_off.test new file mode 100644 index 00000000000..f82b522eefe --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic_xa_lsu_off.test @@ -0,0 +1,2 @@ +# --log-slave-updates OFF version of rpl_parallel_optimistic_xa +--source rpl_parallel_optimistic_xa.test diff --git a/mysql-test/suite/rpl/t/rpl_parallel_xa_same_xid.test b/mysql-test/suite/rpl/t/rpl_parallel_xa_same_xid.test new file mode 100644 index 00000000000..888dd2f177b --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_parallel_xa_same_xid.test @@ -0,0 +1,138 @@ +# The tests verify concurrent execution of replicated (MDEV-742) +# XA transactions in the parallel optimistic mode. +# Prove optimistic scheduler handles xid-namesake XA:s. +# That is despite running in parallel there must be no conflicts +# caused by multiple transactions' same xid. + +--source include/have_binlog_format_mixed_or_row.inc +--source include/have_innodb.inc +--source include/have_perfschema.inc +--source include/master-slave.inc + +--let $xid_num = 19 +--let $repeat = 17 +--let $workers = 7 +--connection slave +call mtr.add_suppression("WSREP: handlerton rollback failed"); + +--source include/stop_slave.inc +# a measure against MDEV-20605 +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; + +SET @old_parallel_threads = @@GLOBAL.slave_parallel_threads; +--disable_query_log +--eval SET @@global.slave_parallel_threads = $workers +--enable_query_log +SET @old_parallel_mode = @@GLOBAL.slave_parallel_mode; +SET @@global.slave_parallel_mode ='optimistic'; +--source include/start_slave.inc + +--connection master +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; + +--let $i = $xid_num +--let $t = t1 +--disable_query_log +while ($i) +{ +--let $k = $repeat +while ($k) +{ +--eval XA START 'xid_$i' +--eval INSERT INTO $t SET a=$i, b=$k +--eval XA END 'xid_$i' +--let $one_phase = `SELECT IF(floor(rand()*10)%2, "ONE PHASE", 0)` + if (!$one_phase) + { + --eval XA PREPARE 'xid_$i' + --eval XA COMMIT 'xid_$i' + } + if ($one_phase) + { + --eval XA COMMIT 'xid_$i' ONE PHASE + } + + if (!$one_phase) + { + --eval XA START 'xid_$i' + --eval INSERT INTO $t SET a=$i, b=$k + --eval XA END 'xid_$i' + --eval XA PREPARE 'xid_$i' + --eval XA ROLLBACK 'xid_$i' + } + +--dec $k +} + +--dec $i +} +--enable_query_log + + + +# Above-like test complicates execution env to create +# data conflicts as well. They will be resolved by the optmistic +# scheduler as usual. + +CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b INT) ENGINE=InnoDB; + +--let $i = $xid_num +--let $t = t2 +--disable_query_log +while ($i) +{ +--let $k = $repeat +while ($k) +{ +--eval XA START 'xid_$i' +--eval INSERT INTO $t SET a=NULL, b=$k +--eval UPDATE $t SET b=$k + 1 WHERE a=last_insert_id() % $workers +--eval XA END 'xid_$i' +--let $one_phase = `SELECT IF(floor(rand()*10)%2, "ONE PHASE", 0)` + if (!$one_phase) + { + --eval XA PREPARE 'xid_$i' + --eval XA COMMIT 'xid_$i' + } + if ($one_phase) + { + --eval XA COMMIT 'xid_$i' ONE PHASE + } + +--eval XA START 'xid_$i' +--eval UPDATE $t SET b=$k + 1 WHERE a=last_insert_id() % $workers +--eval DELETE FROM $t WHERE a=last_insert_id() +--eval XA END 'xid_$i' +--eval XA PREPARE 'xid_$i' +--eval XA ROLLBACK 'xid_$i' + +--let $do_drop_create = `SELECT IF(floor(rand()*10)%100, 1, 0)` +if ($do_drop_create) +{ + DROP TABLE t1; + CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; +} +--dec $k +} + +--dec $i +} +--enable_query_log + +--source include/sync_slave_sql_with_master.inc +--let $diff_tables= master:t1, slave:t1 +--source include/diff_tables.inc + +# +# Clean up. +# +--connection slave +--source include/stop_slave.inc +SET GLOBAL slave_parallel_threads=@old_parallel_threads; +SET GLOBAL slave_parallel_mode=@old_parallel_mode; +--source include/start_slave.inc + +--connection master +DROP TABLE t1, t2; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_temporary_errors.test b/mysql-test/suite/rpl/t/rpl_temporary_errors.test index 6392fb90b9b..85e16afa270 100644 --- a/mysql-test/suite/rpl/t/rpl_temporary_errors.test +++ b/mysql-test/suite/rpl/t/rpl_temporary_errors.test @@ -6,7 +6,7 @@ call mtr.add_suppression("Deadlock found"); call mtr.add_suppression("Can't find record in 't.'"); connection master; -CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=innodb; INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); sync_slave_with_master; SHOW STATUS LIKE 'Slave_retried_transactions'; @@ -14,20 +14,94 @@ SHOW STATUS LIKE 'Slave_retried_transactions'; # the following UPDATE t1 to pass the mode is switched temprorarily set @@global.slave_exec_mode= 'IDEMPOTENT'; UPDATE t1 SET a = 5, b = 47 WHERE a = 1; -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; connection master; UPDATE t1 SET a = 5, b = 5 WHERE a = 1; -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; #SHOW BINLOG EVENTS; sync_slave_with_master; set @@global.slave_exec_mode= default; SHOW STATUS LIKE 'Slave_retried_transactions'; -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; source include/check_slave_is_running.inc; connection slave; call mtr.add_suppression("Slave SQL.*Could not execute Update_rows event on table test.t1"); +call mtr.add_suppression("Slave SQL for channel '': worker thread retried transaction"); +call mtr.add_suppression("The slave coordinator and worker threads are stopped"); +# +# Bug#24764800 REPLICATION FAILING ON SLAVE WITH XAER_RMFAIL ERROR +# +# Verify that a temporary failing replicated xa transaction completes +# upon slave applier restart after previous +# @@global.slave_transaction_retries number of retries in vain. +# +connection slave; + +set @save_innodb_lock_wait_timeout=@@global.innodb_lock_wait_timeout; +set @save_slave_transaction_retries=@@global.slave_transaction_retries; + +# Slave applier parameters for the failed retry +set @@global.innodb_lock_wait_timeout=1; +set @@global.slave_transaction_retries=2; +--source include/restart_slave_sql.inc + +# Temporary error implement: a record is blocked by slave local trx +connection slave1; +BEGIN; +INSERT INTO t1 SET a = 6, b = 7; + +connection master; +INSERT INTO t1 SET a = 99, b = 99; # slave applier warm up trx +XA START 'xa1'; +INSERT INTO t1 SET a = 6, b = 6; # this record eventually must be found on slave +XA END 'xa1'; +XA PREPARE 'xa1'; + +connection slave; +# convert_error(ER_LOCK_WAIT_TIMEOUT) +--let $err_timeout= 1205 +# convert_error(ER_LOCK_DEADLOCK) +--let $err_deadlock= 1213 +--let $slave_sql_errno=$err_deadlock,$err_timeout +--let $show_slave_sql_error= +--source include/wait_for_slave_sql_error.inc + +# b. Slave applier parameters for successful retry after restart +set @@global.innodb_lock_wait_timeout=1; +set @@global.slave_transaction_retries=100; + +--source include/restart_slave_sql.inc + +--let $last_retries= query_get_value(SHOW GLOBAL STATUS LIKE 'Slave_retried_transactions', Value, 1) +--let $status_type=GLOBAL +--let $status_var=Slave_retried_transactions +--let $status_var_value=`SELECT 1 + $last_retries` +--let $$status_var_comparsion= > +--source include/wait_for_status_var.inc + +# Release the record after just one retry +connection slave1; +ROLLBACK; + +connection master; +XA COMMIT 'xa1'; + +--source include/sync_slave_sql_with_master.inc + +# Proof of correctness: the committed XA is on the slave +connection slave; +--let $assert_text=XA transaction record must be in the table +--let $assert_cond=count(*)=1 FROM t1 WHERE a=6 AND b=6 +--source include/assert.inc + +# Bug#24764800 cleanup: +set @@global.innodb_lock_wait_timeout=@save_innodb_lock_wait_timeout; +set @@global.slave_transaction_retries= @save_slave_transaction_retries; +# +# Total cleanup: +# connection master; DROP TABLE t1; --sync_slave_with_master diff --git a/mysql-test/suite/rpl/t/rpl_xa-master.opt b/mysql-test/suite/rpl/t/rpl_xa-master.opt new file mode 100644 index 00000000000..6794216dc45 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa-master.opt @@ -0,0 +1 @@ +--binlog-ignore-db=test_ign diff --git a/mysql-test/suite/rpl/t/rpl_xa.inc b/mysql-test/suite/rpl/t/rpl_xa.inc new file mode 100644 index 00000000000..571a79c1acc --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa.inc @@ -0,0 +1,354 @@ +# +# This "body" file checks general properties of XA transaction replication +# as of MDEV-7974. +# Parameters: +# --let rpl_xa_check= SELECT ... +# +connection master; +create table t1 (a int, b int) engine=InnoDB; +insert into t1 values(0, 0); +xa start 't'; +insert into t1 values(1, 2); +xa end 't'; +xa prepare 't'; +xa commit 't'; + +sync_slave_with_master; +let $diff_tables= master:t1, slave:t1; +source include/diff_tables.inc; + +connection master; + +xa start 't'; +insert into t1 values(3, 4); +xa end 't'; +xa prepare 't'; +xa rollback 't'; + +sync_slave_with_master; +let $diff_tables= master:t1, slave:t1; +source include/diff_tables.inc; + +connection master; +--disable_warnings +SET pseudo_slave_mode=1; +--enable_warnings +create table t2 (a int) engine=InnoDB; +xa start 't'; +insert into t1 values (5, 6); +xa end 't'; +xa prepare 't'; +xa start 's'; +insert into t2 values (0); +xa end 's'; +xa prepare 's'; +--source include/save_master_gtid.inc + +connection slave; +source include/sync_with_master_gtid.inc; +if ($rpl_xa_check) +{ + --eval $rpl_xa_check + if ($rpl_xa_verbose) + { + --eval SELECT $rpl_xa_check_lhs + --eval SELECT $rpl_xa_check_rhs + } +} +xa recover; + +connection master; +xa commit 't'; +xa commit 's'; +--disable_warnings +SET pseudo_slave_mode=0; +--enable_warnings +sync_slave_with_master; +let $diff_tables= master:t1, slave:t1; +source include/diff_tables.inc; +let $diff_tables= master:t2, slave:t2; +source include/diff_tables.inc; + +# +# Read-only XA remains prepared after disconnect and must rollback at XA-complete +# after recoonect. To the read-only also belongs non-transactional engine XA. +# +--connection master + +--echo *** At the start of read-only section gtid list is: +flush logs; +--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) +--source include/show_gtid_list.inc + + +set @query1="select 1"; +set @query2="select count(*) into @s2 from t1"; +--let $ro_cases=2 +--let $db=test + +# No disconnect +--let $p_trx=$ro_cases +while ($p_trx) +{ +--connection master + --let $xid=ro_$p_trx + --let $query=`SELECT @query$p_trx` + --source rpl_create_xa_prepared.inc + --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')` + --error 0 + --disable_query_log + --disable_result_log + --eval xa $complete '$xid' + --enable_result_log + --enable_query_log + + --disconnect master_$xid + --source include/wait_until_disconnected.inc + + --dec $p_trx +} + + +--let $p_trx=$ro_cases +# With diconnect +while ($p_trx) +{ +--connection master + --let $xid=ro_$p_trx + --let $query=`SELECT @query$p_trx` + --source rpl_create_xa_prepared.inc + + --disconnect master_$xid + --source include/wait_until_disconnected.inc + + --dec $p_trx +} + +--echo *** $ro_cases prepared xa:s must be in the list: +--connection master +xa recover; + +--let $p_trx=$ro_cases +while ($p_trx) +{ + --let $xid=ro_$p_trx + --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')` + --disable_query_log + --disable_result_log + --error ER_XA_RBROLLBACK + --eval xa $complete '$xid' + --enable_result_log + --enable_query_log + + --dec $p_trx +} +--echo *** Zero prepared xa:s must be in the list: +xa recover; + +--echo *** At the end of read-only section gtid list has 0 more compare with previous check: +flush logs; +--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) +--source include/show_gtid_list.inc + + +# +# XA logging cases while some of XA resources are read-only +# +# A1. Binlog filter + + +--let $db=test_ign +--eval create database $db +set @@sql_log_bin = 0; +--eval create table $db.t (a int) engine=InnoDB +set @@sql_log_bin = 1; + +--let $xid=rw_no_binlog +--let $query=insert into $db.t set a=1 +--source rpl_create_xa_prepared.inc +--disconnect master_$xid +--source include/wait_until_disconnected.inc + +--echo *** $xid must be in the list: +--connection master +xa recover; + +--let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')` +--error 0 +--disable_query_log +--disable_result_log +--eval xa $complete '$xid' +--enable_result_log +--enable_query_log + +--echo *** Zero must be in the list: +--connection master +xa recover; +# restore for the following tests +--let $db=test + +--echo *** At the end of --binlog-ignore-db section gtid list has 2 more: +flush logs; +--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) +--source include/show_gtid_list.inc + +# +# A2. Opposite to A1, ineffective execution in Engine may create a +# binlog transaction +# +connection master; +create table t3 (a int) engine=innodb; + +--echo *** the disconnected prepare case +--let $xid=rw_binlog_only +--let $query=delete from t3 +--connect (master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,) + set @@binlog_format=statement; + # --source rpl_create_xa_prepared.inc + --eval xa start '$xid' + --eval $query + --eval xa end '$xid' + --eval xa prepare '$xid' + + --disconnect master_$xid + --source include/wait_until_disconnected.inc + +connection master; +--echo *** $xid must be in the list: +xa recover; + + --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')` + --disable_query_log + --disable_result_log + --eval xa $complete '$xid' + --enable_result_log + --enable_query_log + +--echo *** Zero must be in the list: +xa recover; + +--echo *** the same connection complete case. +connection master; + --let $xid=rw_binlog_only + --let $query=delete from t3 +--connect (master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,) + set @@binlog_format=statement; + # --source rpl_create_xa_prepared.inc + --eval xa start '$xid' + --eval $query + --eval xa end '$xid' + --eval xa prepare '$xid' + +--echo *** $xid must be in the list: +xa recover; + +--disable_query_log + --disable_result_log + --eval xa $complete '$xid' + --enable_result_log + --enable_query_log +--disconnect master_$xid +--source include/wait_until_disconnected.inc + +--echo *** Zero must be in the list: +--connection master +xa recover; + +--echo *** At the end of ineffective in engine section gtid list has 5 more: +flush logs; +--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) +--source include/show_gtid_list.inc + +# +# A3 MyISAM "xa" logs empty XA-prepare group, followed by +# an XA-complete event +create table tm (a int) engine=myisam; + +# No disconnect +--connection master + --let $xid=rw_myisam + --let $query=insert into tm set a=1 + --source rpl_create_xa_prepared.inc + --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')` + --error 0 + --disable_query_log + --disable_result_log + --eval xa $complete '$xid' + --enable_result_log + --enable_query_log + + --disconnect master_$xid + --source include/wait_until_disconnected.inc + +# With diconnect +--connection master + --source rpl_create_xa_prepared.inc + --disconnect master_$xid + --source include/wait_until_disconnected.inc + +--echo *** $xid prepared must be in the list: +--connection master +xa recover; + + --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')` + --disable_query_log + --disable_result_log + --eval xa $complete '$xid' + --enable_result_log + --enable_query_log + +--echo *** Zero prepared xa:s must be in the list: +xa recover; + +--echo *** At the end of MyISAM "xa" section gtid list has 7 more compare with previous check: +flush logs; +--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) +--source include/show_gtid_list.inc + + +# B. Session binlog disable does not log even empty XA-prepare but XA-complete will be +# logged despite of that. + +--let $db=test +--let $xid=skip_binlog +--let $query=insert into t2 values(1) +--connect (master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,) +set @@session.sql_log_bin = OFF; +--eval xa start '$xid' + --eval $query +--eval xa end '$xid' +--eval xa prepare '$xid' + +--disconnect master_$xid +--source include/wait_until_disconnected.inc + +--echo *** $xid must be in the list: +--connection master +xa recover; + +--connection master +call mtr.add_suppression("Slave: XAER_NOTA: Unknown XID"); +--eval xa rollback '$xid' + +--echo *** Zero must be in the list: +--connection master +xa recover; + +--echo *** At the end of --binlog-ignore-db section gtid list has 2 more: +flush logs; +--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) +--source include/show_gtid_list.inc +--source include/save_master_gtid.inc + +# +# Expected error on slave and manual correction +# +--connection slave +let $slave_sql_errno= 1397; # ER_XAER_NOTA +source include/wait_for_slave_sql_error.inc; +set @@global.sql_slave_skip_counter= 1; +--source include/start_slave.inc + +connection master; +--eval drop database test_ign +drop table t1, t2, t3, tm; diff --git a/mysql-test/suite/rpl/t/rpl_xa.test b/mysql-test/suite/rpl/t/rpl_xa.test new file mode 100644 index 00000000000..05a1abe59ae --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa.test @@ -0,0 +1,5 @@ +source include/have_innodb.inc; +source include/master-slave.inc; + +source rpl_xa.inc; +source include/rpl_end.inc; diff --git a/mysql-test/suite/rpl/t/rpl_xa_gap_lock-slave.opt b/mysql-test/suite/rpl/t/rpl_xa_gap_lock-slave.opt new file mode 100644 index 00000000000..4602a43ce25 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa_gap_lock-slave.opt @@ -0,0 +1 @@ +--transaction-isolation=READ-COMMITTED diff --git a/mysql-test/suite/rpl/t/rpl_xa_gap_lock.test b/mysql-test/suite/rpl/t/rpl_xa_gap_lock.test new file mode 100644 index 00000000000..9c48891b889 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa_gap_lock.test @@ -0,0 +1,137 @@ +# ==== Purpose ==== +# +# This test will generate two XA transactions on the master in a way that +# they will block each other on the slave if the transaction isolation level +# used by the slave applier is more restrictive than the READ COMMITTED one. +# +# Consider: +# E=execute, P=prepare, C=commit; +# 1=first transaction, 2=second transaction; +# +# Master does: E1, E2, P2, P1, C1, C2 +# Slave does: E2, P2, E1, P1, C1, C2 +# +# The transactions are designed so that, if the applier transaction isolation +# level is more restrictive than the READ COMMITTED, E1 will be blocked on +# the slave waiting for gap locks to be released. +# +# Step 1 +# +# The test will verify that the transactions don't block each other because +# the applier thread automatically changed the isolation level. +# +# Step 2 +# +# The test will verify that applying master's binary log dump in slave doesn't +# block because mysqlbinlog is informing the isolation level to be used. +# +# ==== Related Bugs and Worklogs ==== +# +# BUG#25040331: INTERLEAVED XA TRANSACTIONS MAY DEADLOCK SLAVE APPLIER WITH +# REPEATABLE READ +# +--source include/have_debug.inc +--source include/have_innodb.inc +# The test case only make sense for RBR +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--connection slave +# To hit the issue, we need to split the data in two pages. +# This global variable will help us. +SET @saved_innodb_limit_optimistic_insert_debug = @@GLOBAL.innodb_limit_optimistic_insert_debug; +SET @@GLOBAL.innodb_limit_optimistic_insert_debug = 2; + +# +# Step 1 - Using async replication +# + +# Let's generate the workload on the master +--connection master +CREATE TABLE t1 ( + c1 INT NOT NULL, + KEY(c1) +) ENGINE=InnoDB; + +CREATE TABLE t2 ( + c1 INT NOT NULL, + FOREIGN KEY(c1) REFERENCES t1(c1) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (1), (3), (4); + +--connection master1 +XA START 'XA1'; +INSERT INTO t1 values(2); +XA END 'XA1'; + +# This transaction will reference the gap where XA1 +# was inserted, and will be prepared and committed +# before XA1, so the slave will prepare it (but will +# not commit it) before preparing XA1. +--connection master +XA START 'XA2'; +INSERT INTO t2 values(3); +XA END 'XA2'; + +# The XA2 prepare should be binary logged first +XA PREPARE 'XA2'; + +# The XA1 prepare should be binary logged +# after XA2 prepare and before XA2 commit. +--connection master1 +XA PREPARE 'XA1'; + +# The commit order doesn't matter much for the issue being tested. +XA COMMIT 'XA1'; +--connection master +XA COMMIT 'XA2'; + +# Everything is fine if the slave can sync with the master. +--source include/sync_slave_sql_with_master.inc + +# +# Step 2 - Using mysqlbinlog dump to restore the salve +# +--source include/stop_slave.inc +DROP TABLE t2, t1; +RESET SLAVE; +RESET MASTER; + +--connection master +--let $master_data_dir= `SELECT @@datadir` +--let $master_log_file= query_get_value(SHOW MASTER STATUS, File, 1) +--let $mysql_server= $MYSQL --defaults-group-suffix=.2 +--echo Restore binary log from the master into the slave +--exec $MYSQL_BINLOG --force-if-open $master_data_dir/$master_log_file | $mysql_server + +--let $diff_tables= master:test.t1, slave:test.t1 +--source include/diff_tables.inc +--let $diff_tables= master:test.t2, slave:test.t2 +--source include/diff_tables.inc + +# +# Cleanup +# +--let $master_file= query_get_value(SHOW MASTER STATUS, File, 1) +--let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1) +DROP TABLE t2, t1; + +## When GTID_MODE=OFF, we need to skip already applied transactions +--connection slave +#--let $gtid_mode= `SELECT @@GTID_MODE` +#if ($gtid_mode == OFF) +#{ +# --disable_query_log +# --disable_result_log +# --eval CHANGE MASTER TO MASTER_LOG_FILE='$master_file', MASTER_LOG_POS=$master_pos +# --enable_result_log +# --enable_query_log +#} +--replace_result $master_file LOG_FILE $master_pos LOG_POS +--eval CHANGE MASTER TO MASTER_LOG_FILE='$master_file', MASTER_LOG_POS=$master_pos + +SET @@GLOBAL.innodb_limit_optimistic_insert_debug = @saved_innodb_limit_optimistic_insert_debug; +--source include/start_slave.inc + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_xa_gtid_pos_auto_engine-master.opt b/mysql-test/suite/rpl/t/rpl_xa_gtid_pos_auto_engine-master.opt new file mode 100644 index 00000000000..6794216dc45 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa_gtid_pos_auto_engine-master.opt @@ -0,0 +1 @@ +--binlog-ignore-db=test_ign diff --git a/mysql-test/suite/rpl/t/rpl_xa_gtid_pos_auto_engine.test b/mysql-test/suite/rpl/t/rpl_xa_gtid_pos_auto_engine.test new file mode 100644 index 00000000000..b83493762c3 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa_gtid_pos_auto_engine.test @@ -0,0 +1,29 @@ +--source include/have_innodb.inc +--source include/master-slave.inc + +--connection slave +call mtr.add_suppression("The automatically created table.*name may not be entirely in lowercase"); + +--source include/stop_slave.inc +CHANGE MASTER TO master_use_gtid=slave_pos; + +SET @@global.gtid_pos_auto_engines="innodb"; +--source include/start_slave.inc +--let $rpl_xa_check_lhs= @@global.gtid_slave_pos +--let $rpl_xa_check_rhs= CONCAT(domain_id,"-",server_id,"-",seq_no) FROM mysql.gtid_slave_pos WHERE seq_no = (SELECT DISTINCT max(seq_no) FROM mysql.gtid_slave_pos) +--let $rpl_xa_check=SELECT $rpl_xa_check_lhs = $rpl_xa_check_rhs +--source rpl_xa.inc + +--connection slave +--source include/stop_slave.inc +SET @@global.gtid_pos_auto_engines=""; +SET @@session.sql_log_bin=0; +DROP TABLE mysql.gtid_slave_pos_InnoDB; +if (`SHOW COUNT(*) WARNINGS`) +{ + show tables in mysql like 'gtid_slave_pos%'; +} +SET @@session.sql_log_bin=1; +--source include/start_slave.inc + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect.test b/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect.test new file mode 100644 index 00000000000..28d189364d6 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect.test @@ -0,0 +1,297 @@ +# BUG #12161 Xa recovery and client disconnection +# the test verifies that +# a. disconnection does not lose a prepared transaction +# so it can be committed from another connection +# c. the prepared transaction is logged +# d. interleaved prepared transactions are correctly applied on the slave. + +# +# Both replication format are checked through explict +# set @@binlog_format in the test. +# +--source include/have_innodb.inc +--source include/have_binlog_format_mixed.inc +# +# Prepared XA can't get available to an external connection +# until a connection, that either leaves actively or is killed, +# has completed a necessary part of its cleanup. +# Selecting from P_S.threads provides a method to learn that. +# +--source include/have_perfschema.inc +--source include/master-slave.inc + +--connection master +call mtr.add_suppression("Found 2 prepared XA transactions"); +CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND'; + +CREATE DATABASE d1; +CREATE DATABASE d2; + +CREATE TABLE d1.t (a INT) ENGINE=innodb; +CREATE TABLE d2.t (a INT) ENGINE=innodb; + +connect (master_conn1, 127.0.0.1,root,,test,$MASTER_MYPORT,); +--let $conn_id=`SELECT connection_id()` +SET @@session.binlog_format= statement; +XA START '1-stmt'; +INSERT INTO d1.t VALUES (1); +XA END '1-stmt'; +XA PREPARE '1-stmt'; + +--disconnect master_conn1 + +--connection master + +--let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id +--source include/wait_condition.inc + +connect (master_conn2, 127.0.0.1,root,,test,$MASTER_MYPORT,); +--let $conn_id=`SELECT connection_id()` +SET @@session.binlog_format= row; +XA START '1-row'; +INSERT INTO d2.t VALUES (1); +XA END '1-row'; +XA PREPARE '1-row'; + +--disconnect master_conn2 + +--connection master +--let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id +--source include/wait_condition.inc + +XA START '2'; +INSERT INTO d1.t VALUES (2); +XA END '2'; +XA PREPARE '2'; +XA COMMIT '2'; + +XA COMMIT '1-row'; +XA COMMIT '1-stmt'; +source include/show_binlog_events.inc; + +# the proof: slave is in sync with the table updated by the prepared transactions. +--source include/sync_slave_sql_with_master.inc + +--source include/stop_slave.inc + +# +# Recover with Master server restart +# +--connection master + +connect (master2, 127.0.0.1,root,,test,$MASTER_MYPORT,); +--connection master2 +SET @@session.binlog_format= statement; +XA START '3-stmt'; +INSERT INTO d1.t VALUES (3); +XA END '3-stmt'; +XA PREPARE '3-stmt'; +--disconnect master2 + +connect (master2, 127.0.0.1,root,,test,$MASTER_MYPORT,); +--connection master2 +SET @@session.binlog_format= row; +XA START '3-row'; +INSERT INTO d2.t VALUES (4); +XA END '3-row'; +XA PREPARE '3-row'; +--disconnect master2 + +--connection master + +# +# Testing read-only +# +connect (master2, 127.0.0.1,root,,test,$MASTER_MYPORT,); +--connection master2 +XA START '4'; +SELECT * FROM d1.t; +XA END '4'; +XA PREPARE '4'; +--disconnect master2 + +# +# Logging few disconnected XA:s for replication. +# +--let $bulk_trx_num=10 +--let $i = $bulk_trx_num + +while($i > 0) +{ + --connect (master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,) + --let $conn_id=`SELECT connection_id()` + + --eval XA START 'bulk_trx_$i' + --eval INSERT INTO d1.t VALUES ($i) + --eval INSERT INTO d2.t VALUES ($i) + --eval XA END 'bulk_trx_$i' + --eval XA PREPARE 'bulk_trx_$i' + + --disconnect master_bulk_conn$i + + --connection master + --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id + --source include/wait_condition.inc + + --dec $i +} + +# +# Prove the slave applier is capable to resume the prepared XA:s +# upon its restart. +# +--connection slave +--source include/start_slave.inc +--connection master +--source include/sync_slave_sql_with_master.inc +--source include/stop_slave.inc + +--connection master +--let $i = $bulk_trx_num +while($i > 0) +{ + --let $command=COMMIT + if (`SELECT $i % 2`) + { + --let $command=ROLLBACK + } + --eval XA $command 'bulk_trx_$i' + --dec $i +} + +--let $rpl_server_number= 1 +--source include/rpl_restart_server.inc + +--connection slave +--source include/start_slave.inc + +--connection master +--echo *** '3-stmt','3-row' xa-transactions must be in the list *** +XA RECOVER; +XA COMMIT '3-stmt'; +XA ROLLBACK '3-row'; + +--source include/sync_slave_sql_with_master.inc + +# +# Testing replication with marginal XID values and in two formats. +# + +--connection master +--let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id +--source include/wait_condition.inc + +# Max size XID incl max value of formatID +--let $formatid_range=`SELECT (1<<31)` +--let $max_formatid=`SELECT (1<<31) - 1` + +connect (master_conn2, 127.0.0.1,root,,test,$MASTER_MYPORT,); +--let $conn_id=`SELECT connection_id()` + +--let $gtrid=0123456789012345678901234567890123456789012345678901234567890124 +--let $bqual=0123456789012345678901234567890123456789012345678901234567890124 +--eval XA START '$gtrid','$bqual',$max_formatid + INSERT INTO d1.t VALUES (64); +--eval XA END '$gtrid','$bqual',$max_formatid +--eval XA PREPARE '$gtrid','$bqual',$max_formatid + +--disconnect master_conn2 + +--connection master +--let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id +--source include/wait_condition.inc + +# Max size XID with non-ascii chars +connect (master_conn3, 127.0.0.1,root,,test,$MASTER_MYPORT,); +--let $conn_id=`SELECT connection_id()` + +--let $gtrid_hex=FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF +--let $bqual_hex=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 +--eval XA START X'$gtrid_hex',X'$bqual_hex',0 + INSERT INTO d1.t VALUES (0); +--eval XA END X'$gtrid_hex',X'$bqual_hex',0 +--eval XA PREPARE X'$gtrid_hex',X'$bqual_hex',0 + +--disconnect master_conn3 + +--connection master +--let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id +--source include/wait_condition.inc + +# Random XID +--disable_query_log + +connect (master_conn4, 127.0.0.1,root,,test,$MASTER_MYPORT,); +--let $conn_id=`SELECT connection_id()` + +--let $gtridlen=`SELECT 2*(1 + round(rand()*100) % 31)` +--let $bquallen=`SELECT 2*(1 + round(rand()*100) % 31)` +--let $gtrid_rand=`SELECT substring(concat(MD5(rand()), MD5(rand())), 1, $gtridlen)` +--let $bqual_rand=`SELECT substring(concat(MD5(rand()), MD5(rand())), 1, $bquallen)` +--let $formt_rand=`SELECT floor((rand()*10000000000) % $formatid_range)` +--eval XA START X'$gtrid_rand',X'$bqual_rand',$formt_rand + INSERT INTO d1.t VALUES (0); +--eval XA END X'$gtrid_rand',X'$bqual_rand',$formt_rand +--eval XA PREPARE X'$gtrid_rand',X'$bqual_rand',$formt_rand + +--enable_query_log + +--disconnect master_conn4 + +--connection master +--let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id +--source include/wait_condition.inc + +--eval XA COMMIT '$gtrid','$bqual',$max_formatid +--eval XA COMMIT X'$gtrid_hex',X'$bqual_hex',0 +--disable_query_log +--echo XA COMMIT 'RANDOM XID' +--eval XA COMMIT X'$gtrid_rand',X'$bqual_rand',$formt_rand +--enable_query_log + +--source include/sync_slave_sql_with_master.inc + +# +# Testing ONE PHASE +# +--let $onephase_trx_num=10 +--let $i = $onephase_trx_num +while($i > 0) +{ + --connect (master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,) + + --connection master_bulk_conn$i + --eval XA START 'one_phase_$i' + --eval INSERT INTO d1.t VALUES ($i) + --eval INSERT INTO d2.t VALUES ($i) + --eval XA END 'one_phase_$i' + --eval XA COMMIT 'one_phase_$i' ONE PHASE + + --disconnect master_bulk_conn$i + --dec $i +} +--connection master +--source include/sync_slave_sql_with_master.inc + +# +# Overall consistency check +# +--let $diff_tables= master:d1.t, slave:d1.t +--source include/diff_tables.inc +--let $diff_tables= master:d2.t, slave:d2.t +--source include/diff_tables.inc +# +# cleanup +# +--connection master + +DELETE FROM d1.t; +DELETE FROM d2.t; +DROP TABLE d1.t, d2.t; +DROP DATABASE d1; +DROP DATABASE d2; +DROP VIEW v_processlist; + +--source include/sync_slave_sql_with_master.inc + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect_lsu_off-slave.opt b/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect_lsu_off-slave.opt new file mode 100644 index 00000000000..94c3650024f --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect_lsu_off-slave.opt @@ -0,0 +1,2 @@ +--log-slave-updates=off + diff --git a/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect_lsu_off.test b/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect_lsu_off.test new file mode 100644 index 00000000000..df3811df6ae --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect_lsu_off.test @@ -0,0 +1,8 @@ +# ==== Purpose ==== +# 'rpl_xa_survive_disconnect_lsu_off' verifies the same properties as the sourced file +# in conditions of the slave does not log own updates +# (lsu in the name stands for log_slave_updates). +# Specifically this mode aims at proving correct operations on the slave +# mysql.gtid_executed. + +--source ./rpl_xa_survive_disconnect.test diff --git a/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect_mixed_engines.test b/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect_mixed_engines.test new file mode 100644 index 00000000000..f52a9630a87 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa_survive_disconnect_mixed_engines.test @@ -0,0 +1,68 @@ +# BUG#12161 Xa recovery and client disconnection +# +# The test verifies correct XA transaction two phase logging and its applying +# in a case the transaction updates transactional and non-transactional tables. +# Transactions are terminated according to specfied parameters to +# a sourced inc-file. + +--source include/have_innodb.inc +--source include/master-slave.inc + +--connection master +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); + +--let $command=setup +--source include/rpl_xa_mixed_engines.inc + +--echo === COMMIT === +--let $command=run +--let $xa_terminate=XA COMMIT +--let $xa_prepare_opt=1 +--source include/rpl_xa_mixed_engines.inc + +--source include/sync_slave_sql_with_master.inc +--connection master + +--echo === COMMIT ONE PHASE === + +--let $command=run +--let $xa_terminate=XA COMMIT +--let $one_phase=ONE PHASE +--let $xa_prepare_opt= +--source include/rpl_xa_mixed_engines.inc +--let $one_phase= +--source include/sync_slave_sql_with_master.inc +--connection master + +--echo === ROLLBACK with PREPARE === + +--let $command=run +--let $xa_terminate=xa rollback +--let $xa_prepare_opt=1 +--source include/rpl_xa_mixed_engines.inc + +--source include/sync_slave_sql_with_master.inc +--connection master + +--echo === ROLLBACK with no PREPARE === + +--let $command=run +--let $xa_terminate=xa rollback +--let $xa_prepare_opt= +--source include/rpl_xa_mixed_engines.inc +--let $xa_rollback_only= + +--source include/sync_slave_sql_with_master.inc + +--let $diff_tables= master:tm, slave:tm +--source include/diff_tables.inc + +# Cleanup + +--connection master +--let $command=cleanup +--source include/rpl_xa_mixed_engines.inc + +--source include/sync_slave_sql_with_master.inc + +--source include/rpl_end.inc |