dql7588
dql7588
2018-04-20 20:49
浏览 90
已采纳

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

图片转代码服务由CSDN问答提供 功能建议

我最近格式化了我的mac book pro,从github克隆了proyect并安装了我需要的东西,比如MySql和Sequel Pro我尝试迁移数据库信息,但是我收到此错误:

  Illuminate \ Database \ QueryException:SQLSTATE [42000]:语法错误或访问冲突:1231变量'sql_mode' 不能设置为'NO_AUTO_CREATE_USER'的值(SQL:select * from information_schema.tables where table_schema = fisica和table_name = migrations)
 
异常跟踪:
 
 1 PDOException ::(“SQLSTATE [42000]  ]:语法错误或访问冲突:1231变量'sql_mode'不能设置为'NO_AUTO_CREATE_USER'的值“”
   
 
 

版本:

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 
   
 
 

我从Sequel P创建了数据库 RO GUI

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

5条回答 默认 最新

  • dongxun4110
    dongxun4110 2018-05-16 13:25
    已采纳

    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',
            ],
        ],
    
    点赞 评论
  • douhuigan8063
    douhuigan8063 2018-04-20 22:10

    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.

    点赞 评论
  • dsiuy42084
    dsiuy42084 2018-05-28 08:07

    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',
             ],
    
    点赞 评论
  • dtpn60029
    dtpn60029 2018-08-31 08:25

    My problem was a misspelled database name in the config.

    点赞 评论
  • dtzk85937
    dtzk85937 2019-06-12 14:11

    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';
    
    点赞 评论

相关推荐