| Bug #8982 | Granting a user just SELECT privs on a table breaks replication | ||
|---|---|---|---|
| Submitted: | 5 Mar 2005 20:33 | Modified: | 3 Jun 2005 20:23 |
| Reporter: | Bruce Dembecki | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Administrator | Severity: | S2 (Serious) |
| Version: | MySQL Admin V 1.0.5-Alpha | OS: | MacOS (Mac OS X V 10.3.8) |
| Assigned to: | Alfredo Kojima | CPU Architecture: | Any |
[25 Apr 2005 9:52]
Guilhem Bichot
Hello, What happens is that MySQL Administrator generates this "INSERT ... @new_user ..." statement to update the MySQL system tables where users are stored. @new_user is called a "user variable" it's a feature of the MySQL server (please see the manual). The problem is that in MySQL 4.0, user variables don't work well with replication. So, for what you do (which is nothing wrong) with MySQL Administrator to work, both replication master and slave must be 4.1 (our current production release, which brings subSELECTs, some replication bugfixes etc).
[25 Apr 2005 16:48]
Bruce Dembecki
I'm fine with this response... I suspect however that it needs to be clearly stated somewhere that MySQL Administrator doesn't support 4.0.n.
[26 Apr 2005 13:12]
Alfredo Kojima
We will fix MA (all platforms) so that this problem won't manifest itself, even in 4.0 servers.
[26 Apr 2005 13:23]
Guilhem Bichot
I'm setting it back to "verified", as Alfredo said he would kindly fix MA to avoid hitting this replication bug.
[3 Jun 2005 20:23]
Alfredo Kojima
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: This is all done talking to mysql 4.0.23 I used MySQL Admin to grant one of my users SELECT privilleges on the mysql database, this generated two commands for the other server via replication that it could not run. Separately i note that it appears the user in question does not have the privs i granted them on the master either, so that may also have not worked. In my example the username I was granting privs to was 'ebcbdb' - I'm not sure what that @new_user thing was about... I note that @new_user wasn't encoded in ' ' so it wasn't treated as text and thus was treated by the slave to be null, thus the error I'm going to delete the table spacer lines in the output below, they aren't adding anything. mysql1: show slave status; | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | | 10.42.42.103 | repl | 3306 | 60 | binary-log.204 | 410694197 | mysql1-relay-bin.033 | 148285189 | binary-log.204 | Yes | No | | | 1048 | Error 'Column 'User' cannot be null' on query. Default database: 'mysql'. Query: 'INSERT INTO mysql.db(User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv) VALUES(@new_user,'r%', 'jive_ebaychat_ca', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N')' | 0 | 410507132 | 148472254 | 1 row in set (0.01 sec) mysql1: SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.01 sec) mysql1: show slave status; | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | | 10.42.42.103 | repl | 3306 | 60 | binary-log.204 | 411247422 | mysql1-relay-bin.033 | 148285534 | binary-log.204 | Yes | No | | | 1048 | Error 'Column 'User' cannot be null' on query. Default database: 'mysql'. Query: 'INSERT INTO mysql.tables_priv(Host, Db, User, Table_name, Timestamp, Grantor,Table_priv, Column_priv) VALUES('r%', 'def', @new_user, 'mysql', now(), '', '', '')' | 0 | 410507477 | 149025479 | 1 row in set (0.02 sec) mysql1: SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.01 sec) How to repeat: Open MySQL Administrator on a master where both the master and slave have the same users and databases. In the Accounts tab selectg a user, then choose a host for that user, then go to the Schema Privs area and grant the user SELECT access on one of the databases the user doesn't alreayd have access to. Likely what will happen is that the slave will stop with errors similar to those above, and also on the master the user won't have the privs either.