Description:
When cross-database updates are needed the manual suggests we use replicate-wild-do-table:
====
"If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead."
http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replic...
====
Except, replicate-wild-do-table doesn't filter Stored Procedures/Functions:
====
"This option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events. To filter statements operating on the latter objects, use one or more of the --replicate-*-db options. "
http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replic...
====
But this setup is not recommended:
====
"To make it easier to determine what effect an option set will have, it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options."
http://dev.mysql.com/doc/refman/5.1/en/replication-rules.html
====
If we want to create a SP/SF on an non replicated slave, our slave will either break or become out of sync with:
Last_Errno: 1049
Last_Error: Error 'Unknown database 'db2'' on query. Default database: 'myfoo'. Query: 'CREATE DEFINER=`dba`@`localhost` FUNCTION `sp_test`(land varchar(40)) RETURNS varchar(2) CHARSET latin1
DETERMINISTIC ...
It doesn't seem possible to get cross-database updates and SPs/SFs working.
How to repeat:
On master, with no binlog-*-db rules and with binlog_format=STATEMENT.
All mysqld are running 5.1.50-enterprise-gpl-advanced-log x86_64 on Linux.
================================================================
1) Breaking replication with this on slave1 my.cnf:
replicate-wild-do-table=db1.%
On the master we create a db2 and put a SF under it, which we understand is not to be replicated:
master [localhost] {msandbox} ((none)) > SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
master [localhost] {msandbox} ((none)) > CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} ((none)) > CREATE DATABASE db2;
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} ((none)) > USE db2
Database changed
master [localhost] {msandbox} (db2) > DELIMITER ##
master [localhost] {msandbox} (db2) > CREATE DEFINER=`dba`@`localhost` FUNCTION `sp_test`(land varchar(40)) RETURNS varchar(2) CHARSET latin1 DETERMINISTIC COMMENT 'test' begin if (land='Deutschland') then return 'DE'; elseif (land='Niederlande') then return 'NL'; elseif (land='Vereinigtes Königreich') then return 'UK'; elseif (land='Island') then return 'IS'; elseif (land='Tschechische Republik') then return 'CZ'; elseif (land='Schweden') then return 'SE'; else return '??'; end if; end##
Query OK, 0 rows affected, 1 warning (0.00 sec)
master [localhost] {msandbox} (db2) > DELIMITER ;
Now we look at the slave and see that replication broke:
slave1 [localhost] {msandbox} ((none)) > SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: msandbox
Master_Port: 26165
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 792
Relay_Log_File: mysql_sandbox26166-relay-bin.000002
Relay_Log_Pos: 413
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: db1.%
Replicate_Wild_Ignore_Table:
Last_Errno: 1049
Last_Error: Error 'Unknown database 'db2'' on query. Default database: 'db2'. Query: 'CREATE DEFINER=`dba`@`localhost` FUNCTION `sp_test`(land varchar(40)) RETURNS varchar(2) CHARSET latin1
DETERMINISTIC
COMMENT 'test'
begin if (land='Deutschland') then return 'DE'; elseif (land='Niederlande') then return 'NL'; elseif (land='Vereinigtes Königreich') then return 'UK'; elseif (land='Island') then return 'IS'; elseif (land='Tschechische Republik') then return 'CZ'; elseif (land='Schweden') then return 'SE'; else return '??'; end if; end'
Skip_Counter: 0
Exec_Master_Log_Pos: 268
Relay_Log_Space: 1105
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1049
Last_SQL_Error: Error 'Unknown database 'db2'' on query. Default database: 'db2'. Query: 'CREATE DEFINER=`dba`@`localhost` FUNCTION `sp_test`(land varchar(40)) RETURNS varchar(2) CHARSET latin1
DETERMINISTIC
COMMENT 'test'
begin if (land='Deutschland') then return 'DE'; elseif (land='Niederlande') then return 'NL'; elseif (land='Vereinigtes Königreich') then return 'UK'; elseif (land='Island') then return 'IS'; elseif (land='Tschechische Republik') then return 'CZ'; elseif (land='Schweden') then return 'SE'; else return '??'; end if; end'
1 row in set (0.00 sec)
Conclusion: CREATE DATABASE db2 was filtered but not CREATE FUNCTION (on db2).
================================================================
2) Replication out of sync with slave2 my.cnf:
replicate-do-db=db1
replicate-wild-do-table=db1.%
If we use this replication doesn't break as above, because the CREATE FUNCTION is filtered by replicate-do-db=db1:
slave2 [localhost] {msandbox} ((none)) > SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| test |
+--------------------+
4 rows in set (0.01 sec)
slave2 [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: msandbox
Master_Port: 26165
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 792
Relay_Log_File: mysql_sandbox26167-relay-bin.000002
Relay_Log_Pos: 937
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: db1.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 792
Relay_Log_Space: 1105
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
slave2 [localhost] {msandbox} ((none)) > SELECT * FROM information_schema.ROUTINES\G
Empty set (0.00 sec)
However, this will break cross database updates, example:
master [localhost] {msandbox} (db2) > USE db1;
Database changed
master [localhost] {msandbox} (db1) > CREATE TABLE tbl (id INT);Query OK, 0 rows affected (0.01 sec)
master [localhost] {msandbox} (db1) > USE db2;
Database changed
master [localhost] {msandbox} (db2) > INSERT INTO db1.tbl VALUES (1);
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} (db2) > SELECT * FROM db1.tbl;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
On slave2 replication doesn't break but the INSERT is filtered:
slave2 [localhost] {msandbox} ((none)) > SELECT * FROM db1.tbl;
Empty set (0.00 sec)
Suggested fix:
Not sure what the best way to solve this would be, but here are some possibilities, in order of what I think is best:
1) Add a filter option for Stored Procedures or Functions, eg:
replicate-wild-do-sp=db1.%
2) Have replicate-wild-do-table also filter SPs/SFs
3) Have replicate-wild-do-table not filter the CREATE DATABASE (I don't like this solution because of backwards compatibility)
