Resolve MySQL 1590 Error: Primary-Replica Replication Recovery for Permission Issues

Background​

A production MySQL 5.7.32 instance encountered a replication failure after executing GRANT statements. The error log showed:

Last_Errno: 1590  
Last_Error: The incident LOST_EVENTS occurred on the primary. Message: REVOKE/GRANT failed while granting/revoking privileges in databases.  

‍

This article analyzes the root cause and provides recovery strategies for similar incidents using replica terminology.

‍

Problem Analysis

‍
The 1590 error occurs when a permission change (e.g., GRANT/REVOKE) partially fails, triggering an INCIDENT_EVENT in the binary log. Common scenarios include:

1. Mixed Success/Failure in Permission Commands:

1. Creating users with invalid passwords or conflicting privileges.
2. Granting privileges to non-existent users.

2. ​MySQL 5.7 Specific Behavior:
Using GRANT to create users splits operations into two steps:

1. User creation (always succeeds).
2. Privilege assignment (may fail).
   Partial failures trigger INCIDENT_EVENT.

​Example Scenario:

CREATE USER test@'%', app@'%' IDENTIFIED BY 'WeakPass123';  
-- Password policy violation: Only `app@'%'` is created.  
GRANT ALL ON test.* TO test@'%', app@'%';  
-- Fails for `test@'%'` (non-existent), causing partial execution.  

‍

Solutions​

1. ​Skip Corrupted GTID on the Replica:

1. Identify the problematic GTID from the error log.
2. Execute on the replica:

STOP REPLICA SQL_THREAD;  
SET GTID_NEXT = 'UUID:NUMBER';  
BEGIN; COMMIT;  
SET GTID_NEXT = 'AUTOMATIC';  
START REPLICA SQL_THREAD;  

‍

2. ​Manual Permission Repair:

• Reapply failed commands on the replica.
• Example:

GRANT ALL ON test.* TO test@'%';  

‍

3. Avoid Partial Permissions in Single Commands:

1. Use separate CREATE USER and GRANT statements.
2. Validate passwords against policies before execution.

‍

Key Takeaways​

• ​Partial Failures Trigger Incidents: Ensure all permission operations complete successfully.
• ​GTID Skipping Mitigates Impact: Use SET GTID_NEXT to bypass corrupted events.
• ​Password Policy Enforcement: Disable strict validation during bulk user creation.