MySQL Bugs: #33862: completely failed DROP USER statement gets replicated

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

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.

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.

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

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.

Pushed into 5.0.56

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

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.

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

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.

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.

Fix also documented for 5.1.23-ndb-6.3.11.