bzr commit into mysql-5.0-bugteam branch (kristofer.pettersson:2809) Bug#42108

[Kristofer Pettersson]

#At file:///Users/thek/Development/50-bug42108/ based on revid:sergey.glukhov@stripped

 2809 Kristofer Pettersson	2009-09-22
      Bug#42108 Wrong locking for UPDATE with subqueries leads to broken statement
                      replication
                   
      MySQL server uses wrong lock type (always TL_READ instead of
      TL_READ_NO_INSERT when appropriate) for tables used in
      subqueries of UPDATE statement. This leads in some cases to
      a broken replication as statements are written in the wrong
      order to the binlog.
                        
      The patch explicitly sets the lock level to TL_READ_NO_INSERT
      for any subselect following an UPDATE-statement.
     @ mysql-test/r/binlog.result
        * test case for bug 42108
     @ mysql-test/t/binlog.test
        * test case for bug 42108
     @ sql/sql_yacc.yy
        * Set lock_option to either TL_READ_NO_INSERT or
          TL_READ for any sub-SELECT following UPDATE. 
        * Fixed indentation.

    modified:
      mysql-test/r/binlog.result
      mysql-test/t/binlog.test
      sql/sql_yacc.yy
=== modified file 'mysql-test/r/binlog.result'
--- a/mysql-test/r/binlog.result	2009-05-31 03:26:58 +0000
+++ b/mysql-test/r/binlog.result	2009-09-22 09:48:46 +0000
@@ -653,4 +653,23 @@ DROP PROCEDURE p1;
 DROP PROCEDURE p2;
 DROP PROCEDURE p3;
 DROP PROCEDURE p4;
+#
+# Bug 42108 Wrong locking for UPDATE with subqueries leads to broken
+#           statement replication
+#
+RESET MASTER;
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (j int);
+INSERT INTO t2 VALUES (1);
+UPDATE t1 SET i=10 WHERE sleep(5) + 1 AND (SELECT count(*) FROM t2 WHERE j = i);;
+INSERT INTO t2 VALUES (2);
+** Last row should be INSERT INTO t2 VALUES (2) or the binlog order is
+** mixed up!
+use `test`; INSERT INTO t2 VALUES (2)
+SELECT * FROM t1;
+i
+10
+2
+DROP TABLE t1,t2;
 End of 5.0 tests

=== modified file 'mysql-test/t/binlog.test'
--- a/mysql-test/t/binlog.test	2009-03-25 16:48:10 +0000
+++ b/mysql-test/t/binlog.test	2009-09-22 09:48:46 +0000
@@ -201,4 +201,31 @@ DROP PROCEDURE p2;
 DROP PROCEDURE p3;
 DROP PROCEDURE p4;
 
+--source include/have_log_bin.inc
+--echo #
+--echo # Bug 42108 Wrong locking for UPDATE with subqueries leads to broken
+--echo #           statement replication
+--echo #
+RESET MASTER;
+connect (con1, localhost, root,,);
+connection default;
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (j int);
+INSERT INTO t2 VALUES (1);
+# Using sleep here to emulate long running update or unfortunate scheduling
+--send UPDATE t1 SET i=10 WHERE sleep(5) + 1 AND (SELECT count(*) FROM t2 WHERE j = i);
+connection con1;
+--sleep 2
+INSERT INTO t2 VALUES (2);
+connection default;
+--reap
+--echo ** Last row should be INSERT INTO t2 VALUES (2) or the binlog order is
+--echo ** mixed up!
+let $value= query_get_value(SHOW BINLOG EVENTS, Info, 7);
+echo $value;
+SELECT * FROM t1;
+DROP TABLE t1,t2;
+disconnect con1;
+
 --echo End of 5.0 tests

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2009-08-27 10:22:19 +0000
+++ b/sql/sql_yacc.yy	2009-09-22 09:48:46 +0000
@@ -7805,36 +7805,36 @@ opt_insert_update:
 /* Update rows in a table */
 
 update:
-	UPDATE_SYM
-	{
-	  LEX *lex= Lex;
-	  mysql_init_select(lex);
-          lex->sql_command= SQLCOM_UPDATE;
-	  lex->lock_option= TL_UNLOCK; 	/* Will be set later */
-	  lex->duplicates= DUP_ERROR; 
-        }
-        opt_low_priority opt_ignore join_table_list
-	SET update_list
-	{
-	  LEX *lex= Lex;
-          if (lex->select_lex.table_list.elements > 1)
-            lex->sql_command= SQLCOM_UPDATE_MULTI;
-	  else if (lex->select_lex.get_table_list()->derived)
-	  {
-	    /* it is single table update and it is update of derived table */
-	    my_error(ER_NON_UPDATABLE_TABLE, MYF(0),
-                     lex->select_lex.get_table_list()->alias, "UPDATE");
-	    MYSQL_YYABORT;
-	  }
-          /*
-            In case of multi-update setting write lock for all tables may
-            be too pessimistic. We will decrease lock level if possible in
-            mysql_multi_update().
-          */
-          Select->set_lock_for_tables($3);
-	}
-	where_clause opt_order_clause delete_limit_clause {}
-	;
+  UPDATE_SYM
+  {
+    LEX *lex= Lex;
+    mysql_init_select(lex);
+    lex->sql_command= SQLCOM_UPDATE;
+    lex->lock_option= using_update_log ? TL_READ_NO_INSERT : TL_READ;
+    lex->duplicates= DUP_ERROR; 
+  }
+  opt_low_priority opt_ignore join_table_list
+  SET update_list
+  {
+    LEX *lex= Lex;
+    if (lex->select_lex.table_list.elements > 1)
+      lex->sql_command= SQLCOM_UPDATE_MULTI;
+    else if (lex->select_lex.get_table_list()->derived)
+    {
+      /* it is single table update and it is update of derived table */
+      my_error(ER_NON_UPDATABLE_TABLE, MYF(0),
+               lex->select_lex.get_table_list()->alias, "UPDATE");
+      MYSQL_YYABORT;
+    }
+    /*
+      In case of multi-update setting write lock for all tables may
+      be too pessimistic. We will decrease lock level if possible in
+      mysql_multi_update().
+    */
+    Select->set_lock_for_tables($3);
+  }
+  where_clause opt_order_clause delete_limit_clause {}
+  ;
 
 update_list:
 	update_list ',' update_elem


Attachment: [text/bzr-bundle]