Laravel Incorrect date value sql_mode empty

Question

I have a problem executing a sql update/insert using Laravel's Query Builder (DB Facade). When inserting a row into a table I get the

QueryException: Invalid datetime format: 1292 Incorrect date value: '0000-00-00' for column 'FoundationDate'.

When I copy the SQL statement to PHPMyAdmin, it works without problems. I know it depends on the sql_mode setting. I checked this variable and there is no strict mode enabled! So how is this possible that it works on PHPMyAdmin but in Laravel it doesn't?

I'm using Mysql 5.5.49 and Laravel 5.3.

Thanks in advance!

Where did you check the variable? The value can be set for current session and a decent framework should always do it. Whatever, I think it'd be less messy to just prevent invalid dates to happen in the first place. — Álvaro González
In PhpMyAdmin you can check variable values (Tab "Variables"). The variable sql_mode is empty. — Yannici
PhpMyAdmin is not part of your Laravel application so it'll be establishing its own database connections. You need to verify the value from PHP itself. — Álvaro González
I'll clarify: go to "Search/ Find in files" and look for the string "sql_mode" in your entire codebase. — Álvaro González

cyclobster cyclobster · Accepted Answer · 2017-02-01T16:25:02

For Laravel 5.3 sql_mode is set at runtime by Illuminate\DatabaseConnectors\MySqlConnector@setModes:

/**
 * Set the modes for the connection.
 *
 * @param  \PDO  $connection
 * @param  array  $config
 * @return void
 */
protected function setModes(PDO $connection, array $config)
{
    if (isset($config['modes'])) {
        $modes = implode(',', $config['modes']);

        $connection->prepare("set session sql_mode='{$modes}'")->execute();
    } elseif (isset($config['strict'])) {
        if ($config['strict']) {
            $connection->prepare("set session sql_mode='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'")->execute();
        } else {
            $connection->prepare("set session sql_mode='NO_ENGINE_SUBSTITUTION'")->execute();
        }
    }
}

Notice sql_mode's NO_ZERO_DATE is set in that method. The sql_mode can be altered in your config.database.php file by adding a modes key to your mysql connection like below:

'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
        'modes'  => [],
    ],
]

You can leave modes as an empty array or configure it how you want. More info here

Alternatively, you can just set strict => false,

Laravel Incorrect date value sql_mode empty

2 Answers