connection master;

CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM;

INSERT INTO t1 VALUES (3),(2),(1);

#-------------------------------------------------

# Prepare a dump for initializing a slave

# (run mysqldump --order-by-primary --master-data test)

#-------------------------------------------------

connection slave;

#-------------------------------------------------

# Restore the dump on the slave

#-------------------------------------------------

START SLAVE;

include/wait_for_slave_to_start.inc

connection master;

#-------------------------------------------------

# Since the rows can get updated in an arbitrary order

# and stop in the middle due to the duplicate key error, 

# the statement is actually unsafe, but is not recognized

# as such and is logged in statement format

#-------------------------------------------------

UPDATE t1 SET pk = 5;

ERROR 23000: Duplicate entry '5' for key 'PRIMARY'

SHOW BINLOG EVENTS;

Log_name	Pos	Event_type	Server_id	End_log_pos	Info

...

master-bin.000001	510	Query	1	589	BEGIN

master-bin.000001	589	Query	1	683	use `test`; UPDATE t1 SET pk = 5

master-bin.000001	683	Query	1	763	COMMIT

#-------------------------------------------------

# We have different data on master and slave

#-------------------------------------------------

SELECT * FROM t1;

pk

1

2

5

connection slave;

SELECT * FROM t1;

pk

2

3

5

#----------------------------------

# Now it's easy to break replication completely

#----------------------------------

connection master;

DELETE FROM t1 LIMIT 3;

...

=== SHOW SLAVE STATUS ===

---- 1. ----

...

Last_Errno	1032

Last_Error	Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; 

...

--source include/master-slave.inc

--source include/have_binlog_format_mixed.inc

--connection slave

STOP SLAVE;

--source include/wait_for_slave_to_stop.inc

--enable_connect_log

--connection master

CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM;

INSERT INTO t1 VALUES (3),(2),(1);

--echo #-------------------------------------------------

--echo # Prepare a dump for initializing a slave

--echo # (run mysqldump --order-by-primary --master-data test)

--echo #-------------------------------------------------

--exec $MYSQL_DUMP --order-by-primary --master-data test > $MYSQLTEST_VARDIR/tmp/d.dump

--connection slave

--echo #-------------------------------------------------

--echo # Restore the dump on the slave

--echo #-------------------------------------------------

--exec $MYSQL_SLAVE test < $MYSQLTEST_VARDIR/tmp/d.dump

START SLAVE;

--disable_connect_log

--source include/wait_for_slave_to_start.inc

--enable_connect_log

--connection master

--echo #-------------------------------------------------

--echo # Since the rows can get updated in an arbitrary order

--echo # and stop in the middle due to the duplicate key error, 

--echo # the statement is actually unsafe, but is not recognized

--echo # as such and is logged in statement format

--echo #-------------------------------------------------

--error ER_DUP_ENTRY

UPDATE t1 SET pk = 5;

SHOW BINLOG EVENTS;

--echo #-------------------------------------------------

--echo # We have different data on master and slave

--echo #-------------------------------------------------

SELECT * FROM t1;

--sync_slave_with_master

SELECT * FROM t1;

--echo #----------------------------------

--echo # Now it's easy to break replication completely

--echo #----------------------------------

--connection master

DELETE FROM t1 LIMIT 3;

SHOW BINLOG EVENTS;

--sync_slave_with_master