MySQL Bugs: #54866: Partially failed REVOKE not binlogged, causes inconsistency or replication abort

Bug #54866	Partially failed REVOKE not binlogged, causes inconsistency or replication abort
Submitted:	28 Jun 2010 17:53	Modified:	3 Sep 2010 14:25
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1, 5.5	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
If REVOKE ALL fails for some grants (with ER_NONEXISTING_GRANT) while succeeds for others, it is not written into binary log, although valid parts are executed. Thus, some grants are not revoked on slave, so user configuration on master and slave diverges.

Further, any binlog event written with ER_TABLEACCESS_DENIED_ERROR which involves the revoked grants causes replication failure since the error does not happen on slave.

In the test case below, we grant to and revoke from user 'foo'@'localhost' grants on 'd', also having a non-existent user 'bar' in the same REVOKE statement. We can see that on slave the grants for 'foo'@'localhost' are preserved.

Then, 'foo' connects and attempts to run a query which involves both a table where it has access and a table in 'd'. It is written into the binary log with TABLEACCESS_DENIED_ERROR, but does not cause the error on slave, so replication aborts with

Error on master: '%-.16s command denied to user '%-.48s'@'%-.64s' for table '%-.192s'' (1142), Error on slave: 'no error' (0). Default database: 'test'. Query: 'INSERT INTO test.my VALUES (1)'

It is not new, the same happens in 5.1.39 and 5.5.0 (did not check earlier versions).

How to repeat:
--source include/master-slave.inc

--disable_warnings
DROP DATABASE IF EXISTS d;
--enable_warnings
CREATE DATABASE d;
CREATE TABLE d.t ( i INT );

CREATE USER 'foo'@'localhost';
GRANT ALL ON d.* TO 'foo'@'localhost';
--error ER_NONEXISTING_GRANT
REVOKE ALL ON d.* FROM 'foo'@'localhost', 'bar';

--echo # Grants on master have been revoked:
SHOW GRANTS FOR 'foo'@'localhost';

--sync_slave_with_master
--echo # Grants on slave have not been revoked:
SHOW GRANTS FOR 'foo'@'localhost';

--connect(confoo,localhost,foo,,)

CREATE TABLE test.my ( i INT );
CREATE TRIGGER test.tr AFTER INSERT ON test.my FOR EACH ROW INSERT INTO d.t VALUES (new.i);

--error ER_TABLEACCESS_DENIED_ERROR
INSERT INTO test.my VALUES (1);

--connection master
--sync_slave_with_master
--exit

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/112492

3451 Luis Soares	2010-06-29
      BUG#54866: Partially failed REVOKE not binlogged, causes
      inconsistency or replication abort
      
      The server was skipping binary logging for some half complete
      GRANT and REVOKE operations.
      
      
      We fix this by writing to the binary log GRANT/REVOKE operations,
      those that were successful (nothing new here) and those that may
      have changed master state, even if they ended up in
      error. Additionally, for failed GRANT/REVOKE statements we log
      the error code the master got from the execution. This way we
      give the slave a chance to replay the statement, get the same
      error, and the same side effects from the unsuccessful execution
      as the master did.

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/113036

3451 Luis Soares	2010-07-07
      BUG#54866: Partially failed REVOKE not binlogged, causes
      inconsistency or replication abort
      
      The server was skipping binary logging for some half complete
      GRANT and REVOKE operations.
      
      We fix this by writing to the binary log GRANT/REVOKE operations,
      those that were successful (nothing new here) and those that may
      have changed master state, even if they ended up in
      error. Additionally, for partially succeeded GRANT/REVOKE
      statements we log the error code the master got from the
      execution. This way we give the slave a chance to replay the
      statement, get the same error, and the same side effects from the
      unsuccessful execution as the master did.
      
      Finally, for failed REVOKE/GRANT statements that don't change 
      state, we don't log them.

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/113934

3318 Luis Soares	2010-07-20 [merge]
      BUG#54866: Partially failed REVOKE not binlogged, causes inconsistency or 
                 replication abort
      
      Merge patch based on 5.1 to mysql-next-mr codebase. It also includes
      a cset addressing review comments.

Queued in mysql-next-mr-bugfixing:
- http://pb2.norway.sun.com/web.py?template=push_details&push=1434290

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)

Does not appear in a 5.6 release; no 5.6 changelog entry needed.

Set NM status, waiting for merges to 5.1 and 5.5 trees.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html