diff options
28 files changed, 186 insertions, 23 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_insert_id_pk.test b/mysql-test/extra/rpl_tests/rpl_insert_id_pk.test index c3ecd9381c9..f6152180e7a 100644 --- a/mysql-test/extra/rpl_tests/rpl_insert_id_pk.test +++ b/mysql-test/extra/rpl_tests/rpl_insert_id_pk.test @@ -10,6 +10,8 @@ # We also check how the foreign_key_check variable is replicated -- source include/master-slave.inc +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); + #should work for both SBR and RBR create table t1(a int auto_increment, primary key(a)); @@ -50,6 +52,7 @@ create table t2(b int auto_increment, c int, primary key(b)); insert into t1 values (10); insert into t1 values (null),(null),(null); insert into t2 values (5,0); +--disable_warnings ONCE insert into t2 (c) select * from t1 ORDER BY a; select * from t2 ORDER BY b; sync_slave_with_master; diff --git a/mysql-test/extra/rpl_tests/rpl_multi_update.test b/mysql-test/extra/rpl_tests/rpl_multi_update.test index bf7707f9d6d..cdbdbc191d7 100644 --- a/mysql-test/extra/rpl_tests/rpl_multi_update.test +++ b/mysql-test/extra/rpl_tests/rpl_multi_update.test @@ -1,5 +1,7 @@ source include/master-slave.inc; +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); + eval CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, b int unsigned @@ -11,6 +13,7 @@ eval CREATE TABLE t2 ( ) ENGINE=$engine_type; INSERT INTO t1 VALUES (NULL, 0); +--disable_warnings ONCE INSERT INTO t1 SELECT NULL, 0 FROM t1; INSERT INTO t2 VALUES (NULL, 0), (NULL,1); @@ -18,6 +21,7 @@ INSERT INTO t2 VALUES (NULL, 0), (NULL,1); SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; +--disable_warnings ONCE UPDATE t1, t2 SET t1.b = t2.b WHERE t1.a = t2.a; sync_slave_with_master; diff --git a/mysql-test/extra/rpl_tests/rpl_multi_update2.test b/mysql-test/extra/rpl_tests/rpl_multi_update2.test index ae4261c0516..b85927a2aed 100644 --- a/mysql-test/extra/rpl_tests/rpl_multi_update2.test +++ b/mysql-test/extra/rpl_tests/rpl_multi_update2.test @@ -17,6 +17,8 @@ eval CREATE TABLE t2 ( ) ENGINE=$engine_type; INSERT INTO t1 VALUES (NULL, 0); + +--disable_warnings ONCE INSERT INTO t1 SELECT NULL, 0 FROM t1; INSERT INTO t2 VALUES (NULL, 0), (NULL,1); @@ -24,6 +26,7 @@ INSERT INTO t2 VALUES (NULL, 0), (NULL,1); SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; +--disable_warnings ONCE UPDATE t1, t2 SET t1.b = (t2.b+4) WHERE t1.a = t2.a; SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; diff --git a/mysql-test/extra/rpl_tests/rpl_multi_update3.test b/mysql-test/extra/rpl_tests/rpl_multi_update3.test index 6c7a980aecb..f7d4815e9e5 100644 --- a/mysql-test/extra/rpl_tests/rpl_multi_update3.test +++ b/mysql-test/extra/rpl_tests/rpl_multi_update3.test @@ -18,6 +18,8 @@ eval CREATE TABLE t2 ( ) ENGINE=$engine_type; INSERT INTO t1 VALUES (NULL, 0); + +--disable_warnings ONCE INSERT INTO t1 SELECT NULL, 0 FROM t1; INSERT INTO t2 VALUES (NULL, 0), (NULL,1); @@ -25,6 +27,7 @@ INSERT INTO t2 VALUES (NULL, 0), (NULL,1); SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; +--disable_warnings ONCE UPDATE t2, (SELECT a FROM t1 ORDER BY a) AS t SET t2.b = t.a+5 ; SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index afa105a39b8..310004de983 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1,3 +1,4 @@ +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); drop table if exists t1,t2,t3,t4; drop database if exists client_test_db; create table t1 diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result index a307b765097..9c4330a96c0 100644 --- a/mysql-test/suite/binlog/r/binlog_unsafe.result +++ b/mysql-test/suite/binlog/r/binlog_unsafe.result @@ -1760,7 +1760,7 @@ SELECT COUNT(*) FROM mysql.general_log; Invoking function func_sidef_1 invoking statement that is unsafe in many ways. CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1; RETURN 0; END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. INSERT INTO t1 SELECT func_sidef_1(); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1769,12 +1769,13 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. Invoking function func_sidef_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways. CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. INSERT INTO t2 SELECT func_sidef_2(); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1783,13 +1784,14 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP FUNCTION func_sidef_2; Invoking procedure proc_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways. CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. CALL proc_2(); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1798,13 +1800,14 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP PROCEDURE proc_2; Invoking trigger trig_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways. CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. INSERT INTO trigger_table_2 VALUES (1); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1813,6 +1816,7 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP TRIGGER trig_2; @@ -1826,7 +1830,8 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. -* binlog_format = STATEMENT: expect 6 warnings. +Note 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. +* binlog_format = STATEMENT: expect 7 warnings. INSERT INTO t2 SELECT * FROM view_sidef_2; Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1835,13 +1840,14 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP VIEW view_sidef_2; Invoking prepared statement prep_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways. PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()"; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. EXECUTE prep_2; Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1850,6 +1856,7 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP PREPARE prep_2; @@ -1857,7 +1864,7 @@ DROP FUNCTION func_sidef_1; Invoking procedure proc_1 invoking statement that is unsafe in many ways. CREATE PROCEDURE proc_1() BEGIN INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1; INSERT INTO ta1 VALUES (47); END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. CALL proc_1(); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1866,12 +1873,13 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. Invoking function func_sidef_2 invoking procedure proc_1 invoking statement that is unsafe in many ways. CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. INSERT INTO t2 SELECT func_sidef_2(); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1880,13 +1888,14 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP FUNCTION func_sidef_2; Invoking procedure proc_2 invoking procedure proc_1 invoking statement that is unsafe in many ways. CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. CALL proc_2(); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1895,13 +1904,14 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP PROCEDURE proc_2; Invoking trigger trig_2 invoking procedure proc_1 invoking statement that is unsafe in many ways. CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. INSERT INTO trigger_table_2 VALUES (1); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1910,13 +1920,14 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP TRIGGER trig_2; Invoking prepared statement prep_2 invoking procedure proc_1 invoking statement that is unsafe in many ways. PREPARE prep_2 FROM "CALL proc_1()"; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. EXECUTE prep_2; Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1925,6 +1936,7 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP PREPARE prep_2; @@ -1932,7 +1944,7 @@ DROP PROCEDURE proc_1; Invoking trigger trig_1 invoking statement that is unsafe in many ways. CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1; END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. INSERT INTO trigger_table_1 VALUES (1); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1941,12 +1953,13 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. Invoking function func_sidef_2 invoking trigger trig_1 invoking statement that is unsafe in many ways. CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. INSERT INTO t2 SELECT func_sidef_2(); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1955,13 +1968,14 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP FUNCTION func_sidef_2; Invoking procedure proc_2 invoking trigger trig_1 invoking statement that is unsafe in many ways. CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. CALL proc_2(); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1970,13 +1984,14 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP PROCEDURE proc_2; Invoking trigger trig_2 invoking trigger trig_1 invoking statement that is unsafe in many ways. CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. INSERT INTO trigger_table_2 VALUES (1); Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -1985,13 +2000,14 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP TRIGGER trig_2; Invoking prepared statement prep_2 invoking trigger trig_1 invoking statement that is unsafe in many ways. PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)"; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. EXECUTE prep_2; Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -2000,6 +2016,7 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP PREPARE prep_2; @@ -2007,7 +2024,7 @@ DROP TRIGGER trig_1; Invoking prepared statement prep_1 invoking statement that is unsafe in many ways. PREPARE prep_1 FROM "INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1"; -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. EXECUTE prep_1; Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -2016,12 +2033,13 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. DROP PREPARE prep_1; Invoking statement that is unsafe in many ways. -* binlog_format = STATEMENT: expect 6 warnings. +* binlog_format = STATEMENT: expect 7 warnings. INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1; Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. @@ -2030,6 +2048,7 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. +Note 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. * SQL_LOG_BIN = 0: expect nothing logged and no warning. * binlog_format = MIXED: expect row events in binlog and no warning. diff --git a/mysql-test/suite/binlog/t/binlog_unsafe.test b/mysql-test/suite/binlog/t/binlog_unsafe.test index 040f57597c2..6b342f24ab5 100644 --- a/mysql-test/suite/binlog/t/binlog_unsafe.test +++ b/mysql-test/suite/binlog/t/binlog_unsafe.test @@ -233,7 +233,7 @@ while ($unsafe_type < 9) { --let $value_0= --let $sel_sidef_0= --let $sel_retval_0= - --let $CRC_ARG_expected_number_of_warnings= 6 + --let $CRC_ARG_expected_number_of_warnings= 7 } if ($unsafe_type == 8) { diff --git a/mysql-test/suite/rpl/r/rpl_auto_increment_11932.result b/mysql-test/suite/rpl/r/rpl_auto_increment_11932.result index 62ff28e7159..1d378e2d864 100644 --- a/mysql-test/suite/rpl/r/rpl_auto_increment_11932.result +++ b/mysql-test/suite/rpl/r/rpl_auto_increment_11932.result @@ -1,5 +1,6 @@ include/master-slave.inc [connection master] +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); drop database if exists test1; create database test1; use test1; diff --git a/mysql-test/suite/rpl/r/rpl_insert_id_pk.result b/mysql-test/suite/rpl/r/rpl_insert_id_pk.result index c2d6d2c9b0c..467b8c1e5cc 100644 --- a/mysql-test/suite/rpl/r/rpl_insert_id_pk.result +++ b/mysql-test/suite/rpl/r/rpl_insert_id_pk.result @@ -1,5 +1,6 @@ include/master-slave.inc [connection master] +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); create table t1(a int auto_increment, primary key(a)); create table t2(b int auto_increment, c int, primary key(b)); insert into t1 values (1),(2),(3); diff --git a/mysql-test/suite/rpl/r/rpl_multi_update.result b/mysql-test/suite/rpl/r/rpl_multi_update.result index 8634e86afed..74fb7952a2a 100644 --- a/mysql-test/suite/rpl/r/rpl_multi_update.result +++ b/mysql-test/suite/rpl/r/rpl_multi_update.result @@ -1,5 +1,6 @@ include/master-slave.inc [connection master] +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, b int unsigned diff --git a/mysql-test/suite/rpl/r/rpl_multi_update2.result b/mysql-test/suite/rpl/r/rpl_multi_update2.result index a3cab693322..03ed5de473d 100644 --- a/mysql-test/suite/rpl/r/rpl_multi_update2.result +++ b/mysql-test/suite/rpl/r/rpl_multi_update2.result @@ -1,5 +1,6 @@ include/master-slave.inc [connection master] +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT'); drop table if exists t1,t2; CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, diff --git a/mysql-test/suite/rpl/r/rpl_multi_update3.result b/mysql-test/suite/rpl/r/rpl_multi_update3.result index 6b9ec5c3947..bf9946f219f 100644 --- a/mysql-test/suite/rpl/r/rpl_multi_update3.result +++ b/mysql-test/suite/rpl/r/rpl_multi_update3.result @@ -1,5 +1,6 @@ include/master-slave.inc [connection master] +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); -------- Test for BUG#9361 -------- CREATE TABLE t1 ( diff --git a/mysql-test/suite/rpl/r/rpl_rotate_logs.result b/mysql-test/suite/rpl/r/rpl_rotate_logs.result index ebc75ce9e6e..ef95103c7bc 100644 --- a/mysql-test/suite/rpl/r/rpl_rotate_logs.result +++ b/mysql-test/suite/rpl/r/rpl_rotate_logs.result @@ -1,3 +1,4 @@ +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); start slave; Got one of the listed errors start slave; diff --git a/mysql-test/suite/rpl/t/rpl_auto_increment_11932.test b/mysql-test/suite/rpl/t/rpl_auto_increment_11932.test index d1da69533ec..1e5f9e53478 100644 --- a/mysql-test/suite/rpl/t/rpl_auto_increment_11932.test +++ b/mysql-test/suite/rpl/t/rpl_auto_increment_11932.test @@ -8,6 +8,7 @@ # Test supplied by Are Casilla source include/master-slave.inc; +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); --disable_warnings connection master; drop database if exists test1; @@ -42,12 +43,16 @@ CREATE PROCEDURE simpleproc3 () $ DELIMITER ;$ +--disable_warnings CALL simpleproc3(); +--enable_warnings select * from t2; TRUNCATE TABLE `t1`; +--disable_warnings CALL simpleproc3(); +--enable_warnings select * from t1; diff --git a/mysql-test/suite/rpl/t/rpl_multi_update2.test b/mysql-test/suite/rpl/t/rpl_multi_update2.test index 497568f2738..138c1455952 100644 --- a/mysql-test/suite/rpl/t/rpl_multi_update2.test +++ b/mysql-test/suite/rpl/t/rpl_multi_update2.test @@ -6,6 +6,7 @@ ####################################################### --source include/not_ndb_default.inc --source include/master-slave.inc +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT'); let $engine_type=MyISAM; --source extra/rpl_tests/rpl_multi_update2.test --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_multi_update3.test b/mysql-test/suite/rpl/t/rpl_multi_update3.test index f6e70f14b30..dc12d528c24 100644 --- a/mysql-test/suite/rpl/t/rpl_multi_update3.test +++ b/mysql-test/suite/rpl/t/rpl_multi_update3.test @@ -6,6 +6,7 @@ ####################################################### --source include/not_ndb_default.inc --source include/master-slave.inc +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); let $engine_type=MyISAM; -- source extra/rpl_tests/rpl_multi_update3.test --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_optimize.test b/mysql-test/suite/rpl/t/rpl_optimize.test index ad49df55db7..df863e614a2 100644 --- a/mysql-test/suite/rpl/t/rpl_optimize.test +++ b/mysql-test/suite/rpl/t/rpl_optimize.test @@ -19,6 +19,7 @@ enable_query_log; create table t1 (a int not null auto_increment primary key, b int, key(b)); INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +--disable_warnings INSERT INTO t1 (a) SELECT null FROM t1; INSERT INTO t1 (a) SELECT null FROM t1; INSERT INTO t1 (a) SELECT null FROM t1; @@ -32,6 +33,7 @@ INSERT INTO t1 (a) SELECT null FROM t1; INSERT INTO t1 (a) SELECT null FROM t1; INSERT INTO t1 (a) SELECT null FROM t1; INSERT INTO t1 (a) SELECT null FROM t1; +--enable_warnings save_master_pos; # a few updates to force OPTIMIZE to do something --disable_warnings diff --git a/mysql-test/suite/rpl/t/rpl_rotate_logs.test b/mysql-test/suite/rpl/t/rpl_rotate_logs.test index a1b4f367812..67079b9bea1 100644 --- a/mysql-test/suite/rpl/t/rpl_rotate_logs.test +++ b/mysql-test/suite/rpl/t/rpl_rotate_logs.test @@ -27,6 +27,7 @@ EOF chmod 0000 $MYSQLD_SLAVE_DATADIR/master.info; connection slave; +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); # START SLAVE will fail because it can't read the file (mode 000) # (system error 13) --replace_result $MYSQL_TEST_DIR TESTDIR diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_event.test b/mysql-test/suite/rpl/t/rpl_semi_sync_event.test index cd73a84d569..bf622790fe7 100644 --- a/mysql-test/suite/rpl/t/rpl_semi_sync_event.test +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_event.test @@ -52,9 +52,10 @@ SET GLOBAL event_scheduler = ON; replace_result $engine_type ENGINE_TYPE; eval CREATE TABLE t1 (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f varchar(8)) ENGINE=$engine_type; INSERT INTO t1 (f) VALUES ('a'),('a'),('a'),('a'),('a'); +--disable_warnings INSERT INTO t1 SELECT i+5, f FROM t1; INSERT INTO t1 SELECT i+10, f FROM t1; - +--enable_warnings CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (SLEEP(5),CONCAT('ev1_',CONNECTION_ID())); CREATE EVENT ev2 ON SCHEDULE EVERY 1 SECOND diff --git a/mysql-test/suite/rpl/t/rpl_timezone.test b/mysql-test/suite/rpl/t/rpl_timezone.test index 7355106b6b4..1f0220421ab 100644 --- a/mysql-test/suite/rpl/t/rpl_timezone.test +++ b/mysql-test/suite/rpl/t/rpl_timezone.test @@ -13,14 +13,14 @@ # timezone used in CONVERT_TZ is not binlogged. To debug (by Guilhem # and possibly Konstantin). +source include/master-slave.inc; + --disable_query_log CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); --enable_query_log --disable_ps_protocol -source include/master-slave.inc; - # Save original timezone set @my_time_zone= @@global.time_zone; @@ -90,6 +90,7 @@ insert into t1 values ('20040101000000',NULL), ('20040611093902',NULL); # from originally inserted) # set time_zone='MET'; +--disable_warnings ONCE insert into t2 (select * from t1); SELECT * FROM t1 ORDER BY n; sync_slave_with_master; diff --git a/mysql-test/t/events_restart.test b/mysql-test/t/events_restart.test index facf2912087..c6152e5d961 100644 --- a/mysql-test/t/events_restart.test +++ b/mysql-test/t/events_restart.test @@ -106,3 +106,26 @@ let $wait_condition= select count(*) = 0 from information_schema.processlist where db='events_test' and command = 'Connect' and user=current_user(); --source include/wait_condition.inc + +--echo # +--echo # Test for bug#11748899 -- EVENT SET TO DISABLED AND ON COMPLETION +--echo # NOT PRESERVE IS DELETED AT SERVER +--echo # +SELECT @@event_scheduler; +USE test; +--disable_warnings +DROP EVENT IF EXISTS e1; +--enable_warnings +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO SELECT 1; +--replace_column 6 # 9 # 10 # +SHOW EVENTS; + +--echo "Now we restart the server" +--source include/restart_mysqld.inc +USE test; +SELECT @@event_scheduler; +--replace_column 6 # 9 # 10 # +SHOW EVENTS; +DROP EVENT e1; + +--echo # end test for bug#11748899 diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 4de574fbb0d..99ffe278e44 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -352,6 +352,7 @@ create table `t2` (`c2_id` int(10) unsigned NULL auto_increment, `c2_p_id` int(1 insert into t1 values (0,'A01-Comp',1); insert into t1 values (0,'B01-Comp',1); insert into t2 values (0,1,'A Note',1); +--disable_warnings ONCE update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2; select * from t1; select * from t2; diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index e00bd785789..7fff7472caf 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1,5 +1,6 @@ -- source include/not_embedded.inc -- source include/have_log_bin.inc +call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); # # SQL Syntax for Prepared Statements test # @@ -238,8 +239,10 @@ prepare stmt1 from "select 1 into @var"; execute stmt1; execute stmt1; prepare stmt1 from "create table t1 select 1 as i"; +--disable_warnings ONCE execute stmt1; drop table t1; +--disable_warnings ONCE execute stmt1; prepare stmt1 from "insert into t1 select i from t1"; execute stmt1; @@ -432,6 +435,7 @@ deallocate prepare stmt; create table t1 (a int); insert into t1 values (1),(2),(3); create table t2 select * from t1; +--disable_warnings prepare stmt FROM 'create table t2 select * from t1'; drop table t2; execute stmt; @@ -441,6 +445,7 @@ execute stmt; execute stmt; drop table t2; execute stmt; +--enable_warnings drop table t1,t2; deallocate prepare stmt; @@ -1176,6 +1181,7 @@ create database mysqltest character set utf8; prepare stmt1 from "create table mysqltest.t1 (c char(10))"; prepare stmt2 from "create table mysqltest.t2 select 'test'"; execute stmt1; +--disable_warnings ONCE execute stmt2; show create table mysqltest.t1; show create table mysqltest.t2; @@ -1183,6 +1189,7 @@ drop table mysqltest.t1; drop table mysqltest.t2; alter database mysqltest character set latin1; execute stmt1; +--disable_warnings ONCE execute stmt2; show create table mysqltest.t1; show create table mysqltest.t2; diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index f5c96761da1..d164f75d7d8 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -6486,3 +6486,11 @@ ER_PLUGIN_NO_UNINSTALL ER_PLUGIN_NO_INSTALL eng "Plugin '%s' is marked as not dynamically installable. You have to stop the server to install it." + + +ER_BINLOG_UNSAFE_WRITE_AUTOINC_SELECT + eng "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." + +ER_BINLOG_UNSAFE_CREATE_SELECT_AUTOINC + eng "CREATE TABLE... SELECT... on a table with an auto-increment column is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are inserted. This order cannot be predicted and may differ on master and the slave." + diff --git a/sql/sql_base.cc b/sql/sql_base.cc index e0472e2c9b5..f4776130d2f 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -214,7 +214,8 @@ static bool auto_repair_table(THD *thd, TABLE_LIST *table_list); static void free_cache_entry(TABLE *entry); static bool has_write_table_with_auto_increment(TABLE_LIST *tables); - +static bool +has_write_table_with_auto_increment_and_select(TABLE_LIST *tables); uint cached_open_tables(void) { @@ -5683,6 +5684,17 @@ bool lock_tables(THD *thd, TABLE_LIST *tables, uint count, /* We have to emulate LOCK TABLES if we are statement needs prelocking. */ if (thd->lex->requires_prelocking()) { + + /* + DML statements that modify a table with an auto_increment column based on + rows selected from a table are unsafe as the order in which the rows are + fetched fron the select tables cannot be determined and may differ on + master and slave. + */ + if (thd->variables.binlog_format != BINLOG_FORMAT_ROW && tables && + has_write_table_with_auto_increment_and_select(tables)) + thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT); + /* A query that modifies autoinc column in sub-statement can make the master and slave inconsistent. @@ -9080,6 +9092,41 @@ has_write_table_with_auto_increment(TABLE_LIST *tables) return 0; } +/* + checks if the tables have select tables in the table list and write tables + with auto-increment column. + + SYNOPSIS + has_two_write_locked_tables_with_auto_increment_and_select + tables Table list + + RETURN VALUES + + -true if the table list has atleast one table with auto-increment column + + + and atleast one table to select from. + -false otherwise +*/ + +static bool +has_write_table_with_auto_increment_and_select(TABLE_LIST *tables) +{ + bool has_select= false; + bool has_auto_increment_tables = has_write_table_with_auto_increment(tables); + for(TABLE_LIST *table= tables; table; table= table->next_global) + { + if (!table->placeholder() && + (table->lock_type <= TL_READ_NO_INSERT)) + { + has_select= true; + break; + } + } + return(has_select && has_auto_increment_tables); +} + + /* Open and lock system tables for read. diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 0cabab9fae7..7598ddd3cdd 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -61,9 +61,11 @@ Query_tables_list::binlog_stmt_unsafe_errcode[BINLOG_STMT_UNSAFE_COUNT] = ER_BINLOG_UNSAFE_MIXED_STATEMENT, ER_BINLOG_UNSAFE_INSERT_IGNORE_SELECT, ER_BINLOG_UNSAFE_INSERT_SELECT_UPDATE, + ER_BINLOG_UNSAFE_WRITE_AUTOINC_SELECT, ER_BINLOG_UNSAFE_REPLACE_SELECT, ER_BINLOG_UNSAFE_CREATE_IGNORE_SELECT, ER_BINLOG_UNSAFE_CREATE_REPLACE_SELECT, + ER_BINLOG_UNSAFE_CREATE_SELECT_AUTOINC, ER_BINLOG_UNSAFE_UPDATE_IGNORE }; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 8aaead811a0..1de92057b1f 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1269,6 +1269,13 @@ public: BINLOG_STMT_UNSAFE_INSERT_SELECT_UPDATE, /** + Query that writes to a table with auto_inc column after selecting from + other tables are unsafe as the order in which the rows are retrieved by + select may differ on master and slave. + */ + BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT, + + /** INSERT...REPLACE SELECT is unsafe because which rows are replaced depends on the order that rows are retrieved by SELECT. This order cannot be predicted and may differ on master and the slave. @@ -1290,6 +1297,14 @@ public: BINLOG_STMT_UNSAFE_CREATE_REPLACE_SELECT, /** + CREATE TABLE...SELECT on a table with auto-increment column is unsafe + because which rows are replaced depends on the order that rows are + retrieved from SELECT. This order cannot be predicted and may differ on + master and the slave + */ + BINLOG_STMT_UNSAFE_CREATE_SELECT_AUTOINC, + + /** UPDATE...IGNORE is unsafe because which rows are ignored depends on the order that rows are updated. This order cannot be predicted and may differ on master and the slave. diff --git a/sql/sql_table.cc b/sql/sql_table.cc index be1e0d009a3..c7f454376e2 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -3121,6 +3121,15 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, DBUG_RETURN(TRUE); } + /* + CREATE TABLE[with auto_increment column] SELECT is unsafe as the rows + inserted in the created table depends on the order of the rows fetched + from the select tables. This order may differ on master and slave. We + therefore mark it as unsafe. + */ + if (select_field_count > 0 && auto_increment) + thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_CREATE_SELECT_AUTOINC); + /* Create keys */ List_iterator<Key> key_iterator(alter_info->key_list); |