| Bug #16563 | RBR: Adding grants to master for slave to connect cause slave to get error | ||
|---|---|---|---|
| Submitted: | 17 Jan 2006 14:37 | Modified: | 13 Mar 2007 19:35 |
| Reporter: | Jonathan Miller | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S2 (Serious) |
| Version: | 5.1.6-alpha | OS: | Linux (Linux) |
| Assigned to: | Mats Kindahl | CPU Architecture: | Any |
[19 Aug 2006 14:58]
Jonathan Miller
This is 2 cluster, 1 a master and the other a slave. Start both of the clusters and try to start the slave before you have done the grants on the master. The slave fails because there are no rights on the master. Grant the rights on the master and start the slave again. The slave fails due to the master trying to replicate the mysql.db table. Not sure how one could make a mysql-test case out of this as this is done by the test frame work. /jeb
[28 Nov 2006 11:04]
Sveta Smirnova
I can not repeat it on Linux using current BK sources.
[6 Dec 2006 19:54]
Jonathan Miller
Hi Mats, I have created a test for this case per Calvin/Lars. Could you please give it a review and blessings? ####################################### # Author: JBM # Date: 12-04-2006 # Purpose: Bug #16563 RBR: Adding grants to master # for slave to connect cause slave to get error ########################################## #Setup connections connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,); connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,); connect (slave,127.0.0.1,root,,test,$SLAVE_MYPORT,); connect (slave1,127.0.0.1,root,,test,$SLAVE_MYPORT,); ######### # Test 1 straight grants for a user that already exists # on the slave mysqld ######### # Connect to and setup slave connection slave; GRANT ALL ON *.* TO 'jonathan'@'%'; USE mysql; SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan'; stop slave; --wait_for_slave_to_stop reset slave; # Connect to and cleanup master connection master; reset master; # Start the slave now that the master has been cleaned connection slave; start slave; # Connect to master and grant permissions connection master; GRANT ALL ON *.* TO 'jonathan'@'%'; GRANT REPLICATION SLAVE ON *.* TO 'jonathan'@'%' IDENTIFIED BY 'test'; # Connect back to slave and ensure that the slave is still running --sync_slave_with_master --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 9 # 11 # 23 # 33 # show slave status; ######## # Test 2: Remove permissions from Master ######## # Now removed the permissions and make sure they are replicated connection master; REVOKE REPLICATION SLAVE ON *.* FROM 'jonathan'@'%'; USE mysql; SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan'; # Connect back to slave and ensure that the slave is still running --sync_slave_with_master --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 9 # 11 # 23 # 33 # show slave status; USE mysql; SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan'; # cleanup the mysql database connection master; DROP USER 'jonathan'@'%'; USE mysql; SELECT user FROM user WHERE user = 'jonathan'; # Ckeck the slave --sync_slave_with_master --replace_result $MASTER_MYPORT MASTER_MYPORT --replace_column 1 # 9 # 11 # 23 # 33 # show slave status; USE mysql; SELECT user FROM user WHERE user = 'jonathan'; # End of 5.1 Test Case RESULTS: GRANT ALL ON *.* TO 'jonathan'@'%'; USE mysql; SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan'; user Repl_slave_priv jonathan Y stop slave; Warnings: Note 1255 Slave already has been stopped reset slave; reset master; start slave; GRANT ALL ON *.* TO 'jonathan'@'%'; GRANT REPLICATION SLAVE ON *.* TO 'jonathan'@'%' IDENTIFIED BY 'test'; show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_Retry MasterSeconds_Behind_Master_Table # 127.0.0.1 root MASTER_MYPORT 1 master-bin.000001 331 #oneer-bin.00000100003 REVOKE REPLICATION SLAVE ON *.* FROM 'jonathan'@'%'; USE mysql; SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan'; user Repl_slave_priv jonathan N show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_Retry MasterSeconds_Behind_Master_Table # 127.0.0.1 root MASTER_MYPORT 1 master-bin.000001 445 #oneer-bin.00000100003 USE mysql; SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan'; user Repl_slave_priv jonathan N DROP USER 'jonathan'@'%'; USE mysql; SELECT user FROM user WHERE user = 'jonathan'; user show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_Retry MasterSeconds_Behind_Master_Table # 127.0.0.1 root MASTER_MYPORT 1 master-bin.000001 533 #oneer-bin.00000100003 USE mysql; SELECT user FROM user WHERE user = 'jonathan'; user
[6 Dec 2006 20:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/16546 ChangeSet@1.2375, 2006-12-06 21:11:58+01:00, jmiller@mysql.com +2 -0 Test case for bug#16563
[6 Mar 2007 12:33]
Mats Kindahl
This bug was reported more than a year ago, and is referencing the fact that the a conflict occurs because attempts are made to replicate changes to the mysql.db table. Since replication of the mysql database has been revised several times since then, I doubt that this bug still exists. Setting the bug to Open state for re-verification.
[13 Mar 2007 19:35]
Jonathan Miller
Seems to be no longer an issues with all the changes done since the bug was opened. Best wishes, /Jeb
Description: Using NDB: 1) Start master cluster and master mysqld 2) Start slave cluster and slave mysqld 3) Slave fails due to no permissions 4) Grant permissions for slave on master 5) Slave fails due to attempt to update Table 'db' Last_Errno: 1050 Last_Error: Error 'Table 'db' already exists' on query. Default database: 'mysql'. Query: ' CREATE TABLE db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_gener How to repeat: see above