| 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 |