| Bug #37454 | CREATE USER breaks replication on 5.0.37 when user already exists on slave | ||
|---|---|---|---|
| Submitted: | 17 Jun 2008 17:35 | Modified: | 17 Jun 2008 20:47 |
| Reporter: | Mark Konetchy | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0.37, 5.0, 5.1, 6.0 BK | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | create user, replication | ||
[17 Jun 2008 19:44]
Peter Laursen
I understand your post like this: "Replication [does not break when replicating] from 5.0.37 to 5.0.41" Well -- if this is fixed in 5.0.41 and onwards what else do you think could be done now? It is not possible to fix bug 'backwards' (in old releases) only 'forwards' (in new releases). Frankly I do not understand the idea of posting such report - or I do not understand the meaning of what you are writing! Am I missing something? Peter (not a MySQL person)
[17 Jun 2008 20:47]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php GRANT statements are replicated, so if you have user on slave error is expected. Like in not replication setup: $mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 296 Server version: 5.0.51a-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create user bar; Query OK, 0 rows affected (0.09 sec) mysql> create user bar; ERROR 1396 (HY000): Operation CREATE USER failed for 'bar'@'%'
[19 Mar 2014 13:01]
NOT_FOUND NOT_FOUND
Workaround: slave> stop slave; slave> reset slave; master> reset master; slave> drop user repl_test; slave> start slave;
Description: Replication breaks when a user account is created on master which already exists on slave. The master is 5.0.37 and one slave is also 5.0.37, another is 5.0.41. Replication only breaks on replication from 5.0.37 to 5.0.37, not 5.0.37 to 5.0.41 How to repeat: on slave1 (5.0.37) mysql> create user 'repl_test'@'10.%' identified by 'PW'; Query OK, 0 rows affected (0.08 sec) on slave 2 (5.0.41) mysql> create user 'repl_test'@'10.%' identified by 'PW'; Query OK, 0 rows affected (0.08 sec) on master (5.0.37) mysql> create user 'repl_test'@'10.%' identified by 'PW'; Query OK, 0 rows affected (0.08 sec) on slave1 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: skunk2 Master_User: skunkrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000347 Read_Master_Log_Pos: 35337887 Relay_Log_File: skunk1-relay-bin.000010 Relay_Log_Pos: 35326067 Relay_Master_Log_File: mysql-bin.000347 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: lingo,lingo Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1396 Last_Error: Error 'Operation CREATE USER failed for 'repl_test'@'10.%'' on query. Default database: ''. Query: 'create user 'repl_test'@'10.%' identified by 'PW'' Skip_Counter: 0 Exec_Master_Log_Pos: 35325930 Relay_Log_Space: 35338024 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL 1 row in set (0.00 sec) on slave2 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: skunk2.sc8.syndtk.net Master_User: skunkrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000347 Read_Master_Log_Pos: 35376491 Relay_Log_File: castle-relay-bin.000489 Relay_Log_Pos: 35376628 Relay_Master_Log_File: mysql-bin.000347 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: nexus,lingo,nexus,lingo Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 35376491 Relay_Log_Space: 35376628 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) Suggested fix: dont throw error when user already exists on slave