MySQL's GROUP_CONCAT limitations and cascading bad luck

Jordi Boggiano • August 11, 2015

php web

We had an incident today over at Teamup (where I have worked for the last 9 months by the way:) which is worth a quick blog post if it helps save anyone from having a bad day.

We are using MySQL's GROUP_CONCAT feature to fetch a list of ids to delete when cleaning up old demo calendars. You end up with a list of ids in one row, easy to fetch, split it on commas, and done. So far so good. Then we run a few DELETE ... WHERE id IN (...) queries to clean things up in a few tables. So far so good.

However if you fail to read the fine print on the MySQL docs, you might not have seen this sentence: The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. What this means is that a query that worked just fine in testing conditions, suddenly started failing in production once the data set hit a critical size. Thanks to another stroke of bad luck, it returned a list of ids truncated right after a comma (3,4,5,) so we had an empty id in our WHERE IN (3,4,5,) clause. Unfortunately combined with the fact we had optional relations in some tables (I won't bore you with details) that empty match made it wipe about 60% of the data in those.

Thankfully we have backups on top of the DB replication which let us recover the lost data pretty quickly, and it only affected a small feature in the grand scale of things, but this could have ended much worse so it is worth pointing out a few things:

And now to hope for a more quiet rest of the week!

Edit: There is some good news, MySQL 5.8 might include a fix and turn the current warning for truncation into an error, see http://bugs.mysql.com/bug.php?id=78041