"Strict" mode and other MySQL customizations in Laravel 5.2

If you remember my post How To Disable MySQL Strict Mode on Laravel Forge (Ubuntu), you'll remember that MySQL 5.7 introduced something we've been casually calling "strict mode," which is really a combination of new modes that, in sum, make MySQL process your queries a little more strictly than before.

In my previous post I showed how to disable it on Ubuntu, but since then, Adam Wathan has added a feature to Laravel that allows you to define whether you're using "strict" mode and also allows you to customize exactly which modes you'd like enabled--all in code.

If I can set a configuration option in code instead of on a server without suffering a performance hit, I'll always prefer it--it's one less thing I have to do every time I deploy to a new server. So, I'm totally glad for this new feature.

It's worth noting that you can use this feature not just to disable strict mode on 5.7; you can also enable it on 5.6. It might be wise to enable it on any app running on 5.6 so that you can prepare for 5.7, seeing if anything breaks when you turn on some of the stricter modes.

MySQL 5.7 and "strict" mode

Before we talk about the feature, let's quickly cover what "strict mode" means.

MySQL has "modes", each of which enable or disable a certain behavior. For example, ERROR_FOR_DIVISION_BY_ZERO is a mode that, you guessed it, throws an error when you divide by zero in a SQL division operation. Without this mode enabled, you'll just get a NULL result silently.

"Strict mode," which is really just the list of modes 5.7 enables by default, is comprised of the following modes:

ONLY_FULL_GROUP_BY
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION

You can learn more about these modes at the MySQL documentation.

Prior to 5.7, the only mode that was enabled was NO_ENGINE_SUBSTITUTION.

Enabling and disabling strict mode in Laravel 5.2

With this new feature, Laravel now has the ability to do three things: Disable "strict" mode, returning to the <= 5.6 behavior; enable "strict" mode, setting it to the 5.7 behavior; or customizing exactly which modes are enabled.

These settings live in config/database.php in the connections.mysql section. For starters, let's look into enabling and disabling "strict" mode:

    'connections' => [
        'mysql' => [
            // Behave like MySQL 5.6
            'strict' => false,

            // Behave like MySQL 5.7
            'strict' => true,
        ]
    ]

Customizing which modes are enabled

But what if you're not satisfied with either 5.6's or 5.7's mode defaults? Just customize them yourself.

    'connections' => [
        'mysql' => [
            // Ignore this key and rely on the strict key
            'modes' => null,

            // Explicitly disable all modes, overriding strict setting
            'modes' => [],

            // Explicitly enable specific modes, overriding strict setting
            'modes' => [
                'STRICT_TRANS_TABLES',
                'ONLY_FULL_GROUP_BY',
            ],
        ]
    ]

That's all, folks

You now have the ability to take total control over which modes are enabled on your app's MySQL server, in code, without touching a line of server configuration. Just like that.

By default, I'd recommend leaving them all on. But there may be occasions with particular use cases or old projects where you need to customize this list, and it's now possible--and even simple.