Laravel mysql migrate error

I recently format my mac book pro, after cloning the proyect from github and install the things I need like MySql and Sequel Pro I tried to migrate the database information but I get this error:

   Illuminate\Database\QueryException  : SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' (SQL: select * from information_schema.tables where table_schema = fisica and table_name = migrations)

  Exception trace:

  1   PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'")

Versions:

Mysql 8.0.11

Laravel 5.6.12

PHP 7.1.14 (cli)

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=fisica
DB_USERNAME=xxx
DB_PASSWORD=xxx

I created the database from Sequel PRO GUI

I finally found the solutions a days ago and I remembered this post. In the config/database.php file in mysql tag, the sql modes should be added in order to skip this error. https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-full

My MySQL array ended up like this:

    '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', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
        'modes'  => [
            'ONLY_FULL_GROUP_BY',
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_ENGINE_SUBSTITUTION',
        ],
    ],

In file:

> config/database.php

'mysql' =[
    ...
    'strict' => false
]

Also disable sql_mode

via SQL:

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';

via my.cnf inside heading [mysqld]

sql_mode=NO_ENGINE_SUBSTITUTION

Test the changes:

SHOW VARIABLES LIKE 'sql_mode';

Effectively you must add this code at the end of each of the connections you have with the mysql driver

'modes' => [
             'ONLY_FULL_GROUP_BY',
             'STRICT_TRANS_TABLES',
             'NO_ZERO_IN_DATE',
             'NO_ZERO_DATE',
             'ERROR_FOR_DIVISION_BY_ZERO',
             'NO_ENGINE_SUBSTITUTION',
         ],

I experienced the above situation after upgrading laravel from 5.3 to 5.7 with it previously working with MySQL 8 without any problems the accepted answer did not work for me and after googling around I did not find a solution. what worked for me was searching my project folder

grep -rnw 'Location_to_your_project_folder' -e 'sql_mode'

Which lead me to

~/vendor/laravel/framework/src/Illuminate/Database/Connectors/MySqlConnector.php:183:return "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'";

Once I removed the NO_AUTO_CREATE_USER from the list everything worked again.

In reading the mysql 8.0 documentation it looks like the NO_AUTO_CREATE_USER was removed from sql-mode. I suspect your my.cnf has this referenced and should be removed from your conf and any mysql setting internally and your mysqld restarted.

Keep in mind, I haven't upgraded to mysql 8.0 and just reading documentation. I'm happy using 5.6.

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

> Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.

That SQL Mode is not supported in your version of MYSQL. It was removed in version 8.0. The last version to support this sql mode was 5.8. Your MYSQL Version could be 8 or later. (Laravel should update too)

Comment out or delete that line from your config/database.php file

mysql' => [  ...  'modes' => [    ...    //'NO_AUTO_CREATE_USER',],

MySQL 8 is not supported. I had the same problem and had to use a lower MySQL version (5.7).

In MySQL 8, the option "NO_AUTO_CREATE_USER" was removed from the "sql_mode" parameter and Laravel is looking for that.

I came across same problem today with mysql 8, php 8, laravel 8

Solution

i just updated my config/database file and changed strict from true to false in following section

    'mysql' => [
        ...
        'prefix_indexes' => true,
        'strict' => false,
        'engine' => null,
        ...
    ],

and it worked for me.

Laravel 5.5 and greater tries to auto detect the mysql server version and if mysql 8.0.11 or greater, it leaves out NO_AUTO_CREATE_USER from the modes. However, in my case it was incorrectly detecting the version as 5.6.47.0. In laravel 7 they added the ability to override the version check by manually setting the version in config/database.php

'mysql' => [ // ... 'strict' => true, 'version' => '8.0.11', ]

On laravel versions less than 7.x the easiest solution is to set 'strict' => false

Illuminate/Database/Connectors/MySqlConnector.php

protected function strictMode(PDO $connection, $config) { $version = $config['version'] ?? $connection->getAttribute(PDO::ATTR_SERVER_VERSION);

    if (version_compare($version, '8.0.11') >= 0) {
        return "set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'";
    }

    return "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'";
}

My problem was a misspelled database name in the config.