| 1 |
# ==== Purpose ==== |
| 2 |
# |
| 3 |
# This test will generate two XA transactions on the master in a way that |
| 4 |
# they will block each other on the slave if the transaction isolation level |
| 5 |
# used by the slave applier is more restrictive than the READ COMMITTED one. |
| 6 |
# |
| 7 |
# Consider: |
| 8 |
# E=execute, P=prepare, C=commit; |
| 9 |
# 1=first transaction, 2=second transaction; |
| 10 |
# |
| 11 |
# Master does: E1, E2, P2, P1, C1, C2 |
| 12 |
# Slave does: E2, P2, E1, P1, C1, C2 |
| 13 |
# |
| 14 |
# The transactions are designed so that, if the applier transaction isolation |
| 15 |
# level is more restrictive than the READ COMMITTED, E1 will be blocked on |
| 16 |
# the slave waiting for gap locks to be released. |
| 17 |
# |
| 18 |
# Step 1 |
| 19 |
# |
| 20 |
# The test will verify that the transactions don't block each other because |
| 21 |
# the applier thread automatically changed the isolation level. |
| 22 |
# |
| 23 |
# Step 2 |
| 24 |
# |
| 25 |
# The test will verify that applying master's binary log dump in slave doesn't |
| 26 |
# block because mysqlbinlog is informing the isolation level to be used. |
| 27 |
# |
| 28 |
# ==== Related Bugs and Worklogs ==== |
| 29 |
# |
| 30 |
# BUG#25040331: INTERLEAVED XA TRANSACTIONS MAY DEADLOCK SLAVE APPLIER WITH |
| 31 |
# REPEATABLE READ |
| 32 |
# |
| 33 |
--source include/have_debug.inc |
| 34 |
# The test case only make sense for RBR |
| 35 |
--source include/have_binlog_format_row.inc |
| 36 |
--source include/master-slave.inc |
| 37 |
|
| 38 |
--source include/rpl_connection_slave.inc |
| 39 |
# To hit the issue, we need to split the data in two pages. |
| 40 |
# This global variable will help us. |
| 41 |
SET @saved_innodb_limit_optimistic_insert_debug = @@GLOBAL.innodb_limit_optimistic_insert_debug; |
| 42 |
SET @@GLOBAL.innodb_limit_optimistic_insert_debug = 2; |
| 43 |
|
| 44 |
# |
| 45 |
# Step 1 - Using async replication |
| 46 |
# |
| 47 |
|
| 48 |
# Let's generate the workload on the master |
| 49 |
--source include/rpl_connection_master.inc |
| 50 |
CREATE TABLE t1 ( |
| 51 |
c1 INT NOT NULL, |
| 52 |
KEY(c1) |
| 53 |
) ENGINE=InnoDB; |
| 54 |
|
| 55 |
CREATE TABLE t2 ( |
| 56 |
c1 INT NOT NULL, |
| 57 |
FOREIGN KEY(c1) REFERENCES t1(c1) |
| 58 |
) ENGINE=InnoDB; |
| 59 |
|
| 60 |
INSERT INTO t1 VALUES (1), (3), (4); |
| 61 |
|
| 62 |
--source include/rpl_connection_master1.inc |
| 63 |
XA START 'XA1'; |
| 64 |
INSERT INTO t1 values(2); |
| 65 |
XA END 'XA1'; |
| 66 |
|
| 67 |
# This transaction will reference the gap where XA1 |
| 68 |
# was inserted, and will be prepared and committed |
| 69 |
# before XA1, so the slave will prepare it (but will |
| 70 |
# not commit it) before preparing XA1. |
| 71 |
--source include/rpl_connection_master.inc |
| 72 |
XA START 'XA2'; |
| 73 |
INSERT INTO t2 values(3); |
| 74 |
XA END 'XA2'; |
| 75 |
|
| 76 |
# The XA2 prepare should be binary logged first |
| 77 |
XA PREPARE 'XA2'; |
| 78 |
|
| 79 |
# The XA1 prepare should be binary logged |
| 80 |
# after XA2 prepare and before XA2 commit. |
| 81 |
--source include/rpl_connection_master1.inc |
| 82 |
XA PREPARE 'XA1'; |
| 83 |
|
| 84 |
# The commit order doesn't matter much for the issue being tested. |
| 85 |
XA COMMIT 'XA1'; |
| 86 |
--source include/rpl_connection_master.inc |
| 87 |
XA COMMIT 'XA2'; |
| 88 |
|
| 89 |
# Everything is fine if the slave can sync with the master. |
| 90 |
--source include/sync_slave_sql_with_master.inc |
| 91 |
|
| 92 |
# |
| 93 |
# Step 2 - Using mysqlbinlog dump to restore the salve |
| 94 |
# |
| 95 |
--source include/stop_slave.inc |
| 96 |
DROP TABLE t2, t1; |
| 97 |
RESET SLAVE; |
| 98 |
RESET MASTER; |
| 99 |
|
| 100 |
--source include/rpl_connection_master.inc |
| 101 |
--let $master_data_dir= `SELECT @@datadir` |
| 102 |
--let $master_log_file= query_get_value(SHOW MASTER STATUS, File, 1) |
| 103 |
--let $mysql_server= $MYSQL --defaults-group-suffix=.2 |
| 104 |
--echo Restore binary log from the master into the slave |
| 105 |
--exec $MYSQL_BINLOG --force-if-open $master_data_dir/$master_log_file | $mysql_server |
| 106 |
|
| 107 |
--let $diff_tables= master:test.t1, slave:test.t1 |
| 108 |
--source include/diff_tables.inc |
| 109 |
--let $diff_tables= master:test.t2, slave:test.t2 |
| 110 |
--source include/diff_tables.inc |
| 111 |
|
| 112 |
# |
| 113 |
# Cleanup |
| 114 |
# |
| 115 |
--let $master_file= query_get_value(SHOW MASTER STATUS, File, 1) |
| 116 |
--let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1) |
| 117 |
DROP TABLE t2, t1; |
| 118 |
|
| 119 |
# When GTID_MODE=OFF, we need to skip already applied transactions |
| 120 |
--source include/rpl_connection_slave.inc |
| 121 |
--let $gtid_mode= `SELECT @@GTID_MODE` |
| 122 |
if ($gtid_mode == OFF) |
| 123 |
{ |
| 124 |
--disable_query_log |
| 125 |
--disable_result_log |
| 126 |
--eval CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='$master_file', SOURCE_LOG_POS=$master_pos |
| 127 |
--enable_result_log |
| 128 |
--enable_query_log |
| 129 |
} |
| 130 |
SET @@GLOBAL.innodb_limit_optimistic_insert_debug = @saved_innodb_limit_optimistic_insert_debug; |
| 131 |
--source include/start_slave.inc |
| 132 |
|
| 133 |
--source include/rpl_end.inc |