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
[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.