| 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: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 --replicate-ignore-db=mysql 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
--replicate-wild-{ignore,do}-table=mysql.%
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: http://lists.mysql.com/commits/714
[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: http://lists.mysql.com/commits/848
[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
Mike, 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 mysql-5.0.18 .
[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:
<para>
<emphasis role="bold">Q</emphasis>: How do I prevent GRANT and
REVOKE statements from replicating to slave machines?
</para>
<para>
<emphasis role="bold">A</emphasis>: Start the server with the
<option>--replicate-wild-ignore-table=mysql.%</option> option.
</para>
[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: log-slave-updates replicate-wild-do-table = $somedatabase.% replicate-wild-ignore-table=mysql.% 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; FLUSH PRIVILEGES; # at 1828827 Both the master and the slave are hosted on Windows and uses MySQL v5.0.27.
Description: 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: Master: mysql> use foo; mysql> revoke insert,update on foo.* from bar@'%'; Slave: ... is configured with "Replicate-Do-DB=foo"; so the database 'mysql' isn't replicated. mysql> show slave status\G [...] Replicate_Do_DB: foo Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: 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.%".