Laravel mysql迁移错误

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 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.

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',
         ],

My problem was a misspelled database name in the config.

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';