From 586c0c0ef6e23c4ce3c63f796187178f15e7010a Mon Sep 17 00:00:00 2001 From: Rohit Kalhans Date: Thu, 29 Sep 2011 14:47:27 +0530 Subject: 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. --- mysql-test/r/commit_1innodb.result | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'mysql-test/r/commit_1innodb.result') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index fb1552b6c28..33fad9a0146 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -549,6 +549,8 @@ SUCCESS # 15. Read-write statement: UPDATE IGNORE, change 0 rows. # update ignore t1 set a=2 where a=1; +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. call p_verify_status_increment(2, 2, 1, 0); SUCCESS @@ -814,6 +816,8 @@ SUCCESS insert into t2 select a from t1; commit; replace into t2 select a from t1; +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. commit; call p_verify_status_increment(8, 8, 8, 8); SUCCESS -- cgit v1.2.1 From 0e6afc7f6b1fef4e639f2012332c46301d3af1cb Mon Sep 17 00:00:00 2001 From: Rohit Kalhans Date: Fri, 30 Sep 2011 15:16:35 +0530 Subject: BUG#11758262 BUG#13043055 Problem: commit_1innodb fails on pb2 after the patch for BUG#11758262 Background: Certain statements threw warnings only in statement mode causing the result cintent mismatch. Fix: disabled warnings from the statements. --- mysql-test/r/commit_1innodb.result | 4 ---- 1 file changed, 4 deletions(-) (limited to 'mysql-test/r/commit_1innodb.result') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 33fad9a0146..fb1552b6c28 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -549,8 +549,6 @@ SUCCESS # 15. Read-write statement: UPDATE IGNORE, change 0 rows. # update ignore t1 set a=2 where a=1; -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. call p_verify_status_increment(2, 2, 1, 0); SUCCESS @@ -816,8 +814,6 @@ SUCCESS insert into t2 select a from t1; commit; replace into t2 select a from t1; -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. commit; call p_verify_status_increment(8, 8, 8, 8); SUCCESS -- cgit v1.2.1 From 0122a138e4b8da24fc0f943fad9f610ad5b6f4d2 Mon Sep 17 00:00:00 2001 From: Rohit Kalhans Date: Mon, 3 Oct 2011 16:05:52 +0530 Subject: BUG#11758262 BUG#13043055: Fix for commit_1innodb failure on pb2. Background: as status increment differs for an unsafe statement when logged in stmt and row format, mtr throws a content mismatch error. Fix: call p_verify_status_increment with different arguments for loging format as stmt and row/mixed and disable query log. --- mysql-test/r/commit_1innodb.result | 2 -- 1 file changed, 2 deletions(-) (limited to 'mysql-test/r/commit_1innodb.result') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index fb1552b6c28..3e3d75f66e4 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -549,11 +549,9 @@ SUCCESS # 15. Read-write statement: UPDATE IGNORE, change 0 rows. # update ignore t1 set a=2 where a=1; -call p_verify_status_increment(2, 2, 1, 0); SUCCESS commit; -call p_verify_status_increment(2, 2, 1, 0); SUCCESS # -- cgit v1.2.1