1# Test evaluation of replication table filter rules 2# 3# ==== Purpose ==== 4# 5# Test if replication table filter rules are properly evaluated when 6# some of the tables referenced by the multiple-table update do not 7# exist on slave. 8# 9# ==== Method ==== 10# 11# Master creates tables t1, t2, t3, t4, t5, t6, t7, t8, t9 and the 12# slave is started with the following replication table filter rules: 13# 14# --replicate-do-table=t1 15# --replicate-do-table=t2 16# --replicate-do-table=t3 17# 18# and 19# 20# --replicate-ignore-table=t4 21# --replicate-ignore-table=t5 22# --replicate-ignore-table=t6 23# 24# So the slave only replicate changes to tables t1, t2 and t3 and only 25# these tables exist on slave. 26# 27# From now on, tables t1, t2, and t3 are referenced as do tables, 28# tables t4, t5, t6 are referenced as ignore tables, and tables t7, 29# t8, t9 are referenced as other tables. 30# 31# All multi-table update tests reference tables that are not do 32# tables, which do not exist on slave. And the following situations 33# of multi-table update will be tested: 34# 35# 1. Do tables are not referenced at all 36# 2. Do tables are not referenced for update 37# 3. Ignore tables are referenced for update before do tables 38# 4. Only do tables are referenced for update 39# 5. Do tables and other tables are referenced for update 40# 6. Do tables are referenced for update before ignore tables 41# 42# For 1, 2 and 3, the statement should be ignored by slave, for 4, 5 43# and 6 the statement should be accepted by slave and cause an error 44# because of non-exist tables. 45# 46# ==== Related bugs ==== 47# 48# BUG#37051 Replication rules not evaluated correctly 49 50 51source include/have_binlog_format_statement.inc; 52source include/master-slave.inc; 53 54# These tables are mentioned in do-table rules 55CREATE TABLE t1 (id int, a int); 56CREATE TABLE t2 (id int, b int); 57CREATE TABLE t3 (id int, c int); 58 59# These tables are mentioned in ignore-table rules 60CREATE TABLE t4 (id int, d int); 61CREATE TABLE t5 (id int, e int); 62CREATE TABLE t6 (id int, f int); 63 64# These tables are not mentioned in do-table or ignore-table rules 65CREATE TABLE t7 (id int, g int); 66CREATE TABLE t8 (id int, h int); 67CREATE TABLE t9 (id int, i int); 68 69INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); 70INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3); 71INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3); 72 73INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3); 74INSERT INTO t5 VALUES (1, 1), (2, 2), (3, 3); 75INSERT INTO t6 VALUES (1, 1), (2, 2), (3, 3); 76 77INSERT INTO t7 VALUES (1, 1), (2, 2), (3, 3); 78INSERT INTO t8 VALUES (1, 1), (2, 2), (3, 3); 79INSERT INTO t9 VALUES (1, 1), (2, 2), (3, 3); 80 81# Only t1, t2, t3 should be replicated to slave 82sync_slave_with_master; 83SHOW TABLES LIKE 't%'; 84 85connection master; 86 87# 88# Do tables are not referenced, these statements should be ignored by 89# slave. 90# 91UPDATE t7 LEFT JOIN t4 ON (t4.id=t7.id) SET d=0, g=0 where t7.id=1; 92UPDATE 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; 93UPDATE 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; 94UPDATE 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; 95 96# 97# Do tables are not referenced for update, these statements should be 98# ignored by slave. 99# 100UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET d=0 where t1.id=1; 101UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET g=0 where t1.id=1; 102UPDATE 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; 103UPDATE 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; 104UPDATE 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; 105UPDATE 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; 106 107# 108# Ignore tables are referenced for update before do tables, these 109# statements should be ignore by slave. 110# 111UPDATE t4 LEFT JOIN t1 ON (t1.id=t4.id) SET a=0, d=0 where t4.id=1; 112UPDATE 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; 113UPDATE 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; 114UPDATE 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; 115UPDATE 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; 116UPDATE 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; 117UPDATE 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; 118 119# Sync slave to make sure all above statements are correctly ignored, 120# if any of the above statement are not ignored, it would cause error 121# and stop slave sql thread. 122sync_slave_with_master; 123connection slave; 124call mtr.add_suppression("Slave SQL.*Error .Table .test.t[47]. doesn.t exist. on query.* error.* 1146"); 125connection master; 126 127# Parameters for include/wait_for_slave_sql_error_and_skip.inc: 128# Ask it to show SQL error message. 129let $show_slave_sql_error= 1; 130# The expected error will always be 1146 (ER_NO_SUCH_TABLE). 131let $slave_sql_errno= 1146; 132 133# 134# Only do tables are referenced for update, these statements should 135# cause error on slave 136# 137UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0 where t1.id=1; 138source include/wait_for_slave_sql_error_and_skip.inc; 139 140UPDATE t1 LEFT JOIN (t4, t7) ON (t1.id=t4.id and t1.id=t7.id) SET a=0 where t1.id=1; 141source include/wait_for_slave_sql_error_and_skip.inc; 142 143UPDATE 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; 144source include/wait_for_slave_sql_error_and_skip.inc; 145 146UPDATE 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; 147source include/wait_for_slave_sql_error_and_skip.inc; 148 149# 150# Do tables and other tables are referenced for update, these 151# statements should cause error on slave 152# 153UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET a=0, g=0 where t1.id=1; 154source include/wait_for_slave_sql_error_and_skip.inc; 155 156UPDATE t7 LEFT JOIN t1 ON (t1.id=t7.id) SET a=0, g=0 where t7.id=1; 157source include/wait_for_slave_sql_error_and_skip.inc; 158 159UPDATE 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; 160source include/wait_for_slave_sql_error_and_skip.inc; 161 162UPDATE 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; 163source include/wait_for_slave_sql_error_and_skip.inc; 164 165UPDATE 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; 166source include/wait_for_slave_sql_error_and_skip.inc; 167 168UPDATE 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; 169source include/wait_for_slave_sql_error_and_skip.inc; 170 171UPDATE 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; 172source include/wait_for_slave_sql_error_and_skip.inc; 173 174UPDATE 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; 175source include/wait_for_slave_sql_error_and_skip.inc; 176 177# 178# Do tables are referenced for update before ignore tables 179# 180UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0, d=0 where t1.id=1; 181source include/wait_for_slave_sql_error_and_skip.inc; 182 183UPDATE 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; 184source include/wait_for_slave_sql_error_and_skip.inc; 185 186UPDATE 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; 187source include/wait_for_slave_sql_error_and_skip.inc; 188 189UPDATE 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; 190source include/wait_for_slave_sql_error_and_skip.inc; 191 192sync_slave_with_master; 193echo [on slave]; 194 195# We should only have tables t1, t2, t3 on slave 196show tables like 't%'; 197 198# The rows in these tables should remain untouched 199SELECT * FROM t1; 200SELECT * FROM t2; 201SELECT * FROM t3; 202 203# Clean up 204connection master; 205drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; 206 207--sync_slave_with_master 208 209# 210# BUG#11754117 - 45670: INTVAR_EVENTS FOR FILTERED-OUT QUERY_LOG_EVENTS ARE EXECUTED 211# Int-, Rand- and User- var events accompaning a filtered out Query-log-event should 212# be filtered as well. 213# 214connection master; 215# Although RAND() is from 0 to 1.0, DECIMAL(M,D), requires that M must be >= D. 216CREATE TABLE test.t5 (a INT AUTO_INCREMENT PRIMARY KEY, b DECIMAL(20,20), c INT); # ignored on slave 217CREATE TABLE test.t1 (a INT); # accepted on slave 218INSERT INTO test.t1 VALUES(1); 219 220--sync_slave_with_master 221call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 222# Although RAND() is from 0 to 1.0, DECIMAL(M,D), requires that M must be >= D. 223CREATE TABLE test.t_slave (a INT AUTO_INCREMENT PRIMARY KEY, b DECIMAL(20,20), c INT); 224CREATE TRIGGER t1_update AFTER UPDATE ON test.t1 FOR EACH ROW 225 INSERT INTO test.t_slave VALUES(NULL, RAND(), @c); 226 227connection master; 228SET INSERT_ID=2; 229SET @c=2; 230SET @@rand_seed1=10000000, @@rand_seed2=1000000; 231INSERT INTO t5 VALUES (NULL, RAND(), @c); # to be ignored 232SELECT b into @b FROM test.t5; 233--let $b_master=`select @b` 234UPDATE test.t1 SET a=2; # to run trigger on slave 235 236--sync_slave_with_master 237 238# The proof: 239SELECT a AS 'ONE' into @a FROM test.t_slave; 240SELECT c AS 'NULL' into @c FROM test.t_slave; 241 242let $count= 1; 243let $table= test.t_slave; 244source include/wait_until_rows_count.inc; 245 246if (`SELECT @a != 2 and @c != NULL`) 247{ 248 SELECT * FROM test.t_slave; 249 --die Intvar or user var from replication events unexpetedly escaped out to screw a following query applying context. 250} 251 252SELECT b into @b FROM test.t_slave; 253--let $b_slave=`select @b` 254 255--let $assert_text= Random values from master and slave must be different 256--let $assert_cond= $b_master != $b_slave 257--source include/assert.inc 258 259# cleanup BUG#11754117 260connection master; 261drop table test.t5; 262drop table test.t1; 263 264--sync_slave_with_master 265drop table test.t_slave; 266 267--source include/rpl_end.inc 268