| 1 |
include/master-slave.inc |
| 2 |
Warnings: |
| 3 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 4 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 5 |
[connection master] |
| 6 |
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB; |
| 7 |
INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6); |
| 8 |
include/sync_slave_sql_with_master.inc |
| 9 |
include/stop_slave_sql.inc |
| 10 |
SET @saved_replica_parallel_type = @@GLOBAL.replica_parallel_type; |
| 11 |
Warnings: |
| 12 |
Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release. |
| 13 |
SET @saved_replica_parallel_workers = @@GLOBAL.replica_parallel_workers; |
| 14 |
SET @saved_replica_preserve_commit_order = @@GLOBAL.replica_preserve_commit_order; |
| 15 |
SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout; |
| 16 |
SET @saved_replica_transaction_retries = @@GLOBAL.replica_transaction_retries; |
| 17 |
SET GLOBAL replica_transaction_retries = 2; |
| 18 |
SET GLOBAL replica_parallel_type = "LOGICAL_CLOCK"; |
| 19 |
Warnings: |
| 20 |
Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release. |
| 21 |
SET GLOBAL replica_parallel_workers = 3; |
| 22 |
SET GLOBAL replica_preserve_commit_order = ON; |
| 23 |
SET GLOBAL innodb_lock_wait_timeout = 1000; |
| 24 |
[connection slave] |
| 25 |
# |
| 26 |
# Case 1: Verify slave can find the deadlock when DELETE is waiting |
| 27 |
# for its turn to commit |
| 28 |
# |
| 29 |
[connection master] |
| 30 |
INSERT INTO t1 VALUES(10, 10); |
| 31 |
# Adding debug point 'd,set_commit_parent_100' to @@GLOBAL.debug |
| 32 |
INSERT INTO t1 VALUES(11, NULL); |
| 33 |
DELETE FROM t1 WHERE c2 <= 3; |
| 34 |
[connection slave] |
| 35 |
BEGIN; |
| 36 |
INSERT INTO t1 VALUES(11, 11); |
| 37 |
[connection slave1] |
| 38 |
include/start_slave_sql.inc |
| 39 |
[connection slave] |
| 40 |
ROLLBACK; |
| 41 |
include/rpl_diff.inc |
| 42 |
[connection master] |
| 43 |
# Removing debug point 'd,set_commit_parent_100' from @@GLOBAL.debug |
| 44 |
TRUNCATE t1; |
| 45 |
INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6); |
| 46 |
include/sync_slave_sql_with_master.inc |
| 47 |
include/stop_slave_sql.inc |
| 48 |
# |
| 49 |
# Case 2: Verify slave can find the deadlock when it begins to applying |
| 50 |
# second DELETE statement. |
| 51 |
# |
| 52 |
[connection master] |
| 53 |
INSERT INTO t1 VALUES(20, NULL); |
| 54 |
# Adding debug point 'd,set_commit_parent_100' to @@GLOBAL.debug |
| 55 |
INSERT INTO t1 VALUES(21, NULL); |
| 56 |
BEGIN; |
| 57 |
INSERT INTO t1 VALUES(22, 22); |
| 58 |
DELETE FROM t1 WHERE c2 <= 3; |
| 59 |
INSERT INTO t1 VALUES(23, 23); |
| 60 |
INSERT INTO t1 VALUES(24, 24); |
| 61 |
INSERT INTO t1 VALUES(25, 25); |
| 62 |
COMMIT; |
| 63 |
[connection slave] |
| 64 |
BEGIN; |
| 65 |
INSERT INTO t1 VALUES(21, 21); |
| 66 |
[connection server_2_1] |
| 67 |
BEGIN; |
| 68 |
INSERT INTO t1 VALUES(23, 23); |
| 69 |
[connection slave1] |
| 70 |
include/start_slave_sql.inc |
| 71 |
[connection slave] |
| 72 |
ROLLBACK; |
| 73 |
[connection server_2_1] |
| 74 |
ROLLBACK; |
| 75 |
include/rpl_diff.inc |
| 76 |
[connection master] |
| 77 |
# Removing debug point 'd,set_commit_parent_100' from @@GLOBAL.debug |
| 78 |
TRUNCATE t1; |
| 79 |
INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (32, 32); |
| 80 |
include/sync_slave_sql_with_master.inc |
| 81 |
include/stop_slave_sql.inc |
| 82 |
# |
| 83 |
# Test case 3: Verify the worker can handle it correctly when it is |
| 84 |
# retrying a transaction. |
| 85 |
# |
| 86 |
[connection master] |
| 87 |
INSERT INTO t1 VALUES(30, NULL); |
| 88 |
# Adding debug point 'd,set_commit_parent_100' to @@GLOBAL.debug |
| 89 |
INSERT INTO t1 VALUES(31, NULL); |
| 90 |
INSERT INTO t1 VALUES(33, NULL); |
| 91 |
DELETE FROM t1 WHERE c2 <= 3; |
| 92 |
[connection slave] |
| 93 |
BEGIN; |
| 94 |
INSERT INTO t1 VALUES(31, 31); |
| 95 |
[connection server_2_1] |
| 96 |
BEGIN; |
| 97 |
INSERT INTO t1 VALUES(33, 33); |
| 98 |
[connection slave1] |
| 99 |
include/start_slave_sql.inc |
| 100 |
[connection slave] |
| 101 |
ROLLBACK; |
| 102 |
[connection server_2_1] |
| 103 |
ROLLBACK; |
| 104 |
include/rpl_diff.inc |
| 105 |
# |
| 106 |
# Test Case 4: Innodb internal transaction deadlock |
| 107 |
# |
| 108 |
[connection master] |
| 109 |
CALL mtr.add_suppression(".*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*"); |
| 110 |
# Removing debug point 'd,set_commit_parent_100' from @@GLOBAL.debug |
| 111 |
CREATE TABLE t2(database_name VARCHAR(200), table_name VARCHAR(200)); |
| 112 |
INSERT INTO t2 SELECT database_name, table_name FROM mysql.innodb_table_stats; |
| 113 |
include/sync_slave_sql_with_master.inc |
| 114 |
include/stop_slave_sql.inc |
| 115 |
CALL mtr.add_suppression(".* Cannot save table statistics for table.*"); |
| 116 |
LOCK TABLE t1 WRITE; |
| 117 |
[connection master] |
| 118 |
TRUNCATE t2; |
| 119 |
# Adding debug point 'd,set_commit_parent_100' to @@GLOBAL.debug |
| 120 |
ANALYZE TABLE t1; |
| 121 |
Table Op Msg_type Msg_text |
| 122 |
test.t1 analyze status OK |
| 123 |
INSERT INTO t2 SELECT database_name, table_name FROM mysql.innodb_table_stats; |
| 124 |
[connection slave] |
| 125 |
include/start_slave_sql.inc |
| 126 |
UNLOCK TABLES; |
| 127 |
# |
| 128 |
# Test Case 5: It won't cause transaction rollback if |
| 129 |
# replica_preserve_commit_order is OFF |
| 130 |
# |
| 131 |
[connection master] |
| 132 |
# Removing debug point 'd,set_commit_parent_100' from @@GLOBAL.debug |
| 133 |
TRUNCATE t1; |
| 134 |
INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6); |
| 135 |
include/sync_slave_sql_with_master.inc |
| 136 |
include/stop_slave_sql.inc |
| 137 |
[connection master] |
| 138 |
INSERT INTO t1 VALUES(50, 50); |
| 139 |
# Adding debug point 'd,set_commit_parent_100' to @@GLOBAL.debug |
| 140 |
INSERT INTO t1 VALUES(51, NULL); |
| 141 |
BEGIN; |
| 142 |
INSERT INTO t1 VALUES(52, 52); |
| 143 |
DELETE FROM t1 WHERE c2 <= 3; |
| 144 |
INSERT INTO t1 VALUES(53, 53); |
| 145 |
INSERT INTO t1 VALUES(54, 54); |
| 146 |
INSERT INTO t1 VALUES(55, 55); |
| 147 |
COMMIT; |
| 148 |
[connection slave] |
| 149 |
BEGIN; |
| 150 |
INSERT INTO t1 VALUES(51, 51); |
| 151 |
[connection server_2_1] |
| 152 |
BEGIN; |
| 153 |
INSERT INTO t1 VALUES(53, 53); |
| 154 |
[connection slave1] |
| 155 |
SET GLOBAL replica_preserve_commit_order = OFF; |
| 156 |
SET GLOBAL replica_transaction_retries = 0; |
| 157 |
include/start_slave_sql.inc |
| 158 |
[connection slave] |
| 159 |
ROLLBACK; |
| 160 |
[connection server_2_1] |
| 161 |
ROLLBACK; |
| 162 |
include/rpl_diff.inc |
| 163 |
# |
| 164 |
# Deinitialize |
| 165 |
# |
| 166 |
[connection master] |
| 167 |
# Removing debug point 'd,set_commit_parent_100' from @@GLOBAL.debug |
| 168 |
DROP TABLE t1, t2; |
| 169 |
include/sync_slave_sql_with_master.inc |
| 170 |
include/stop_slave.inc |
| 171 |
SET GLOBAL replica_transaction_retries = @saved_replica_transaction_retries; |
| 172 |
SET GLOBAL replica_parallel_type = @saved_replica_parallel_type; |
| 173 |
Warnings: |
| 174 |
Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release. |
| 175 |
SET GLOBAL replica_parallel_workers = @saved_replica_parallel_workers; |
| 176 |
SET GLOBAL replica_preserve_commit_order = @saved_replica_preserve_commit_order; |
| 177 |
SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout; |
| 178 |
include/start_slave.inc |
| 179 |
include/rpl_end.inc |