List:Commits« Previous MessageNext Message »
From:Rohit Kalhans Date:March 30 2012 1:36pm
Subject:bzr push into mysql-5.5 branch (rohit.kalhans:3776 to 3777) Bug#11765650
View as plain text  
 3777 Rohit Kalhans	2012-03-30
      BUG#11765650 - 58637: MARK UPDATES THAT DEPEND ON ORDER OF TWO KEYS UNSAFE
            
       Description: When the table has more than one unique or primary key, 
       INSERT... ON DUP KEY UPDATE statement is sensitive to the order in which
       the storage engines checks the keys. Depending on this order, the storage
       engine may determine different rows to mysql, and hence mysql can update
       different rows on master and slave.
            
       Solution: We mark INSERT...ON DUP KEY UPDATE on a table with more than on unique
       key as unsafe therefore the event will be logged in row format if it is available
       (ROW/MIXED). If only STATEMENT format is available, a warning will be thrown. 
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated result file
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Added test to check  for warning being thrown when the unsafe statement is executed
     @ mysql-test/suite/rpl/r/rpl_known_bugs_detection.result
        Updated result file
     @ sql/share/errmsg-utf8.txt
        Added new warning message
     @ sql/sql_base.cc
        check for tables in the query with more than one UNIQUE KEY and INSERT ON DUPLICATE KEY UPDATE, and mark such statements unsafe.

    modified:
      mysql-test/suite/binlog/r/binlog_unsafe.result
      mysql-test/suite/binlog/t/binlog_unsafe.test
      mysql-test/suite/rpl/r/rpl_known_bugs_detection.result
      sql/share/errmsg-utf8.txt
      sql/sql_base.cc
      sql/sql_lex.cc
      sql/sql_lex.h
 3776 Hery Ramilison	2012-03-29
      Starting 5.5.23 build

    modified:
      VERSION
=== modified file 'mysql-test/suite/binlog/r/binlog_unsafe.result'
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result	2012-02-09 17:58:33 +0000
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result	2012-03-30 13:05:53 +0000
@@ -2679,6 +2679,8 @@ CREATE TABLE replace_table (a INT, b INT
 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);
+CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY) ENGINE = InnoDB;
+INSERT INTO insert_2_keys values (1, 1);
 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.
@@ -2702,10 +2704,15 @@ Note	1592	Unsafe statement written to th
 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.
+INSERT INTO insert_2_keys VALUES (1, 2) 
+ON DUPLICATE KEY UPDATE a=VALUES(a)+10, b=VALUES(b)+10;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe
 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;
+DROP TABLE insert_2_keys;
 "End of tests"

=== modified file 'mysql-test/suite/binlog/t/binlog_unsafe.test'
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test	2012-02-09 17:58:33 +0000
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test	2012-03-30 13:05:53 +0000
@@ -17,6 +17,7 @@
 #  - CREATE TABLE [IGNORE/REPLACE] SELECT
 #  - INSERT IGNORE...SELECT
 #  - UPDATE IGNORE
+#  - INSERT... ON DUPLICATE KEY UPDATE on a table with two UNIQUE KEYS
 #
 # Note that statements that use stored functions, stored procedures,
 # triggers, views, or prepared statements that invoke unsafe
@@ -714,6 +715,9 @@ DROP TABLE t1;
 #UPDATE IGNORE 
 #CREATE TABLE... IGNORE SELECT 
 #CREATE TABLE... REPLACE SELECT
+#
+###BUG 11765650 - 58637: MARK UPDATES THAT DEPEND ON ORDER OF TWO KEYS UNSAFE 
+#INSERT.... ON DUP KEY UPDATE on a table with more than one UNIQUE KEY
 
 #setup tables
 CREATE TABLE filler_table (a INT, b INT);
@@ -723,6 +727,8 @@ CREATE TABLE replace_table (a INT, b INT
 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);
+CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY) ENGINE = InnoDB;
+INSERT INTO insert_2_keys values (1, 1);
 
 #INSERT IGNORE... SELECT
 INSERT IGNORE INTO insert_table SELECT * FROM filler_table;
@@ -740,6 +746,10 @@ CREATE TABLE create_replace_test (a INT,
 #temporary tables should not throw the warning.
 CREATE TEMPORARY TABLE temp1 (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table;
 
+#INSERT.... ON DUP KEY UPDATE on a table with more than one UNIQUE KEY
+INSERT INTO insert_2_keys VALUES (1, 2) 
+       ON DUPLICATE KEY UPDATE a=VALUES(a)+10, b=VALUES(b)+10;
+
 ###clean up
 DROP TABLE filler_table;
 DROP TABLE insert_table;
@@ -747,5 +757,6 @@ DROP TABLE update_table;
 DROP TABLE replace_table;
 DROP TABLE create_ignore_test;
 DROP TABLE create_replace_test;
+DROP TABLE insert_2_keys;
 
 --echo "End of tests"

=== modified file 'mysql-test/suite/rpl/r/rpl_known_bugs_detection.result'
--- a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result	2012-02-13 08:42:13 +0000
+++ b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result	2012-03-30 13:05:53 +0000
@@ -4,6 +4,8 @@ call mtr.add_suppression("Unsafe stateme
 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
 UNIQUE(b));
 INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe
 SELECT * FROM t1;
 a	b
 1	10

=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt	2012-03-15 09:15:50 +0000
+++ b/sql/share/errmsg-utf8.txt	2012-03-30 13:05:53 +0000
@@ -6494,6 +6494,9 @@ ER_BINLOG_UNSAFE_WRITE_AUTOINC_SELECT
 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."
 
+ER_BINLOG_UNSAFE_INSERT_TWO_KEYS
+  eng "INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe"
+
 #
 #  End of 5.5 error messages.
 #

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2012-02-16 09:48:16 +0000
+++ b/sql/sql_base.cc	2012-03-30 13:05:53 +0000
@@ -5691,6 +5691,30 @@ bool lock_tables(THD *thd, TABLE_LIST *t
         has_write_table_with_auto_increment_and_select(tables))
       thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT);
 
+    /* 
+     INSERT...ON DUPLICATE KEY UPDATE on a table with more than one unique keys
+     can be unsafe.
+     */
+    uint unique_keys= 0;
+    for (TABLE_LIST *query_table= tables; query_table && unique_keys <= 1;
+         query_table= query_table->next_global)
+      if(query_table->table)
+      {
+        uint keys= query_table->table->s->keys, i= 0;
+        unique_keys= 0;
+        for (KEY* keyinfo= query_table->table->s->key_info;
+             i < keys && unique_keys <= 1; i++, keyinfo++)
+        {
+          if (keyinfo->flags & HA_NOSAME)
+            unique_keys++;
+        }
+        if (!query_table->placeholder() &&
+            query_table->lock_type >= TL_WRITE_ALLOW_WRITE &&
+            unique_keys > 1 && thd->lex->sql_command == SQLCOM_INSERT &&
+            thd->lex->duplicates == DUP_UPDATE)
+          thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_INSERT_TWO_KEYS);
+      }
+ 
     /* We have to emulate LOCK TABLES if we are statement needs prelocking. */
     if (thd->lex->requires_prelocking())
     {

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2012-03-29 13:07:54 +0000
+++ b/sql/sql_lex.cc	2012-03-30 13:05:53 +0000
@@ -66,7 +66,8 @@ Query_tables_list::binlog_stmt_unsafe_er
   ER_BINLOG_UNSAFE_CREATE_IGNORE_SELECT,
   ER_BINLOG_UNSAFE_CREATE_REPLACE_SELECT,
   ER_BINLOG_UNSAFE_CREATE_SELECT_AUTOINC,
-  ER_BINLOG_UNSAFE_UPDATE_IGNORE
+  ER_BINLOG_UNSAFE_UPDATE_IGNORE,
+  ER_BINLOG_UNSAFE_INSERT_TWO_KEYS
 };
 
 

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2012-03-29 13:07:54 +0000
+++ b/sql/sql_lex.h	2012-03-30 13:05:53 +0000
@@ -1323,6 +1323,12 @@ public:
     */
     BINLOG_STMT_UNSAFE_UPDATE_IGNORE,
 
+    /**
+      INSERT... ON DUPLICATE KEY UPDATE on a table with more than one
+      UNIQUE KEYS  is unsafe.
+    */
+    BINLOG_STMT_UNSAFE_INSERT_TWO_KEYS,
+
     /* The last element of this enumeration type. */
     BINLOG_STMT_UNSAFE_COUNT
   };

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-5.5 branch (rohit.kalhans:3776 to 3777) Bug#11765650Rohit Kalhans31 Mar