Debian Codegraph @

mysql-test/suite/rpl_nogtid/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/not_group_replication_plugin.inc
21 --source include/force_myisam_default.inc
22 --source include/have_myisam.inc
23 --source include/have_binlog_format_statement.inc
24 --source include/set_privilege_checks_user_as_system_user.inc
25 --source include/master-slave.inc
26
27 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
28
29 --echo ########################################################################
30 --echo # Environment
31 --echo ########################################################################
32 connection master;
33
34 CREATE TABLE t (i INT, PRIMARY KEY(i), f CHAR(8)) engine = Innodb;
35 CREATE TABLE n (d DATETIME, f CHAR(32)) engine = MyIsam;
36
37 DELIMITER |;
38 CREATE TRIGGER tr AFTER UPDATE ON t FOR EACH ROW
39 BEGIN
40 INSERT INTO n VALUES ( now(), concat( 'updated t: ', old.f, ' -> ', new.f ) );
41 END |
42 DELIMITER ;|
43
44 INSERT INTO t VALUES (4,'black'), (2,'red'), (3,'yelow'), (1,'cyan');
45
46 connect (conn1, 127.0.0.1,root,,);
47 connect (conn2, 127.0.0.1,root,,);
48
49 --echo ########################################################################
50 --echo # Testing ER_LOCK_WAIT_TIMEOUT
51 --echo ########################################################################
52
53 let $type=2;
54
55 while ($type)
56 {
57 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
58 connection conn1;
59 if ($type == 2)
60 {
61 SET AUTOCOMMIT = 1;
62 BEGIN;
63 }
64 if ($type == 1)
65 {
66 SET AUTOCOMMIT = 0;
67 }
68 eval UPDATE t SET f = 'yellow $type' WHERE i = 3;
69
70 connection conn2;
71 if ($type == 2)
72 {
73 SET AUTOCOMMIT = 1;
74 BEGIN;
75 }
76 if ($type == 1)
77 {
78 SET AUTOCOMMIT = 0;
79 }
80 --error ER_LOCK_WAIT_TIMEOUT
81 eval UPDATE IGNORE t SET f = 'magenta $type' WHERE f = 'red';
82 eval INSERT INTO t VALUES (5 + ($type * 10),"brown");
83 INSERT INTO n VALUES (now(),"brown");
84
85 connection conn1;
86 COMMIT;
87
88 connection conn2;
89 ROLLBACK;
90 --source include/show_binlog_events.inc
91
92 let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
93 connection conn1;
94 if ($type == 2)
95 {
96 SET AUTOCOMMIT = 1;
97 BEGIN;
98 }
99 if ($type == 1)
100 {
101 SET AUTOCOMMIT = 0;
102 }
103 eval UPDATE t SET f = 'gray $type' WHERE i = 3;
104
105 connection conn2;
106 if ($type == 2)
107 {
108 SET AUTOCOMMIT = 1;
109 BEGIN;
110 }
111 if ($type == 1)
112 {
113 SET AUTOCOMMIT = 0;
114 }
115 --error ER_LOCK_WAIT_TIMEOUT
116 eval UPDATE IGNORE t SET f = 'dark blue $type' WHERE f = 'red';
117 eval INSERT INTO t VALUES (6 + ($type * 10),"brown");
118 INSERT INTO n VALUES (now(),"brown");
119
120 connection conn1;
121 COMMIT;
122
123 connection conn2;
124 COMMIT;
125 --source include/show_binlog_events.inc
126
127 dec $type;
128 }
129
130 connection master;
131 --source include/sync_slave_sql_with_master.inc
132
133 let $rpl_diff_statement= SELECT * FROM t order by i;
134 source include/rpl_diff.inc;
135
136 let $rpl_diff_statement= SELECT * FROM n order by d, f;
137 source include/rpl_diff.inc;
138
139 --echo ########################################################################
140 --echo # Cleanup
141 --echo ########################################################################
142
143 connection master;
144 DROP TRIGGER tr;
145 DROP TABLE t;
146 DROP TABLE n;
147
148 --source include/sync_slave_sql_with_master.inc
149
150 connection master;
151 disconnect conn1;
152 disconnect conn2;
153 --source include/rpl_end.inc