1call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); 2include/master-slave.inc 3connection master; 4CREATE TABLE t1 ( 5id bigint(20) unsigned NOT NULL auto_increment, 6field_1 int(10) unsigned NOT NULL, 7field_2 varchar(255) NOT NULL, 8field_3 varchar(255) NOT NULL, 9PRIMARY KEY (id), 10UNIQUE KEY field_1 (field_1, field_2) 11); 12CREATE TABLE t2 ( 13field_a int(10) unsigned NOT NULL, 14field_b varchar(255) NOT NULL, 15field_c varchar(255) NOT NULL 16); 17INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a'); 18INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b'); 19INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c'); 20INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d'); 21INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e'); 22connection slave; 23connection master; 24INSERT INTO t1 (field_1, field_2, field_3) 25SELECT t2.field_a, t2.field_b, t2.field_c 26FROM t2 27ON DUPLICATE KEY UPDATE 28t1.field_3 = t2.field_c; 29Warnings: 30Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... SELECT... ON DUPLICATE KEY UPDATE is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are updated. This order cannot be predicted and may differ on master and the slave. 31Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. 32INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f'); 33INSERT INTO t1 (field_1, field_2, field_3) 34SELECT t2.field_a, t2.field_b, t2.field_c 35FROM t2 36ON DUPLICATE KEY UPDATE 37t1.field_3 = t2.field_c; 38Warnings: 39Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... SELECT... ON DUPLICATE KEY UPDATE is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are updated. This order cannot be predicted and may differ on master and the slave. 40Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. 41SELECT * FROM t1; 42id field_1 field_2 field_3 431 1 a 1a 442 2 b 2b 453 3 c 3c 464 4 d 4d 475 5 e 5e 486 6 f 6f 49connection slave; 50include/wait_for_slave_sql_error.inc [errno=1105] 51Last_SQL_Error = 'Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'test'. Query: 'INSERT INTO t1 (field_1, field_2, field_3) 52SELECT t2.field_a, t2.field_b, t2.field_c 53FROM t2 54ON DUPLICATE KEY UPDATE 55t1.field_3 = t2.field_c'' 56SELECT * FROM t1; 57id field_1 field_2 field_3 58connection master; 59drop table t1, t2; 60connection slave; 61drop table t1, t2; 62include/stop_slave_io.inc 63RESET SLAVE; 64include/rpl_end.inc 65