A replication issue can occur in MySQL when a GRANT operation fails due to mismatched user information between the in-memory privilege cache and the mysql.user table. This can lead to SQL thread stoppage on replicas.

Replication Error Scenario

When checking replica status with SHOW SLAVE STATUS\G, an error like the following may appear:

Last_Errno: 1410
Last_Error: Worker failed executing transaction... Error 'You are not allowed to create a user with GRANT' on query. Query: 'GRANT ALL PRIVILEGES ON *.* TO `app_user`@`%`'

This typically happens after direct modifications to mysql.user using DML statements like UPDATE, without reloading privileges.

Reproduction Example

Consider a MySQL 8.0 primary-replica setup with a read-only user analyst@'192.168.%':

-- On primary
UPDATE mysql.user SET host='%' WHERE user='analyst';
GRANT ALL ON *.* TO analyst@'%';
-- First attempt returns ERROR 1410
GRANT ALL ON *.* TO analyst@'%';
-- Second attempt succeeds

After this sequence, the replica SQL thread stops with error 1410 when applying the first GRANT statement from the binary log.

Privilege Loading Behavior

MySQL handles privilege table changes differently depending on the method:

• Account management statements (GRANT, REVOKE, etc.) trigger immediate in-memory reload.
• Direct DML modifications (INSERT, UPDATE, DELETE) require manual privilege reload via FLUSH PRIVILEGES or server restart.

This explains the two-step GRANT behavior:

1. The UPDATE changes disk data but not memory cache
2. First GRANT fails (no matching user in cache) but triggers implicit cache reload
3. Second GRANT succeeds (user now exists in cache)

Non-Atomic Nature of GRANT

The GRANT operation exhibits partial commitment behavior. Even when returning an error, it performs the priviledge cache reload. This can be demonstrated through testing:

-- Setup test
CREATE USER auditor@'localhost' IDENTIFIED BY 'password';
REVOKE ALL ON *.* FROM auditor@'localhost';

-- Modify directly
UPDATE mysql.user SET Select_priv='Y' WHERE user='auditor';

-- Attempt GRANT (will fail but reload cache)
GRANT INSERT ON test.* TO auditor@'localhost';
-- Returns ERROR 1410

-- Verify cache was reloaded
SELECT * FROM mysql.user WHERE user='auditor';
-- Shows updated privileges

Recommendations

1. Avoid direct DML modifications to privilege tables in production
2. Use account management statements (GRANT, CREATE USER, etc.) exclusive
3. If direct modifications are unavoidable, execute FLUSH PRIVILEGES immediately afterward
4. Monitor replica status after privilege changes to catch potential replication breaks