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.

1 files changed, 38 insertions, 0 deletions

diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result
index 9f35f09e220..a307b765097 100644
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result
@@ -2352,6 +2352,7 @@ Note	1592	Unsafe statement written to the binary log using statement format sinc
 REPLACE INTO t1 SELECT * FROM t1 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.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave.
 UPDATE t1 SET a=1 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.
@@ -2368,6 +2369,7 @@ END|
 CALL p1();
 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.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave.
 DROP PROCEDURE p1;
 DROP TABLE t1;
 DROP TABLE IF EXISTS t1;
@@ -2651,4 +2653,40 @@ a
 13:46:40
 1970-01-12 13:46:40
 DROP TABLE t1;
+CREATE TABLE filler_table (a INT, b INT);
+INSERT INTO filler_table values (1,1),(1,2);
+CREATE TABLE insert_table (a INT, b INT, PRIMARY KEY(a));
+CREATE TABLE replace_table (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO replace_table values (1,1),(2,2);
+CREATE TABLE update_table (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO update_table values (1,1),(2,2);
+INSERT IGNORE INTO insert_table SELECT * FROM filler_table;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT IGNORE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave.
+TRUNCATE TABLE insert_table;
+INSERT INTO insert_table SELECT * FROM filler_table ON DUPLICATE KEY UPDATE a = 1;
+Warnings:
+Note	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.
+TRUNCATE TABLE insert_table;
+REPLACE INTO replace_table SELECT * FROM filler_table;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave.
+UPDATE IGNORE update_table SET a=2;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. UPDATE IGNORE is unsafe because the order in which rows are updated determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave.
+CREATE TABLE create_ignore_test (a INT, b INT, PRIMARY KEY(b)) IGNORE SELECT * FROM filler_table;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE... IGNORE SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave.
+CREATE TABLE create_replace_test (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE... REPLACE SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave.
+CREATE TEMPORARY TABLE temp1 (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE... REPLACE SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave.
+DROP TABLE filler_table;
+DROP TABLE insert_table;
+DROP TABLE update_table;
+DROP TABLE replace_table;
+DROP TABLE create_ignore_test;
+DROP TABLE create_replace_test;
 "End of tests"