Laravel mysql 迁移错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49949526/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel mysql migrate error
提问by Julian Mendez
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:
我最近格式化了我的 mac book pro，在从 github 克隆 proyect 并安装了我需要的东西（如 MySql 和 Sequel Pro）后，我尝试迁移数据库信息，但出现此错误：
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
mysql 8.0.11
Laravel 5.6.12
Laravel 5.6.12
PHP 7.1.14 (cli)
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
我从 Sequel PRO GUI 创建了数据库
回答by Julian Mendez
I finally found the solutions a days ago and I remembered this post.
In the config/database.phpfile 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
几天前我终于找到了解决方案，我记得这篇文章。在config/database.phpmysql 标签中的文件中，应添加 sql 模式以跳过此错误。https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-full
My MySQL array ended up like this:
我的 MySQL 数组最终是这样的：
'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',
],
],
回答by eValdezate
Effectively you must add this code at the end of each of the connections you have with the mysql driver
实际上，您必须在与 mysql 驱动程序的每个连接的末尾添加此代码
'modes' => [
???????????? 'ONLY_FULL_GROUP_BY',
???????????? 'STRICT_TRANS_TABLES',
???????????? 'NO_ZERO_IN_DATE',
???????????? 'NO_ZERO_DATE',
???????????? 'ERROR_FOR_DIVISION_BY_ZERO',
???????????? 'NO_ENGINE_SUBSTITUTION',
???????? ],
回答by Efrén
In file:
在文件中：
config/database.php
配置/数据库.php
'mysql' =[
...
'strict' => false
]
Also disable sql_mode
via SQL:
还可以
通过 SQL禁用 sql_mode ：
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
via my.cnf inside heading [mysqld]
通过 my.cnf 里面的标题 [mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
Test the changes:
测试更改：
SHOW VARIABLES LIKE 'sql_mode';
回答by Dom DaFonte
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.
在阅读 mysql 8.0 文档时，看起来 NO_AUTO_CREATE_USER 已从 sql-mode 中删除。我怀疑你的 my.cnf 引用了这个，应该从你的 conf 和内部的任何 mysql 设置中删除，你的 mysqld 重新启动。
Keep in mind, I haven't upgraded to mysql 8.0 and just reading documentation. I'm happy using 5.6.
请记住，我还没有升级到 mysql 8.0，只是在阅读文档。我很高兴使用 5.6。
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
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.
使用 GRANT 创建用户。相反，使用创建用户。遵循这种做法会使 NO_AUTO_CREATE_USER SQL 模式对于 GRANT 语句变得无关紧要，因此它也被删除了。
回答by hendrikus van katwijk
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
在将 laravel 从 5.3 升级到 5.7 之后，我遇到了上述情况，它以前使用 MySQL 8 没有任何问题，接受的答案对我不起作用，在谷歌搜索后我没有找到解决方案。对我有用的是搜索我的项目文件夹
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_USERfrom the list everything worked again.
一旦我NO_AUTO_CREATE_USER从列表中删除了它，一切又恢复了。
回答by Victor Mwenda
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)
您的 MYSQL 版本不支持该 SQL 模式。它在 8.0 版中被删除。支持这种 sql 模式的最后一个版本是 5.8。您的 MYSQL 版本可能是 8 或更高版本。（Laravel 也应该更新）
Comment out or delete that line from your config/database.phpfile
从config/database.php文件中注释掉或删除该行
mysql' => [
...
'modes' => [
...
//'NO_AUTO_CREATE_USER',
],
回答by gedijedi
My problem was a misspelled database name in the config.
我的问题是配置中的数据库名称拼写错误。
