Debian Codegraph @

mysql-test/suite/rpl_nogtid/r/rpl_deadlock_innodb.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 *** Prepare tables and data ***
7 CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=innodb;
8 CREATE TABLE t2 (a INT) ENGINE=innodb;
9 CREATE TABLE t3 (a INT NOT NULL, KEY(a)) ENGINE=innodb;
10 SHOW CREATE TABLE t1;
11 Table Create Table
12 t1 CREATE TABLE `t1` (
13 `a` int NOT NULL,
14 KEY `a` (`a`)
15 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
16 SHOW CREATE TABLE t2;
17 Table Create Table
18 t2 CREATE TABLE `t2` (
19 `a` int DEFAULT NULL
20 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
21 SHOW CREATE TABLE t3;
22 Table Create Table
23 t3 CREATE TABLE `t3` (
24 `a` int NOT NULL,
25 KEY `a` (`a`)
26 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
27 SELECT @@GLOBAL.replica_transaction_retries;
28 @@GLOBAL.replica_transaction_retries
29 2
30 SELECT @@GLOBAL.innodb_lock_wait_timeout;
31 @@GLOBAL.innodb_lock_wait_timeout
32 4
33 include/stop_slave.inc
34 BEGIN;
35 INSERT INTO t1 VALUES (1);
36 INSERT INTO t2 VALUES (2), (2), (2), (2), (2), (2), (2), (2), (2), (2);
37 INSERT INTO t3 VALUES (3);
38 COMMIT;
39
40 *** Test deadlock ***
41 BEGIN;
42 SELECT * FROM t1 FOR UPDATE;
43 a
44 include/start_slave.inc
45 SELECT COUNT(*) FROM t2;
46 COUNT(*)
47 0
48 COMMIT;
49
50 # Test that the performance schema column shows > 0 values.
51
52 include/assert.inc [current number of retries should be more than the value saved before deadlock.]
53 include/check_slave_is_running.inc
54 SELECT * FROM t1;
55 a
56 1
57 SELECT * FROM t3;
58 a
59 3
60
61 *** Test lock wait timeout ***
62 include/stop_slave.inc
63 DELETE FROM t2;
64 RESET MASTER;
65 CHANGE REPLICATION SOURCE TO SOURCE_LOG_POS=MASTER_POS_BEGIN;
66 BEGIN;
67 SELECT * FROM t1 FOR UPDATE;
68 a
69 1
70 START SLAVE;
71 Warnings:
72 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
73 include/wait_for_slave_sql_error.inc [errno=1205]
74 SELECT COUNT(*) FROM t2;
75 COUNT(*)
76 0
77 COMMIT;
78 include/start_slave.inc
79 SELECT * FROM t1;
80 a
81 1
82 1
83 SELECT * FROM t3;
84 a
85 3
86 3
87 include/check_slave_is_running.inc
88
89 *** Test lock wait timeout and purged relay logs ***
90 SET @my_max_relay_log_size= @@global.max_relay_log_size;
91 SET global max_relay_log_size=0;
92 include/stop_slave.inc
93 DELETE FROM t2;
94 RESET MASTER;
95 CHANGE REPLICATION SOURCE TO SOURCE_LOG_POS=MASTER_POS_BEGIN;
96 BEGIN;
97 SELECT * FROM t1 FOR UPDATE;
98 a
99 1
100 1
101 START SLAVE;
102 Warnings:
103 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
104 include/wait_for_slave_sql_error.inc [errno=1205]
105 SELECT COUNT(*) FROM t2;
106 COUNT(*)
107 0
108 COMMIT;
109 include/start_slave.inc
110 SELECT * FROM t1;
111 a
112 1
113 1
114 1
115 SELECT * FROM t3;
116 a
117 3
118 3
119 3
120 include/check_slave_is_running.inc
121
122 *** Test the deadlock warning to be escalated into the error ***
123 delete from t1;
124 delete from t2;
125 delete from t3;
126 delete from t1;
127 delete from t2;
128 delete from t3;
129 set @save.replica_transaction_retries= @@global.replica_transaction_retries;
130 set @@global.replica_transaction_retries= 0;
131 include/stop_slave.inc
132 BEGIN;
133 INSERT INTO t1 VALUES (1);
134 INSERT INTO t2 VALUES (2), (2), (2), (2), (2), (2), (2), (2), (2), (2);
135 INSERT INTO t3 VALUES (3);
136 COMMIT;
137 BEGIN;
138 SELECT count(*) as zero FROM t1 FOR UPDATE;
139 zero
140 0
141 START SLAVE;
142 Warnings:
143 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
144 *** Now the slave must be stopped due to timeout ***
145 include/wait_for_slave_sql_error.inc [errno=1205]
146 rollback;
147 set @@global.replica_transaction_retries= @save.replica_transaction_retries;
148 include/start_slave.inc
149 *** Clean up ***
150 DROP TABLE t1,t2,t3;
151 SET global max_relay_log_size= @my_max_relay_log_size;
152 CALL mtr.add_suppression(".*worker thread retried transaction.*");
153 CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*");
154 End of 5.1 tests
155 include/rpl_end.inc