Laravel mysql迁移错误

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

My problem was a misspelled database name in the config.

Answer 5

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