Debian Codegraph @

mysql-test/suite/rpl/r/rpl_mts_replica_preserve_commit_order_deadlock.result

raw
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