List:Commits« Previous MessageNext Message »
From:He Zhenxing Date:August 26 2008 12:30pm
Subject:bzr commit into mysql-6.0 branch (hezx:2676) Bug#37051
View as plain text  
#At file:///media/sda3/work/mysql/bzrwork/merge/6.0-rpl-merge/

 2676 He Zhenxing	2008-08-26
      Merge 5.1-rpl-merge -> 6.0-rpl-merge: cherry picking patch of BUG#37051 
added:
  mysql-test/include/wait_for_slave_sql_error_and_skip.inc
  mysql-test/suite/rpl/r/rpl_filter_tables_not_exist.result
  mysql-test/suite/rpl/t/rpl_filter_tables_not_exist-slave.opt
  mysql-test/suite/rpl/t/rpl_filter_tables_not_exist.test
modified:
  mysql-test/r/multi_update.result
  sql/log_event.cc
  sql/log_event.h
  sql/sql_class.cc
  sql/sql_class.h
  sql/sql_parse.cc
  sql/sql_update.cc

=== added file 'mysql-test/include/wait_for_slave_sql_error_and_skip.inc'
--- a/mysql-test/include/wait_for_slave_sql_error_and_skip.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/wait_for_slave_sql_error_and_skip.inc	2008-08-26 12:29:33 +0000
@@ -0,0 +1,24 @@
+# ==== Purpose ====
+#
+# Wait for slave SQL error, skip the erroneous statement and restart
+# slave
+#
+# ==== Usage ====
+#
+# let show_sql_error=0|1;
+# source include/wait_for_slave_sql_error_and_skip.inc;
+
+echo --source include/wait_for_slave_sql_error_and_skip.inc;
+connection slave;
+source include/wait_for_slave_sql_error.inc;
+if ($show_sql_error)
+{
+  let $error= query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1);
+  echo Last_SQL_Error = $error;
+}
+
+# skip the erroneous statement
+set global sql_slave_skip_counter=1;
+start slave;
+source include/wait_for_slave_to_start.inc;
+connection master;

=== modified file 'mysql-test/r/multi_update.result'
--- a/mysql-test/r/multi_update.result	2008-04-01 15:13:57 +0000
+++ b/mysql-test/r/multi_update.result	2008-08-26 12:29:33 +0000
@@ -627,7 +627,7 @@ a	b
 4	4
 show master status /* there must be the UPDATE query event */;
 File	Position	Binlog_Do_DB	Binlog_Ignore_DB
-master-bin.000001	198		
+master-bin.000001	207		
 delete from t1;
 delete from t2;
 insert into t1 values (1,2),(3,4),(4,4);
@@ -637,7 +637,7 @@ UPDATE t2,t1  SET t2.a=t2.b where t2.a=t
 ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
 show master status /* there must be the UPDATE query event */;
 File	Position	Binlog_Do_DB	Binlog_Ignore_DB
-master-bin.000001	213		
+master-bin.000001	222		
 drop table t1, t2;
 set @@session.binlog_format= @sav_binlog_format;
 drop table if exists t1, t2, t3;

=== added file 'mysql-test/suite/rpl/r/rpl_filter_tables_not_exist.result'
--- a/mysql-test/suite/rpl/r/rpl_filter_tables_not_exist.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_filter_tables_not_exist.result	2008-08-26 12:29:33 +0000
@@ -0,0 +1,151 @@
+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);
+CREATE TABLE t4 (id int, d int);
+CREATE TABLE t5 (id int, e int);
+CREATE TABLE t6 (id int, f int);
+CREATE TABLE t7 (id int, g int);
+CREATE TABLE t8 (id int, h int);
+CREATE TABLE t9 (id int, i 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);
+INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t5 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t6 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t7 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t8 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t9 VALUES (1, 1), (2, 2), (3, 3);
+[on slave]
+SHOW TABLES LIKE 't%';
+Tables_in_test (t%)
+t1
+t2
+t3
+[on master]
+UPDATE t7 LEFT JOIN t4 ON (t4.id=t7.id) SET d=0, g=0 where t7.id=1;
+UPDATE t7 LEFT JOIN (t4, t5, t6) ON (t7.id=t4.id and t7.id=t5.id and t7.id=t6.id) SET d=0, e=0, f=0, g=0 where t7.id=1;
+UPDATE t4 LEFT JOIN (t7, t8, t9) ON (t4.id=t7.id and t4.id=t8.id and t4.id=t9.id) SET d=0, g=0, h=0, i=0 where t4.id=1;
+UPDATE t7 LEFT JOIN (t8, t9) ON (t7.id=t8.id and t7.id=t9.id) SET g=0, h=0, i=0 where t7.id=1;
+UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET d=0 where t1.id=1;
+UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET g=0 where t1.id=1;
+UPDATE t1 LEFT JOIN (t4, t5, t6) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t6.id) SET d=0, e=0, f=0 where t1.id=1;
+UPDATE t1 LEFT JOIN (t4, t5, t8) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t8.id) SET d=0, e=0, h=0 where t1.id=1;
+UPDATE t1 LEFT JOIN (t7, t8, t5) ON (t1.id=t7.id and t1.id=t8.id and t1.id=t5.id) SET g=0, h=0, e=0 where t1.id=1;
+UPDATE t1 LEFT JOIN (t2, t3, t5) ON (t1.id=t2.id and t1.id=t3.id and t1.id=t5.id) SET e=0 where t1.id=1;
+UPDATE t4 LEFT JOIN t1 ON (t1.id=t4.id) SET a=0, d=0 where t4.id=1;
+UPDATE t4 LEFT JOIN (t1, t7) ON (t4.id=t1.id and t7.id=t4.id) SET a = 0, d=0, g=0 where t4.id=1;
+UPDATE t4 LEFT JOIN (t1, t2, t3) ON (t1.id=t4.id and t2.id=t4.id and t3.id=t4.id) SET a=0, b=0, c=0, d=0 where t4.id=1;
+UPDATE t4 LEFT JOIN (t1, t2, t5) ON (t1.id=t4.id and t2.id=t4.id and t5.id=t4.id) SET a=0, b=0, e=0, d=0 where t4.id=1;
+UPDATE t4 LEFT JOIN (t1, t6, t7) ON (t4.id=t1.id and t4.id=t6.id and t4.id=t7.id) SET a=0, d=0, f=0, g=0 where t4.id=1;
+UPDATE t7 LEFT JOIN (t4, t1, t2) ON (t7.id=t4.id and t7.id=t1.id and t7.id=t2.id) SET a=0, b=0, d=0, g=0 where t7.id=1;
+UPDATE t7 LEFT JOIN (t8, t4, t1) ON (t7.id=t8.id and t7.id=t4.id and t7.id=t1.id) SET a=0, d=0, g=0, h=0 where t7.id=1;
+UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0 where t1.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0 where t1.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t1 LEFT JOIN (t4, t7) ON (t1.id=t4.id and t1.id=t7.id) SET a=0 where t1.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t4, t7) ON (t1.id=t4.id and t1.id=t7.id) SET a=0 where t1.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t1 LEFT JOIN (t2, t4, t7) ON (t1.id=t2.id and t1.id=t4.id and t1.id=t7.id) SET a=0, b=0 where t1.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t2, t4, t7) ON (t1.id=t2.id and t1.id=t4.id and t1.id=t7.id) SET a=0, b=0 where t1.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t1 LEFT JOIN (t2, t3, t7) ON (t1.id=t2.id and t1.id=t3.id and t1.id=t7.id) SET a=0, b=0, c=0 where t1.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t2, t3, t7) ON (t1.id=t2.id and t1.id=t3.id and t1.id=t7.id) SET a=0, b=0, c=0 where t1.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET a=0, g=0 where t1.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET a=0, g=0 where t1.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t7 LEFT JOIN t1 ON (t1.id=t7.id) SET a=0, g=0 where t7.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t7 LEFT JOIN t1 ON (t1.id=t7.id) SET a=0, g=0 where t7.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t1 LEFT JOIN (t4, t5, t7) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t7.id) SET a=0, g=0 where t1.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t4, t5, t7) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t7.id) SET a=0, g=0 where t1.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t1 LEFT JOIN (t4, t7, t8) ON (t1.id=t4.id and t1.id=t7.id and t1.id=t8.id) SET a=0, g=0 where t1.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t4, t7, t8) ON (t1.id=t4.id and t1.id=t7.id and t1.id=t8.id) SET a=0, g=0 where t1.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t1 LEFT JOIN (t7, t8, t9) ON (t1.id=t7.id and t1.id=t8.id and t1.id=t9.id) SET a=0, g=0, h=0, i=0 where t1.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t7, t8, t9) ON (t1.id=t7.id and t1.id=t8.id and t1.id=t9.id) SET a=0, g=0, h=0, i=0 where t1.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t7 LEFT JOIN (t1, t2, t3) ON (t7.id=t1.id and t7.id=t2.id and t7.id=t3.id) SET g=0, a=0, b=0, c=0 where t7.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t7 LEFT JOIN (t1, t2, t3) ON (t7.id=t1.id and t7.id=t2.id and t7.id=t3.id) SET g=0, a=0, b=0, c=0 where t7.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t7 LEFT JOIN (t4, t5, t3) ON (t7.id=t4.id and t7.id=t5.id and t7.id=t3.id) SET g=0, c=0 where t7.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t7 LEFT JOIN (t4, t5, t3) ON (t7.id=t4.id and t7.id=t5.id and t7.id=t3.id) SET g=0, c=0 where t7.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t7 LEFT JOIN (t8, t9, t3) ON (t7.id=t8.id and t7.id=t9.id and t7.id=t3.id) SET g=0, h=0, i=0, c=0 where t7.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t7 LEFT JOIN (t8, t9, t3) ON (t7.id=t8.id and t7.id=t9.id and t7.id=t3.id) SET g=0, h=0, i=0, c=0 where t7.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0, d=0 where t1.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0, d=0 where t1.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t1 LEFT JOIN (t4, t5, t6) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t6.id) SET a=0, d=0, e=0, f=0 where t1.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t4, t5, t6) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t6.id) SET a=0, d=0, e=0, f=0 where t1.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t4 LEFT JOIN (t1, t5, t6) ON (t4.id=t1.id and t4.id=t5.id and t4.id=t6.id) SET a=0, e=0, f=0 where t4.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t4 LEFT JOIN (t1, t5, t6) ON (t4.id=t1.id and t4.id=t5.id and t4.id=t6.id) SET a=0, e=0, f=0 where t4.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+UPDATE t7 LEFT JOIN (t1, t4, t2) ON (t7.id=t1.id and t7.id=t4.id and t7.id=t2.id) SET a=0, b=0, d=0, g=0 where t7.id=1;
+--source include/wait_for_slave_sql_error_and_skip.inc
+Last_SQL_Error = Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t7 LEFT JOIN (t1, t4, t2) ON (t7.id=t1.id and t7.id=t4.id and t7.id=t2.id) SET a=0, b=0, d=0, g=0 where t7.id=1'
+set global sql_slave_skip_counter=1;
+start slave;
+[on slave]
+show tables like 't%';
+Tables_in_test (t%)
+t1
+t2
+t3
+SELECT * FROM t1;
+id	a
+1	1
+2	2
+3	3
+SELECT * FROM t2;
+id	b
+1	1
+2	2
+3	3
+SELECT * FROM t3;
+id	c
+1	1
+2	2
+3	3
+[on master]
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;

=== added file 'mysql-test/suite/rpl/t/rpl_filter_tables_not_exist-slave.opt'
--- a/mysql-test/suite/rpl/t/rpl_filter_tables_not_exist-slave.opt	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_filter_tables_not_exist-slave.opt	2008-08-26 12:29:33 +0000
@@ -0,0 +1 @@
+--replicate-do-table=test.t1 --replicate-do-table=test.t2 --replicate-do-table=test.t3 --replicate-ignore-table=test.t4 --replicate-ignore-table=test.t5 --replicate-ignore-table=test.t6

=== added file 'mysql-test/suite/rpl/t/rpl_filter_tables_not_exist.test'
--- a/mysql-test/suite/rpl/t/rpl_filter_tables_not_exist.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_filter_tables_not_exist.test	2008-08-26 12:29:33 +0000
@@ -0,0 +1,205 @@
+# Test evaluation of replication table filter rules
+#
+# ==== Purpose ====
+#
+# Test if replication table filter rules are properly evaluated when
+# some of the tables referenced by the multiple-table update do not
+# exist on slave.
+#
+# ==== Method ====
+#
+# Master creates tables t1, t2, t3, t4, t5, t6, t7, t8, t9 and the
+# slave is started with the following replication table filter rules:
+#
+# --replicate-do-table=t1
+# --replicate-do-table=t2
+# --replicate-do-table=t3
+#
+# and
+#
+# --replicate-ignore-table=t4
+# --replicate-ignore-table=t5
+# --replicate-ignore-table=t6
+#
+# So the slave only replicate changes to tables t1, t2 and t3 and only
+# these tables exist on slave.
+#
+# From now on, tables t1, t2, and t3 are referenced as do tables,
+# tables t4, t5, t6 are referenced as ignore tables, and tables t7,
+# t8, t9 are referenced as other tables.
+#
+# All multi-table update tests reference tables that are not do
+# tables, which do not exist on slave. And the following situations
+# of multi-table update will be tested:
+#
+# 1. Do tables are not referenced at all
+# 2. Do tables are not referenced for update
+# 3. Ignore tables are referenced for update before do tables
+# 4. Only do tables are referenced for update
+# 5. Do tables and other tables are referenced for update
+# 6. Do tables are referenced for update before ignore tables
+#
+# For 1, 2 and 3, the statement should be ignored by slave, for 4, 5
+# and 6 the statement should be accepted by slave and cause an error
+# because of non-exist tables.
+#
+# ==== Related bugs ====
+#
+# BUG#37051 Replication rules not evaluated correctly
+
+
+source include/have_binlog_format_statement.inc;
+source include/master-slave.inc;
+
+# These tables are mentioned in do-table rules
+CREATE TABLE t1 (id int, a int);
+CREATE TABLE t2 (id int, b int);
+CREATE TABLE t3 (id int, c int);
+
+# These tables are mentioned in ignore-table rules
+CREATE TABLE t4 (id int, d int);
+CREATE TABLE t5 (id int, e int);
+CREATE TABLE t6 (id int, f int);
+
+# These tables are not mentioned in do-table or ignore-table rules
+CREATE TABLE t7 (id int, g int);
+CREATE TABLE t8 (id int, h int);
+CREATE TABLE t9 (id int, i 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);
+
+INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t5 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t6 VALUES (1, 1), (2, 2), (3, 3);
+
+INSERT INTO t7 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t8 VALUES (1, 1), (2, 2), (3, 3);
+INSERT INTO t9 VALUES (1, 1), (2, 2), (3, 3);
+
+# Only t1, t2, t3 should be replicated to slave
+sync_slave_with_master;
+echo [on slave];
+SHOW TABLES LIKE 't%';
+
+connection master;
+echo [on master];
+
+#
+# Do tables are not referenced, these statements should be ignored by
+# slave.
+#
+UPDATE t7 LEFT JOIN t4 ON (t4.id=t7.id) SET d=0, g=0 where t7.id=1;
+UPDATE t7 LEFT JOIN (t4, t5, t6) ON (t7.id=t4.id and t7.id=t5.id and t7.id=t6.id) SET d=0, e=0, f=0, g=0 where t7.id=1;
+UPDATE t4 LEFT JOIN (t7, t8, t9) ON (t4.id=t7.id and t4.id=t8.id and t4.id=t9.id) SET d=0, g=0, h=0, i=0 where t4.id=1;
+UPDATE t7 LEFT JOIN (t8, t9) ON (t7.id=t8.id and t7.id=t9.id) SET g=0, h=0, i=0 where t7.id=1;
+
+#
+# Do tables are not referenced for update, these statements should be
+# ignored by slave.
+#
+UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET d=0 where t1.id=1;
+UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET g=0 where t1.id=1;
+UPDATE t1 LEFT JOIN (t4, t5, t6) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t6.id) SET d=0, e=0, f=0 where t1.id=1;
+UPDATE t1 LEFT JOIN (t4, t5, t8) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t8.id) SET d=0, e=0, h=0 where t1.id=1;
+UPDATE t1 LEFT JOIN (t7, t8, t5) ON (t1.id=t7.id and t1.id=t8.id and t1.id=t5.id) SET g=0, h=0, e=0 where t1.id=1;
+UPDATE t1 LEFT JOIN (t2, t3, t5) ON (t1.id=t2.id and t1.id=t3.id and t1.id=t5.id) SET e=0 where t1.id=1;
+
+#
+# Ignore tables are referenced for update before do tables, these
+# statements should be ignore by slave.
+#
+UPDATE t4 LEFT JOIN t1 ON (t1.id=t4.id) SET a=0, d=0 where t4.id=1;
+UPDATE t4 LEFT JOIN (t1, t7) ON (t4.id=t1.id and t7.id=t4.id) SET a = 0, d=0, g=0 where t4.id=1;
+UPDATE t4 LEFT JOIN (t1, t2, t3) ON (t1.id=t4.id and t2.id=t4.id and t3.id=t4.id) SET a=0, b=0, c=0, d=0 where t4.id=1;
+UPDATE t4 LEFT JOIN (t1, t2, t5) ON (t1.id=t4.id and t2.id=t4.id and t5.id=t4.id) SET a=0, b=0, e=0, d=0 where t4.id=1;
+UPDATE t4 LEFT JOIN (t1, t6, t7) ON (t4.id=t1.id and t4.id=t6.id and t4.id=t7.id) SET a=0, d=0, f=0, g=0 where t4.id=1;
+UPDATE t7 LEFT JOIN (t4, t1, t2) ON (t7.id=t4.id and t7.id=t1.id and t7.id=t2.id) SET a=0, b=0, d=0, g=0 where t7.id=1;
+UPDATE t7 LEFT JOIN (t8, t4, t1) ON (t7.id=t8.id and t7.id=t4.id and t7.id=t1.id) SET a=0, d=0, g=0, h=0 where t7.id=1;
+
+# Sync slave to make sure all above statements are correctly ignored,
+# if any of the above statement are not ignored, it would cause error
+# and stop slave sql thread.
+sync_slave_with_master;
+connection master;
+
+# Parameter for include/wait_for_slave_sql_error_and_skip.inc, ask it
+# to show SQL error message
+let show_sql_error=1;
+
+#
+# Only do tables are referenced for update, these statements should
+# cause error on slave
+#
+UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0 where t1.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t1 LEFT JOIN (t4, t7) ON (t1.id=t4.id and t1.id=t7.id) SET a=0 where t1.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t1 LEFT JOIN (t2, t4, t7) ON (t1.id=t2.id and t1.id=t4.id and t1.id=t7.id) SET a=0, b=0 where t1.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t1 LEFT JOIN (t2, t3, t7) ON (t1.id=t2.id and t1.id=t3.id and t1.id=t7.id) SET a=0, b=0, c=0 where t1.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+#
+# Do tables and other tables are referenced for update, these
+# statements should cause error on slave
+#
+UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET a=0, g=0 where t1.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t7 LEFT JOIN t1 ON (t1.id=t7.id) SET a=0, g=0 where t7.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t1 LEFT JOIN (t4, t5, t7) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t7.id) SET a=0, g=0 where t1.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t1 LEFT JOIN (t4, t7, t8) ON (t1.id=t4.id and t1.id=t7.id and t1.id=t8.id) SET a=0, g=0 where t1.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t1 LEFT JOIN (t7, t8, t9) ON (t1.id=t7.id and t1.id=t8.id and t1.id=t9.id) SET a=0, g=0, h=0, i=0 where t1.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t7 LEFT JOIN (t1, t2, t3) ON (t7.id=t1.id and t7.id=t2.id and t7.id=t3.id) SET g=0, a=0, b=0, c=0 where t7.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t7 LEFT JOIN (t4, t5, t3) ON (t7.id=t4.id and t7.id=t5.id and t7.id=t3.id) SET g=0, c=0 where t7.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t7 LEFT JOIN (t8, t9, t3) ON (t7.id=t8.id and t7.id=t9.id and t7.id=t3.id) SET g=0, h=0, i=0, c=0 where t7.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+#
+# Do tables are referenced for update before ignore tables
+#
+UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0, d=0 where t1.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t1 LEFT JOIN (t4, t5, t6) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t6.id) SET a=0, d=0, e=0, f=0 where t1.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t4 LEFT JOIN (t1, t5, t6) ON (t4.id=t1.id and t4.id=t5.id and t4.id=t6.id) SET a=0, e=0, f=0 where t4.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+UPDATE t7 LEFT JOIN (t1, t4, t2) ON (t7.id=t1.id and t7.id=t4.id and t7.id=t2.id) SET a=0, b=0, d=0, g=0 where t7.id=1;
+source include/wait_for_slave_sql_error_and_skip.inc;
+
+sync_slave_with_master;
+echo [on slave];
+
+# We should only have tables t1, t2, t3 on slave
+show tables like 't%';
+
+# The rows in these tables should remain untouched
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+
+# Clean up
+connection master;
+echo [on master];
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+source include/master-slave-end.inc;

=== modified file 'sql/log_event.cc'
--- a/sql/log_event.cc	2008-07-24 10:00:56 +0000
+++ b/sql/log_event.cc	2008-08-26 12:29:33 +0000
@@ -1474,6 +1474,11 @@ void Query_log_event::pack_info(Protocol
 static void write_str_with_code_and_len(char **dst, const char *src,
                                         int len, uint code)
 {
+  /*
+    only 1 byte to store the length of catalog, so it should not
+    surpass 255
+  */
+  DBUG_ASSERT(len <= 255);
   DBUG_ASSERT(src);
   *((*dst)++)= code;
   *((*dst)++)= (uchar) len;
@@ -1493,21 +1498,8 @@ static void write_str_with_code_and_len(
 
 bool Query_log_event::write(IO_CACHE* file)
 {
-  /**
-    @todo if catalog can be of length FN_REFLEN==512, then we are not
-    replicating it correctly, since the length is stored in a byte
-    /sven
-  */
-  uchar buf[QUERY_HEADER_LEN+
-            1+4+           // code of flags2 and flags2
-            1+8+           // code of sql_mode and sql_mode
-            1+1+FN_REFLEN+ // code of catalog and catalog length and catalog
-            1+4+           // code of autoinc and the 2 autoinc variables
-            1+6+           // code of charset and charset
-            1+1+MAX_TIME_ZONE_NAME_LENGTH+ // code of tz and tz length and tz name
-            1+2+           // code of lc_time_names and lc_time_names_number
-            1+2            // code of charset_database and charset_database_number
-            ], *start, *start_of_status;
+  uchar buf[QUERY_HEADER_LEN + MAX_SIZE_LOG_EVENT_STATUS];
+  uchar *start, *start_of_status;
   ulong event_length;
 
   if (!query)
@@ -1613,10 +1605,8 @@ bool Query_log_event::write(IO_CACHE* fi
   {
     /* In the TZ sys table, column Name is of length 64 so this should be ok */
     DBUG_ASSERT(time_zone_len <= MAX_TIME_ZONE_NAME_LENGTH);
-    *start++= Q_TIME_ZONE_CODE;
-    *start++= time_zone_len;
-    memcpy(start, time_zone_str, time_zone_len);
-    start+= time_zone_len;
+    write_str_with_code_and_len((char **)(&start),
+                                time_zone_str, time_zone_len, Q_TIME_ZONE_CODE);
   }
   if (lc_time_names_number)
   {
@@ -1632,7 +1622,17 @@ bool Query_log_event::write(IO_CACHE* fi
     int2store(start, charset_database_number);
     start+= 2;
   }
+  if (table_map_for_update)
+  {
+    *start++= Q_TABLE_MAP_FOR_UPDATE_CODE;
+    int8store(start, table_map_for_update);
+    start+= 8;
+  }
   /*
+    NOTE: When adding new status vars, please don't forget to update
+    the MAX_SIZE_LOG_EVENT_STATUS in log_event.h and update function
+    code_name in this file.
+   
     Here there could be code like
     if (command-line-option-which-says-"log_this_variable" && inited)
     {
@@ -1709,7 +1709,8 @@ Query_log_event::Query_log_event(THD* th
    auto_increment_increment(thd_arg->variables.auto_increment_increment),
    auto_increment_offset(thd_arg->variables.auto_increment_offset),
    lc_time_names_number(thd_arg->variables.lc_time_names->number),
-   charset_database_number(0)
+   charset_database_number(0),
+   table_map_for_update((ulonglong)thd_arg->table_map_for_update)
 {
   time_t end_time;
 
@@ -1838,6 +1839,7 @@ code_name(int code)
   case Q_CATALOG_NZ_CODE: return "Q_CATALOG_NZ_CODE";
   case Q_LC_TIME_NAMES_CODE: return "Q_LC_TIME_NAMES_CODE";
   case Q_CHARSET_DATABASE_CODE: return "Q_CHARSET_DATABASE_CODE";
+  case Q_TABLE_MAP_FOR_UPDATE_CODE: return "Q_TABLE_MAP_FOR_UPDATE_CODE";
   }
   sprintf(buf, "CODE#%d", code);
   return buf;
@@ -1874,7 +1876,8 @@ Query_log_event::Query_log_event(const c
    db(NullS), catalog_len(0), status_vars_len(0),
    flags2_inited(0), sql_mode_inited(0), charset_inited(0),
    auto_increment_increment(1), auto_increment_offset(1),
-   time_zone_len(0), lc_time_names_number(0), charset_database_number(0)
+   time_zone_len(0), lc_time_names_number(0), charset_database_number(0),
+   table_map_for_update(0)
 {
   ulong data_len;
   uint32 tmp;
@@ -2016,6 +2019,11 @@ Query_log_event::Query_log_event(const c
       charset_database_number= uint2korr(pos);
       pos+= 2;
       break;
+    case Q_TABLE_MAP_FOR_UPDATE_CODE:
+      CHECK_SPACE(pos, end, 8);
+      table_map_for_update= uint8korr(pos);
+      pos+= 8;
+      break;
     default:
       /* That's why you must write status vars in growing order of code */
       DBUG_PRINT("info",("Query_log_event has unknown status vars (first has\
@@ -2423,6 +2431,8 @@ int Query_log_event::do_apply_event(Rela
       else
         thd->variables.collation_database= thd->db_charset;
       
+      thd->table_map_for_update= (table_map)table_map_for_update;
+      
       /* Execute the query (note that we bypass dispatch_command()) */
       const char* found_semicolon= NULL;
       mysql_parse(thd, thd->query, thd->query_length, &found_semicolon);

=== modified file 'sql/log_event.h'
--- a/sql/log_event.h	2008-04-01 15:13:57 +0000
+++ b/sql/log_event.h	2008-08-26 12:29:33 +0000
@@ -238,12 +238,15 @@ struct sql_ex_info
   packet (i.e. a query) sent from client to master;
   First, an auxiliary log_event status vars estimation:
 */
-#define MAX_SIZE_LOG_EVENT_STATUS (4 /* flags2 */   + \
-                                   8 /* sql mode */ + \
-                                   1 + 1 + 255 /* catalog */ + \
-                                   4 /* autoinc */ + \
-                                   6 /* charset */ + \
-                                   MAX_TIME_ZONE_NAME_LENGTH)
+#define MAX_SIZE_LOG_EVENT_STATUS (1 + 4          /* type, flags2 */   + \
+                                   1 + 8          /* type, sql_mode */ + \
+                                   1 + 1 + 255    /* type, length, catalog */ + \
+                                   1 + 4          /* type, auto_increment */ + \
+                                   1 + 6          /* type, charset */ + \
+                                   1 + 1 + 255    /* type, length, time_zone */ + \
+                                   1 + 2          /* type, lc_time_names_number */ + \
+                                   1 + 2          /* type, charset_database_number */ + \
+                                   1 + 8          /* type, table_map_for_update */)
 #define MAX_LOG_EVENT_HEADER   ( /* in order of Query_log_event::write */ \
   LOG_EVENT_HEADER_LEN + /* write_header */ \
   QUERY_HEADER_LEN     + /* write_data */   \
@@ -307,6 +310,8 @@ struct sql_ex_info
 #define Q_LC_TIME_NAMES_CODE    7
 
 #define Q_CHARSET_DATABASE_CODE 8
+
+#define Q_TABLE_MAP_FOR_UPDATE_CODE 9
 /* Intvar event post-header */
 
 #define I_TYPE_OFFSET        0
@@ -1476,6 +1481,22 @@ protected:
     This field is written if it is not 0.
     </td>
   </tr>
+  <tr>
+    <td>table_map_for_update</td>
+    <td>Q_TABLE_MAP_FOR_UPDATE_CODE == 9</td>
+    <td>8 byte integer</td>
+
+    <td>The value of the table map that is to be updated by the
+    multi-table update query statement. Every bit of this variable
+    represents a table, and is set to 1 if the corresponding table is
+    to be updated by this statement.
+
+    The value of this variable is set when executing a multi-table update
+    statement and used by slave to apply filter rules without opening
+    all the tables on slave. This is required because some tables may
+    not exist on slave because of the filter rules.
+    </td>
+  </tr>
   </table>
 
   @subsection Query_log_event_notes_on_previous_versions Notes on Previous Versions
@@ -1492,6 +1513,9 @@ protected:
 
   * See Q_CHARSET_DATABASE_CODE in the table above.
 
+  * When adding new status vars, please don't forget to update the
+  MAX_SIZE_LOG_EVENT_STATUS, and update function code_name
+
 */
 class Query_log_event: public Log_event
 {
@@ -1569,6 +1593,11 @@ public:
   const char *time_zone_str;
   uint lc_time_names_number; /* 0 means en_US */
   uint charset_database_number;
+  /*
+    map for tables that will be updated for a multi-table update query
+    statement, for other query statements, this will be zero.
+  */
+  ulonglong table_map_for_update;
 
 #ifndef MYSQL_CLIENT
 

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2008-07-25 17:21:55 +0000
+++ b/sql/sql_class.cc	2008-08-26 12:29:33 +0000
@@ -513,6 +513,7 @@ THD::THD()
    lock_id(&main_lock_id),
    user_time(0), in_sub_stmt(0),
    binlog_table_maps(0), binlog_flags(0UL),
+   table_map_for_update(0),
    arg_of_last_insert_id_function(FALSE),
    first_successful_insert_id_in_prev_stmt(0),
    first_successful_insert_id_in_prev_stmt_for_binlog(0),
@@ -1156,6 +1157,8 @@ void THD::cleanup_after_query()
   free_items();
   /* Reset where. */
   where= THD::DEFAULT_WHERE;
+  /* reset table map for multi-table update */
+  table_map_for_update= 0;
 }
 
 

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2008-07-26 16:38:20 +0000
+++ b/sql/sql_class.h	2008-08-26 12:29:33 +0000
@@ -442,7 +442,6 @@ struct system_variables
   DATE_TIME_FORMAT *datetime_format;
   DATE_TIME_FORMAT *time_format;
   my_bool sysdate_is_now;
-
 };
 
 
@@ -1587,6 +1586,13 @@ public:
     Note: in the parser, stmt_arena == thd, even for PS/SP.
   */
   Query_arena *stmt_arena;
+
+  /*
+    map for tables that will be updated for a multi-table update query
+    statement, for other query statements, this will be zero.
+  */
+  table_map table_map_for_update;
+
   /* Tells if LAST_INSERT_ID(#) was called for the current statement */
   bool arg_of_last_insert_id_function;
   /*

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2008-08-22 13:21:31 +0000
+++ b/sql/sql_parse.cc	2008-08-26 12:29:33 +0000
@@ -1906,6 +1906,10 @@ mysql_execute_command(THD *thd)
   TABLE_LIST *all_tables;
   /* most outer SELECT_LEX_UNIT of query */
   SELECT_LEX_UNIT *unit= &lex->unit;
+#ifdef HAVE_REPLICATION
+  /* have table map for update for multi-update statement (BUG#37051) */
+  bool have_table_map_for_update= FALSE;
+#endif
   /* Saved variable value */
   DBUG_ENTER("mysql_execute_command");
 #ifdef WITH_PARTITION_STORAGE_ENGINE
@@ -1971,6 +1975,48 @@ mysql_execute_command(THD *thd)
       // force searching in slave.cc:tables_ok() 
       all_tables->updating= 1;
     }
+
+    /*
+      For fix of BUG#37051, the master stores the table map for update
+      in the Query_log_event, and the value is assigned to
+      thd->variables.table_map_for_update before executing the update
+      query.
+
+      If thd->variables.table_map_for_update is set, then we are
+      replicating from a new master, we can use this value to apply
+      filter rules without opening all the tables. However If
+      thd->variables.table_map_for_update is not set, then we are
+      replicating from an old master, so we just skip this and
+      continue with the old method. And of course, the bug would still
+      exist for old masters.
+    */
+    if (lex->sql_command == SQLCOM_UPDATE_MULTI &&
+        thd->table_map_for_update)
+    {
+      have_table_map_for_update= TRUE;
+      table_map table_map_for_update= thd->table_map_for_update;
+      uint nr= 0;
+      TABLE_LIST *table;
+      for (table=all_tables; table; table=table->next_global, nr++)
+      {
+        if (table_map_for_update & ((table_map)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
@@ -2946,7 +2992,7 @@ end_with_restore_list:
 
 #ifdef HAVE_REPLICATION
     /* Check slave filtering rules */
-    if (unlikely(thd->slave_thread))
+    if (unlikely(thd->slave_thread && !have_table_map_for_update))
     {
       if (all_tables_not_ok(thd, all_tables))
       {

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2008-07-17 19:55:18 +0000
+++ b/sql/sql_update.cc	2008-08-26 12:29:33 +0000
@@ -1007,7 +1007,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-6.0 branch (hezx:2676) Bug#37051He Zhenxing26 Aug