phpMyAdmin breaks Replication in MySQL 5.6

Recently i updated to MySQL 5.6 and we were really excited about the very good overall performance. But beside a major bug concerning wrong results when running a SELECT that includes a HAVING based on a function (see http://bugs.mysql.com/bug.php?id=69638) we also noticed that from time to time the replication breaks with the following error:

Last_SQL_Errno: 1590
Last_SQL_Error: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log

After some investigation it seemed like this happens if one modifies some user privileges, so we stumbled upon http://bugs.mysql.com/bug.php?id=68892.

Essentially the bug report says that if you use the wrong syntax for GRANT-statements the replication will break. So far, so bad. I told everyone who had the privileges to modify user privileges that they should really watch what they are doing and inform us if they accidentially used the wrong syntax.

Unfortunately that didn’t help. Not because they didn’t inform us but because phpMyAdmin sends a

REVOKE GRANT OPTION ON `database`@`table_name` FROM `user`@`host`;

if you modify the user-privileges for a specific table for example. If the user has no GRANT Option on that table the replication also breaks. MySQL throws the Error:

ERROR 1141 (42000): There is no such grant defined for user 'user' on host 'host'

Just adds the following to the binary log:

# Incident: LOST_EVENTS
RELOAD DATABASE; # Shall generate syntax error
# at 177

And the SLAVEs go out of sync. This should really be fixed as soon as possible but MySQL-Developers marked the bug only as “S3 (Non-critical)” so it seems that we gonna have to fix the replication very often in the next months or give console access to everyone who can grant/revoke privileges.

Sigh!

One thought on “phpMyAdmin breaks Replication in MySQL 5.6

  1. Fixed in 5.6.15:

    [19 Sep 14:16] Jonathan Stephens

    Fixed in 5.6+. Documented fix in the 5.6.15 and 5.7.3 changelogs, as follows:

    Issuing a GRANT statement with invalid parameters caused the
    master to write LOST_EVENTS events into its binary logs, causing
    replication to stop. Now such cases, if one or more grants or
    revocations of privileges are successful, an incident is written
    to the log; otherwise, only a warning is logged.