List:Commits« Previous MessageNext Message »
From:He Zhenxing Date:July 25 2008 8:24am
Subject:bzr commit into mysql-5.1 branch (hezx:2632) Bug#37051
View as plain text  
#At file:///media/sda3/work/mysql/bzrwork/b37051/5.1-rpl-new/

 2632 He Zhenxing	2008-07-25
      BUG#37051 Replication rules not evaluated correctly
      
      The problem of this bug is that we need to get the list of tables
      to be updated for a multi-update statement, which requires to
      open all the tables referenced by the statement and resolve all
      the fields involved in update in order to figure out the list of
      tables for update. However if there are replicate filter rules,
      some tables might not exist on slave and result in a failure
      before we could examine the filter rules.
      
      I think the whole problem can not be solved on slave alone,
      the master must record and send the information of tables
      involved for update to slave, so that the slave do not need to
      open all the tables referencec by the multi-update statement to
      figure out which tables are involved for udpate.
      
      So I added an internal user variable to store the
      value of table map for update on master and written it to binlog
      before the multi-update query log event. And on slave, it will
      try to get the value of this variable and use it to examine
      filter rules without opening any tables on slave.
added:
  mysql-test/suite/rpl/r/rpl_bug37051.result
  mysql-test/suite/rpl/t/rpl_bug37051-slave.opt
  mysql-test/suite/rpl/t/rpl_bug37051.test
modified:
  sql/log.cc
  sql/sql_class.h
  sql/sql_parse.cc
  sql/sql_update.cc

per-file messages:
  sql/log.cc
    Write the user var event of table_map_for_update for multi-upate
  sql/sql_class.h
    add member table_map_for_update to THD
  sql/sql_parse.cc
    check filter rules by using table_map_for_update value
  sql/sql_update.cc
    save the value of table_map_for_update
=== added file 'mysql-test/suite/rpl/r/rpl_bug37051.result'
--- a/mysql-test/suite/rpl/r/rpl_bug37051.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_bug37051.result	2008-07-25 08:23:42 +0000
@@ -0,0 +1,29 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+CREATE TABLE t1 (id int, a int);
+CREATE TABLE t2 (id int, b int);
+CREATE TABLE t3 (id int, c int);
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);
+UPDATE t2 LEFT JOIN t1 ON (t1.id = t2.id) SET a=a+10, b=a+10 where t1.id = 1;
+UPDATE t1 LEFT JOIN t2 ON (t1.id = t2.id) SET a=a+10, b=a+10 where t1.id = 1;
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t1 NATURAL JOIN t2 SET b = a+100 where id = 1;
+UPDATE t1 NATURAL JOIN t2 SET a = a+100 where id = 1;
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t3 LEFT JOIN t1 ON (t1.id = t3.id) SET c = c+1000 where t1.id = 1;
+UPDATE t1 NATURAL JOIN t3 SET c = c+10000 where id = 1;
+UPDATE t3 LEFT JOIN t2 ON (t2.id = t3.id) SET b=b+100000, c=c+100000 where t2.id = 1;
+UPDATE t2 NATURAL JOIN t3 SET b=b+1000000, c=c+1000000 where id = 1;
+SELECT * FROM t1;
+id	a
+1	1
+2	2
+3	3

=== added file 'mysql-test/suite/rpl/t/rpl_bug37051-slave.opt'
--- a/mysql-test/suite/rpl/t/rpl_bug37051-slave.opt	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_bug37051-slave.opt	2008-07-25 08:23:42 +0000
@@ -0,0 +1 @@
+--replicate-do-table=test.t1 --replicate-ignore-table=test.t2 --replicate-ignore-table=test.t3

=== added file 'mysql-test/suite/rpl/t/rpl_bug37051.test'
--- a/mysql-test/suite/rpl/t/rpl_bug37051.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_bug37051.test	2008-07-25 08:23:42 +0000
@@ -0,0 +1,43 @@
+source include/have_binlog_format_statement.inc;
+source include/master-slave.inc;
+
+CREATE TABLE t1 (id int, a int);
+CREATE TABLE t2 (id int, b int);
+CREATE TABLE t3 (id int, c int);
+
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);
+
+# This will be ignored by slave
+UPDATE t2 LEFT JOIN t1 ON (t1.id = t2.id) SET a=a+10, b=a+10 where t1.id = 1;
+
+# This will cause error on slave
+UPDATE t1 LEFT JOIN t2 ON (t1.id = t2.id) SET a=a+10, b=a+10 where t1.id = 1;
+connection slave;
+source include/wait_for_slave_sql_to_stop.inc;
+set global sql_slave_skip_counter=1;
+start slave;
+
+connection master;
+
+# This will be ignored by slave
+UPDATE t1 NATURAL JOIN t2 SET b = a+100 where id = 1;
+
+# This will cause error on slave
+UPDATE t1 NATURAL JOIN t2 SET a = a+100 where id = 1;
+connection slave;
+source include/wait_for_slave_sql_to_stop.inc;
+set global sql_slave_skip_counter=1;
+start slave;
+
+connection master;
+
+# These will be ignored by slave
+UPDATE t3 LEFT JOIN t1 ON (t1.id = t3.id) SET c = c+1000 where t1.id = 1;
+UPDATE t1 NATURAL JOIN t3 SET c = c+10000 where id = 1;
+UPDATE t3 LEFT JOIN t2 ON (t2.id = t3.id) SET b=b+100000, c=c+100000 where t2.id = 1;
+UPDATE t2 NATURAL JOIN t3 SET b=b+1000000, c=c+1000000 where id = 1;
+
+sync_slave_with_master;
+SELECT * FROM t1;

=== modified file 'sql/log.cc'
--- a/sql/log.cc	2008-05-21 12:44:30 +0000
+++ b/sql/log.cc	2008-07-25 08:23:42 +0000
@@ -3965,6 +3965,17 @@ bool MYSQL_BIN_LOG::write(Log_event *eve
               goto err;
           }
         }
+        if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI)
+        {
+          User_var_log_event e(thd,
+                               (char *)STRING_WITH_LEN("__TABLE_MAP_FOR_UPDATE__"),
+                               (char *)&thd->table_map_for_update,
+                               sizeof(ulonglong),
+                               INT_RESULT,
+                               my_charset_bin.number);
+          if (e.write(file))
+            goto err;
+        }
       }
     }
 

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2008-05-20 07:38:17 +0000
+++ b/sql/sql_class.h	2008-07-25 08:23:42 +0000
@@ -1196,6 +1196,7 @@ class THD :public Statement,
            public Open_tables_state
 {
 public:
+  table_map table_map_for_update;
   /* Used to execute base64 coded binlog events in MySQL server */
   Relay_log_info* rli_fake;
 

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2008-05-20 16:36:26 +0000
+++ b/sql/sql_parse.cc	2008-07-25 08:23:42 +0000
@@ -1953,6 +1953,40 @@ mysql_execute_command(THD *thd)
       all_tables->updating= 1;
     }
     
+    if (lex->sql_command == SQLCOM_UPDATE_MULTI)
+    {
+      my_bool null_value;
+      table_map table_map_for_update= 0;
+      LEX_STRING name= {(char *)STRING_WITH_LEN("__TABLE_MAP_FOR_UPDATE__")};
+      user_var_entry *var_entry= (user_var_entry *)hash_search(&thd->user_vars,
+                                                               (const uchar *)name.str,
+                                                               name.length);
+      if (var_entry)
+        table_map_for_update= (table_map)var_entry->val_int(&null_value);
+
+      uint nr= 0;
+      TABLE_LIST *table;
+      for (table=all_tables; table; table=table->next_global)
+      {
+        if (table_map_for_update & (1 << nr))
+          table->updating= TRUE;
+        else
+          table->updating= FALSE;
+      }
+
+      if (all_tables_not_ok(thd, all_tables))
+      {
+        /* we warn the slave SQL thread */
+        my_message(ER_SLAVE_IGNORED_TABLE, ER(ER_SLAVE_IGNORED_TABLE), MYF(0));
+        if (thd->one_shot_set)
+          reset_one_shot_variables(thd);
+        DBUG_RETURN(0);
+      }
+      
+      for (table=all_tables; table; table=table->next_global)
+        table->updating= TRUE;
+    }
+    
     /*
       Check if statment should be skipped because of slave filtering
       rules

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2008-05-20 07:38:17 +0000
+++ b/sql/sql_update.cc	2008-07-25 08:23:42 +0000
@@ -1000,7 +1000,7 @@ reopen_tables:
     DBUG_RETURN(TRUE);
   }
 
-  tables_for_update= get_table_map(fields);
+  thd->table_map_for_update= tables_for_update= get_table_map(fields);
 
   /*
     Setup timestamp handling and locking mode

Thread
bzr commit into mysql-5.1 branch (hezx:2632) Bug#37051He Zhenxing25 Jul
  • Re: bzr commit into mysql-5.1 branch (hezx:2632) Bug#37051Sven Sandberg28 Jul
    • Re: bzr commit into mysql-5.1 branch (hezx:2632) Bug#37051He Zhenxing29 Jul
    • Re: bzr commit into mysql-5.1 branch (hezx:2632) Bug#37051Ingo Strüwing29 Jul
      • Re: bzr commit into mysql-5.1 branch (hezx:2632) Bug#37051Sven Sandberg29 Jul
        • Re: bzr commit into mysql-5.1 branch (hezx:2632) Bug#37051He Zhenxing30 Jul
      • Re: bzr commit into mysql-5.1 branch (hezx:2632) Bug#37051He Zhenxing30 Jul