commit aec385f2c6e68fc76d2b79b02951a8e933ef4241 Author: Davi Arnaut Date: Wed Feb 15 11:02:47 2012 -0800 MYSQL-46: Make Replication filter settings dynamic Make the slave options --replicate-* dynamic variables so that these options can be changed dynamically while the server is running, which enables users to modify replication filtering rules without having to stop and restart the server. This is accomplished by just requiring that the slave threads are stopped when these options are set dynamically. Since filtering rules are only used by the SQL slave thread, setting them while the thread is not running avoids the need for locking. diff --git a/mysql-test/suite/rpl/r/rpl_filter_dbs_dynamic.result b/mysql-test/suite/rpl/r/rpl_filter_dbs_dynamic.result new file mode 100644 index 0000000..321b8d9 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_filter_dbs_dynamic.result @@ -0,0 +1,40 @@ +include/master-slave.inc +[connection master] +SET @@GLOBAL.replicate_do_db="db1"; +ERROR HY000: This operation cannot be performed with a running slave; run STOP SLAVE first +SET @@GLOBAL.replicate_ignore_db="db2"; +ERROR HY000: This operation cannot be performed with a running slave; run STOP SLAVE first +include/stop_slave.inc +SET @@GLOBAL.replicate_do_db="db1"; +SET @@GLOBAL.replicate_ignore_db="db2"; +include/start_slave.inc +CREATE DATABASE db1; +CREATE DATABASE db2; +CREATE DATABASE db3; +USE db1; +CREATE TABLE t1 (a INT); +USE db2; +CREATE TABLE t2 (a INT); +USE db3; +CREATE TABLE t3 (a INT); +USE db1; +INSERT INTO t1 VALUES (1); +USE db2; +INSERT INTO t2 VALUES (2); +USE db3; +INSERT INTO t3 VALUES (3); +[on slave] +SHOW DATABASES LIKE 'db%'; +Database (db%) +db1 +SHOW TABLES IN db1 LIKE 't%'; +Tables_in_db1 (t%) +t1 +[on master] +[on master] +DROP DATABASE IF EXISTS db1; +DROP DATABASE IF EXISTS db2; +DROP DATABASE IF EXISTS db3; +include/rpl_end.inc +SET @@GLOBAL.replicate_do_db=""; +SET @@GLOBAL.replicate_ignore_db=""; diff --git a/mysql-test/suite/rpl/r/rpl_filter_tables_dynamic.result b/mysql-test/suite/rpl/r/rpl_filter_tables_dynamic.result new file mode 100644 index 0000000..9eb803d --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_filter_tables_dynamic.result @@ -0,0 +1,127 @@ +include/master-slave.inc +[connection master] +SET @@GLOBAL.replicate_do_table="test.t1,test.t2,test.t3"; +ERROR HY000: This operation cannot be performed with a running slave; run STOP SLAVE first +SET @@GLOBAL.replicate_ignore_table="test.t4,test.t5,test.t6"; +ERROR HY000: This operation cannot be performed with a running slave; run STOP SLAVE first +include/stop_slave.inc +SET @@GLOBAL.replicate_do_table="test.t1,test.t2,test.t3"; +SET @@GLOBAL.replicate_ignore_table="test.t4,test.t5,test.t6"; +include/start_slave.inc +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; +call mtr.add_suppression("Slave SQL.*Error .Table .test.t[47]. doesn.t exist. on query.* Error_code: 1146"); +UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0 where t1.id=1; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +UPDATE t1 LEFT JOIN (t4, t7) ON (t1.id=t4.id and t1.id=t7.id) SET a=0 where t1.id=1; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET a=0, g=0 where t1.id=1; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +UPDATE t7 LEFT JOIN t1 ON (t1.id=t7.id) SET a=0, g=0 where t7.id=1; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0, d=0 where t1.id=1; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +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; +include/wait_for_slave_sql_error_and_skip.inc [errno=1146] +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'' +[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; +include/rpl_end.inc +SET @@GLOBAL.replicate_do_table=""; +SET @@GLOBAL.replicate_ignore_table=""; diff --git a/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result b/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result new file mode 100644 index 0000000..6858181 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result @@ -0,0 +1,26 @@ +include/master-slave.inc +[connection master] +SET @@GLOBAL.replicate_wild_do_table="test.a%"; +ERROR HY000: This operation cannot be performed with a running slave; run STOP SLAVE first +SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; +ERROR HY000: This operation cannot be performed with a running slave; run STOP SLAVE first +include/stop_slave.inc +SET @@GLOBAL.replicate_wild_do_table="test.a%"; +SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; +include/start_slave.inc +CREATE TABLE a1 (a INT); +CREATE TABLE b1 (a INT); +CREATE TABLE c1 (a INT); +INSERT INTO a1 VALUES (1); +INSERT INTO b1 VALUES (2); +INSERT INTO c1 VALUES (3); +[on slave] +SHOW TABLES LIKE '%1'; +Tables_in_test (%1) +a1 +[on master] +[on master] +DROP TABLE IF EXISTS a1,b1,c1; +include/rpl_end.inc +SET @@GLOBAL.replicate_wild_do_table=""; +SET @@GLOBAL.replicate_wild_ignore_table=""; diff --git a/mysql-test/suite/rpl/t/rpl_filter_dbs_dynamic.test b/mysql-test/suite/rpl/t/rpl_filter_dbs_dynamic.test new file mode 100644 index 0000000..9439ee3 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_filter_dbs_dynamic.test @@ -0,0 +1,65 @@ +# +# Test if dynamic replication database filter rules are properly evaluated. +# + +source include/have_binlog_format_statement.inc; +source include/master-slave.inc; + +connection slave; +--error ER_SLAVE_MUST_STOP +SET @@GLOBAL.replicate_do_db="db1"; +--error ER_SLAVE_MUST_STOP +SET @@GLOBAL.replicate_ignore_db="db2"; + +connection slave; +source include/stop_slave.inc; +SET @@GLOBAL.replicate_do_db="db1"; +SET @@GLOBAL.replicate_ignore_db="db2"; +source include/start_slave.inc; +connection master; + +CREATE DATABASE db1; +CREATE DATABASE db2; +CREATE DATABASE db3; + +# db is mentioned in do-db rules +USE db1; +CREATE TABLE t1 (a INT); + +# db is mentioned in ignore-db rules +USE db2; +CREATE TABLE t2 (a INT); + +# db is not mentioned in do-db or ignore-db rules +USE db3; +CREATE TABLE t3 (a INT); + +USE db1; +INSERT INTO t1 VALUES (1); + +USE db2; +INSERT INTO t2 VALUES (2); + +USE db3; +INSERT INTO t3 VALUES (3); + +# Only db1 should be replicated to slave +sync_slave_with_master; +echo [on slave]; +SHOW DATABASES LIKE 'db%'; +SHOW TABLES IN db1 LIKE 't%'; + +connection master; +echo [on master]; + +# Clean up +connection master; +echo [on master]; +DROP DATABASE IF EXISTS db1; +DROP DATABASE IF EXISTS db2; +DROP DATABASE IF EXISTS db3; +--source include/rpl_end.inc + +connection slave; +SET @@GLOBAL.replicate_do_db=""; +SET @@GLOBAL.replicate_ignore_db=""; diff --git a/mysql-test/suite/rpl/t/rpl_filter_tables_dynamic.test b/mysql-test/suite/rpl/t/rpl_filter_tables_dynamic.test new file mode 100644 index 0000000..5cc9e8e --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_filter_tables_dynamic.test @@ -0,0 +1,218 @@ +# Test evaluation of replication table filter rules +# +# ==== Purpose ==== +# +# Test if dynamic 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: +# +# SET @@GLOBAL.replicate_do_table="test.t1,test.t2,test.t3"; +# +# and +# +# SET @@GLOBAL.replicate_ignore_table="test.t4,test.t5,test.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. +# + +source include/have_binlog_format_statement.inc; +source include/master-slave.inc; + +connection slave; +--error ER_SLAVE_MUST_STOP +SET @@GLOBAL.replicate_do_table="test.t1,test.t2,test.t3"; +--error ER_SLAVE_MUST_STOP +SET @@GLOBAL.replicate_ignore_table="test.t4,test.t5,test.t6"; + +connection slave; +source include/stop_slave.inc; +SET @@GLOBAL.replicate_do_table="test.t1,test.t2,test.t3"; +SET @@GLOBAL.replicate_ignore_table="test.t4,test.t5,test.t6"; +source include/start_slave.inc; +connection master; + +# 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 slave; +call mtr.add_suppression("Slave SQL.*Error .Table .test.t[47]. doesn.t exist. on query.* Error_code: 1146"); +connection master; + +# Parameters for include/wait_for_slave_sql_error_and_skip.inc: +# Ask it to show SQL error message. +let $show_slave_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/rpl_end.inc + +connection slave; +SET @@GLOBAL.replicate_do_table=""; +SET @@GLOBAL.replicate_ignore_table=""; diff --git a/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test b/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test new file mode 100644 index 0000000..c822c81 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test @@ -0,0 +1,50 @@ +# +# Test if dynamic replication wild table filter rules are properly evaluated. +# + +source include/have_binlog_format_statement.inc; +source include/master-slave.inc; + +connection slave; +--error ER_SLAVE_MUST_STOP +SET @@GLOBAL.replicate_wild_do_table="test.a%"; +--error ER_SLAVE_MUST_STOP +SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; + +connection slave; +source include/stop_slave.inc; +SET @@GLOBAL.replicate_wild_do_table="test.a%"; +SET @@GLOBAL.replicate_wild_ignore_table="test.b%"; +source include/start_slave.inc; +connection master; + +# Table is mentioned in wild-do-table rules +CREATE TABLE a1 (a INT); + +# Table is mentioned in wild-ignore-table rules +CREATE TABLE b1 (a INT); + +# Table is not mentioned in wild-do-table or wild-ignore-table rules +CREATE TABLE c1 (a INT); + +INSERT INTO a1 VALUES (1); +INSERT INTO b1 VALUES (2); +INSERT INTO c1 VALUES (3); + +# Only a1 should be replicated to slave +sync_slave_with_master; +echo [on slave]; +SHOW TABLES LIKE '%1'; + +connection master; +echo [on master]; + +# Clean up +connection master; +echo [on master]; +DROP TABLE IF EXISTS a1,b1,c1; +--source include/rpl_end.inc + +connection slave; +SET @@GLOBAL.replicate_wild_do_table=""; +SET @@GLOBAL.replicate_wild_ignore_table=""; diff --git a/mysql-test/suite/sys_vars/r/replicate_do_db_basic.result b/mysql-test/suite/sys_vars/r/replicate_do_db_basic.result new file mode 100644 index 0000000..b964d3d --- /dev/null +++ b/mysql-test/suite/sys_vars/r/replicate_do_db_basic.result @@ -0,0 +1,37 @@ +# +# Basic testing of replicate_do_db. +# +SET @save_replicate_do_db = @@GLOBAL.replicate_do_db; +SELECT @save_replicate_do_db; +@save_replicate_do_db + +# Scope. +SET @@SESSION.replicate_do_db = ""; +ERROR HY000: Variable 'replicate_do_db' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@SESSION.replicate_do_db; +ERROR HY000: Variable 'replicate_do_db' is a GLOBAL variable +# Incorrect type. +SET @@GLOBAL.replicate_do_db=1; +ERROR 42000: Incorrect argument type to variable 'replicate_do_db' +SET @@GLOBAL.replicate_do_db=1.1; +ERROR 42000: Incorrect argument type to variable 'replicate_do_db' +SET @@GLOBAL.replicate_do_db=1e1; +ERROR 42000: Incorrect argument type to variable 'replicate_do_db' +# Argument syntax. +SET @@GLOBAL.replicate_do_db="db1,,,,,db3"; +SELECT @@GLOBAL.replicate_do_db; +@@GLOBAL.replicate_do_db +db1,db3 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_do_db'; +VARIABLE_NAME VARIABLE_VALUE +REPLICATE_DO_DB db1,db3 +SET @@GLOBAL.replicate_do_db="db1,,,db2,,,db3"; +SELECT @@GLOBAL.replicate_do_db; +@@GLOBAL.replicate_do_db +db1,db2,db3 +SET @@GLOBAL.replicate_do_db=""; +SELECT @@GLOBAL.replicate_do_db; +@@GLOBAL.replicate_do_db + +# Cleanup. +SET @@GLOBAL.replicate_do_db = @save_replicate_do_db; diff --git a/mysql-test/suite/sys_vars/r/replicate_do_table_basic.result b/mysql-test/suite/sys_vars/r/replicate_do_table_basic.result new file mode 100644 index 0000000..fac2372 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/replicate_do_table_basic.result @@ -0,0 +1,44 @@ +# +# Basic testing of replicate_do_table. +# +SET @save_replicate_do_table = @@GLOBAL.replicate_do_table; +SELECT @save_replicate_do_table; +@save_replicate_do_table + +# Scope. +SET @@SESSION.replicate_do_table = ""; +ERROR HY000: Variable 'replicate_do_table' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@SESSION.replicate_do_table; +ERROR HY000: Variable 'replicate_do_table' is a GLOBAL variable +# Incorrect type. +SET @@GLOBAL.replicate_do_table=1; +ERROR 42000: Incorrect argument type to variable 'replicate_do_table' +SET @@GLOBAL.replicate_do_table=1.1; +ERROR 42000: Incorrect argument type to variable 'replicate_do_table' +SET @@GLOBAL.replicate_do_table=1e1; +ERROR 42000: Incorrect argument type to variable 'replicate_do_table' +# Incorrect arguments. +SET @@GLOBAL.replicate_do_table="t1"; +ERROR HY000: Incorrect arguments to SET +SET @@GLOBAL.replicate_do_table="test.t1, t2"; +ERROR HY000: Incorrect arguments to SET +SET @@GLOBAL.replicate_do_table="test.,t1"; +ERROR HY000: Incorrect arguments to SET +# Argument syntax. +SET @@GLOBAL.replicate_do_table="test.t1,,,,,test.t3"; +SELECT @@GLOBAL.replicate_do_table; +@@GLOBAL.replicate_do_table +test.t3,test.t1 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_do_table'; +VARIABLE_NAME VARIABLE_VALUE +REPLICATE_DO_TABLE test.t3,test.t1 +SET @@GLOBAL.replicate_do_table="test.t1,,,test2.t2,,,test.t3"; +SELECT @@GLOBAL.replicate_do_table; +@@GLOBAL.replicate_do_table +test.t3,test.t1,test2.t2 +SET @@GLOBAL.replicate_do_table=""; +SELECT @@GLOBAL.replicate_do_table; +@@GLOBAL.replicate_do_table + +# Cleanup. +SET @@GLOBAL.replicate_do_table = @save_replicate_do_table; diff --git a/mysql-test/suite/sys_vars/r/replicate_ignore_db_basic.result b/mysql-test/suite/sys_vars/r/replicate_ignore_db_basic.result new file mode 100644 index 0000000..c4d7a37 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/replicate_ignore_db_basic.result @@ -0,0 +1,37 @@ +# +# Basic testing of replicate_ignore_db. +# +SET @save_replicate_ignore_db = @@GLOBAL.replicate_ignore_db; +SELECT @save_replicate_ignore_db; +@save_replicate_ignore_db + +# Scope. +SET @@SESSION.replicate_ignore_db = ""; +ERROR HY000: Variable 'replicate_ignore_db' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@SESSION.replicate_ignore_db; +ERROR HY000: Variable 'replicate_ignore_db' is a GLOBAL variable +# Incorrect type. +SET @@GLOBAL.replicate_ignore_db=1; +ERROR 42000: Incorrect argument type to variable 'replicate_ignore_db' +SET @@GLOBAL.replicate_ignore_db=1.1; +ERROR 42000: Incorrect argument type to variable 'replicate_ignore_db' +SET @@GLOBAL.replicate_ignore_db=1e1; +ERROR 42000: Incorrect argument type to variable 'replicate_ignore_db' +# Argument syntax. +SET @@GLOBAL.replicate_ignore_db="db1,,,,,db3"; +SELECT @@GLOBAL.replicate_ignore_db; +@@GLOBAL.replicate_ignore_db +db1,db3 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_ignore_db'; +VARIABLE_NAME VARIABLE_VALUE +REPLICATE_IGNORE_DB db1,db3 +SET @@GLOBAL.replicate_ignore_db="db1,,,db2,,,db3"; +SELECT @@GLOBAL.replicate_ignore_db; +@@GLOBAL.replicate_ignore_db +db1,db2,db3 +SET @@GLOBAL.replicate_ignore_db=""; +SELECT @@GLOBAL.replicate_ignore_db; +@@GLOBAL.replicate_ignore_db + +# Cleanup. +SET @@GLOBAL.replicate_ignore_db = @save_replicate_ignore_db; diff --git a/mysql-test/suite/sys_vars/r/replicate_ignore_table_basic.result b/mysql-test/suite/sys_vars/r/replicate_ignore_table_basic.result new file mode 100644 index 0000000..bc463d0 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/replicate_ignore_table_basic.result @@ -0,0 +1,44 @@ +# +# Basic testing of replicate_ignore_table. +# +SET @save_replicate_ignore_table = @@GLOBAL.replicate_ignore_table; +SELECT @save_replicate_ignore_table; +@save_replicate_ignore_table + +# Scope. +SET @@SESSION.replicate_ignore_table = ""; +ERROR HY000: Variable 'replicate_ignore_table' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@SESSION.replicate_ignore_table; +ERROR HY000: Variable 'replicate_ignore_table' is a GLOBAL variable +# Incorrect type. +SET @@GLOBAL.replicate_ignore_table=1; +ERROR 42000: Incorrect argument type to variable 'replicate_ignore_table' +SET @@GLOBAL.replicate_ignore_table=1.1; +ERROR 42000: Incorrect argument type to variable 'replicate_ignore_table' +SET @@GLOBAL.replicate_ignore_table=1e1; +ERROR 42000: Incorrect argument type to variable 'replicate_ignore_table' +# Incorrect arguments. +SET @@GLOBAL.replicate_ignore_table="t1"; +ERROR HY000: Incorrect arguments to SET +SET @@GLOBAL.replicate_ignore_table="test.t1, t2"; +ERROR HY000: Incorrect arguments to SET +SET @@GLOBAL.replicate_ignore_table="test.,t1"; +ERROR HY000: Incorrect arguments to SET +# Argument syntax. +SET @@GLOBAL.replicate_ignore_table="test.t1,,,,,test.t3"; +SELECT @@GLOBAL.replicate_ignore_table; +@@GLOBAL.replicate_ignore_table +test.t3,test.t1 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_ignore_table'; +VARIABLE_NAME VARIABLE_VALUE +REPLICATE_IGNORE_TABLE test.t3,test.t1 +SET @@GLOBAL.replicate_ignore_table="test.t1,,,test2.t2,,,test.t3"; +SELECT @@GLOBAL.replicate_ignore_table; +@@GLOBAL.replicate_ignore_table +test.t3,test.t1,test2.t2 +SET @@GLOBAL.replicate_ignore_table=""; +SELECT @@GLOBAL.replicate_ignore_table; +@@GLOBAL.replicate_ignore_table + +# Cleanup. +SET @@GLOBAL.replicate_ignore_table = @save_replicate_ignore_table; diff --git a/mysql-test/suite/sys_vars/r/replicate_wild_do_table_basic.result b/mysql-test/suite/sys_vars/r/replicate_wild_do_table_basic.result new file mode 100644 index 0000000..5647cc9 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/replicate_wild_do_table_basic.result @@ -0,0 +1,44 @@ +# +# Basic testing of replicate_wild_do_table. +# +SET @save_replicate_wild_do_table = @@GLOBAL.replicate_wild_do_table; +SELECT @save_replicate_wild_do_table; +@save_replicate_wild_do_table + +# Scope. +SET @@SESSION.replicate_wild_do_table = ""; +ERROR HY000: Variable 'replicate_wild_do_table' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@SESSION.replicate_wild_do_table; +ERROR HY000: Variable 'replicate_wild_do_table' is a GLOBAL variable +# Incorrect type. +SET @@GLOBAL.replicate_wild_do_table=1; +ERROR 42000: Incorrect argument type to variable 'replicate_wild_do_table' +SET @@GLOBAL.replicate_wild_do_table=1.1; +ERROR 42000: Incorrect argument type to variable 'replicate_wild_do_table' +SET @@GLOBAL.replicate_wild_do_table=1e1; +ERROR 42000: Incorrect argument type to variable 'replicate_wild_do_table' +# Incorrect arguments. +SET @@GLOBAL.replicate_wild_do_table="t1"; +ERROR HY000: Incorrect arguments to SET +SET @@GLOBAL.replicate_wild_do_table="test.t, t2"; +ERROR HY000: Incorrect arguments to SET +SET @@GLOBAL.replicate_wild_do_table="test.,t1"; +ERROR HY000: Incorrect arguments to SET +# Argument syntax. +SET @@GLOBAL.replicate_wild_do_table="test.%,,,,,test.t3"; +SELECT @@GLOBAL.replicate_wild_do_table; +@@GLOBAL.replicate_wild_do_table +test.%,test.t3 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_wild_do_table'; +VARIABLE_NAME VARIABLE_VALUE +REPLICATE_WILD_DO_TABLE test.%,test.t3 +SET @@GLOBAL.replicate_wild_do_table="test.t1,,,test2.%,,,test.t3"; +SELECT @@GLOBAL.replicate_wild_do_table; +@@GLOBAL.replicate_wild_do_table +test.t1,test2.%,test.t3 +SET @@GLOBAL.replicate_wild_do_table=""; +SELECT @@GLOBAL.replicate_wild_do_table; +@@GLOBAL.replicate_wild_do_table + +# Cleanup. +SET @@GLOBAL.replicate_wild_do_table = @save_replicate_wild_do_table; diff --git a/mysql-test/suite/sys_vars/r/replicate_wild_ignore_table_basic.result b/mysql-test/suite/sys_vars/r/replicate_wild_ignore_table_basic.result new file mode 100644 index 0000000..c6829b7 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/replicate_wild_ignore_table_basic.result @@ -0,0 +1,44 @@ +# +# Basic testing of replicate_wild_ignore_table. +# +SET @save_replicate_wild_ignore_table = @@GLOBAL.replicate_wild_ignore_table; +SELECT @save_replicate_wild_ignore_table; +@save_replicate_wild_ignore_table + +# Scope. +SET @@SESSION.replicate_wild_ignore_table = ""; +ERROR HY000: Variable 'replicate_wild_ignore_table' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@SESSION.replicate_wild_ignore_table; +ERROR HY000: Variable 'replicate_wild_ignore_table' is a GLOBAL variable +# Incorrect type. +SET @@GLOBAL.replicate_wild_ignore_table=1; +ERROR 42000: Incorrect argument type to variable 'replicate_wild_ignore_table' +SET @@GLOBAL.replicate_wild_ignore_table=1.1; +ERROR 42000: Incorrect argument type to variable 'replicate_wild_ignore_table' +SET @@GLOBAL.replicate_wild_ignore_table=1e1; +ERROR 42000: Incorrect argument type to variable 'replicate_wild_ignore_table' +# Incorrect arguments. +SET @@GLOBAL.replicate_wild_ignore_table="t1"; +ERROR HY000: Incorrect arguments to SET +SET @@GLOBAL.replicate_wild_ignore_table="test.t, t2"; +ERROR HY000: Incorrect arguments to SET +SET @@GLOBAL.replicate_wild_ignore_table="test.,t1"; +ERROR HY000: Incorrect arguments to SET +# Argument syntax. +SET @@GLOBAL.replicate_wild_ignore_table="test.%,,,,,test.t3"; +SELECT @@GLOBAL.replicate_wild_ignore_table; +@@GLOBAL.replicate_wild_ignore_table +test.%,test.t3 +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_wild_ignore_table'; +VARIABLE_NAME VARIABLE_VALUE +REPLICATE_WILD_IGNORE_TABLE test.%,test.t3 +SET @@GLOBAL.replicate_wild_ignore_table="test.t1,,,test2.%,,,test.t3"; +SELECT @@GLOBAL.replicate_wild_ignore_table; +@@GLOBAL.replicate_wild_ignore_table +test.t1,test2.%,test.t3 +SET @@GLOBAL.replicate_wild_ignore_table=""; +SELECT @@GLOBAL.replicate_wild_ignore_table; +@@GLOBAL.replicate_wild_ignore_table + +# Cleanup. +SET @@GLOBAL.replicate_wild_ignore_table = @save_replicate_wild_ignore_table; diff --git a/mysql-test/suite/sys_vars/t/replicate_do_db_basic.test b/mysql-test/suite/sys_vars/t/replicate_do_db_basic.test new file mode 100644 index 0000000..ccf50b1 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/replicate_do_db_basic.test @@ -0,0 +1,39 @@ +--source include/not_embedded.inc + +--echo # +--echo # Basic testing of replicate_do_db. +--echo # + +SET @save_replicate_do_db = @@GLOBAL.replicate_do_db; +SELECT @save_replicate_do_db; + +--echo # Scope. + +--error ER_GLOBAL_VARIABLE +SET @@SESSION.replicate_do_db = ""; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@SESSION.replicate_do_db; + +--echo # Incorrect type. + +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_do_db=1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_do_db=1.1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_do_db=1e1; + +--echo # Argument syntax. + +SET @@GLOBAL.replicate_do_db="db1,,,,,db3"; +SELECT @@GLOBAL.replicate_do_db; +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_do_db'; + +SET @@GLOBAL.replicate_do_db="db1,,,db2,,,db3"; +SELECT @@GLOBAL.replicate_do_db; + +SET @@GLOBAL.replicate_do_db=""; +SELECT @@GLOBAL.replicate_do_db; + +--echo # Cleanup. +SET @@GLOBAL.replicate_do_db = @save_replicate_do_db; diff --git a/mysql-test/suite/sys_vars/t/replicate_do_table_basic.test b/mysql-test/suite/sys_vars/t/replicate_do_table_basic.test new file mode 100644 index 0000000..f3b1585 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/replicate_do_table_basic.test @@ -0,0 +1,48 @@ +--source include/not_embedded.inc + +--echo # +--echo # Basic testing of replicate_do_table. +--echo # + +SET @save_replicate_do_table = @@GLOBAL.replicate_do_table; +SELECT @save_replicate_do_table; + +--echo # Scope. + +--error ER_GLOBAL_VARIABLE +SET @@SESSION.replicate_do_table = ""; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@SESSION.replicate_do_table; + +--echo # Incorrect type. + +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_do_table=1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_do_table=1.1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_do_table=1e1; + +--echo # Incorrect arguments. + +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_do_table="t1"; +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_do_table="test.t1, t2"; +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_do_table="test.,t1"; + +--echo # Argument syntax. + +SET @@GLOBAL.replicate_do_table="test.t1,,,,,test.t3"; +SELECT @@GLOBAL.replicate_do_table; +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_do_table'; + +SET @@GLOBAL.replicate_do_table="test.t1,,,test2.t2,,,test.t3"; +SELECT @@GLOBAL.replicate_do_table; + +SET @@GLOBAL.replicate_do_table=""; +SELECT @@GLOBAL.replicate_do_table; + +--echo # Cleanup. +SET @@GLOBAL.replicate_do_table = @save_replicate_do_table; diff --git a/mysql-test/suite/sys_vars/t/replicate_ignore_db_basic.test b/mysql-test/suite/sys_vars/t/replicate_ignore_db_basic.test new file mode 100644 index 0000000..3a0bc88 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/replicate_ignore_db_basic.test @@ -0,0 +1,39 @@ +--source include/not_embedded.inc + +--echo # +--echo # Basic testing of replicate_ignore_db. +--echo # + +SET @save_replicate_ignore_db = @@GLOBAL.replicate_ignore_db; +SELECT @save_replicate_ignore_db; + +--echo # Scope. + +--error ER_GLOBAL_VARIABLE +SET @@SESSION.replicate_ignore_db = ""; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@SESSION.replicate_ignore_db; + +--echo # Incorrect type. + +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_ignore_db=1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_ignore_db=1.1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_ignore_db=1e1; + +--echo # Argument syntax. + +SET @@GLOBAL.replicate_ignore_db="db1,,,,,db3"; +SELECT @@GLOBAL.replicate_ignore_db; +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_ignore_db'; + +SET @@GLOBAL.replicate_ignore_db="db1,,,db2,,,db3"; +SELECT @@GLOBAL.replicate_ignore_db; + +SET @@GLOBAL.replicate_ignore_db=""; +SELECT @@GLOBAL.replicate_ignore_db; + +--echo # Cleanup. +SET @@GLOBAL.replicate_ignore_db = @save_replicate_ignore_db; diff --git a/mysql-test/suite/sys_vars/t/replicate_ignore_table_basic.test b/mysql-test/suite/sys_vars/t/replicate_ignore_table_basic.test new file mode 100644 index 0000000..aebe907 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/replicate_ignore_table_basic.test @@ -0,0 +1,48 @@ +--source include/not_embedded.inc + +--echo # +--echo # Basic testing of replicate_ignore_table. +--echo # + +SET @save_replicate_ignore_table = @@GLOBAL.replicate_ignore_table; +SELECT @save_replicate_ignore_table; + +--echo # Scope. + +--error ER_GLOBAL_VARIABLE +SET @@SESSION.replicate_ignore_table = ""; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@SESSION.replicate_ignore_table; + +--echo # Incorrect type. + +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_ignore_table=1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_ignore_table=1.1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_ignore_table=1e1; + +--echo # Incorrect arguments. + +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_ignore_table="t1"; +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_ignore_table="test.t1, t2"; +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_ignore_table="test.,t1"; + +--echo # Argument syntax. + +SET @@GLOBAL.replicate_ignore_table="test.t1,,,,,test.t3"; +SELECT @@GLOBAL.replicate_ignore_table; +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_ignore_table'; + +SET @@GLOBAL.replicate_ignore_table="test.t1,,,test2.t2,,,test.t3"; +SELECT @@GLOBAL.replicate_ignore_table; + +SET @@GLOBAL.replicate_ignore_table=""; +SELECT @@GLOBAL.replicate_ignore_table; + +--echo # Cleanup. +SET @@GLOBAL.replicate_ignore_table = @save_replicate_ignore_table; diff --git a/mysql-test/suite/sys_vars/t/replicate_wild_do_table_basic.test b/mysql-test/suite/sys_vars/t/replicate_wild_do_table_basic.test new file mode 100644 index 0000000..b96a62f --- /dev/null +++ b/mysql-test/suite/sys_vars/t/replicate_wild_do_table_basic.test @@ -0,0 +1,48 @@ +--source include/not_embedded.inc + +--echo # +--echo # Basic testing of replicate_wild_do_table. +--echo # + +SET @save_replicate_wild_do_table = @@GLOBAL.replicate_wild_do_table; +SELECT @save_replicate_wild_do_table; + +--echo # Scope. + +--error ER_GLOBAL_VARIABLE +SET @@SESSION.replicate_wild_do_table = ""; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@SESSION.replicate_wild_do_table; + +--echo # Incorrect type. + +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_wild_do_table=1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_wild_do_table=1.1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_wild_do_table=1e1; + +--echo # Incorrect arguments. + +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_wild_do_table="t1"; +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_wild_do_table="test.t, t2"; +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_wild_do_table="test.,t1"; + +--echo # Argument syntax. + +SET @@GLOBAL.replicate_wild_do_table="test.%,,,,,test.t3"; +SELECT @@GLOBAL.replicate_wild_do_table; +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_wild_do_table'; + +SET @@GLOBAL.replicate_wild_do_table="test.t1,,,test2.%,,,test.t3"; +SELECT @@GLOBAL.replicate_wild_do_table; + +SET @@GLOBAL.replicate_wild_do_table=""; +SELECT @@GLOBAL.replicate_wild_do_table; + +--echo # Cleanup. +SET @@GLOBAL.replicate_wild_do_table = @save_replicate_wild_do_table; diff --git a/mysql-test/suite/sys_vars/t/replicate_wild_ignore_table_basic.test b/mysql-test/suite/sys_vars/t/replicate_wild_ignore_table_basic.test new file mode 100644 index 0000000..2900dea --- /dev/null +++ b/mysql-test/suite/sys_vars/t/replicate_wild_ignore_table_basic.test @@ -0,0 +1,48 @@ +--source include/not_embedded.inc + +--echo # +--echo # Basic testing of replicate_wild_ignore_table. +--echo # + +SET @save_replicate_wild_ignore_table = @@GLOBAL.replicate_wild_ignore_table; +SELECT @save_replicate_wild_ignore_table; + +--echo # Scope. + +--error ER_GLOBAL_VARIABLE +SET @@SESSION.replicate_wild_ignore_table = ""; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@SESSION.replicate_wild_ignore_table; + +--echo # Incorrect type. + +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_wild_ignore_table=1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_wild_ignore_table=1.1; +--error ER_WRONG_TYPE_FOR_VAR +SET @@GLOBAL.replicate_wild_ignore_table=1e1; + +--echo # Incorrect arguments. + +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_wild_ignore_table="t1"; +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_wild_ignore_table="test.t, t2"; +--error ER_WRONG_ARGUMENTS +SET @@GLOBAL.replicate_wild_ignore_table="test.,t1"; + +--echo # Argument syntax. + +SET @@GLOBAL.replicate_wild_ignore_table="test.%,,,,,test.t3"; +SELECT @@GLOBAL.replicate_wild_ignore_table; +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='replicate_wild_ignore_table'; + +SET @@GLOBAL.replicate_wild_ignore_table="test.t1,,,test2.%,,,test.t3"; +SELECT @@GLOBAL.replicate_wild_ignore_table; + +SET @@GLOBAL.replicate_wild_ignore_table=""; +SELECT @@GLOBAL.replicate_wild_ignore_table; + +--echo # Cleanup. +SET @@GLOBAL.replicate_wild_ignore_table = @save_replicate_wild_ignore_table; diff --git a/sql/rpl_filter.cc b/sql/rpl_filter.cc index e8b7837..81ef571 100644 --- a/sql/rpl_filter.cc +++ b/sql/rpl_filter.cc @@ -42,8 +42,8 @@ Rpl_filter::~Rpl_filter() free_string_array(&wild_do_table); if (wild_ignore_table_inited) free_string_array(&wild_ignore_table); - free_list(&do_db); - free_list(&ignore_db); + free_string_list(&do_db); + free_string_list(&ignore_db); free_list(&rewrite_db); } @@ -255,6 +255,47 @@ Rpl_filter::is_on() } +/** + Parse and add the given comma-separated sequence of filter rules. + + @param spec Comma-separated sequence of filter rules. + @param add Callback member function to add a filter rule. + + @return true if error, false otherwise. +*/ + +int +Rpl_filter::parse_filter_rule(const char* spec, Add_filter add) +{ + int status= 0; + char *arg, *ptr, *str; + + if (! (ptr= my_strdup(spec, MYF(MY_WME)))) + return true; + + str= ptr; + + while ((arg= strsep(&str, ",")) != NULL) + { + if (arg[0] == '\0') + continue; + + /* Skip leading spaces. */ + while (my_isspace(system_charset_info, *arg)) + arg++; + + status= (this->*add)(arg); + + if (status) + break; + } + + my_free(ptr); + + return status; +} + + int Rpl_filter::add_do_table(const char* table_spec) { @@ -277,6 +318,46 @@ Rpl_filter::add_ignore_table(const char* table_spec) } +int +Rpl_filter::set_do_table(const char* table_spec) +{ + int status; + + if (do_table_inited) + my_hash_reset(&do_table); + + status= parse_filter_rule(table_spec, &Rpl_filter::add_do_table); + + if (!do_table.records) + { + my_hash_free(&do_table); + do_table_inited= 0; + } + + return status; +} + + +int +Rpl_filter::set_ignore_table(const char* table_spec) +{ + int status; + + if (ignore_table_inited) + my_hash_reset(&ignore_table); + + status= parse_filter_rule(table_spec, &Rpl_filter::add_ignore_table); + + if (!ignore_table.records) + { + my_hash_free(&ignore_table); + ignore_table_inited= 0; + } + + return status; +} + + int Rpl_filter::add_wild_do_table(const char* table_spec) { @@ -299,6 +380,46 @@ Rpl_filter::add_wild_ignore_table(const char* table_spec) } +int +Rpl_filter::set_wild_do_table(const char* table_spec) +{ + int status; + + if (wild_do_table_inited) + free_string_array(&wild_do_table); + + status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_do_table); + + if (!wild_do_table.elements) + { + delete_dynamic(&wild_do_table); + wild_do_table_inited= 0; + } + + return status; +} + + +int +Rpl_filter::set_wild_ignore_table(const char* table_spec) +{ + int status; + + if (wild_ignore_table_inited) + free_string_array(&wild_ignore_table); + + status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_ignore_table); + + if (!wild_ignore_table.elements) + { + delete_dynamic(&wild_ignore_table); + wild_ignore_table_inited= 0; + } + + return status; +} + + void Rpl_filter::add_db_rewrite(const char* from_db, const char* to_db) { @@ -347,25 +468,59 @@ Rpl_filter::add_wild_table_rule(DYNAMIC_ARRAY* a, const char* table_spec) } -void +int +Rpl_filter::add_string_list(I_List *list, const char* spec) +{ + char *str; + i_string *node; + + if (! (str= my_strdup(spec, MYF(MY_WME)))) + return true; + + if (! (node= new i_string(str))) + { + my_free(str); + return true; + } + + list->push_back(node); + + return false; +} + + +int Rpl_filter::add_do_db(const char* table_spec) { DBUG_ENTER("Rpl_filter::add_do_db"); - i_string *db = new i_string(table_spec); - do_db.push_back(db); - DBUG_VOID_RETURN; + DBUG_RETURN(add_string_list(&do_db, table_spec)); } -void +int Rpl_filter::add_ignore_db(const char* table_spec) { DBUG_ENTER("Rpl_filter::add_ignore_db"); - i_string *db = new i_string(table_spec); - ignore_db.push_back(db); - DBUG_VOID_RETURN; + DBUG_RETURN(add_string_list(&ignore_db, table_spec)); +} + + +int +Rpl_filter::set_do_db(const char* db_spec) +{ + free_string_list(&do_db); + return parse_filter_rule(db_spec, &Rpl_filter::add_do_db); } + +int +Rpl_filter::set_ignore_db(const char* db_spec) +{ + free_string_list(&ignore_db); + return parse_filter_rule(db_spec, &Rpl_filter::add_ignore_db); +} + + extern "C" uchar *get_table_key(const uchar *, size_t *, my_bool); extern "C" void free_table_ent(void* a); @@ -440,6 +595,23 @@ Rpl_filter::free_string_array(DYNAMIC_ARRAY *a) } +void +Rpl_filter::free_string_list(I_List *l) +{ + void *ptr; + i_string *tmp; + + while ((tmp= l->get())) + { + ptr= (void *) tmp->ptr; + my_free(ptr); + delete tmp; + } + + l->empty(); +} + + /* Builds a String from a HASH of TABLE_RULE_ENT. Cannot be used for any other hash, as it assumes that the hash entries are TABLE_RULE_ENT. @@ -549,3 +721,37 @@ Rpl_filter::get_ignore_db() { return &ignore_db; } + + +void +Rpl_filter::db_rule_ent_list_to_str(String* str, I_List* list) +{ + I_List_iterator it(*list); + i_string* s; + + str->length(0); + + while ((s= it++)) + { + str->append(s->ptr); + str->append(','); + } + + // Remove last ',' + if (!str->is_empty()) + str->chop(); +} + + +void +Rpl_filter::get_do_db(String* str) +{ + db_rule_ent_list_to_str(str, get_do_db()); +} + + +void +Rpl_filter::get_ignore_db(String* str) +{ + db_rule_ent_list_to_str(str, get_ignore_db()); +} diff --git a/sql/rpl_filter.h b/sql/rpl_filter.h index b3a6a07..c05633c 100644 --- a/sql/rpl_filter.h +++ b/sql/rpl_filter.h @@ -59,11 +59,20 @@ public: int add_do_table(const char* table_spec); int add_ignore_table(const char* table_spec); + int set_do_table(const char* table_spec); + int set_ignore_table(const char* table_spec); + int add_wild_do_table(const char* table_spec); int add_wild_ignore_table(const char* table_spec); - void add_do_db(const char* db_spec); - void add_ignore_db(const char* db_spec); + int set_wild_do_table(const char* table_spec); + int set_wild_ignore_table(const char* table_spec); + + int add_do_db(const char* db_spec); + int add_ignore_db(const char* db_spec); + + int set_do_db(const char* db_spec); + int set_ignore_db(const char* db_spec); void add_db_rewrite(const char* from_db, const char* to_db); @@ -80,6 +89,9 @@ public: I_List* get_do_db(); I_List* get_ignore_db(); + void get_do_db(String* str); + void get_ignore_db(String* str); + private: bool table_rules_on; @@ -89,13 +101,21 @@ private: int add_table_rule(HASH* h, const char* table_spec); int add_wild_table_rule(DYNAMIC_ARRAY* a, const char* table_spec); + typedef int (Rpl_filter::*Add_filter)(char const*); + + int parse_filter_rule(const char* spec, Add_filter func); + void free_string_array(DYNAMIC_ARRAY *a); + void free_string_list(I_List *l); void table_rule_ent_hash_to_str(String* s, HASH* h, bool inited); void table_rule_ent_dynamic_array_to_str(String* s, DYNAMIC_ARRAY* a, bool inited); + void db_rule_ent_list_to_str(String* s, I_List* l); TABLE_RULE_ENT* find_wild(DYNAMIC_ARRAY *a, const char* key, int len); + int add_string_list(I_List *list, const char* spec); + /* Those 4 structures below are uninitialized memory unless the corresponding *_inited variables are "true". diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 239691c..14e3e21 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3108,6 +3108,150 @@ static Sys_var_mybool Sys_relay_log_recovery( "processed", GLOBAL_VAR(relay_log_recovery), CMD_LINE(OPT_ARG), DEFAULT(FALSE)); +bool Sys_var_rpl_filter::do_check(THD *thd, set_var *var) +{ + bool status; + + mysql_mutex_lock(&LOCK_active_mi); + mysql_mutex_lock(&active_mi->rli.run_lock); + + status= active_mi->rli.slave_running; + + mysql_mutex_unlock(&active_mi->rli.run_lock); + mysql_mutex_unlock(&LOCK_active_mi); + + if (status) + my_error(ER_SLAVE_MUST_STOP, MYF(0)); + else + status= Sys_var_charptr::do_string_check(thd, var, charset(thd)); + + return status; +} + +bool Sys_var_rpl_filter::global_update(THD *thd, set_var *var) +{ + bool slave_running, status= false; + + mysql_mutex_lock(&LOCK_active_mi); + mysql_mutex_lock(&active_mi->rli.run_lock); + + if (! (slave_running= active_mi->rli.slave_running)) + status= set_filter_value(var->save_result.string_value.str); + + mysql_mutex_unlock(&active_mi->rli.run_lock); + mysql_mutex_unlock(&LOCK_active_mi); + + if (slave_running) + my_error(ER_SLAVE_MUST_STOP, MYF(0)); + + return slave_running || status; +} + +bool Sys_var_rpl_filter::set_filter_value(const char *value) +{ + bool status= true; + + switch (opt_id) { + case OPT_REPLICATE_DO_DB: + status= rpl_filter->set_do_db(value); + break; + case OPT_REPLICATE_DO_TABLE: + status= rpl_filter->set_do_table(value); + break; + case OPT_REPLICATE_IGNORE_DB: + status= rpl_filter->set_ignore_db(value); + break; + case OPT_REPLICATE_IGNORE_TABLE: + status= rpl_filter->set_ignore_table(value); + break; + case OPT_REPLICATE_WILD_DO_TABLE: + status= rpl_filter->set_wild_do_table(value); + break; + case OPT_REPLICATE_WILD_IGNORE_TABLE: + status= rpl_filter->set_wild_ignore_table(value); + break; + } + + return status; +} + +uchar *Sys_var_rpl_filter::global_value_ptr(THD *thd, LEX_STRING *base) +{ + char buf[256]; + String tmp(buf, sizeof(buf), &my_charset_bin); + + tmp.length(0); + + mysql_mutex_lock(&LOCK_active_mi); + mysql_mutex_lock(&active_mi->rli.run_lock); + + switch (opt_id) { + case OPT_REPLICATE_DO_DB: + rpl_filter->get_do_db(&tmp); + break; + case OPT_REPLICATE_DO_TABLE: + rpl_filter->get_do_table(&tmp); + break; + case OPT_REPLICATE_IGNORE_DB: + rpl_filter->get_ignore_db(&tmp); + break; + case OPT_REPLICATE_IGNORE_TABLE: + rpl_filter->get_ignore_table(&tmp); + break; + case OPT_REPLICATE_WILD_DO_TABLE: + rpl_filter->get_wild_do_table(&tmp); + break; + case OPT_REPLICATE_WILD_IGNORE_TABLE: + rpl_filter->get_wild_ignore_table(&tmp); + break; + } + + mysql_mutex_unlock(&active_mi->rli.run_lock); + mysql_mutex_unlock(&LOCK_active_mi); + + return (uchar *) thd->strmake(tmp.ptr(), tmp.length()); +} + +static Sys_var_rpl_filter Sys_replicate_do_db( + "replicate_do_db", OPT_REPLICATE_DO_DB, + "Tell the slave to restrict replication to updates of tables " + "whose names appear in the comma-separated list. For " + "statement-based replication, only the default database (that " + "is, the one selected by USE) is considered, not any explicitly " + "mentioned tables in the query. For row-based replication, the " + "actual names of table(s) being updated are checked."); + +static Sys_var_rpl_filter Sys_replicate_do_table( + "replicate_do_table", OPT_REPLICATE_DO_TABLE, + "Tells the slave to restrict replication to tables in the " + "comma-separated list."); + +static Sys_var_rpl_filter Sys_replicate_ignore_db( + "replicate_ignore_db", OPT_REPLICATE_IGNORE_DB, + "Tell the slave to restrict replication to updates of tables " + "whose names do not appear in the comma-separated list. For " + "statement-based replication, only the default database (that " + "is, the one selected by USE) is considered, not any explicitly " + "mentioned tables in the query. For row-based replication, the " + "actual names of table(s) being updated are checked."); + +static Sys_var_rpl_filter Sys_replicate_ignore_table( + "replicate_ignore_table", OPT_REPLICATE_IGNORE_TABLE, + "Tells the slave thread not to replicate any statement that " + "updates the specified table, even if any other tables might be " + "updated by the same statement."); + +static Sys_var_rpl_filter Sys_replicate_wild_do_table( + "replicate_wild_do_table", OPT_REPLICATE_WILD_DO_TABLE, + "Tells the slave thread to restrict replication to statements " + "where any of the updated tables match the specified database " + "and table name patterns."); + +static Sys_var_rpl_filter Sys_replicate_wild_ignore_table( + "replicate_wild_ignore_table", OPT_REPLICATE_WILD_IGNORE_TABLE, + "Tells the slave thread to not replicate to the tables that " + "match the given wildcard pattern."); + static Sys_var_charptr Sys_slave_load_tmpdir( "slave_load_tmpdir", "The location where the slave should put " "its temporary files when replicating a LOAD DATA INFILE command", diff --git a/sql/sys_vars.h b/sql/sys_vars.h index bde4820..5737f44 100644 --- a/sql/sys_vars.h +++ b/sql/sys_vars.h @@ -390,11 +390,11 @@ public: my_free(global_var(char*)); flags&= ~ALLOCATED; } - bool do_check(THD *thd, set_var *var) + static bool do_string_check(THD *thd, set_var *var, CHARSET_INFO *charset) { char buff[STRING_BUFFER_USUAL_SIZE], buff2[STRING_BUFFER_USUAL_SIZE]; - String str(buff, sizeof(buff), charset(thd)); - String str2(buff2, sizeof(buff2), charset(thd)), *res; + String str(buff, sizeof(buff), charset); + String str2(buff2, sizeof(buff2), charset), *res; if (!(res=var->value->val_str(&str))) var->save_result.string_value.str= 0; @@ -402,10 +402,10 @@ public: { uint32 unused; if (String::needs_conversion(res->length(), res->charset(), - charset(thd), &unused)) + charset, &unused)) { uint errors; - str2.copy(res->ptr(), res->length(), res->charset(), charset(thd), + str2.copy(res->ptr(), res->length(), res->charset(), charset, &errors); res=&str2; @@ -416,6 +416,8 @@ public: return false; } + bool do_check(THD *thd, set_var *var) + { return do_string_check(thd, var, charset(thd)); } bool session_update(THD *thd, set_var *var) { DBUG_ASSERT(FALSE); @@ -510,6 +512,44 @@ protected: } }; +class Sys_var_rpl_filter: public sys_var +{ +private: + int opt_id; + +public: + Sys_var_rpl_filter(const char *name, int getopt_id, const char *comment) + : sys_var(&all_sys_vars, name, comment, sys_var::GLOBAL, 0, -1, + NO_ARG, SHOW_CHAR, 0, NULL, VARIABLE_NOT_IN_BINLOG, + NULL, NULL, 0, NULL, PARSE_NORMAL), opt_id(getopt_id) + { + option.var_type= GET_STR; + } + + bool check_update_type(Item_result type) + { return type != STRING_RESULT; } + + bool do_check(THD *thd, set_var *var); + + void session_save_default(THD *thd, set_var *var) + { DBUG_ASSERT(FALSE); } + + void global_save_default(THD *thd, set_var *var) + { DBUG_ASSERT(FALSE); } + + bool session_update(THD *thd, set_var *var) + { + DBUG_ASSERT(FALSE); + return true; + } + + bool global_update(THD *thd, set_var *var); + +protected: + uchar *global_value_ptr(THD *thd, LEX_STRING *base); + bool set_filter_value(const char *value); +}; + /** The class for string variables. Useful for strings that aren't necessarily \0-terminated. Otherwise the same as Sys_var_charptr. diff --git a/mysql-test/suite/rpl/r/rpl_start_slave_deadlock_sys_vars.result b/mysql-test/suite/rpl/r/rpl_start_slave_deadlock_sys_vars.result new file mode 100644 index 0000000..f69a323 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_start_slave_deadlock_sys_vars.result @@ -0,0 +1,31 @@ +include/master-slave.inc +[connection master] +# connection: slave +SET @save_slave_net_timeout = @@GLOBAL.slave_net_timeout; +STOP SLAVE; +include/wait_for_slave_to_stop.inc +# open an extra connection to the slave +# connection: slave2 +# set debug synchronization point +SET DEBUG_SYNC='fix_slave_net_timeout SIGNAL parked WAIT_FOR go'; +# attempt to set slave_net_timeout, will wait on sync point +SET @@GLOBAL.slave_net_timeout = 100; +# connection: slave +SET DEBUG_SYNC='now WAIT_FOR parked'; +# connection: slave1 +# attempt to start the SQL thread +START SLAVE SQL_THREAD; +# connection: slave +# wait until SQL thread has been started +# sleep a bit so that the SQL thread THD handle is initialized +# signal the set slave_net_timeout to continue +SET DEBUG_SYNC='now SIGNAL go'; +# connection: slave2 +# reap result of set slave_net_timeout +# connection: slave1 +# reap result of starting the SQL thread +# disconnect: slave2 +# connection: slave +# cleanup +SET @@GLOBAL.slave_net_timeout = @save_slave_net_timeout; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_slave_deadlock_sys_vars.test b/mysql-test/suite/rpl/t/rpl_start_slave_deadlock_sys_vars.test new file mode 100644 index 0000000..dfcec67 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_start_slave_deadlock_sys_vars.test @@ -0,0 +1,57 @@ +source include/master-slave.inc; +source include/have_debug_sync.inc; + +--echo # connection: slave +connection slave; +SET @save_slave_net_timeout = @@GLOBAL.slave_net_timeout; +STOP SLAVE; +source include/wait_for_slave_to_stop.inc; + +--echo # open an extra connection to the slave +connect(slave2,127.0.0.1,root,,test,$SLAVE_MYPORT,); +--echo # connection: slave2 +--echo # set debug synchronization point +SET DEBUG_SYNC='fix_slave_net_timeout SIGNAL parked WAIT_FOR go'; +--echo # attempt to set slave_net_timeout, will wait on sync point +--send SET @@GLOBAL.slave_net_timeout = 100 + +--echo # connection: slave +connection slave; +SET DEBUG_SYNC='now WAIT_FOR parked'; + +--echo # connection: slave1 +connection slave1; +--echo # attempt to start the SQL thread +--send START SLAVE SQL_THREAD + +--echo # connection: slave +connection slave; +--echo # wait until SQL thread has been started +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for slave thread to start" and info = "START SLAVE SQL_THREAD"; +--source include/wait_condition.inc +--echo # sleep a bit so that the SQL thread THD handle is initialized +sleep 2; +--echo # signal the set slave_net_timeout to continue +SET DEBUG_SYNC='now SIGNAL go'; + +--echo # connection: slave2 +connection slave2; +--echo # reap result of set slave_net_timeout +--reap + +--echo # connection: slave1 +connection slave1; +--echo # reap result of starting the SQL thread +--reap + +--echo # disconnect: slave2 +disconnect slave2; + +--echo # connection: slave +connection slave; +--echo # cleanup +SET @@GLOBAL.slave_net_timeout = @save_slave_net_timeout; + +source include/rpl_end.inc; diff --git a/sql/slave.cc b/sql/slave.cc index d665c7b..f12b5aa 100644 --- a/sql/slave.cc +++ b/sql/slave.cc @@ -2768,6 +2768,8 @@ pthread_handler_t handle_slave_io(void *arg) mysql= NULL ; retry_count= 0; + thd= new THD; // note that contructor of THD uses DBUG_ ! + mysql_mutex_lock(&mi->run_lock); /* Inform waiting threads that slave has started */ mi->slave_run_id++; @@ -2776,7 +2778,6 @@ pthread_handler_t handle_slave_io(void *arg) mi->events_till_disconnect = disconnect_slave_event_count; #endif - thd= new THD; // note that contructor of THD uses DBUG_ ! THD_CHECK_SENTRY(thd); mi->io_thd = thd; @@ -3189,6 +3190,8 @@ pthread_handler_t handle_slave_sql(void *arg) my_thread_init(); DBUG_ENTER("handle_slave_sql"); + thd = new THD; // note that contructor of THD uses DBUG_ ! + DBUG_ASSERT(rli->inited); mysql_mutex_lock(&rli->run_lock); DBUG_ASSERT(!rli->slave_running); @@ -3197,7 +3200,6 @@ pthread_handler_t handle_slave_sql(void *arg) rli->events_till_abort = abort_slave_event_count; #endif - thd = new THD; // note that contructor of THD uses DBUG_ ! thd->thread_stack = (char*)&thd; // remember where our stack is rli->sql_thd= thd; diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index f30fafb..703bc57 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -44,6 +44,7 @@ // mysql_user_table_is_in_short_password_format #include "derror.h" // read_texts #include "sql_base.h" // close_cached_tables +#include "debug_sync.h" // DEBUG_SYNC #ifdef WITH_PERFSCHEMA_STORAGE_ENGINE #include "../storage/perfschema/pfs_server.h" @@ -3128,6 +3129,8 @@ bool Sys_var_rpl_filter::do_check(THD *thd, set_var *var) { bool status; + mysql_mutex_assert_not_owner(&LOCK_global_system_variables); + mysql_mutex_lock(&LOCK_active_mi); mysql_mutex_lock(&active_mi->rli.run_lock); @@ -3144,22 +3147,43 @@ bool Sys_var_rpl_filter::do_check(THD *thd, set_var *var) return status; } -bool Sys_var_rpl_filter::global_update(THD *thd, set_var *var) +void Sys_var_rpl_filter::lock(void) { - bool slave_running, status= false; + /* + Starting a slave thread causes the new thread to attempt to + acquire LOCK_global_system_variables (in THD::init) while + LOCK_active_mi is being held by the thread that initiated + the process. In order to not violate the lock order, unlock + LOCK_global_system_variables before grabbing LOCK_active_mi. + */ + mysql_mutex_unlock(&LOCK_global_system_variables); mysql_mutex_lock(&LOCK_active_mi); mysql_mutex_lock(&active_mi->rli.run_lock); +} - if (! (slave_running= active_mi->rli.slave_running)) - status= set_filter_value(var->save_result.string_value.str); - +void Sys_var_rpl_filter::unlock(void) +{ mysql_mutex_unlock(&active_mi->rli.run_lock); mysql_mutex_unlock(&LOCK_active_mi); + mysql_mutex_lock(&LOCK_global_system_variables); +} + +bool Sys_var_rpl_filter::global_update(THD *thd, set_var *var) +{ + bool slave_running, status= false; + + lock(); + + if (! (slave_running= active_mi->rli.slave_running)) + status= set_filter_value(var->save_result.string_value.str); + if (slave_running) my_error(ER_SLAVE_MUST_STOP, MYF(0)); + unlock(); + return slave_running || status; } @@ -3198,8 +3222,7 @@ uchar *Sys_var_rpl_filter::global_value_ptr(THD *thd, LEX_STRING *base) tmp.length(0); - mysql_mutex_lock(&LOCK_active_mi); - mysql_mutex_lock(&active_mi->rli.run_lock); + lock(); switch (opt_id) { case OPT_REPLICATE_DO_DB: @@ -3222,8 +3245,7 @@ uchar *Sys_var_rpl_filter::global_value_ptr(THD *thd, LEX_STRING *base) break; } - mysql_mutex_unlock(&active_mi->rli.run_lock); - mysql_mutex_unlock(&LOCK_active_mi); + unlock(); return (uchar *) thd->strmake(tmp.ptr(), tmp.length()); } @@ -3276,6 +3298,9 @@ static Sys_var_charptr Sys_slave_load_tmpdir( static bool fix_slave_net_timeout(sys_var *self, THD *thd, enum_var_type type) { + DEBUG_SYNC(thd, "fix_slave_net_timeout"); + + mysql_mutex_unlock(&LOCK_global_system_variables); mysql_mutex_lock(&LOCK_active_mi); DBUG_PRINT("info", ("slave_net_timeout=%u mi->heartbeat_period=%.3f", slave_net_timeout, @@ -3285,6 +3310,7 @@ static bool fix_slave_net_timeout(sys_var *self, THD *thd, enum_var_type type) ER_SLAVE_HEARTBEAT_VALUE_OUT_OF_RANGE_MAX, ER(ER_SLAVE_HEARTBEAT_VALUE_OUT_OF_RANGE_MAX)); mysql_mutex_unlock(&LOCK_active_mi); + mysql_mutex_lock(&LOCK_global_system_variables); return false; } static Sys_var_uint Sys_slave_net_timeout( @@ -3311,6 +3337,7 @@ static bool check_slave_skip_counter(sys_var *self, THD *thd, set_var *var) } static bool fix_slave_skip_counter(sys_var *self, THD *thd, enum_var_type type) { + mysql_mutex_unlock(&LOCK_global_system_variables); mysql_mutex_lock(&LOCK_active_mi); mysql_mutex_lock(&active_mi->rli.run_lock); /* @@ -3326,6 +3353,7 @@ static bool fix_slave_skip_counter(sys_var *self, THD *thd, enum_var_type type) } mysql_mutex_unlock(&active_mi->rli.run_lock); mysql_mutex_unlock(&LOCK_active_mi); + mysql_mutex_lock(&LOCK_global_system_variables); return 0; } static Sys_var_uint Sys_slave_skip_counter( diff --git a/sql/sys_vars.h b/sql/sys_vars.h index df92067..00ac9b5 100644 --- a/sql/sys_vars.h +++ b/sql/sys_vars.h @@ -548,6 +548,8 @@ public: protected: uchar *global_value_ptr(THD *thd, LEX_STRING *base); bool set_filter_value(const char *value); + void lock(void); + void unlock(void); }; /**