Fixing MySQL 5.7 troubles easily

Many hosters are now updating their machines to use MySQL 5.7. A minor update doesn't sound that bad, unfortunately one small change in MySQL's default config can cause major headaches and late night patching parties.

What has changed? "ONLY_FULL_GROUP_BY" was added to the default SQL mode ANSI, which is per se a good thing. Now MySQL is even more ANSI compatible, who wouldn't be happy with that? Unfortunately it wasn't that compatible before, and some queries in SilverStripe don't care about this. This is where funny error messages and big WTFs in front of your computer suddenly start to pop up. StackOverFlow has a lot of questions about this topic. You're not the only one.

But why is this causing errors, what have we done wrong in the past? Again, the docs shed some light on this:

ONLY_FULL_GROUP_BY: Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

So you might get error messages when you want to e.g. sort by a column that is not selected like

Expression #1 of ORDER BY clause is not in SELECT list, references column 'database.LinkableLink.Sort' which is not in SELECT list; this is incompatible with DISTINCT

If you're lucky the error message even reveils its root, and when you read and try to understand that fancy ORM query SilverStripe generates for you you might find this at the end:

this is incompatible with sql_mode=only_full_group_by

Now that we know what is causing the problems we can think of fixing it. My hoster, which is taking care of my managed server, suggested, to disable this option globally. But I think it's a good thing to reason about compatibilty and while it's a very quick fix for all sites making problems I doubt if it's very future proof.

The best solution is to fix the erroring queries and solve your technical debt. In a project I was able to update SilverStripe and some modules which fixed this in the mean time. But we're not always so lucky that someone else stumbled in this problem and solved it for us.

If nothing works you have to tell SilverStripe to use the "old" standard for ANSI, which is the "new" ANSI without ONLY_FULL_GROUP_BY. It worked all the time and it's ok to tell a bunch of websites to use the old standard.

A look at the MySQL manual shows, that ANSI contains this flags:

ANSI: Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and ONLY_FULL_GROUP_BY.

So if we have to configure SilverStripe's database settings to use "REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE", which is the old MySQL 5.6 setting and we're done.

Luckily there are ready to use solutions. For SilverStripe 4 there is a quick config fix by SunnySideUp you can copy in your project and you're done. Based on this solution, Lukas Erni created a composer patch for SilverStripe 3.x you can add to your composer.json.

Rate this post

Post your comment

Comments

No one has commented on this page yet.

RSS feed for comments on this page | RSS feed for all comments