1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
include/master-slave.inc
[connection master]
*** Prepare tables and data ***
connection master;
CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=innodb;
CREATE TABLE t2 (a INT) ENGINE=innodb;
CREATE TABLE t3 (a INT NOT NULL, KEY(a)) ENGINE=innodb;
connection slave;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SHOW CREATE TABLE t3;
Table Create Table
t3 CREATE TABLE `t3` (
`a` int(11) NOT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SHOW VARIABLES LIKE 'slave_transaction_retries';
Variable_name Value
slave_transaction_retries 2
include/stop_slave.inc
connection master;
BEGIN;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2), (2), (2), (2), (2), (2), (2), (2), (2), (2);
INSERT INTO t3 VALUES (3);
COMMIT;
*** Test deadlock ***
connection slave;
BEGIN;
SELECT * FROM t1 FOR UPDATE;
a
connection slave1;
START SLAVE;
connection slave;
SELECT COUNT(*) FROM t2;
COUNT(*)
0
COMMIT;
# Test that the performance schema coulumn shows > 0 values.
include/assert.inc [current number of retries should be more than the value saved before deadlock.]
include/check_slave_is_running.inc
SELECT * FROM t1;
a
1
SELECT * FROM t3;
a
3
include/check_slave_is_running.inc
*** Test lock wait timeout ***
connection slave;
include/stop_slave.inc
DELETE FROM t2;
CHANGE MASTER TO MASTER_LOG_POS=<master_pos_begin>, MASTER_USE_GTID=NO;
BEGIN;
SELECT * FROM t1 FOR UPDATE;
a
1
connection slave1;
START SLAVE;
include/wait_for_slave_sql_error.inc [errno=1205]
connection slave;
SELECT COUNT(*) FROM t2;
COUNT(*)
0
COMMIT;
include/start_slave.inc
SELECT * FROM t1;
a
1
1
SELECT * FROM t3;
a
3
3
include/check_slave_is_running.inc
*** Test lock wait timeout and purged relay logs ***
connection slave;
SET @my_max_relay_log_size= @@global.max_relay_log_size;
SET global max_relay_log_size=0;
Warnings:
Warning 1292 Truncated incorrect max_relay_log_size value: '0'
include/stop_slave.inc
DELETE FROM t2;
CHANGE MASTER TO MASTER_LOG_POS=<master_pos_begin>, MASTER_USE_GTID=NO;
BEGIN;
SELECT * FROM t1 FOR UPDATE;
a
1
1
connection slave1;
START SLAVE;
include/wait_for_slave_sql_error.inc [errno=1205]
connection slave;
SELECT COUNT(*) FROM t2;
COUNT(*)
0
COMMIT;
include/start_slave.inc
SELECT * FROM t1;
a
1
1
1
SELECT * FROM t3;
a
3
3
3
include/check_slave_is_running.inc
*** Clean up ***
connection master;
DROP TABLE t1,t2,t3;
connection slave;
SET global max_relay_log_size= @my_max_relay_log_size;
End of 5.1 tests
include/rpl_end.inc
|
