| Bug #70877 | DCL statement is failing on slave with replicate-ignore-db=mysql | ||
|---|---|---|---|
| Submitted: | 11 Nov 2013 7:44 | Modified: | 19 Nov 2013 7:53 |
| Reporter: | Ramesh Sivaraman | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | MySQL 5.x, 5.5.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[11 Nov 2013 9:54]
MySQL Verification Team
Hello Ramesh, Thank you for the report. Verified as described. Thanks, Umesh
[11 Nov 2013 9:55]
MySQL Verification Team
Also see, Bug #9483
[17 Nov 2013 3:53]
Venkatesh Duggirala
Posted by developer: Hello Ramesh, Thank you for reporting the issue. We have looked into the scenario, please find our analysis below on the same. Setting --replicate-ignore-db=db_name means, In Statement-based replication, tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name. The same holds true in case of DDLs in Row-base replication as well. --replicate-ignore-db=db1 use db1; create table db2.t1(i int); => will *not* be replicated to slave though it is db2's t1. use db2; create table db1.t1(i int); => will be replicated to slave though it is db1' t1. In your example, there is no problem in the scenario mentioned. MySQL behaved the same as the way documentation says. Please note that grant/drop user were done when default database 'test' and replicate-ignore-db contains only 'mysql'. Even though they grant/drop user are related to 'mysql' database, they wont be filtered out as explained above. For more details on replication filters, please visit http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replic... Please let us know if you need more information on the same.
[19 Nov 2013 7:53]
Ramesh Sivaraman
Thanks Venkatesh for the info. As a feature request, is it possible to restrict these statements( specific to schemas in --replicate-ignore-db list) to write in binary files ?. So that we wont end up with replication slave error. -- Thanks, Ramesh
[21 Nov 2013 2:14]
Venkatesh Duggirala
Hello Ramesh, You can use --replicate-wild* filter rules to achieve the same. As bug9483 mentioned, one can govern replication of GRANT/REVOKE only via explicitly providing --replicate-wild-{ignore,do}-table=mysql.% I.e if one does not want to replicate GRANT/REVOKE, --replicate-wild-ingore-table=mysql.% is necessary.
Description: Normally in mysql replication Data Control Language (DCL) statements won’t replicate into slave if we are configuring replication with replicate-ignore-db=mysql. But if we are executing DCL statements from different database schema (other than mysql schema) in master, the event will replicate to slave. And the replication will break if we are trying to update/delete non-existing user row in slave. How to repeat: slave configuration mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql-test-01 Master_User: repl Master_Port: 3306 -- -- Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql -- -- Seconds_Behind_Master: 0 -- -- 1 row in set (0.00 sec) mysql> eg : 1, Create one user on master from diff database schema. mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec) mysql> grant usage on *.* to ram_test@'%'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user where user='ram_test' and host='%'; +----------+------+ | user | host | +----------+------+ | ram_test | % | +----------+------+ 1 row in set (0.00 sec) mysql> On slave also user creation DCL event will execute even after giving replicate-ignore-db=mysql parameter. If you look at the binlog event of user creation you can see ‘use `test`’ statement attached with that event. Since mysql is taking default database schema for binary logging this event will execute in slave without any issue. mysql> show binlog events in 'binary-log.000817' from 7275 limit 1; +-------------------+------+------------+-----------+-------------+------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+------+------------+-----------+-------------+------------------------------------------------+ | binary-log.000817 | 7275 | Query | 1000 | 7386 | use `test`; grant usage on *.* to ram_test@'%' | +-------------------+------+------------+-----------+-------------+------------------------------------------------+ 1 row in set (0.00 sec) mysql> On slave mysql> select user,host from mysql.user where user='ram_test' and host='%'; +----------+------+ | user | host | +----------+------+ | ram_test | % | +----------+------+ 1 row in set (0.00 sec) mysql> select @@hostname; +-------------------------+ | @@hostname | +-------------------------+ | mysql-test-02 | +-------------------------+ 1 row in set (0.00 sec) mysql> 2, There is a chance of replication error with this configuration. In this example I am dropping one user on master from diff database schema. mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec) mysql> drop user ram_test1@'%'; Query OK, 0 rows affected (0.00 sec) mysql> select @@hostname; +-------------------------+ | @@hostname | +-------------------------+ | mysql-test-01 | +-------------------------+ 1 row in set (0.00 sec) mysql> Here you can see replication failed with ‘Operation DROP USER failed’. This is because user ‘ram_test1′@’%’ was not present in slave instance. mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql-test-01 Master_User: repl Master_Port: 3306 -- -- Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: mysql -- -- Last_Errno: 1396 Last_Error: Error 'Operation DROP USER failed for 'ram_test1'@'%'' on query. Default database: 'test'. Query: 'drop user ram_test1@'%'' -- -- mysql> Suggested fix: Try to add "use `mysql`" command in binary log with DCL statements if we are in different schema other than mysql schema.