MySQL Problems following Homestead upgrade

on January 6, 2016 Technical with 0 comments

We recently upgraded our standard Homestead development environment to V3.0. Initial testing with new Laravel projects went well, however we encountered two MySQL Problems following Homestead upgrade with some of our existing projects.

We use the Laravel Homestead Vagrant machine for all development work as it gives us a stable and consistent development environment. We use this environment for all Laravel development work, but also other projects such as Codeigniter development and WordPress sites as well.

MySQL Problems following Homestead upgrade

  • On older Laravel projects (using Laravel 4.2) the database migrations stopped working, emitting an error regarding the default values for timestamp columns.
  • Aggregate queries which included a GROUP BY clause were failing in some situations.

 

In the end both of the problems were tracked down to differences in the way MySQL operates between version 5.6 and 5.7. In later versions of 5.7 the default SQL-MODE has been set to a stricter configuration.

The configuration for 5.7 prevented the use of ZERO values as the default for date/time columns. This was the standard approach in earlier versions of Laravel – and in many other projects.

The problem with GROUP BY was slightly more insidious since it was only failing in some cases. Investigation showed that the queries that were failing had non aggregate columns in the select clause that were not in the GROUP BY clause.

Arguably this is bad practice, but MySQL allowed it where the extra columns are “identity” columns (meaning 1:1 mapping with a primary key) – in our case the queries were grouped on the primary key column only and the extra columns in the select were from the same table meeting the identity requirement.

Again in version 5.7 the SQL-MODE was defaulted to not allow these queries.

Solution

The default mode contains two components that caused the different problems we encountered.

NO_ZERO_DATE, ONLY_FULL_GROUP_BY

It is not easy to see the modes that have changed between different releases of the 5.7.x series. It appears that early versions of 5.7 grouped the zero date handling modes in with STRICT_TRANS_TABLES. There was clearly a reaction to this so the modes were separated out in later releases of 5.7 to give more granular control. See http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html for a full explanation of all the different modes.

Both of these modes should be encouraged for new applications, but we did not have the luxury of changing the schema or the runtime configuration at this time. We therefore have fixed the problem by adding a SQL-MODE directive to the .cnf file in Homestead.

Our production environment relies on MySQL 5.6 so we wanted to mirror the modes on this platform as closely as possible – which basically turns out to be an empty SQL-MODE.

We therefore created a file /etc/mysql/conf.d/mysql56.cnf that contains just two lines…

[mysqld]
sql-mode=""

After this Homestead behaved as before the upgrade. We are also updating our standards for new projects to adopt the stricter modes, and will likely end up using different configurations of Homestead for new and old projects. See the Homestead documentation for advice on how you can do this.