Bug #4251 Problem with grant and replication.

• Submitted: 22 Jun 2004 18:10
• Modified: 22 Jun 2004 22:31
• Reporter: Dimitrij HIlt
• Status: Won't fix
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 4.0.20-standard-log
• OS: Linux (Linux 2.4.21 i686)
• CPU Architecture: Any

[22 Jun 2004 18:10] Dimitrij HIlt

Description:
Slaves crashes if mysql database is in replication and replicate-do-db=XX on slave exists and you run on master GRANT-Commando on master.

How to repeat:
Install 2 Servers as master/slave.

1)create database do_replic with table test.
2) create database do_not_replic with table test.
2) on slave write
replicate-do-db=mysql
replicate-do-db=do_replic
to my.cnf and run replication.
3) on master run 'grant select on do_not_replic.test to anyuser@'%' identified
by 'password'

You may got error:
No Slave_SQL_Running: Error 'Table 'do_not_replic.test' doesn't exist' on query. Default database: 'mysql'. Query: 'grant select on do_not_replic.test to anyuser@'%' identified by 'password''

With insert into user/db you can workaround this issue, but it is dirty solution.

[22 Jun 2004 22:31] Guilhem Bichot

Hello,

I would not consider it a real bug. The GRANT means "modify the mysql.* tables like this". As you have replicate-do-db=mysql, the slave simply does the modification on the mysql.* tables, but this modification shows to be impossible because the object of the GRANT does not exist. GRANT is designed so that it does not accept a non existing object.
The other behaviour, where the GRANT would be ignored, is not perfect either. It could be that the database exists on slave, but is not replicated from master, and you still want the GRANT to have effect on the slave.
I understand there is an annoyance but there's not much we can do about this problem. As you don't care about privileges to be replicated to your slave, you could consider removing the replicate-do-db=mysql. Or wrapping that particular GRANT like this SET SQL_LOG_BIN=0 ; GRANT... ; SET SQL_LOG_BIN=1;

Regards,
Guilhem