MySQL Bugs: #102232: Consider making REVOKE replication safe

Bug #102232	Consider making REVOKE replication safe
Submitted:	12 Jan 2021 11:13	Modified:	4 Apr 2022 14:56
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	8.0.22 and earlier	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	grants, replication friendly, revoke

Description:
MySQL replicates commands from master to replicas to keep all servers consistent.
In the past there have been issues with replication breaking due to inconsistencies. While these inconsistencies should not happen in many cases the reported error causes more problems than the failure to complete the command.

e.g. DROP USER for a non-existent user causes no harm. This was resolved with an extended syntax DROP USER IF EXISTS.... Similar changes where made for CREATE USER , ALTER USER.

However, the same issue persists when removing grants. If the grant does not exist, or the user does not exist there's no harm done and the end result is that requested by the command. However, via replication this will break.

Recovering from such issues in replication requires adding the required user/grant without adding additional GTIDs or adding a dummy GTID to prevent the required change being attempted on the slave. In both cases automated code to handle this is messy.

How to repeat:
e.g.

REVOKE EXECUTE, SELECT ON sys.* FROM 'someuser'@'%' will generate the error:

There is no such grant defined for user 'someuser' on host '%' on query.

The error of course is correct.

Suggested fix:
Make it possible for this command to be replication-friendly. If the user or requested right that is being requested to be removed does not exist (so the end result will be as requested) do not generate an error.

For consistency this might be better via a similar format used elsewhere:

e.g. REVOKE IF EXISTS EXECUTE, SELECT ON sys.* FROM 'someuser'@'%';

Please use:
REVOKE IF USER EXISTS EXECUTE, SELECT ON sys.* FROM 'someuser'@'%';

Instead of:
REVOKE IF EXISTS EXECUTE, SELECT ON sys.* FROM 'someuser'@'%';

Hello Simon,

Thank you for the reasonable feature request!

regards,
Umesh

Fixed in MySQL 8.0.30 by WL#14690.

Closed.

Thanks for the suggestion!