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: | |
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 |
[17 Jul 2012 13:31]
Van Stokes
[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.