Bug #65923 Users and Privileges not replicated when binlog-ignore-db is used.
Submitted: 17 Jul 2012 13:31 Modified: 10 Sep 2012 13:07
Reporter: Van Stokes Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.5.25a OS:Linux (Ubuntu 12.04 x86_64)
Assigned to: CPU Architecture:Any
Tags: grant, privileges, replication
Triage: Needs Triage: D2 (Serious)

[17 Jul 2012 13:31] Van Stokes
Description:
It appears there may be a bug in replication when adding/changing/deleting users/privileges when the option binlog-ignore-db is used in the my.cnf file.

Topology: Dual-Masters with RO slaves.

We use MySQL Workbench (5.2.40 as of this post) to manage users. We noticed that when adding/modifying/removing users/privileges are not being replicated to the other master or the slaves.

Also, if you attempt to manually perform the command, it does not work UNLESS you set the 'mysql' database as the default/current database.

How to repeat:
1. Create two-masters replicating to one another.
2. Create a dummy database and add binlog-ignore-db=dummy to BOTH masters.
3. Restart masters and ensure that dummy is not being logged/replicated.

Via Workbench 5.2.40
1. Use WB and create a user. (Note the user isn't replicated).
2. Change privs of an existing user. (Note changes not replicated).

Manually perform the command while not using mysql database.
1. USE dummy;
2. GRANT SELECT ON dummy.* TO 'anyuser'@'%';
3. flush privileges;

Note that privilege change is not replicated.

Manually perform the command while using mysql database.
1. USE mysql;
2. GRANT SELECT ON dummy.* TO 'anyuser'@'%';
3. flush privileges;

Note that privilege change is replicated.

Suggested fix:
Unless the 'mysql' database is being ignored, all user/privilege changes should be written to the bin log for replication regardless of the users current database.
[17 Jul 2012 14:49] Valeriy Kravchuk
Please, read the manual, http://dev.mysql.com/doc/refman/5.5/en/replication-rules-db-options.html:

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

I think it clearly explains the results you described. This is NOT a bug.

What I see here is WB bug that it does NOT explicitly set default database to mysql when implementing changes in Users and Privileges section.
[17 Jul 2012 15:20] Van Stokes
The binary logging logic should only be valid for user defined databases.

All user/privilege commands by definition imply the "mysql" database and therefore it should not have the same considerations as user defined databases.

By your own admission, in reference to your WB remark of not setting the default database to "mysql", just goes to prove that "mysql" is "implied" with all user/privilege commands.

This may not be a bug, but it is an oversight and the logic for treating the "mysql" database the same as user defined databases needs to be re-evaluated.
[17 Jul 2012 17:43] Valeriy Kravchuk
OK, I've got your point. Indeed, even explicit CREATE USER and GRANT statements (not just INSERTs into mysql.* tables) are NOT logged without default database in this case:

macbook-pro:5.5 openxs$ bin/mysqld_safe --log-bin --binlog-ignore-db=test &
[1] 90873
macbook-pro:5.5 openxs$ 120717 20:37:44 mysqld_safe Logging to '/Users/openxs/dbs/5.5/data/macbook-pro.err'.
chown: /Users/openxs/dbs/5.5/data/macbook-pro.err: Operation not permitted
120717 20:37:44 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.5/data

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.26-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
macbook-pro:5.5 openxs$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.26-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user u3@localhost identified by 'u3';
Query OK, 0 rows affected (0.04 sec)

mysql> grant all on *.* to u3@localhost;
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| macbook-pro-bin.000002 |      107 |              | test             |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'macbook-pro-bin.000002';
+------------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| Log_name               | Pos | Event_type  | Server_id | End_log_pos | Info                                        |
+------------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| macbook-pro-bin.000002 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.26-debug-log, Binlog ver: 4 |
+------------------------+-----+-------------+-----------+-------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create user u4@localhost identified by 'u4';
Query OK, 0 rows affected (0.00 sec)

mysql> show binlog events in 'macbook-pro-bin.000002';
+------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| Log_name               | Pos | Event_type  | Server_id | End_log_pos | Info                                                     |
+------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| macbook-pro-bin.000002 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.26-debug-log, Binlog ver: 4              |
| macbook-pro-bin.000002 | 107 | Query       |         1 |         214 | use `mysql`; create user u4@localhost identified by 'u4' |
+------------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
2 rows in set (0.00 sec)

We can call this a bug (even if it will end up as documentation request and/or feature request).

Feel free to report Workbench bug separately. It does not work properly with current server versions, while it could implement workaround of adding explicit "use mysql" easily.
[10 Sep 2012 13:05] Van Stokes
Refer to workbench bug 65959.

Workbench has addressed this issue by changing the default/selected database to mysql when issuing user/privilege commands.

However, this does not address the underlying problem with the server because the Workbench fix does work when using other clients such as mysql, sqlyog, API's, etc.
[10 Sep 2012 13:07] Van Stokes
*correction to previous comment*

However, this does not address the underlying problem with the server because the Workbench fix does *NOT* work when using other clients such as mysql, sqlyog, API's, etc.