Bug #9483 grant/revoke replicated to the default db instead of 'mysql' breaks replication
Submitted: 30 Mar 2005 12:12 Modified: 2 May 2006 2:54
Reporter: Anders Henke Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:4.1.10 OS:Linux (Linux)
Assigned to: Mike Hillyer CPU Architecture:Any

[30 Mar 2005 12:12] Anders Henke
Any grant/revoke commands issued after a "use $somedatabase" are written to
the binlog, although the commands don't affect $somedatabase.

While not exactly, this bug might also relate to #180.

How to repeat:

mysql> use foo;
mysql> revoke insert,update on foo.* from bar@'%';

... is configured with "Replicate-Do-DB=foo"; so the database 'mysql' isn't replicated.

mysql> show slave status\G
            Replicate_Do_DB: foo
Replicate_Wild_Ignore_Table: foo.shadow%
                 Last_Errno: 1141
                 Last_Error: Error 'There is no such grant defined for user
'bar' on host '%'' on query. Default database: 'foo'. Query: 'revoke
insert,update on foo.* from bar@'%''

Suggested fix:
Temporarily switch the default database when administrative commands are being issued.
As the mysql-database itself isn't replicated, administrative commands affecting that database shouldn't be replicated  as well.

A colleague of mine had a similar problem with different commands and a different setup,
he has been told to use "replicate-wild-do-table=foo.%" instead of "replicate-do-db=foo" - which solved the issue for him.
I haven't checked back wether this would also solve this issue, but if it does, it might
be a good idea to internally map "replicate-do-db=$db" to "replicate-wild-do-table=$db.%".
[30 Mar 2005 12:15] Anders Henke
Changed to a much more descriptive subject.
I also haven't verified wether the same issue exists in the 4.0-tree.
[30 Mar 2005 20:33] Jorge del Conde
tested w/4.1.11 from bk
[3 Jan 2006 10:27] Andrei Elkin
The bug relates to the 180th. The latter fixed ignoring GRANT/REVOKE queries on the slave when the slave  started with --replicate-wild-ignore-table=mysql.%
In the same time it should be that
does the same. But it does not.
[3 Jan 2006 13:03] Andrei Elkin
GRANT/REVOKE belongs to `cross-database updates' [mysql.info:replication/6.8] class
in a sense they can affect different than default db.
Following that I would suggest always to govern their accepting/ignoring on the slave via
only, consequently --replicate-ignore-db=mysql is not applicable for GRANT/REVOKE.

A question remains how to treat `implicit' do/ignore rules. Particular example of
ignoring implicitly is when the slave rejects updates to any db not listed among do-db/table except the case when these lists are empty. The bug actually extends the exception to allow GRANT/REVOKE when the list is not emply and no .--replicate-wild-ingore-table=mysql.%.

I would suggest to fix this bug only in documentation to state that currently 
is just a workaround:
      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.
[6 Jan 2006 18:05] Bugs System
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:

[9 Jan 2006 18:51] Andrei Elkin
The bug has #980 one more ancestor. #980th test case did not cover REVOKE query so that I have decided to commit the test with name rpl_ignore_grant_revoke.test
[10 Jan 2006 11:44] Bugs System
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:

[10 Jan 2006 13:20] Andrei Elkin
After specific to grant test was discovered I eliminated its part from mine and renamed 
it to be rpl_ignore_revoke.test.
Bug status is being turned into Doc.
[10 Jan 2006 13:43] Andrei Elkin
Doc team please use the last paragraph of  comments of [3 Jan 14:03] to
improve my wording.
[10 Jan 2006 17:39] Andrei Elkin
I beg a pardon for playing with the version. The orig value is restored.
[18 Jan 2006 18:55] Mike Hillyer
Cannot add to changelog and close without version numbers of the bugfix.
[21 Jan 2006 16:12] Andrei Elkin
I am sorry for
ChangeSet@1.1981.22.1, 2006-01-10 13:44:08+02:00, aelkin@mysql.com
was committed without magic 'Bug#9483' word in comments.
The latest tag for 5.0 tree at that time was 
[23 Jan 2006 18:18] Andrei Elkin
Fixed mysql-5.0.18.
[26 Jan 2006 2:24] Mike Hillyer
One more question: Is there a change here? Or is this more a matter of mentioning that --replication-do must be used? Does this require a changelog entry or a refman update? If so on either, what is needed?
[2 Feb 2006 5:13] Mike Hillyer
Changing to documentation bug, assigning to myself.
[2 May 2006 2:54] Mike Hillyer
Added note to the FAQ section about preventing the replication of GRANT/REVOKE statements:

      <emphasis role="bold">Q</emphasis>: How do I prevent GRANT and
          REVOKE statements from replicating to slave machines?

      <emphasis role="bold">A</emphasis>: Start the server with the
      <option>--replicate-wild-ignore-table=mysql.%</option> option.
[20 Feb 2007 13:15] Guillaume Faussard
The FLUSH PRIVILEGES statements seem to be replicated whereas I've explicitly disabled replication of the 'mysql' database. I think these statements should not be replicated as GRANT/REVOKE statements are not.

Part of my slave replication config:
replicate-wild-do-table = $somedatabase.%

A flush privileges statement found in my slave binary logs:
# at 1828752
#070216  8:40:02 server id 1  end_log_pos 1828827       Query   thread_id=834   exec_time=364412        error_code=0
SET TIMESTAMP=1171611602;
# at 1828827

Both the master and the slave are hosted on Windows and uses MySQL v5.0.27.