Bug #68137 Privilege grants are replicated when slave is configured to ignore them
Submitted: 22 Jan 2013 7:43 Modified: 22 Jan 2013 18:55
Reporter: Kevan Carstensen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5, 5.5.31, 5.7.1 OS:Linux
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[22 Jan 2013 7:43] Kevan Carstensen
Description:
We have two DB servers configured to replicate in a master/slave
arrangement. Specifically, we have some tables in db1 the master server
that we want to have a copy of in db2 on the slave server. This is
achieved using the replicate-rewrite-db directive. As specified in the
docs, replicate-rewrite-db only works on certain queries, so some
operations don't replicate cleanly from master to slave. I noticed that
a statement like:

  GRANT SELECT ON d1.t1 TO 'foo'@'localhost' IDENTIFIED BY 'bar';

executed on the master breaks replication on the slave, yielding:

  130122  1:59:50 [ERROR] Slave SQL: Error 'Table 'd1.t1' doesn't exist' on query. Default database: ''. Query: 'grant select on d1.t1 to 'foo'@'localhost' identified by 'bar'', Error_code: 1146
  130122  1:59:50 [Warning] Slave: Table 'd1.t1' doesn't exist Error_code: 1146

This is consistent with the replicate-rewrite-db docs. As we have no
particular reason to replicate users/privilege grants from one DB to
another, we'd like to disable this. According to
http://dev.mysql.com/doc/refman/5.0/en/replication-features-userprivs.html,
we can achieve this by adding:

  replicate-wild-ignore-table=mysql.%

to the slave's my.cnf. We did this, but still get the same error with
the above GRANT statement. We're on 5.1.67, but I tested the procedure to reproduce the bug on 5.5.29  with the same results.

How to repeat:
- Configure two MySQL DB instances, master and slave.
- Execute 'CREATE DATABASE d1' on the master.
- Execute 'CREATE DATABASE d2' on the slave.
- Edit the master's my.cnf to specify binlog-format=mixed.
- Edit the slave's my.cnf to contain replicate-wild-ignore-table=mysql.% and replicate-rewrite-db="d1->d2"
- Configure the slave to replicate from the master according to http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
- Start replication by issuing START SLAVE on the slave
- On the master, issue USE d1; CREATE TABLE t1 (foo integer not null); confirm that d2.t1 exists on slave.
- Issue 'GRANT SELECT ON `d1`.`t1` TO 'foo'@'localhost' IDENTIFIED BY 'bar'; on the master.
- On the slave, execute 'SHOW SLAVE STATUS' to see whether replication is still working.

My expectation is that the slave would ignore the GRANT SELECT statement. Instead, in both 5.1.67 and 5.5.29, I get Error 'Table 'd1.t1' doesn't exist' on query. Default database: 'd2'. Query: 'grant select on `d1`.`t1` to 'foo'@'localhost' IDENTIFIED BY 'bar'
[22 Jan 2013 18:55] Sveta Smirnova
Thank you for the report.

Verified as described.
[20 Feb 2014 15:48] Jamie Taylor
I am also falling foul of this bug running 5.5.35, is there a way to restart replication and skip over the grant statement? The only way I can think of to get passed the error is to manually create the table/user combo on the slave and then remove it afterwards, but this is not desirable as there are likely to be several similar grant statements on different tables in the binlog.