|
In the test case below, UPDATE t2 SET pk = 0 is not considered unsafe for SBR, because it does not fit any existing "unsafe" conditions, so it is logged in statement format; but expectedly, it breaks after updating one row, and this row ends up to be different on master and slave.
So, the problem here is either that a different row gets updated – but since there is no ORDER BY, it can hardly be considered a bug, – or that the statement is not marked as unsafe, even though it turns out to be one.
|
Test case
|
--source include/master-slave.inc
|
--source include/have_binlog_format_mixed.inc
|
--enable_connect_log
|
|
CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=MyISAM;
|
CREATE TABLE t2 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=MyISAM;
|
|
INSERT INTO t2 VALUES (8),(9),(10);
|
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SELECT 1;
|
CREATE TRIGGER tr2 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t1 SELECT 1 FROM DUAL LIMIT 0;
|
|
--echo #
|
--echo # Safe statement for SBR:
|
DELETE FROM t2 WHERE pk != 9;
|
|
--echo #
|
--echo # Unsafe statement for SBR because of triggers:
|
INSERT INTO t2 VALUES (11),(12);
|
|
--echo #
|
--echo # Appears to be safe statement for SBR:
|
--error ER_DUP_ENTRY
|
UPDATE t2 SET pk = 0;
|
|
--echo #
|
--echo # The discrepancy is already there:
|
SELECT * FROM t2;
|
|
--sync_slave_with_master
|
SELECT * FROM t2;
|
|
--connection master
|
--echo #
|
--echo # ... and the following causes replication failure:
|
DELETE FROM t2 LIMIT 4;
|
|
--sync_slave_with_master
|
|
Partial output
|
#
|
# Safe statement for SBR:
|
DELETE FROM t2 WHERE pk != 9;
|
#
|
# Unsafe statement for SBR because of triggers:
|
INSERT INTO t2 VALUES (11),(12);
|
#
|
# Appears to be safe statement for SBR:
|
UPDATE t2 SET pk = 0;
|
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
|
#
|
# The discrepancy is already there:
|
SELECT * FROM t2;
|
pk
|
0
|
11
|
12
|
connection slave;
|
SELECT * FROM t2;
|
pk
|
0
|
9
|
11
|
connection master;
|
#
|
# ... and the following causes replication failure:
|
DELETE FROM t2 LIMIT 4;
|
See the different contents on master and slave after the UPDATE.
|