1
###############################################################################
2
#BUG#44581 Slave stops when transaction with non-transactional table gets
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.
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
###############################################################################
20
--source include/master-slave.inc
21
--source include/have_innodb.inc
22
--source include/have_binlog_format_statement.inc
24
--echo ########################################################################
26
--echo ########################################################################
29
CREATE TABLE t (i INT, PRIMARY KEY(i), f CHAR(8)) engine = Innodb;
30
CREATE TABLE n (d DATETIME, f CHAR(32)) engine = MyIsam;
33
CREATE TRIGGER tr AFTER UPDATE ON t FOR EACH ROW
35
INSERT INTO n VALUES ( now(), concat( 'updated t: ', old.f, ' -> ', new.f ) );
39
INSERT INTO t VALUES (4,'black'), (2,'red'), (3,'yelow'), (1,'cyan');
41
connect (conn1, 127.0.0.1,root,,);
42
connect (conn2, 127.0.0.1,root,,);
44
--echo ########################################################################
45
--echo # Testing ER_LOCK_WAIT_TIMEOUT
46
--echo ########################################################################
52
let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
54
if (`select $type = 2`)
59
if (`select $type = 1`)
63
eval UPDATE t SET f = 'yellow $type' WHERE i = 3;
66
if (`select $type = 2`)
71
if (`select $type = 1`)
75
--error ER_LOCK_WAIT_TIMEOUT
76
eval UPDATE t SET f = 'magenta $type' WHERE f = 'red';
77
eval INSERT INTO t VALUES (5 + ($type * 10),"brown");
78
INSERT INTO n VALUES (now(),"brown");
85
--source include/show_binlog_events.inc
87
let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);
89
if (`select $type = 2`)
94
if (`select $type = 1`)
98
eval UPDATE t SET f = 'gray $type' WHERE i = 3;
101
if (`select $type = 2`)
106
if (`select $type = 1`)
110
--error ER_LOCK_WAIT_TIMEOUT
111
eval UPDATE t SET f = 'dark blue $type' WHERE f = 'red';
112
eval INSERT INTO t VALUES (6 + ($type * 10),"brown");
113
INSERT INTO n VALUES (now(),"brown");
120
--source include/show_binlog_events.inc
126
sync_slave_with_master;
129
let $diff_statement= SELECT * FROM t order by i;
130
source include/diff_master_slave.inc;
133
let $diff_statement= SELECT * FROM n order by d, f;
134
source include/diff_master_slave.inc;
136
--echo ########################################################################
138
--echo ########################################################################
145
sync_slave_with_master;