| Bug #29749 | Failed DROP USER still binlogged and replicated | ||
|---|---|---|---|
| Submitted: | 12 Jul 2007 6:59 | Modified: | 6 Feb 2008 22:17 |
| Reporter: | Jeremy Cole (Basic Quality Contributor) (OCA) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0.41, 5.0 BK-community, 5.1 BK -falcon | OS: | Any |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
| Tags: | binlog, drop user, replication | ||
[12 Jul 2007 7:16]
Sveta Smirnova
Thank you for the report. Verified as described. Replication failure only repeatable with Community and Falcon trees.
[12 Jul 2007 7:26]
Sveta Smirnova
test case
Attachment: rpl_bug29749.test (application/octet-stream, text), 233 bytes.
[12 Jul 2007 18:52]
Jeremy Cole
Fixed tags
[23 Jul 2007 13:37]
Georgi Kodinov
Jeremy, can you please be more specific on what you mean by "breaking replication on all slaves" ? If it is something different from stopping the slave SQL (and possibly the IO) threads or is not one of the cases described below please reopen the bug. MySQL can fully well replicate statements with errors : it stores the error code in the master binlog. When the slave executes such a statement it will expect the same error code as there was on the master. If this is the case the slave will continue. If a different error code (or no error code at all) is received when executing the statement on the slave the replication should stop. This error code is not displayed by SHOW BINLOG EVENTS, but is visible through mysqlbinlog utility. It may not seem very logical to replicate statements that are known to produce errors, but this sometimes needs to be done because of the side effects. In the particular case of DROP USER I can think of 2 side effects : - "DROP USER u1,non_existent" : the user u1 is dropped even if the user non_existent will throw an error. - "DROP USER non_existent" : this has no effect on the server (no such user), but has the side effect of verifying that the the slave also doesn't have that user (consistency check). I've performed an experiment using the latest BT build and "DROP USER 'non-existent'@'localhost' is replicated with the error code (I've got 1396: ER_CANNOT_USER) and doesn't cause the slave to stop because it gets the same error as the master does.
[5 Feb 2008 12:45]
Bugs System
Pushed into 5.0.56
[5 Feb 2008 13:04]
Bugs System
Pushed into 5.1.24-rc
[5 Feb 2008 13:08]
Bugs System
Pushed into 6.0.5-alpha
[6 Feb 2008 22:17]
Jon Stephens
Marked this as a duplicate of Bug#33862. (This bug was filed first, but the other is the one which was properly investigated, and for which the fix was actually made.)
Description: If you use DROP USER to remove a user that doesn't exist, the DROP USER command is still written to the binlog, and then replicated, thus breaking replication on all slaves. If the slave has log-slave-updates turned on, the statement is logged there as well (even though replication stops because of the failure), further complicating recovery. mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> drop user 'foo'@'bar'; ERROR 1396 (HY000): Operation DROP USER failed for 'foo'@'bar' mysql> show binlog events limit 1,1 \G *************************** 1. row *************************** Log_name: hekla-bin.000001 Pos: 98 Event_type: Query Server_id: 1 End_log_pos: 178 Info: drop user 'foo'@'bar' 1 row in set (0.00 sec) How to repeat: reset master; drop user 'foo'@'bar'; show binlog events limit 1,1 \G Suggested fix: --- sql/sql_acl.cc.orig 2007-07-11 22:08:54.000000000 -0700 +++ sql/sql_acl.cc 2007-07-11 22:09:21.000000000 -0700 @@ -5353,7 +5353,7 @@ VOID(pthread_mutex_unlock(&acl_cache->lock)); - if (mysql_bin_log.is_open()) + if (mysql_bin_log.is_open() && !result) { Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE); mysql_bin_log.write(&qinfo);