From: Date: October 6 2005 2:05pm
Subject: bk commit into 4.1 tree (lars:1.2466) BUG#12618
List-Archive: http://lists.mysql.com/internals/30755
X-Bug: 12618
Message-Id: <200510061205.j96C5XZ3026821@dl145h.mysql.com>

Below is the list of changes that have just been committed into a local
4.1 repository of lthalmann. When lthalmann does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2466 05/10/06 14:05:23 lars@stripped +4 -0
  BUG#12618: Removed fake locking code that caused problems.

  sql/sql_update.cc
    1.144 05/10/06 14:04:45 lars@stripped +0 -3
    Moved call outside of this function.

  sql/sql_parse.cc
    1.469 05/10/06 14:04:45 lars@stripped +18 -23
    Removed fake locking code
    Moved mysql_multi_update_lock call outside of sql_update.cc, since
    we execute that at different times depending on if the statement is 
    executed on slave or master.

  mysql-test/t/rpl_multi_update3.test
    1.5 05/10/06 14:04:45 lars@stripped +59 -0
    New test case

  mysql-test/r/rpl_multi_update3.result
    1.4 05/10/06 14:04:44 lars@stripped +72 -0
    New test case

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	lars
# Host:	dl145h.mysql.com
# Root:	/users/lthalmann/bk/mysql-4.1-bug12618

--- 1.468/sql/sql_parse.cc	2005-09-20 09:50:07 +02:00
+++ 1.469/sql/sql_parse.cc	2005-10-06 14:04:45 +02:00
@@ -1941,8 +1941,6 @@
 {
   int	res= 0;
   LEX	*lex= thd->lex;
-  bool slave_fake_lock= 0;
-  MYSQL_LOCK *fake_prev_lock= 0;
   SELECT_LEX *select_lex= &lex->select_lex;
   TABLE_LIST *tables= (TABLE_LIST*) select_lex->table_list.first;
   SELECT_LEX_UNIT *unit= &lex->unit;
@@ -1973,21 +1971,16 @@
   {
     if (lex->sql_command == SQLCOM_UPDATE_MULTI)
     {
-      DBUG_PRINT("info",("need faked locked tables"));
-      
+      /*
+	For replication this function is called to make sure that each
+	table->updating value is set correctly before calling
+	all_tables_not_ok().
+      */
       if (check_multi_update_lock(thd, tables, &select_lex->item_list,
 				  select_lex))
         goto error;
-
-      /* Fix for replication, the tables are opened and locked,
-         now we pretend that we have performed a LOCK TABLES action */
-	 
-      fake_prev_lock= thd->locked_tables;
-      if (thd->lock)
-        thd->locked_tables= thd->lock;
-      thd->lock= 0;
-      slave_fake_lock= 1;
     }
+
     /*
       Skip if we are in the slave thread, some table rules have been
       given and the table list says the query should not be replicated.
@@ -2852,6 +2845,16 @@
   {
     if ((res= multi_update_precheck(thd, tables)))
       break;
+
+    /*
+      Slave thread has already called this function in
+      check_multi_update_lock().
+    */
+    if (!thd->slave_thread &&
+	(res= mysql_multi_update_lock(thd, tables, &select_lex->item_list,
+				      select_lex)))
+      break;
+
     res= mysql_multi_update(thd,tables,
 			    &select_lex->item_list,
 			    &lex->value_list,
@@ -3764,14 +3767,6 @@
     send_error(thd,thd->killed ? ER_SERVER_SHUTDOWN : 0);
 
 error:
-  if (unlikely(slave_fake_lock))
-  {
-    DBUG_PRINT("info",("undoing faked lock"));
-    thd->lock= thd->locked_tables;
-    thd->locked_tables= fake_prev_lock;
-    if (thd->lock == thd->locked_tables)
-      thd->lock= 0;
-  }
   DBUG_VOID_RETURN;
 }
 
@@ -5345,10 +5340,10 @@
 	goto error;
     table->next= save;
   }
-    
+
   if (mysql_multi_update_lock(thd, tables, fields, select_lex))
     goto error;
-  
+
   res= 0;
   
 error:

--- 1.143/sql/sql_update.cc	2005-09-25 20:22:21 +02:00
+++ 1.144/sql/sql_update.cc	2005-10-06 14:04:45 +02:00
@@ -672,9 +672,6 @@
   multi_update *result;
   DBUG_ENTER("mysql_multi_update");
 
-  if ((res= mysql_multi_update_lock(thd, table_list, fields, select_lex)))
-    DBUG_RETURN(res);
-
   /* Setup timestamp handling */
   for (tl= update_list; tl; tl= tl->next)
   {

--- 1.3/mysql-test/r/rpl_multi_update3.result	2005-06-22 17:11:55 +02:00
+++ 1.4/mysql-test/r/rpl_multi_update3.result	2005-10-06 14:04:44 +02:00
@@ -122,3 +122,75 @@
 i	j	x	y	z
 1	2	23	24	71
 DROP TABLE t1, t2, t3;
+DROP TABLE IF EXISTS t1;
+Warnings:
+Note	1051	Unknown table 't1'
+DROP TABLE IF EXISTS t2;
+Warnings:
+Note	1051	Unknown table 't2'
+CREATE TABLE t1 (
+idp int(11) NOT NULL default '0',
+idpro int(11) default NULL,
+price decimal(19,4) default NULL,
+PRIMARY KEY (idp)
+);
+CREATE TABLE t2 (
+idpro int(11) NOT NULL default '0',
+price decimal(19,4) default NULL,
+nbprice int(11) default NULL,
+PRIMARY KEY (idpro)
+);
+INSERT INTO t1 VALUES 
+(1,1,'3.0000'),
+(2,2,'1.0000'),
+(3,1,'1.0000'),
+(4,1,'4.0000'),
+(5,3,'2.0000'),
+(6,2,'4.0000');
+INSERT INTO t2 VALUES 
+(1,'0.0000',0),
+(2,'0.0000',0),
+(3,'0.0000',0);
+update 
+t2
+join 
+( select    idpro, min(price) as min_price, count(*) as nbr_price
+from      t1 
+where     idpro>0 and price>0 
+group by  idpro
+) as table_price
+on   t2.idpro = table_price.idpro 
+set  t2.price = table_price.min_price, 
+t2.nbprice = table_price.nbr_price;
+select "-- MASTER AFTER JOIN --" as "";
+
+-- MASTER AFTER JOIN --
+select * from t1;
+idp	idpro	price
+1	1	3.0000
+2	2	1.0000
+3	1	1.0000
+4	1	4.0000
+5	3	2.0000
+6	2	4.0000
+select * from t2;
+idpro	price	nbprice
+1	1.0000	3
+2	1.0000	2
+3	2.0000	1
+select "-- SLAVE AFTER JOIN --" as "";
+
+-- SLAVE AFTER JOIN --
+select * from t1;
+idp	idpro	price
+1	1	3.0000
+2	2	1.0000
+3	1	1.0000
+4	1	4.0000
+5	3	2.0000
+6	2	4.0000
+select * from t2;
+idpro	price	nbprice
+1	1.0000	3
+2	1.0000	2
+3	2.0000	1

--- 1.4/mysql-test/t/rpl_multi_update3.test	2005-07-28 02:21:48 +02:00
+++ 1.5/mysql-test/t/rpl_multi_update3.test	2005-10-06 14:04:45 +02:00
@@ -158,4 +158,63 @@
 connection master;
 DROP TABLE t1, t2, t3;
 
+##############################################################################
+#
+# BUG#12618
+#
+# TEST: Replication of a statement containing a join in a multi-update.
+
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+
+CREATE TABLE t1 (
+  idp int(11) NOT NULL default '0',
+  idpro int(11) default NULL,
+  price decimal(19,4) default NULL,
+  PRIMARY KEY (idp)
+);
+
+CREATE TABLE t2 (
+  idpro int(11) NOT NULL default '0',
+  price decimal(19,4) default NULL,
+  nbprice int(11) default NULL,
+  PRIMARY KEY (idpro)
+);
+
+INSERT INTO t1 VALUES 
+  (1,1,'3.0000'),
+  (2,2,'1.0000'),
+  (3,1,'1.0000'),
+  (4,1,'4.0000'),
+  (5,3,'2.0000'),
+  (6,2,'4.0000');
+
+INSERT INTO t2 VALUES 
+  (1,'0.0000',0),
+  (2,'0.0000',0),
+  (3,'0.0000',0);
+
+# This update sets t2 to the minimal prices for each product
+update 
+  t2
+    join 
+  ( select    idpro, min(price) as min_price, count(*) as nbr_price
+    from      t1 
+    where     idpro>0 and price>0 
+    group by  idpro
+  ) as table_price
+on   t2.idpro = table_price.idpro 
+set  t2.price = table_price.min_price, 
+     t2.nbprice = table_price.nbr_price;
+
+select "-- MASTER AFTER JOIN --" as "";
+select * from t1;
+select * from t2;
+
+sync_slave_with_master;
+
+select "-- SLAVE AFTER JOIN --" as "";
+select * from t1;
+select * from t2;
+
 # End of 4.1 tests