Bug #33862 completely failed DROP USER statement gets replicated
Submitted: 14 Jan 2008 13:49 Modified: 6 Feb 2008 22:19
Reporter: Axel Schwenke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.56-bk OS:Any
Assigned to: Zhenxing He CPU Architecture:Any
Tags: binlog, drop user, replication
Triage: D2 (Serious) / R2 (Low) / E2 (Low)

[14 Jan 2008 13:49] Axel Schwenke
Description:
This is a reincarnation of bug #29749. The problem is that DROP USER statements that completely fail on the master are written to the binlog and may cause the SQL thread to stop on the slave.

This behaviour is inconsistent. In general we do not log statements that did not modify any data on the master. A failed DROP USER statement does not modify any data on the master and thus should not be logged.

How to repeat:
startup fresh (that is: only with default GRANTs) MySQL with binlog enabled

mysql> create user 'exists'@'localhost';
Query OK, 0 rows affected (0,00 sec)
mysql> drop user 'not_here'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'not_here'@'localhost'
mysql> drop user 'exists'@'localhost', 'not_here2'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'not_here2'@'localhost'
mysql> drop user 'not_here3'@'localhost', 'not_here4'@'localhost';
ERROR 1396 (HY000): Operation DROP USER failed for 'not_here3'@'localhost','not_
here4'@'localhost'

Now check the binlog. It contains all 3 DROP USER statements.

Suggested fix:
In the example above the 
  drop user 'not_here'@'localhost' 
and 
  drop user 'not_here3'@'localhost', 'not_here4'@'localhost'
statements should not be logged because they do not modify any data on the master (the statements completely fail)

The statement
  drop user 'exists'@'localhost', 'not_here2'@'localhost'
however should be logged because it partially succeeds.
[17 Jan 2008 7:00] 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/41044

ChangeSet@1.2565, 2008-01-17 14:53:55+08:00, hezx@mail.hezx.com +3 -0
  BUG#33862 completely failed DROP USER statement gets replicated
  
  When create/rename/drop users, the statement will be logged regardless of error, even if no data has been changed, the statement will be logged.
  
  When create/rename/drop users, don't write the binlog if the statement make no changes, if the statement do made any changes, log the statement with possible error code.
[25 Jan 2008 7:02] 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/41249

ChangeSet@1.2565, 2008-01-25 15:02:26+08:00, hezx@mail.hezx.com +3 -0
  BUG#33862 completely failed DROP USER statement gets replicated
  
  The problem is when create/rename/drop users, the statement was logged regardless of error, even if no data has been changed, the statement was logged.
  
  After this patch, create/rename/drop users don't write the binlog if the statement makes no changes, if the statement does make any changes, log the statement with possible error code.
  
  This patch is based on the patch for BUG#29749, which is not pushed
[1 Feb 2008 11:27] Axel Schwenke
Another customer us running into a variant of this bug. A duplicate CREATE USER results in error 1396 on the master, but is logged with error 0 in the binlog. Replication stops because it gets error 1396 but expects 0.

I verified that the current patch (41044) fixes this behaviour too.
[5 Feb 2008 12:46] 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:19] Jon Stephens
Documented in the 5.0.56, 5.1.24, and 6.0.5 changelogs as follows:

      A DROP USER statement that failed on the master
      were written to the binlog, as was a duplicate CREATE
      USER statement; either of these occurrences could cause
      the slave to fail.
[14 Feb 2008 15:37] Robert Krzykawski
I Am experiencing the same failures when running RENAME USER. Will this also be fixed with patch 41044 that is pushed into 5.0.56?

Br
Robert
[14 Feb 2008 16:02] Chris Calender
Yes, the patch will ensure that RENAME USER, CREATE USER, and DROP USER commands that do not make any changes (e.g., failed statements) will not be written to the binary log, thus preventing replication from breaking.
[6 Mar 2008 6:15] Jon Stephens
Updated changelog entry to read:

        A CREATE USER, DROP USER, or RENAME USER statement that fails on the 
        master, or that is a duplicate of any of these statements, is no longer
        written to the binlog; previously, either of these occurrences could cause 
        the slave to fail.

Also noted the fix in the 6.1.23-ndb-6.2.14 changelog.
[30 Mar 2008 17:37] Jon Stephens
Fix also documented for 5.1.23-ndb-6.3.11.