1include/master-slave.inc 2[connection master] 3CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 4connection slave; 5CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 6connection master; 7CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY); 8CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY); 9CREATE PROCEDURE p1() 10BEGIN 11DECLARE ins_count INT DEFAULT 10; 12WHILE ins_count > 0 DO 13INSERT INTO t1 VALUES (NULL); 14SET ins_count = ins_count - 1; 15END WHILE; 16DELETE FROM t1 WHERE id = 1; 17DELETE FROM t1 WHERE id = 2; 18DELETE FROM t2 WHERE id = 1; 19DELETE FROM t2 WHERE id = 2; 20END// 21CREATE PROCEDURE p2() 22BEGIN 23INSERT INTO t1 VALUES (NULL); 24DELETE FROM t1 WHERE id = f1(3); 25DELETE FROM t1 WHERE id = f1(4); 26DELETE FROM t2 WHERE id = 3; 27DELETE FROM t2 WHERE id = 4; 28END// 29CREATE TRIGGER tr1 BEFORE DELETE 30ON t1 FOR EACH ROW 31BEGIN 32INSERT INTO t2 VALUES (NULL); 33END// 34CREATE FUNCTION f1 (i int) RETURNS int 35BEGIN 36INSERT INTO t2 VALUES (NULL); 37RETURN i; 38END// 39CALL p1(); 40Warnings: 41Note 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 42include/show_binlog_events.inc 43Log_name Pos Event_type Server_id End_log_pos Info 44master-bin.000001 # Gtid # # BEGIN GTID #-#-# 45master-bin.000001 # Intvar # # INSERT_ID=1 46master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 47master-bin.000001 # Query # # COMMIT 48master-bin.000001 # Gtid # # BEGIN GTID #-#-# 49master-bin.000001 # Intvar # # INSERT_ID=2 50master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 51master-bin.000001 # Query # # COMMIT 52master-bin.000001 # Gtid # # BEGIN GTID #-#-# 53master-bin.000001 # Intvar # # INSERT_ID=3 54master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 55master-bin.000001 # Query # # COMMIT 56master-bin.000001 # Gtid # # BEGIN GTID #-#-# 57master-bin.000001 # Intvar # # INSERT_ID=4 58master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 59master-bin.000001 # Query # # COMMIT 60master-bin.000001 # Gtid # # BEGIN GTID #-#-# 61master-bin.000001 # Intvar # # INSERT_ID=5 62master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 63master-bin.000001 # Query # # COMMIT 64master-bin.000001 # Gtid # # BEGIN GTID #-#-# 65master-bin.000001 # Intvar # # INSERT_ID=6 66master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 67master-bin.000001 # Query # # COMMIT 68master-bin.000001 # Gtid # # BEGIN GTID #-#-# 69master-bin.000001 # Intvar # # INSERT_ID=7 70master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 71master-bin.000001 # Query # # COMMIT 72master-bin.000001 # Gtid # # BEGIN GTID #-#-# 73master-bin.000001 # Intvar # # INSERT_ID=8 74master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 75master-bin.000001 # Query # # COMMIT 76master-bin.000001 # Gtid # # BEGIN GTID #-#-# 77master-bin.000001 # Intvar # # INSERT_ID=9 78master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 79master-bin.000001 # Query # # COMMIT 80master-bin.000001 # Gtid # # BEGIN GTID #-#-# 81master-bin.000001 # Intvar # # INSERT_ID=10 82master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 83master-bin.000001 # Query # # COMMIT 84master-bin.000001 # Gtid # # BEGIN GTID #-#-# 85master-bin.000001 # Intvar # # INSERT_ID=1 86master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = 1 87master-bin.000001 # Query # # COMMIT 88master-bin.000001 # Gtid # # BEGIN GTID #-#-# 89master-bin.000001 # Intvar # # INSERT_ID=2 90master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = 2 91master-bin.000001 # Query # # COMMIT 92master-bin.000001 # Gtid # # BEGIN GTID #-#-# 93master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 1 94master-bin.000001 # Query # # COMMIT 95master-bin.000001 # Gtid # # BEGIN GTID #-#-# 96master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 2 97master-bin.000001 # Query # # COMMIT 98# Result on master 99SELECT * FROM t1; 100id 1013 1024 1035 1046 1057 1068 1079 10810 109SELECT * FROM t2; 110id 111connection slave; 112SELECT * FROM t1; 113id 1143 1154 1165 1176 1187 1198 1209 12110 122SELECT * FROM t2; 123id 124connection master; 125DROP TRIGGER tr1; 126CALL p2(); 127Warnings: 128Note 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 129include/show_binlog_events.inc 130Log_name Pos Event_type Server_id End_log_pos Info 131master-bin.000001 # Gtid # # BEGIN GTID #-#-# 132master-bin.000001 # Intvar # # INSERT_ID=11 133master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL) 134master-bin.000001 # Query # # COMMIT 135master-bin.000001 # Gtid # # BEGIN GTID #-#-# 136master-bin.000001 # Intvar # # INSERT_ID=3 137master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = f1(3) 138master-bin.000001 # Query # # COMMIT 139master-bin.000001 # Gtid # # BEGIN GTID #-#-# 140master-bin.000001 # Intvar # # INSERT_ID=12 141master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = f1(4) 142master-bin.000001 # Query # # COMMIT 143master-bin.000001 # Gtid # # BEGIN GTID #-#-# 144master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 3 145master-bin.000001 # Query # # COMMIT 146master-bin.000001 # Gtid # # BEGIN GTID #-#-# 147master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 4 148master-bin.000001 # Query # # COMMIT 149# Result on master 150SELECT * FROM t1; 151id 1525 1536 1547 1558 1569 15710 15811 159SELECT * FROM t2; 160id 1615 1626 1637 1648 1659 16610 16711 16812 16913 17014 17115 17216 17317 17418 17519 176connection slave; 177SELECT * FROM t1; 178id 1795 1806 1817 1828 1839 18410 18511 186SELECT * FROM t2; 187id 1885 1896 1907 1918 1929 19310 19411 19512 19613 19714 19815 19916 20017 20118 20219 203connection master; 204DROP TABLE IF EXISTS t1, t2; 205DROP PROCEDURE IF EXISTS p1; 206DROP PROCEDURE IF EXISTS p2; 207DROP FUNCTION IF EXISTS f1; 208DROP TRIGGER IF EXISTS tr1; 209connection slave; 210include/rpl_end.inc 211