From 91cb65a60443d87f9c461c47c7ba16d50f8c4e60 Mon Sep 17 00:00:00 2001 From: He Zhenxing Date: Thu, 5 Mar 2009 18:10:44 +0800 Subject: BUG#37051 Replication rules not evaluated correctly Backporting patch to 5.0. --- mysql-test/include/master-slave-end.inc | 6 + mysql-test/include/start_slave.inc | 21 +++ mysql-test/include/stop_slave.inc | 21 +++ mysql-test/include/wait_for_slave_sql_error.inc | 40 ++++ .../include/wait_for_slave_sql_error_and_skip.inc | 39 ++++ mysql-test/r/multi_update.result | 4 +- mysql-test/r/rpl_filter_tables_not_exist.result | 151 +++++++++++++++ mysql-test/t/rpl_filter_tables_not_exist-slave.opt | 1 + mysql-test/t/rpl_filter_tables_not_exist.test | 206 +++++++++++++++++++++ 9 files changed, 487 insertions(+), 2 deletions(-) create mode 100644 mysql-test/include/master-slave-end.inc create mode 100644 mysql-test/include/start_slave.inc create mode 100644 mysql-test/include/stop_slave.inc create mode 100644 mysql-test/include/wait_for_slave_sql_error.inc create mode 100644 mysql-test/include/wait_for_slave_sql_error_and_skip.inc create mode 100644 mysql-test/r/rpl_filter_tables_not_exist.result create mode 100644 mysql-test/t/rpl_filter_tables_not_exist-slave.opt create mode 100644 mysql-test/t/rpl_filter_tables_not_exist.test (limited to 'mysql-test') diff --git a/mysql-test/include/master-slave-end.inc b/mysql-test/include/master-slave-end.inc new file mode 100644 index 00000000000..74e4c7b608a --- /dev/null +++ b/mysql-test/include/master-slave-end.inc @@ -0,0 +1,6 @@ +--connection master +--sync_slave_with_master +--connection slave +--disable_query_log +STOP SLAVE; +--enable_query_log diff --git a/mysql-test/include/start_slave.inc b/mysql-test/include/start_slave.inc new file mode 100644 index 00000000000..78a02736de8 --- /dev/null +++ b/mysql-test/include/start_slave.inc @@ -0,0 +1,21 @@ +# ==== Purpose ==== +# +# Issues START SLAVE on the current connection. Then waits until both +# the IO and SQL threads have started, or until a timeout is reached. +# +# Please use this instead of 'START SLAVE', to reduce the risk of test +# case bugs. +# +# ==== Usage ==== +# +# source include/wait_for_slave_to_start.inc; +# +# Parameters to this macro are $slave_timeout and +# $master_connection. See wait_for_slave_param.inc for +# descriptions. + +--disable_query_log +START SLAVE; +--enable_query_log +--echo include/start_slave.inc +source include/wait_for_slave_to_start.inc; diff --git a/mysql-test/include/stop_slave.inc b/mysql-test/include/stop_slave.inc new file mode 100644 index 00000000000..7161e6fe739 --- /dev/null +++ b/mysql-test/include/stop_slave.inc @@ -0,0 +1,21 @@ +# ==== Purpose ==== +# +# Issues STOP SLAVE on the current connection. Then waits until both +# the IO and SQL threads have stopped, or until a timeout is reached. +# +# Please use this instead of 'STOP SLAVE', to reduce the risk of test +# case bugs. +# +# ==== Usage ==== +# +# source include/wait_for_slave_to_start.inc; +# +# Parameters to this macro are $slave_timeout and +# $master_connection. See wait_for_slave_param.inc for +# descriptions. + +--disable_query_log +STOP SLAVE; +--enable_query_log +--echo include/stop_slave.inc +source include/wait_for_slave_to_stop.inc; diff --git a/mysql-test/include/wait_for_slave_sql_error.inc b/mysql-test/include/wait_for_slave_sql_error.inc new file mode 100644 index 00000000000..1a708cee780 --- /dev/null +++ b/mysql-test/include/wait_for_slave_sql_error.inc @@ -0,0 +1,40 @@ +# ==== Purpose ==== +# +# Waits until the SQL thread of the current connection has got an +# error, or until a timeout is reached. Also waits until the SQL +# thread has completely stopped. +# +# ==== Usage ==== +# +# source include/wait_for_slave_sql_error.inc; +# +# Parameters: +# +# $slave_sql_errno +# The expected SQL error number. This is required. +# (After BUG#41956 has been fixed, this will be required to be a +# symbolic name instead of a number.) +# +# $slave_timeout +# See wait_for_slave_param.inc for description. +# +# $master_connection +# See wait_for_slave_param.inc for description. + +if (`SELECT '$slave_sql_errno' = ''`) { + --echo !!!ERROR IN TEST: you must set \$slave_sql_errno before sourcing wait_fro_slave_sql_error.inc + exit; +} + +let $slave_param= Slave_SQL_Running; +let $slave_param_value= No; +let $slave_error_message= Failed while waiting for slave to stop the SQL thread (expecting error in the SQL thread); +source include/wait_for_slave_param.inc; + +# NOTE: on mysql-5.0, there is no way to distinguish slave SQL error from IO error +let $_error= query_get_value(SHOW SLAVE STATUS, Last_Errno, 1); +if (`SELECT '$_error' != '$slave_sql_errno'`) { + --echo Slave stopped with wrong error code: $_error (expected $slave_sql_errno) + source include/show_rpl_debug_info.inc; + exit; +} diff --git a/mysql-test/include/wait_for_slave_sql_error_and_skip.inc b/mysql-test/include/wait_for_slave_sql_error_and_skip.inc new file mode 100644 index 00000000000..5723720f4b7 --- /dev/null +++ b/mysql-test/include/wait_for_slave_sql_error_and_skip.inc @@ -0,0 +1,39 @@ +# ==== Purpose ==== +# +# Wait for slave SQL error, skip the erroneous statement and restart +# slave +# +# ==== Usage ==== +# +# let $slave_sql_error= ; +# source include/wait_for_slave_sql_error_and_skip.inc; +# +# Parameters: +# +# $slave_sql_errno +# The error number to wait for. This is required. (See +# wait_for_slave_sql_error.inc) +# +# $show_sql_error +# If set, will print the error to the query log. +# +# $slave_timeout +# See wait_for_slave_param.inc for description. +# +# $master_connection +# See wait_for_slave_param.inc for description. + +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) +{ + # NOTE: on mysql-5.0, there is no way to distinguish slave SQL error from IO error + let $error= query_get_value("SHOW SLAVE STATUS", Last_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; diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index f6dbac31c2a..c04948c6fa7 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -522,7 +522,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 189 +master-bin.000001 198 delete from t1; delete from t2; insert into t1 values (1,2),(3,4),(4,4); @@ -532,7 +532,7 @@ UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a; ERROR 23000: Duplicate entry '4' for key 1 show master status /* there must be the UPDATE query event */; File Position Binlog_Do_DB Binlog_Ignore_DB -master-bin.000001 204 +master-bin.000001 213 drop table t1, t2; drop table if exists t1, t2, t3; CREATE TABLE t1 (a int, PRIMARY KEY (a)); diff --git a/mysql-test/r/rpl_filter_tables_not_exist.result b/mysql-test/r/rpl_filter_tables_not_exist.result new file mode 100644 index 00000000000..7eddaabc636 --- /dev/null +++ b/mysql-test/r/rpl_filter_tables_not_exist.result @@ -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; diff --git a/mysql-test/t/rpl_filter_tables_not_exist-slave.opt b/mysql-test/t/rpl_filter_tables_not_exist-slave.opt new file mode 100644 index 00000000000..42acd3ea33d --- /dev/null +++ b/mysql-test/t/rpl_filter_tables_not_exist-slave.opt @@ -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 diff --git a/mysql-test/t/rpl_filter_tables_not_exist.test b/mysql-test/t/rpl_filter_tables_not_exist.test new file mode 100644 index 00000000000..d1153e584c8 --- /dev/null +++ b/mysql-test/t/rpl_filter_tables_not_exist.test @@ -0,0 +1,206 @@ +# 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/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; + +# Parameters for include/wait_for_slave_sql_error_and_skip.inc: +# Ask it to show SQL error message. +let $show_sql_error= 1; +# The expected error will always be 1146 (ER_NO_SUCH_TABLE). +let $slave_sql_errno= 1146; + +# +# 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; -- cgit v1.2.1