MySQL Bugs: #25482: GRANT statements are not replicated if you use "replicate-ignore-table"

Bug #25482	GRANT statements are not replicated if you use "replicate-ignore-table"
Submitted:	8 Jan 2007 23:36	Modified:	7 Apr 2007 19:14
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.32, 5.0.30, 4.1.19, 4.1.22, 4.1.18, 4.1.7	OS:	Windows (Windows, Linux)
Assigned to:	Magnus Blåudd	CPU Architecture:	Any
Tags:	"GRANT not replicated", bfsm_2007_01_18, grant, replicate-ignore-table, replicate-wild-ignore-table

Description:
GRANT statements are not replicated if you use "replicate-ignore-table" or "replicate-wild-ignore-table" options in your slave's my.cnf/my.ini file.

This occurs even if the tables being restricted have nothing to do with the tables or database in the GRANT statement.

How to repeat:
On Windows create two 5.0.32 installations and make one the master and the other
the slave, using different ports for each and different my.ini files roughly as
described at
http://dev.mysql.com/doc/mysql/en/multiple-windows-command-line-servers.html.

In the slave’s my.ini, add the following line:

replicate-ignore-table=db1.t1

Restart the slave.

On the master, issue a GRANT statement for any db/table, for example:

CREATE DATABASE IF NOT EXISTS db2;
CREATE TABLE IF NOT EXISTS db2.t2 (id INT(1));
GRANT SELECT ON db2.t2 TO 'user2'@'localhost' IDENTIFIED BY 'pass';

If you were to "SELECT User FROM mysql.user", then you would see user "user2" on the master.  You will not see this user on the slave with the same statement.  However, this GRANT should replicate to the slave, as that table is not being ignored with the "replicate-ignore-table".  Not to mention, it is not even the same database.

Now, remove the "replicate-ignore-table" entry, or comment it out, and run:

CREATE DATABASE IF NOT EXISTS db3;
CREATE TABLE IF NOT EXISTS db3.t3 (id INT(1));
GRANT SELECT ON db3.t3 TO 'user3'@'localhost' IDENTIFIED BY 'pass';

Now, you will see "user3" on both the master and slave.  It replicates fine.

SELECT User FROM mysql.user;

I have verified that the GRANT statements are written to the master's binary log with "SHOW BINLOG EVENTS;".

Also, this occurs with either replicate-ignore-table or replicate-wild-ignore-table.

Suggested fix:
Not sure, but as a work-around, you will have to either comment out the "replicate-ignore-table" and/or "replicate-wild-ignore-table" options or re-issue the GRANT command on the slave(s).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22735

ChangeSet@1.2625, 2007-03-23 10:04:03+01:00, msvensson@pilot.blaudden +3 -0
  Bug#25482 GRANT statements are not replicated if you use "replicate-ignore-table"
   - GRANT and REVOKE statments didn't have the "updating" flag set and
     thus statements with a table specified would not replicate if
     slave filtering rules where turned on.
     For example "GRANT ... ON test.t1 TO ..." would not replicate.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/23289

ChangeSet@1.2625, 2007-03-29 14:12:32+02:00, msvensson@pilot.blaudden +3 -0
  Bug#25482 GRANT statements are not replicated if you use "replicate-ignore-table"
   - GRANT and REVOKE statments didn't have the "updating" flag set and
     thus statements with a table specified would not replicate if
     slave filtering rules where turned on.
     For example "GRANT ... ON test.t1 TO ..." would not replicate.

Pushed into 4.1.23

Pushed into 5.0.40

Pushed into 5.1.18-beta

Noted in 4.1.23, 5.0.40, 5.1.18 changelogs.

GRANT statements were not replicated if the server was started with
the --replicate-ignore-table or --replicate-wild-ignore-table option.