Debian Codegraph @

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

raw
1 #
2 # 1. Create source-replica topolgy.
3 include/master-slave.inc
4 Warnings:
5 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
6 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.
7 [connection master]
8 #
9 # 2. Setup testing environment.
10 # 2.1. Create table on source and insert few rows.
11 # 2.2. Setup necessary variables on replica server.
12 CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB;
13 INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
14 include/sync_slave_sql_with_master.inc
15 include/stop_slave_sql.inc
16 SET @saved_replica_parallel_type = @@GLOBAL.replica_parallel_type;
17 Warnings:
18 Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release.
19 SET @saved_replica_parallel_workers = @@GLOBAL.replica_parallel_workers;
20 SET @saved_replica_preserve_commit_order = @@GLOBAL.replica_preserve_commit_order;
21 SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout;
22 SET @saved_replica_transaction_retries = @@GLOBAL.replica_transaction_retries;
23 SET GLOBAL replica_transaction_retries = 0;
24 SET GLOBAL replica_parallel_type = "LOGICAL_CLOCK";
25 Warnings:
26 Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release.
27 SET GLOBAL replica_parallel_workers = 8;
28 SET GLOBAL replica_preserve_commit_order = ON;
29 SET GLOBAL innodb_lock_wait_timeout = 1000;
30 #
31 # Case 1
32 # ======
33 #
34 # Verify replica server can find the deadlock when the victim thread
35 # (DELETE) is waiting for its turn to commit and is handled properly.
36 #
37 # 3.1 Execute transactions on source server that would generate deadlock on the
38 # replica server.
39 [connection master]
40 INSERT INTO t1 VALUES(10, 10);
41 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
42 INSERT INTO t1 VALUES(11, NULL);
43 DELETE FROM t1 WHERE c2 <= 3;
44 INSERT INTO t1 VALUES(21, 21);
45 INSERT INTO t1 VALUES(22, 22);
46 INSERT INTO t1 VALUES(23, 23);
47 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
48 [connection slave]
49 BEGIN;
50 INSERT INTO t1 VALUES(11, 11);
51 [connection slave1]
52 include/start_slave_sql.inc
53 [connection slave]
54 ROLLBACK;
55 # Add error supressions.
56 CALL mtr.add_suppression("Worker .* failed executing transaction.*");
57 #
58 # 3.2. Wait till the co-ordinator thread to error out with ER_LOCK_DEADLOCK.
59 include/wait_for_slave_sql_error.inc [errno=1213]
60 #
61 # 3.3. Restart replication threads, sync the replica and verify that table
62 # data is consistent.
63 include/start_slave.inc
64 include/rpl_diff.inc
65 #
66 # Case 2
67 # ======
68 #
69 # Verify that replica server can find the deadlock when the victim
70 # thread (DELETE) is in the middle of transaction and is handled
71 # properly.
72 #
73 # 3.4 Execute transactions on source server that would generate deadlock on the
74 # replica server.
75 [connection master]
76 TRUNCATE t1;
77 INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
78 include/sync_slave_sql_with_master.inc
79 include/stop_slave_sql.inc
80 [connection master]
81 INSERT INTO t1 VALUES(20, NULL);
82 # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug
83 INSERT INTO t1 VALUES(21, NULL);
84 BEGIN;
85 INSERT INTO t1 VALUES(22, 22);
86 DELETE FROM t1 WHERE c2 <= 3;
87 INSERT INTO t1 VALUES(23, 23);
88 INSERT INTO t1 VALUES(24, NULL);
89 INSERT INTO t1 VALUES(25, 25);
90 COMMIT;
91 INSERT INTO t1 VALUES(26, 26);
92 INSERT INTO t1 VALUES(27, 27);
93 INSERT INTO t1 VALUES(28, 28);
94 # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug
95 [connection slave]
96 BEGIN;
97 INSERT INTO t1 VALUES(21, 21);
98 [connection server_2_1]
99 BEGIN;
100 INSERT INTO t1 VALUES(23, 23);
101 [connection slave1]
102 include/start_slave_sql.inc
103 [connection slave]
104 ROLLBACK;
105 [connection server_2_1]
106 ROLLBACK;
107 #
108 # 3.5. Wait till the co-ordinator thread to error out with ER_MTA_INCONSISTENT_DATA.
109 include/wait_for_slave_sql_error.inc [errno=1756]
110 #
111 # 3.6. Restart replication threads, sync the replica and verify that table
112 # data is consistent.
113 include/start_slave.inc
114 include/rpl_diff.inc
115 #
116 # 4. Cleanup.
117 #
118 [connection master]
119 DROP TABLE t1;
120 include/sync_slave_sql_with_master.inc
121 CALL mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state.");
122 include/stop_slave.inc
123 SET GLOBAL replica_transaction_retries = @saved_replica_transaction_retries;
124 SET GLOBAL replica_parallel_type = @saved_replica_parallel_type;
125 Warnings:
126 Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release.
127 SET GLOBAL replica_parallel_workers = @saved_replica_parallel_workers;
128 SET GLOBAL replica_preserve_commit_order = @saved_replica_preserve_commit_order;
129 SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout;
130 include/rpl_end.inc