Analysis of MySQL GRANT Failure in Replication and Its Non‑Atomic Behavior

Problem background : A customer reported that the master‑slave replication in a test environment broke. On the slave, show slave status\G showed that the SQL thread stopped with error 1410 because a GRANT statement failed.

The failure originated from an attempt to modify the mysql.user table directly (UPDATE) to change a user's host and then execute GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' . The first GRANT failed with “You are not allowed to create a user with GRANT”, and the slave’s SQL thread disconnected.

Local reproduction : A MySQL 8.0 master‑slave setup with a read‑only user test@'10.186.%' was used. The following commands reproduced the issue:

mysql> show grants for test@'10.186.%';
+------------------------------------------+
| Grants for [email protected].%                 |
+------------------------------------------+
| GRANT SELECT ON *.* TO `test`@`10.186.%` |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> update mysql.user set host='%' where user='test';
Query OK, 1 row affected (0.00 sec)

mysql> grant all on *.* to test@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

mysql> grant all on *.* to test@'%';
Query OK, 0 rows affected (0.00 sec)

After the first failed GRANT, the slave’s show slave status\G displayed the same error, confirming the replication break.

Official explanation (MySQL 8.0 documentation):

If privilege‑changing statements such as GRANT , REVOKE , SET PASSWORD , RENAME USER are used, the server automatically reloads the privilege tables into memory.

If the privilege tables are modified directly with INSERT , UPDATE or DELETE , the changes are **not** loaded into memory unless FLUSH PRIVILEGES is executed or the server is restarted.

The FLUSH PRIVILEGES command forces the server to reload the tables.

Analysis : After the UPDATE mysql.user , the new host value was not loaded, so the first GRANT could not find the user and failed. However, the failed GRANT implicitly performed a FLUSH PRIVILEGES (which is not recorded in the binlog), loading the changes into memory. The second GRANT then succeeded, and the slave could resume replication.

Is the GRANT operation atomic? The experiment shows that even when GRANT returns an error, it may still trigger side effects (privilege reload). Therefore, GRANT is not a fully atomic operation.

Experiment verification : The described steps were executed in a controlled environment, reproducing the replication break and confirming the implicit privilege reload behavior.

Summary :

GRANT is not atomic; it always triggers an implicit privilege‑table reload, regardless of success or failure.

Direct DML changes to mysql.user are discouraged in production. If such changes are made, manually run FLUSH PRIVILEGES to ensure the server reloads the updated privileges.

Reference :

MySQL 8.0 Reference Manual – Privilege Changes: https://dev.mysql.com/doc/refman/8.0/en/privilege-changes.html