Debian Codegraph @

mysql-test/suite/rpl/t/rpl_concurrency_error.test

raw
1 ###############################################################################
2 #BUG#44581 Slave stops when transaction with non-transactional table gets
3 #lock wait timeout
4 #
5 # In STMT and MIXED modes, a statement that changes both non-transactional and
6 # transactional tables must be written to the binary log whenever there are
7 # changes to non-transactional tables. This means that the statement gets into
8 # the # binary log even when the changes to the transactional tables fail. In
9 # particular, in the presence of a failure such statement is annotated with the
10 # error number and wrapped in a begin/rollback. On the slave, while applying
11 # the statement, it is expected the same failure and the rollback prevents the
12 # transactional changes to be persisted.
13
14 # This test aims to verify if a statement that updates both transactional and
15 # non-transacitonal tables and fails due to concurrency problems is correctly
16 # processed by the slave in the sense that the statements get into the binary
17 # log, the error is ignored and only the non-transactional tables are changed.
18 ###############################################################################
19
20 --source include/have_innodb.inc
21 --source include/have_binlog_format_statement.inc
22 --source include/master-slave.inc
23
24 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
25
26 --echo ########################################################################
27 --echo # Environment
28 --echo ########################################################################
29 connection master;
30
31 CREATE TABLE t (i INT, PRIMARY KEY(i), f CHAR(8)) engine = Innodb;
32 CREATE TABLE n (d DATETIME, f CHAR(32)) engine = MyIsam;
33
34 DELIMITER |;
35 CREATE TRIGGER tr AFTER UPDATE ON t FOR EACH ROW
36 BEGIN
37 INSERT INTO n VALUES ( now(), concat( 'updated t: ', old.f, ' -> ', new.f ) );
38 END |
39 DELIMITER ;|
40
41 INSERT INTO t VALUES (4,'black'), (2,'red'), (3,'yelow'), (1,'cyan');
42
43 connect (conn1, 127.0.0.1,root,,);
44 connect (conn2, 127.0.0.1,root,,);
45
46 --echo ########################################################################
47 --echo # Testing ER_LOCK_WAIT_TIMEOUT
48 --echo ########################################################################
49
50 let $type=2;
51
52 while ($type)
53 {
54 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
55 connection conn1;
56 if ($type == 2)
57 {
58 SET AUTOCOMMIT = 1;
59 BEGIN;
60 }
61 if ($type == 1)
62 {
63 SET AUTOCOMMIT = 0;
64 }
65 eval UPDATE t SET f = 'yellow $type' WHERE i = 3;
66
67 connection conn2;
68 if ($type == 2)
69 {
70 SET AUTOCOMMIT = 1;
71 BEGIN;
72 }
73 if ($type == 1)
74 {
75 SET AUTOCOMMIT = 0;
76 }
77 --error ER_LOCK_WAIT_TIMEOUT
78 eval UPDATE IGNORE t SET f = 'magenta $type' WHERE f = 'red';
79 eval INSERT INTO t VALUES (5 + ($type * 10),"brown");
80 INSERT INTO n VALUES (now(),"brown");
81
82 connection conn1;
83 COMMIT;
84
85 connection conn2;
86 ROLLBACK;
87 --source include/show_binlog_events.inc
88
89 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
90 connection conn1;
91 if ($type == 2)
92 {
93 SET AUTOCOMMIT = 1;
94 BEGIN;
95 }
96 if ($type == 1)
97 {
98 SET AUTOCOMMIT = 0;
99 }
100 eval UPDATE t SET f = 'gray $type' WHERE i = 3;
101
102 connection conn2;
103 if ($type == 2)
104 {
105 SET AUTOCOMMIT = 1;
106 BEGIN;
107 }
108 if ($type == 1)
109 {
110 SET AUTOCOMMIT = 0;
111 }
112 --error ER_LOCK_WAIT_TIMEOUT
113 eval UPDATE IGNORE t SET f = 'dark blue $type' WHERE f = 'red';
114 eval INSERT INTO t VALUES (6 + ($type * 10),"brown");
115 INSERT INTO n VALUES (now(),"brown");
116
117 connection conn1;
118 COMMIT;
119
120 connection conn2;
121 COMMIT;
122 --source include/show_binlog_events.inc
123
124 dec $type;
125 }
126
127 connection master;
128 sync_slave_with_master;
129
130 let $rpl_diff_statement= SELECT * FROM t order by i;
131 source include/rpl_diff.inc;
132
133 let $rpl_diff_statement= SELECT * FROM n order by d, f;
134 source include/rpl_diff.inc;
135
136 --echo ########################################################################
137 --echo # Cleanup
138 --echo ########################################################################
139
140 connection master;
141 DROP TRIGGER tr;
142 DROP TABLE t;
143 DROP TABLE n;
144
145 sync_slave_with_master;
146
147 connection master;
148 disconnect conn1;
149 disconnect conn2;
150 --source include/rpl_end.inc