bk commit into 4.1 tree (lars:1.2486) BUG#12618

[Lars Thalmann]

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.2486 05/10/10 15:10:14 lars@stripped +4 -0
  BUG#12618: Removing fake locking

  sql/sql_update.cc
    1.144 05/10/10 15:09:44 lars@stripped +0 -3
    Moved call to sql_parse.cc

  sql/sql_parse.cc
    1.470 05/10/10 15:09:44 lars@stripped +20 -82
    Removed fake locking
    Changed so that filtering is done later for multi-update
    Removed redundant code

  mysql-test/t/rpl_multi_update3.test
    1.5 05/10/10 15:09:44 lars@stripped +59 -0
    New test case

  mysql-test/r/rpl_multi_update3.result
    1.4 05/10/10 15:09:43 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-2

--- 1.469/sql/sql_parse.cc	2005-10-08 14:45:49 +02:00
+++ 1.470/sql/sql_parse.cc	2005-10-10 15:09:44 +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;
@@ -1971,35 +1969,21 @@
 #ifdef HAVE_REPLICATION
   if (thd->slave_thread)
   {
-    if (lex->sql_command == SQLCOM_UPDATE_MULTI)
-    {
-      DBUG_PRINT("info",("need faked locked tables"));
-      
-      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.
+      Check if statment should be skipped because of slave filtering
+      rules
 
       Exceptions are:
+      - UPDATE MULTI: For this statement, we want to check the filtering
+        rules later in the code
       - SET: we always execute it (Not that many SET commands exists in
         the binary log anyway -- only 4.1 masters write SET statements,
 	in 5.0 there are no SET statements in the binary log)
       - DROP TEMPORARY TABLE IF EXISTS: we always execute it (otherwise we
         have stale files on slave caused by exclusion of one tmp table).
     */
-    if (!(lex->sql_command == SQLCOM_SET_OPTION) &&
+    if (!(lex->sql_command == SQLCOM_UPDATE_MULTI) &&
+	!(lex->sql_command == SQLCOM_SET_OPTION) &&
 	!(lex->sql_command == SQLCOM_DROP_TABLE &&
           lex->drop_temporary && lex->drop_if_exists) &&
         all_tables_not_ok(thd,tables))
@@ -2852,6 +2836,20 @@
   {
     if ((res= multi_update_precheck(thd, tables)))
       break;
+
+    if ((res= mysql_multi_update_lock(thd, tables, &select_lex->item_list,
+				      select_lex)))
+      break;
+
+    /* Check slave filtering rules */
+    if (thd->slave_thread)
+      if (all_tables_not_ok(thd,tables))
+      {
+	/* we warn the slave SQL thread */
+	my_error(ER_SLAVE_IGNORED_TABLE, MYF(0));
+	break;
+      }
+
     res= mysql_multi_update(thd,tables,
 			    &select_lex->item_list,
 			    &lex->value_list,
@@ -3764,14 +3762,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;
 }
 
@@ -5301,58 +5291,6 @@
     return 1;
   }
   return 0;
-}
-
-/*
-  Setup locking for multi-table updates. Used by the replication slave.
-  Replication slave SQL thread examines (all_tables_not_ok()) the
-  locking state of referenced tables to determine if the query has to
-  be executed or ignored. Since in multi-table update, the 
-  'default' lock is read-only, this lock is corrected early enough by
-  calling this function, before the slave decides to execute/ignore.
-
-  SYNOPSIS
-    check_multi_update_lock()
-    thd		Current thread
-    tables	List of user-supplied tables
-    fields	List of fields requiring update
-
-  RETURN VALUES
-    0	ok
-    1	error
-*/
-static bool check_multi_update_lock(THD *thd, TABLE_LIST *tables, 
-				    List<Item> *fields, SELECT_LEX *select_lex)
-{
-  bool res= 1;
-  TABLE_LIST *table;
-  DBUG_ENTER("check_multi_update_lock");
-  
-  if (check_db_used(thd, tables))
-    goto error;
-
-  /*
-    Ensure that we have UPDATE or SELECT privilege for each table
-    The exact privilege is checked in mysql_multi_update()
-  */
-  for (table= tables ; table ; table= table->next)
-  {
-    TABLE_LIST *save= table->next;
-    table->next= 0;
-    if ((check_access(thd, UPDATE_ACL, table->db, &table->grant.privilege,0,1)
||
-        (grant_option && check_grant(thd, UPDATE_ACL, table,0,1,1))) &&
-	check_one_table_access(thd, SELECT_ACL, table))
-	goto error;
-    table->next= save;
-  }
-    
-  if (mysql_multi_update_lock(thd, tables, fields, select_lex))
-    goto error;
-  
-  res= 0;
-  
-error:
-  DBUG_RETURN(res);
 }
 
 

--- 1.143/sql/sql_update.cc	2005-09-25 20:22:21 +02:00
+++ 1.144/sql/sql_update.cc	2005-10-10 15:09:44 +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-10 15:09:43 +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-10 15:09:44 +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