How to disable MySQL strict mode on Laravel Forge (Ubuntu)

MySQL has had a strict mode since 5.1, but in 5.7 it became the default. If you've spun up a server recently and all of a sudden your apps have broken, this may be the source.

In Laravel, you can fix this in code: edit your database.php config file, and add a key of strict with a value of false. But if you're using a non-Laravel application (we've run into this with both CodeIgniter and CraftCMS applications), you won't have that option. Here's how to disable strict mode globally on any Laravel Forge server (and any other Ubuntu server).

Note: I'm not advocating for disabling strict mode. These new modes provide speed and consistency benefits that are worth keeping them enabled. I want to give you the options in case you need them, but my recommendation is to keep them enabled and to learn how to work with them.

Your configuration file

MySQL actually looks five different places for configuration files, so you can make the change I'm about to recommend several places. It'll look in /etc/my.cnf, /etc/mysql/my.cnf, SYSCONFDIR/my.cnf, $MYSQL_HOME/my.cnf, and ~/my.cnf. ~/.my.cnf is user-specific, and the third and fourth options rely on specifics from your environment. So let's stick with one of the first two.

On a default Laravel Forge box, the default MySQL configuration will live in /etc/mysql/my.cnf, so let's put our changes there. SSH into your server and use Vim or Pico to edit that file.

Your lines

If you scroll down the file a bit, you'll find the [mysqld] section. We're going to add a new key, sql_mode. On MySQL 5.7, the default values for this key out of the box are:

STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The strict mode comes from STRICT_TRANS_TABLES. So, let's overwrite the sql_mode and set it to be the same as the default, but without strict mode.

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

That's it! Save the file, and restart MySQL. From the command line that would be sudo /etc/init.d/mysql restart, or from the Laravel Forge interface, open the server, click the Restart Services icon at the bottom, and choose Restart MySQL.

Note: If you're using CraftCMS or certain versions of Laravel, you'll likely also want to disable the "ONLY_FULL_GROUP_BY" option.