Hi,
Patch is good to push. Great work!
/Sven
He Zhenxing wrote:
> #At file:///media/sda3/work/mysql/bzrwork/b37051/5.1-rpl-new/
>
> 2632 He Zhenxing 2008-07-31
> 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-table 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 referenced by the multi-table update statement to
> figure out which tables are involved for update.
>
> So a status variable is added to Query_log event to store the
> value of table map for update on master. 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, if this values
> is not available, the old approach is used and thus the bug will
> still occur for when replicating from old masters.
> 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:
> sql/log_event.cc
> sql/log_event.h
> sql/sql_class.cc
> sql/sql_class.h
> sql/sql_parse.cc
> sql/sql_update.cc
>
> per-file messages:
> 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/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-07-31 06:24:27
> +0000
> @@ -0,0 +1,23 @@
> +# ==== 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;
> +source include/start_slave.inc;
> +connection master;
>
> === 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-07-31 06:24:27
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.inc
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.inc
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.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
> +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;
> +include/start_slave.inc
> +[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-07-31
> 06:24:27 +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-07-31 06:24:27
> +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-05-30 09:12:07 +0000
> +++ b/sql/log_event.cc 2008-07-31 06:24:27 +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\
> @@ -2425,6 +2433,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-03-28 13:52:33 +0000
> +++ b/sql/log_event.h 2008-07-31 06:24:27 +0000
> @@ -237,12 +237,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 */ \
> @@ -306,6 +309,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
> @@ -1455,6 +1460,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
> @@ -1471,6 +1492,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
> {
> @@ -1548,6 +1572,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-06-19 18:47:59 +0000
> +++ b/sql/sql_class.cc 2008-07-31 06:24:27 +0000
> @@ -1113,6 +1113,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-05-20 07:38:17 +0000
> +++ b/sql/sql_class.h 2008-07-31 06:24:27 +0000
> @@ -396,7 +396,6 @@ struct system_variables
> DATE_TIME_FORMAT *datetime_format;
> DATE_TIME_FORMAT *time_format;
> my_bool sysdate_is_now;
> -
> };
>
>
> @@ -1446,6 +1445,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-05-20 16:36:26 +0000
> +++ b/sql/sql_parse.cc 2008-07-31 06:24:27 +0000
> @@ -1887,6 +1887,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
> @@ -1952,6 +1956,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
> @@ -2866,7 +2912,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-05-20 07:38:17 +0000
> +++ b/sql/sql_update.cc 2008-07-31 06:24:27 +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
>
>
--
Sven Sandberg, Software Engineer
MySQL AB, www.mysql.com
