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.



mysql-test/extra/rpl_tests/rpl_insert_duplicate.test:
  Test removed: Added the test to rpl.rpl_insert_ignore.test
  
  
  
  ******
  Test removed: the test is redundant as the same is being tested in rpl.rpl_insert_ignore.
mysql-test/extra/rpl_tests/rpl_insert_id.test:
  Warnings disabled for the unsafe statements.
mysql-test/extra/rpl_tests/rpl_insert_ignore.test:
  1. Disabled warnings while  for unsafe statements
  2. As INSERT...IGNORE is an unsafe statement, an insert ignore not changing any rows, 
  will not be logged in the binary log, in the ROW and MIXED modes. It will however be logged
  in STATEMENT mode.
mysql-test/r/commit_1innodb.result:
  updated result file
  
  
  
  ******
  updated result file
mysql-test/suite/binlog/r/binlog_stm_blackhole.result:
  Updated result file.
mysql-test/suite/binlog/r/binlog_unsafe.result:
  updated result file
mysql-test/suite/binlog/t/binlog_unsafe.test:
   added tests for the statements marked as unsafe.
mysql-test/suite/rpl/r/rpl_insert_duplicate.result:
  File Removed :Result file of rpl_insert_duplicate, which has been removed.
mysql-test/suite/rpl/r/rpl_insert_ignore.result:
  Added the content of rpl.rpl_insert_duplicate here.
mysql-test/suite/rpl/r/rpl_insert_select.result:
  Result file removed as the corresponding test has beenn removed.
mysql-test/suite/rpl/r/rpl_known_bugs_detection.result:
  Updated result file.
mysql-test/suite/rpl/t/rpl_insert_duplicate.test:
  File Removed: this was a wrapper for rpl.rpl_insert_duplicate.test, which has been removed.
mysql-test/suite/rpl/t/rpl_insert_select.test:
  File Removed: This test became redundant after this fix, This test showed how INSERT IGNORE...SELECT break replication, which has been handled in this fix.
mysql-test/suite/rpl/t/rpl_known_bugs_detection.test:
  Since all the tests are statement based bugs are being tested, having mixed format
  forces the event to be written in row format. When the statement and causes the
  test to fail as certain known bugs do not occur when the even is logged in row format.
sql/share/errmsg-utf8.txt:
  added 6 new Warning messages.
  
  
  
  ******
  added 6 new Warning messages.
sql/sql_lex.cc:
  Added 6 new error Identifier [ER_BINLOG_STMT_UNSAFEE_*]
sql/sql_lex.h:
  Added 6 new BINLOG_STMT_UNSAFE_* enums to identify the type of unsafe statement dealt with in this bug.
  
  
  ******
  Added 6 new BINLOG_STMT_UNSAFE_* enums to identify the type of unsafe statement dealt with in this bug.
sql/sql_parse.cc:
  added check for specific queries and marked them as unsafe.
  
  
  ******
  added check for specific queries and marked them as unsafe.

3 files changed, 65 insertions, 74 deletions

diff --git a/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test b/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test
deleted file mode 100644
index a81eeba3231..00000000000
--- a/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test
+++ /dev/null
@@ -1,59 +0,0 @@
-# BUG#59338 Inconsistency in binlog for statements that don't change any rows STATEMENT SBR
-# In SBR, if a statement does not fail, it is always written to the binary log,
-# regardless if rows are changed or not. If there is a failure, a statement is
-# only written to the binary log if a non-transactional (.e.g. MyIsam) engine
-# is updated. INSERT ON DUPLICATE KEY UPDATE was not following the rule above
-# and was not written to the binary log, if then engine was Innodb.
-#
-# In this test case, we check if INSERT ON DUPLICATE KEY UPDATE that does not
-# change anything is still written to the binary log.
-
-# Prepare environment
---connection master
-
-eval CREATE TABLE t1 (
- a INT UNSIGNED NOT NULL PRIMARY KEY
-) ENGINE=$engine_type;
-
-eval CREATE TABLE t2 (
- a INT UNSIGNED
-) ENGINE=$engine_type;
-
-INSERT INTO t1 VALUES (1);
-INSERT INTO t2 VALUES (1);
-
-# An insert duplicate that does not update anything must be written to the binary
-# log in SBR and MIXED modes. We check this property by summing a before and after
-# the update and comparing the binlog positions. The sum should be the same at both
-# points and the statement should be in the binary log.
---let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1)
---let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1)
---let $statement_file=INSERT INTO t1 SELECT t2.a FROM t2 ORDER BY t2.a ON DUPLICATE KEY UPDATE t1.a= t1.a
---eval $statement_file
-
---let $assert_cond= SUM(a) = 1 FROM t1
---let $assert_text= Sum of elements in t1 should be 1.
---source include/assert.inc
-
-if (`SELECT @@BINLOG_FORMAT = 'ROW'`)
-{
-  --let $binlog_position_cmp= =
-  --let $assert_cond= [SHOW MASTER STATUS, Position, 1] $binlog_position_cmp $binlog_start
-  --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.
-}
-if (`SELECT @@BINLOG_FORMAT != 'ROW'`)
-{
-  --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 1, 1\', Info, 1]\' LIKE \'%$statement_file\'
-  --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.
-}
---source include/assert.inc
-
-# Compare master and slave
---sync_slave_with_master
---let $diff_tables= master:test.t1 , slave:test.t1
---source include/diff_tables.inc
-
-# Clean up
---connection master
-drop table t1, t2;
---sync_slave_with_master
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;
diff --git a/mysql-test/extra/rpl_tests/rpl_insert_ignore.test b/mysql-test/extra/rpl_tests/rpl_insert_ignore.test
index 270dde7675c..f422ef35f8c 100644
--- a/mysql-test/extra/rpl_tests/rpl_insert_ignore.test
+++ b/mysql-test/extra/rpl_tests/rpl_insert_ignore.test
@@ -31,7 +31,7 @@ INSERT INTO t2 VALUES (3, 5);
 INSERT INTO t2 VALUES (4, 3);
 INSERT INTO t2 VALUES (5, 4);
 INSERT INTO t2 VALUES (6, 6);
-
+--disable_warnings ONCE
 INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
 --let $assert_cond= COUNT(*) = 6 FROM t1
 --let $assert_text= Count of elements in t1 should be 6.
@@ -51,25 +51,70 @@ INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
 --let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1)
 --let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1)
 --let $statement_file=INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a
+--disable_warnings ONCE
 --eval $statement_file
 
 --let $assert_cond= COUNT(*) = 6 FROM t1
 --let $assert_text= Count of elements in t1 should be 6.
 --source include/assert.inc
 
-if (`SELECT @@BINLOG_FORMAT = 'ROW'`)
+if (`SELECT @@BINLOG_FORMAT != 'STATEMENT'`)
 {
   --let $binlog_position_cmp= =
   --let $assert_cond= [SHOW MASTER STATUS, Position, 1] $binlog_position_cmp $binlog_start
   --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.
 }
-if (`SELECT @@BINLOG_FORMAT != 'ROW'`)
+if (`SELECT @@BINLOG_FORMAT = 'STATEMENT'`)
 {
   --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 2, 1\', Info, 1]\' LIKE \'%$statement_file\'
   --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.
 }
+
+--source include/assert.inc
+
+# An insert duplicate that does not update anything must be written to the binary
+# log in SBR and MIXED modes. We check this property by summing a before and after
+# the update and comparing the binlog positions. The sum should be the same at both
+# points and the statement should be in the binary log.
+--disable_warnings
+DROP TABLE t1;
+DROP TABLE t2;
+--enable_warnings
+eval CREATE TABLE t1 (
+ a INT UNSIGNED NOT NULL PRIMARY KEY
+) ENGINE=$engine_type;
+
+eval CREATE TABLE t2 (
+ a INT UNSIGNED
+) ENGINE=$engine_type;
+
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1);
+
+--let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1)
+--let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1)
+--let $statement_file=INSERT INTO t1 SELECT t2.a FROM t2 ORDER BY t2.a ON DUPLICATE KEY UPDATE t1.a= t1.a
+--disable_warnings ONCE
+--eval $statement_file
+
+--let $assert_cond= SUM(a) = 1 FROM t1
+--let $assert_text= Sum of elements in t1 should be 1.
+--source include/assert.inc
+
+if (`SELECT @@BINLOG_FORMAT != 'STATEMENT'`)
+{
+  --let $binlog_position_cmp= =
+  --let $assert_cond= [SHOW MASTER STATUS, Position, 1] $binlog_position_cmp $binlog_start
+  --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.
+}
+if (`SELECT @@BINLOG_FORMAT = 'STATEMENT'`)
+{
+  --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 1, 1\', Info, 1]\' LIKE \'%$statement_file\'
+  --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.
+}
 --source include/assert.inc
 
+
 # Clean up
 --connection master
 drop table t1, t2;