include/master-slave.inc [connection master] CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); connection slave; CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); connection master; CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY); CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY); CREATE PROCEDURE p1() BEGIN DECLARE ins_count INT DEFAULT 10; WHILE ins_count > 0 DO INSERT INTO t1 VALUES (NULL); SET ins_count = ins_count - 1; END WHILE; DELETE FROM t1 WHERE id = 1; DELETE FROM t1 WHERE id = 2; DELETE FROM t2 WHERE id = 1; DELETE FROM t2 WHERE id = 2; END// CREATE PROCEDURE p2() BEGIN INSERT INTO t1 VALUES (NULL); DELETE FROM t1 WHERE id = f1(3); DELETE FROM t1 WHERE id = f1(4); DELETE FROM t2 WHERE id = 3; DELETE FROM t2 WHERE id = 4; END// CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES (NULL); END// CREATE FUNCTION f1 (i int) RETURNS int BEGIN INSERT INTO t2 VALUES (NULL); RETURN i; END// CALL p1(); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=1 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=2 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=4 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=5 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=6 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=7 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=8 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=9 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=1 master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = 1 master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=2 master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = 2 master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 1 master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 2 master-bin.000001 # Query # # COMMIT # Result on master SELECT * FROM t1; id 3 4 5 6 7 8 9 10 SELECT * FROM t2; id connection slave; SELECT * FROM t1; id 3 4 5 6 7 8 9 10 SELECT * FROM t2; id connection master; DROP TRIGGER tr1; CALL p2(); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=11 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = f1(3) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=12 master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = f1(4) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 3 master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 4 master-bin.000001 # Query # # COMMIT # Result on master SELECT * FROM t1; id 5 6 7 8 9 10 11 SELECT * FROM t2; id 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 connection slave; SELECT * FROM t1; id 5 6 7 8 9 10 11 SELECT * FROM t2; id 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 connection master; DROP TABLE IF EXISTS t1, t2; DROP PROCEDURE IF EXISTS p1; DROP PROCEDURE IF EXISTS p2; DROP FUNCTION IF EXISTS f1; DROP TRIGGER IF EXISTS tr1; connection slave; include/rpl_end.inc