MySQL Bugs: #50460: Grant and/or revoke not replicating if not connected to base `mysql`

Bug #50460	Grant and/or revoke not replicating if not connected to base `mysql`
Submitted:	20 Jan 2010 0:46	Modified:	21 Jan 2010 6:17
Reporter:	Paul Pech	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.36	OS:	Linux (openSuSE 11.2)
Assigned to:		CPU Architecture:	Any
Tags:	grant revoke replication

Description:
Hi,

we have two MySQL-Servers running in a standard master/slave replication setup. All databases are being replicated from the master to the slave.

Let's assume that there's is a user 'alice' (with PWD set) who has access to base `test_db` (according to base `mysql`). Another user 'bob' wants to grant 'alice' access to base `new_db` (which already exists; 'bob' has all appropriate rights to grant 'alice' access to `test_db`).

Bob uses his mysql command line client to issue the following command:

#mysql -ubob -p
>grant Select on `new_db` to 'alice'@'somehost';
 Command successful  

This grant command (or the corresponding revoke command) will not get replicated to the slave server (without any error messages), if 'bob' doesn't connect to base `mysql` first. Grant (and revoke) do work on the master (db `mysql` is changed accordingly) but the grant command does not show up in the master's binlog file or the relay file of the slave.

How to repeat:
Easy to repeat, just issue grant or revoke commands (without the IDENTIFIED BY part; I have not tested if the same issue occurs if that part is not omitted) and examine base `mysql` on the master and the slave (or the bin-logs).

The problem seems to be independent of the command line client, as it can be reproduced using PHP as well.

--

This quite a severe error, as it will go unnoticed until stopping the master and changing to the slave server. Users' access rights are messed up pretty good...

Suggested fix:
Cf. description:

If Bob uses the following commands:

#mysql -ubob -p
>connect mysql;
>grant Select on `new_db` to 'alice'@'somehost';
 Command successful
>revoke Select on `new_db` from 'alice'@'somehost';
 Command successful

grant and revoke get replicated to the slave. So issue a "connect mysl;" command BEFORE using grant and/or revoke in a replication setup.

Please, send my.cnf file content from master and slave.

my.cnf for the master server

Attachment: my.cnf.master (application/x-master-page, text), 1.37 KiB.

my.cnf for the slave server

Attachment: my.cnf.slave (application/octet-stream, text), 1.37 KiB.

Regarding the my.cnf files:

Both servers are bin-logging and the slave server is configured with a "change master to..." command to connect to the master server.

The master server and the slave server are in a circular replication setup, with heartbeat and virtual IP addresses making sure that clients can only connect to one of the servers. This means server1 (dbserv1) is master to server2 (dbserv1-1) while server2 (dbserv1-1) is master to server1 (dbserv1). At any given time, clients can only connect to either server1 or server2. This setup has been working with different versions of MySQL for more than three years (and don't suspect it to be responsible for the grant/revoke problem described earlier).

We first encountered the grant/revoke issue after changing our privilege system from direct inserts into db `mysql` to the grant/revoke mechanism.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/replication-rules-db-options.html:

-----<q>-----
 For binary logging, the steps involved are listed here:

   1.

      Are there any --binlog-do-db or --binlog-ignore-db options?
          * Yes.  Continue to step 2.
          * No.  Log the statement and exit.
   2.

      Is there a default database (has any database been selected by USE)?
          * Yes.  Continue to step 3.
          * No.  Ignore the statement and exit.
-----</q>-----

So no statement will be written into binary log if you have binlog-ignore-db and didn't issue USE db