Critical MySQL 5.6 bug: any user with GRANT privileges can unwillingly cause all replicas to break
The latest major release of MySQL brought us a lot of new and exciting features. As always, new features come with brand new bugs waiting to bite you in the least expected way.
I was implementing a monitoring system for a client and needed to create a new dedicated user. I went ahead and granted the usual basic privileges but later, I discovered that this user also needed the REPLICATION CLIENT privilege because the monitoring needed it to check for replication status.
No big deal, let’s grant this as well. Unfortunately I did not get it right at first:
grant replication client on database.* to [email protected]’10.%’;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
Ouch! My bad! This is a global privilege and I should grant it on “*.*”:
grant replication client on *.* to [email protected]’10.%’;
Query OK, 0 rows affected (0.10 sec)
I flushed privilege table and moved forward to other things.
After a short while, a page came in: replication had stopped on a replica of the database I was working on. I quickly connected to the paging replica and issued a SHOW SLAVE STATUS:
Seconds_Behind_Master: NULL
Last_SQL_Error: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log
LOST EVENTS? I heard about those in MySQL Cluster before, but we are using stock 5.6 replication here. A bit of digging on the MySQL bugzilla site lead me to the following bug:
Bug #68892 Invalid use of GRANT command breaks replication
Basically, any GRANT command that errors out will break replication, and you will have to skip the offending event on ALL replicas (or inject an empty transaction if GTID in use).
The reason behind this is the fact that MySQL 5.6 writes the following in the binlog when your grant fails:
#130410 18:06:40 server id 1 end_log_pos 874 CRC32 0x4d815dbc
# Incident: LOST_EVENTS
RELOAD DATABASE; # Shall generate syntax error
# at 874
The purpose is to avoid that a grant that fails on the master due to insufficient privileges, succeeds on the replicas instead (due to the fact that replication thread runs with SUPER privileges). To avoid that, replication is stopped with an error.
Bottom line is, any user on 5.6 which has GRANT privileges should be careful when GRANTing – as you have seen, it is very easy to break replication completely.
Verified on MySQL 5.6.12.
No comments