BUG#11758262 - 50439: MARK INSERT...SEL...ON DUP KEY UPD,REPLACE...SEL,CREATE...[IGN|REPL] SEL

Problem: The following statements can cause the slave to go out of sync 
if logged in statement format: 
INSERT IGNORE...SELECT 
INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 
REPLACE ... SELECT 
UPDATE IGNORE :
CREATE ... IGNORE SELECT 
CREATE ... REPLACE SELECT  
           
Background: Since the order of the rows returned by the SELECT 
statement or otherwise may differ on master and slave, therefore
the above statements may cuase the salve to go out of sync with
the master. 
      
Fix:
Issue a warning when statements like the above are exectued and 
the bin-logging format is statement. If the logging format is mixed,
use row based logging. Marking a statement as unsafe has been 
done in the sql/sql_parse.cc instead of sql/sql_yacc.cc, because while
parsing for a token has been done we cannot be sure if the parsing
of the other tokens has been done as well.
      
Six new warning  messages has been added for each unsafe statement. 
      
binlog.binlog_unsafe.test has been updated to incoporate these additional unsafe statments.


******
BUG#11758262 - 50439: MARK INSERT...SEL...ON DUP KEY UPD,REPLACE...SEL,CREATE...[IGN|REPL] SEL 
Problem: The following statements can cause the slave to go out of sync 
if logged in statement format: 
INSERT IGNORE...SELECT 
INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 
REPLACE ... SELECT 
UPDATE IGNORE :
CREATE ... IGNORE SELECT 
CREATE ... REPLACE SELECT  
           
Background: Since the order of the rows returned by the SELECT 
statement or otherwise may differ on master and slave, therefore
the above statements may cuase the salve to go out of sync with
the master. 
      
Fix:
Issue a warning when statements like the above are exectued and 
the bin-logging format is statement. If the logging format is mixed,
use row based logging. Marking a statement as unsafe has been 
done in the sql/sql_parse.cc instead of sql/sql_yacc.cc, because while
parsing for a token has been done we cannot be sure if the parsing
of the other tokens has been done as well.
      
Six new warning  messages has been added for each unsafe statement. 
      
binlog.binlog_unsafe.test has been updated to incoporate these additional unsafe statments.

1 files changed, 17 insertions, 12 deletions

diff --git a/mysql-test/extra/rpl_tests/rpl_insert_id.test b/mysql-test/extra/rpl_tests/rpl_insert_id.test
index 565ab4a67ad..32d861bd45a 100644
--- a/mysql-test/extra/rpl_tests/rpl_insert_id.test
+++ b/mysql-test/extra/rpl_tests/rpl_insert_id.test
@@ -77,6 +77,7 @@ eval create table t2(b int auto_increment, c int, key(b)) engine=$engine_type;
 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;
@@ -113,8 +114,10 @@ set @@session.sql_auto_is_null=1;
 eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
 eval create table t2(a int) engine=$engine_type;
 insert into t1 (a) values (null);
+--disable_warnings
 insert into t2 (a) select a from t1 where a is null;
 insert into t2 (a) select a from t1 where a is null;
+--enable_warnings
 select * from t2;
 sync_slave_with_master;
 connection slave;
@@ -172,17 +175,15 @@ begin
 end|
 delimiter ;|
 
---disable_warnings
+--disable_warnings ONCE
 insert into t1 (last_id) values (0);
---enable_warnings
 
 drop trigger t1_bi;
 
 # Check that nested call doesn't affect outer context.
 select last_insert_id();
---disable_warnings
+--disable_warnings ONCE
 select bug15728_insert();
---enable_warnings
 select last_insert_id();
 insert into t1 (last_id) values (bug15728());
 # This should be exactly one greater than in the previous call.
@@ -190,9 +191,8 @@ select last_insert_id();
 
 # BUG#20339 - stored procedure using LAST_INSERT_ID() does not
 # replicate statement-based
---disable_warnings
+--disable_warnings ONCE
 drop procedure if exists foo;
---enable_warnings
 delimiter |;
 create procedure foo()
 begin
@@ -252,6 +252,7 @@ select * from t1 order by n;
 # table's counter, the counter for next row is bigger than the
 # after-value of the updated row.
 connection master;
+--disable_warnings ONCE
 insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
 select * from t1 order by n;
 sync_slave_with_master;
@@ -270,6 +271,7 @@ delete from t1 where b <> 100;
 select * from t1 order by n;
 
 connection master;
+--disable_warnings ONCE
 insert into t1 values(null,100),(null,350) on duplicate key update n=2;
 select * from t1 order by n;
 sync_slave_with_master;
@@ -287,6 +289,7 @@ connection master;
 # testcase with INSERT VALUES
 eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
                       UNIQUE(b)) ENGINE=$engine_type;
+--disable_warnings ONCE
 INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
 SELECT * FROM t1 ORDER BY a;
 sync_slave_with_master;
@@ -314,19 +317,23 @@ INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
 INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
 INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
 # Updating table t1 based on values from table t2
+--disable_warnings
 INSERT INTO t1 (field_1, field_2, field_3)
 SELECT t2.field_a, t2.field_b, t2.field_c
 FROM t2
 ON DUPLICATE KEY UPDATE
 t1.field_3 = t2.field_c;
+--enable_warnings
 # Inserting new record into t2
 INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
 # Updating t1 again
+--disable_warnings
 INSERT INTO t1 (field_1, field_2, field_3)
 SELECT t2.field_a, t2.field_b, t2.field_c
 FROM t2
 ON DUPLICATE KEY UPDATE
 t1.field_3 = t2.field_c;
+--enable_warnings
 SELECT * FROM t1 ORDER BY id;
 sync_slave_with_master;
 SELECT * FROM t1 ORDER BY id;
@@ -433,9 +440,8 @@ delimiter ;|
 
 INSERT INTO t1 VALUES (NULL, -1);
 CALL p1();
---disable_warnings
+--disable_warnings ONCE
 SELECT f1();
---enable_warnings
 INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
                       (NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
 INSERT INTO t1 VALUES (NULL, f2());
@@ -504,16 +510,14 @@ insert into t2 (id) values(1),(2),(3);
 delete from t2;
 set sql_log_bin=1;
 #inside SELECT, then inside INSERT
---disable_warnings
+--disable_warnings ONCE
 select insid();
---enable_warnings
 set sql_log_bin=0;
 insert into t2 (id) values(5),(6),(7);
 delete from t2 where id>=5;
 set sql_log_bin=1;
---disable_warnings
+--disable_warnings ONCE
 insert into t1 select insid();
---enable_warnings
 select * from t1 order by id;
 select * from t2 order by id;
 
@@ -537,6 +541,7 @@ begin
   insert into t2 values(null,3);
 end|
 delimiter ;|
+--disable_warnings ONCE
 call foo();
 select * from t1 order by n;
 select * from t2 order by id;