From: Kristofer Pettersson
Date: September 22 2009 9:48am
Subject: bzr commit into mysql-5.0-bugteam branch (kristofer.pettersson:2809)
 Bug#42108
List-Archive: http://lists.mysql.com/commits/84076
X-Bug: 42108
Message-Id: <0KQD002269XJH900@fe-emea-09.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="Boundary_(ID_/TX6ZCuBU1PoQgUiWFCfnw)"

--Boundary_(ID_/TX6ZCuBU1PoQgUiWFCfnw)
MIME-version: 1.0
Content-type: text/plain; CHARSET=US-ASCII
Content-transfer-encoding: 7BIT
Content-disposition: inline

#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


--Boundary_(ID_/TX6ZCuBU1PoQgUiWFCfnw)
MIME-version: 1.0
Content-type: text/bzr-bundle; CHARSET=US-ASCII;
 name*0="bzr/kristofer.pettersson@stripped";
 name*1=2094846-wxitmmtfcpz80f5p.bundle
Content-transfer-encoding: 7BIT
Content-disposition: inline;
 filename*0="bzr/kristofer.pettersson@stripped";
 filename*1=2094846-wxitmmtfcpz80f5p.bundle

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: kristofer.pettersson@stripped\
#   wxitmmtfcpz80f5p
# target_branch: file:///Users/thek/Development/50-bug42108/
# testament_sha1: 2e81c0ab80fab178e72b727d0c4370e9a43cbf01
# timestamp: 2009-09-22 11:48:55 +0200
# base_revision_id: sergey.glukhov@stripped\
#   quchrnpayidj0eyx
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWR4hCKAABLzfgFC9fX/////v
/+D////6YAth327VnW197Ko+2eDaj7bQ9Pvra92+vOn13l52oGr4SiBTT1T0nlTGaNFP0JtRPSeo
9TENDamT1DR6Q0GIZDQSUEAAaCUzUaA9CBoyPSBoAAaAaA0GRTaUntUDaT9Sepp6mhoBoaBoAAAA
ABoBIiKaTDQqaPwTVP09Ep+qPFD8pGnpMg9TQaAAAaekEVJpTxpGmqMemp6RlPU0yPSDQGgAAAAA
DQSSAmgmTRkU8RhNEZT0aUPU0eoaGajTQAAGSQuKghjpqqv1yqQVfv/MfR0lrF3dxkiJhVuJms5/
WZOyMWfuVo3RdlzRMjXL2fV4/Zc/pXB169KYAUMCs760Z7IZ1gqDK5hsOrK3blEGAoilzbjqc6t0
IN2WO8Ntt1tIgkcLERBIgh3hRExTIVWdQDhdB1PpScuFsiQ6C6eQN66E202N5Lg6KWu3xF0Xumpt
oRQ2bXO0GRp7R5FsuHmd9LD+HE+P0+43DdewuQaYEx4/zsL4kv0n2DDp9G3yR3tzqamSiNyc8CBS
RwJcjZYwjOso5JjpWBWQexrNByNH3yH4ca2SS1Lk7ERtO65ubRMR2MvIXOdRtnUKMDA5z5FLAwOj
1qqZOjUbLDIayMiHF2rMdWX0T9tmdZjGh9ryaX4Vt/pbFgUDHR7CY4PkMO8MGpNMZ9mGZiJxrtug
OkyfOTbXUHxv2Ojm/bLBTcTgOY4HLj0+3Lv+MvY6hmfgr3bGuj+L1Nmt0CDdimp1tUQOJiqqqq3u
Zp2rftl9TociFoqYPVEephq2M9BWSNNqBMmKul0EtBGyeztnuzGnIeKkEFpSo/NCPDqZ6/x3Zc2V
wYBHBSlUAZrt9JS8hBTt4RILziZoVYjgwpA3lHSJtTE1VhN4R15rV66YfNqq9INcySdmbQpC1Ici
DIojuOfy6BZg1YMML7kUfYrZy59d2IUMRBorwnsrTrb1qsCtGAciKgExUGK+ZMiotJycOE9PcdtY
11FWvLxrZ2UzKZgrASsTG2EgolKozmnptgnHxTbA3DC5jGDQtbiGw83N4lxsWbFlQBqz43rLG27f
pfHLK0TRBj0b5ocCm/4t3WF2EPNayVW02mAcjYdFWMIcR3Xc8WgkcEpXpIkOWAhtBMT9ojel6ptA
TyS0ziqtlTiZ8T8HkyzGE5yqcC4zN06GpQJ5o6KDRq/fUsH8ZKFJkUt2yRVpSbYUKGsgdjiV5L7V
aad3nZSom5VuGKgVZxMJdnC0Faak2G6+g0CMK8ZRy5wSGI7TqKszmu1TT8a67sB+Mm5H1Yi3EgPK
/LSayYd8fuaSwNInlWV8SODsVMl74HG45F5WUOJ86+Ky6q1wi7PfyVSs4X8BGpxBnlCl5gP+Y3dC
hXqKS9ctmJzF4FDyJWuhKKZYOy7UxnNZYRmFNTEzBiMRxVjg6shtWXFShcowmU5Zogt2EzkRZjSZ
XrGsoVklLlO2LbEZW5d1UiBsGEb4FhuLgcScTNN02Jm4Jntg6za/Sq5lEghFwjXYM4RoZVlxNQgO
NldhAgxKyWQmjHCGm1RhLL2AV6wi//F3m7fHPywG/mf+SSw7tsUaxOs+myj8PwF/LAHJrZaazwFn
vWnuyYhk9sKHVWCI+E6e4OrHNYl2YqzXsUtKnt3q8ggRrA79iXegvn0HRQGC5tA2xtuv9IKwavOX
goj0F5fahULJDGG4HfGMGDahIrKPOi2p/oel6F4+VBMORRMSjIOR7+MI4+3QVhImDRqD2M08qklk
j6B2enJBalOgYYA4ua55oqPSSGEjRGtBfAP8WNVuTgkLhKzAKB7AqSnCw4w0lUYlNhF8jac9CFEu
UO5xnkX7YXYDCKDkw5mHijJJaRjKAtFwhnPQcZvIMtzDxtTCLNTgHF9VrT7JhLRQLUnl1Q/6fHn5
d5n9TQXeopwNFwnJhKorHjNKvy87UkVBylnNrNigtF7kIgaDjOVN85Sf24EhSg8BGQRwmuaUV64j
0J6MhX93PLWutsIkviaz4nqcXt2Ki+t+IhrdBNjiVqW7tYvYqYvFVJMDuBpkSeNhMXm1e4kNXKbR
zaU3TGOLF09dsaYEr34J5fQmljEZXwYDPWEpg5iea7PMR318UoU/XaG4xPrGPZoCtEkVmw58Dctu
uZ0015auqU1DwWFGT2FuVH/JAPMfYx6rWTjERdrZIts8B6c9wa1VcoogEoImQqGJCJHBL2jjG0JZ
K5n+Vi5lYXWr0V3VlYGd9neoVMoVaLJsyMUKVIvc9QoRUDOpLyyM986PqocgSjSjQe55htjCHUmO
VuTepcMkRMeyeLlrxSkKDmZN7vAdVAJHUOLGy4iJlt3ZWsN4FpkF3pfYF/h6y86utLztwdVHftTF
CVZkjMVYJYyxsSK04BMHJcJ6WmKka8MVOfChhJ11BXCrW5ZnMN1k25UWUSIeRFF1RSxm7lSk2pnK
RLGZTrEDLiWsMzZwWkGF/eI7zyLjtj0HViNJ4nQkPTGrARacAvBXhn2fguPUuK0hnN3UpSq1uam9
1wbwthAxhEQxHB/enwvWay7U4CHrLoh3QgGvLP2Yf+7IyMOXBMya0BeBoexbVD+G++ltjOkETLrG
ANdybiVAcJLKKKLeisYxSMMNnMjg5qApJcMNQU692iQuWRbbWlWnCN4w19s6yKprs9HmFyFYjPvu
fUE2QzMBlmoHoxnvEXIykBRDbXXrc0wEPjYq/K4sVHDSQG2LW19VS3mC2plFWBvDs8Q3iku8JK5f
Z4Hmhlq1YM2KHon1lBjxJnj3VT9VwkMPWDALADlXRQ0cD0QR5M6/M4nIU5WibXzlMKKvM1HShOHn
OCy2C5PQVg0Ym8N2MehGe1A0G82xpSxeF0c+4ytExXI6Tk4HG2iOXNjDJOARkw6Ltl879s8T1Mhg
biHbM/Ywgo3BqAIgOKYIi1FI9+/FkQJjvXIg5rIo0NDQyQeoJcQ1ETOqtAsFAUgIhAQpKLVKewSg
MHJcoWnYrkjH4AqClJfjQL5uQHU1lUMCE0OSGD2xROKKnoKLtGogZPdATzxjnXJXNNNuuLmlJY0K
itXrsGGJkOQ0YpULhbniGrV0UOYsgnSjDf04Ck4EjG5S2hwYJqzliwqwVOzCCYTVzRo2iK1q1b1u
3aACSztn6cl6w18u5YbBgGLHJzM2WZS6ARXclY3TprVQx3Vzif7I50w3YtsGPZxDkrmCbj0qdE5G
sQc+28zPlCk8LbZYu7E3GxWwgEd9iRvfvMdzvzeQ9dJoqQqc+KS8N9PtewitvNcYDIyFrx9659Pg
yrqqwnAnIKgeXH7an3CIrxxRSIVAnY1veGIhw4zqjUYyClFWS1Gl6gVpBmHBSFDCt+4ajcYRQrhQ
Dq0Ee8AhLAhvteOKEIkDaycRYHXRinqOUh+li0RkyR/c9KHm2g0VrhctDi2PtE6Ca0vXk4c9KlZU
yVVTiNjOFvEmoVhmSP6PgJ/MRYpqCHfCObWbyRbUGKxc2iliRMDoqwTmFiqMtLFrImPQnpzEuUxd
3VYB8HI+R4DNAMsCf36BrGiMAZXaKhyNe5zVzpLo5SXCi6r9Ewr+peyvJjaZVbB4OKgTpPUsPHft
xl1XZYkHUwDBxJhFiyekVQcE1jEZYzERA+fXr3qyZGgTCTnO43WqPRr0mCVZrHS1Ron0NS1tjzQv
YVb2w74le0lwJ/FJRLku0nGq9zD/i7kinChIDxCEUAA=


--Boundary_(ID_/TX6ZCuBU1PoQgUiWFCfnw)--