| 1 |
--source include/have_innodb.inc |
| 2 |
--source include/master-slave.inc |
| 3 |
|
| 4 |
if (`select @@binlog_format = "statement"`) |
| 5 |
{ |
| 6 |
call mtr.add_suppression("Unsafe statement written to the binary log using statement"); |
| 7 |
} |
| 8 |
|
| 9 |
## MDEV-28310 loss of binlog event for multi-record IODKU |
| 10 |
# Check that the duplicate key error does not cause |
| 11 |
# loss of replication event for IODKU that specifies values |
| 12 |
# for at least two unique columns per record. |
| 13 |
# "Implicit" NULL value of the auto-increment column also counts. |
| 14 |
|
| 15 |
CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, a INT, b INT, c INT, |
| 16 |
UNIQUE (a), UNIQUE (b)) ENGINE=innodb; |
| 17 |
INSERT INTO t1 (`a`,`c`) VALUES (1,1), (2,1) ON DUPLICATE KEY UPDATE c = 1; |
| 18 |
--echo # UNSAFE |
| 19 |
# because of two keys involved: a UK and PK even though implicitly via auto-inc |
| 20 |
INSERT INTO t1 (`a`,`c`) VALUES (3, 1),(2,1), (1,1) ON DUPLICATE KEY UPDATE c = a * 10 + VALUES(c); |
| 21 |
SELECT * from t1; |
| 22 |
|
| 23 |
--sync_slave_with_master |
| 24 |
--let $diff_tables = master:t1,slave:t1 |
| 25 |
--source include/diff_tables.inc |
| 26 |
|
| 27 |
## MDEV-21810 MBR: Unexpected "Unsafe statement" warning for unsafe IODKU |
| 28 |
# Unnecessary unsafe statement warning is not error-logged anymore. |
| 29 |
|
| 30 |
|
| 31 |
--connection master |
| 32 |
CREATE OR REPLACE TABLE t1 (a INT, b INT, c INT, UNIQUE (a), UNIQUE (b)) ENGINE=innodb; |
| 33 |
INSERT INTO t1 VALUES (1,10,1); |
| 34 |
--echo # eligable for the statement format run unsafe warning |
| 35 |
INSERT INTO t1 VALUES (2,20,2) ON DUPLICATE KEY UPDATE c = 100; |
| 36 |
--echo # not eligable: no warning in the statement format run |
| 37 |
INSERT INTO t1 (`a`,`c`) VALUES (3, 1) ON DUPLICATE KEY UPDATE c = 99; |
| 38 |
SELECT * from t1; |
| 39 |
|
| 40 |
--sync_slave_with_master |
| 41 |
--let $diff_tables = master:t1,slave:t1 |
| 42 |
--source include/diff_tables.inc |
| 43 |
|
| 44 |
# Cleanup |
| 45 |
--connection master |
| 46 |
DROP TABLE t1; |
| 47 |
--sync_slave_with_master |
| 48 |
|
| 49 |
|
| 50 |
--source include/rpl_end.inc |