遇到 MySQL 8.0.11 的一些坑

遇到的坑

1、Authentication type:
用户的 Authentication type 默认为 caching_sha2_password,导致数据库连接错误,抛出如下异常:
Illuminate\Database\QueryException : SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client
解决方案:修改密码认证方式
ALTER USER 'YOURUSERNAME'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOURPASSWORD';

2、删除了 NO_AUTO_CREATE_USER 模式
在 5.7.*的日志中提到已废除该模式,在8.0.11中删除了,迁移时会抛出如下异常:
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'

解决方案:将 config/database.php 配置文件中mysql 的 strict 的值改为false即可!
目前就发现这两个,如有有其他的坑可以评论一下:hand:

Deprecation and Removal Notes

Incompatible Change: These deprecated compatibility SQL modes have been removed: DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS. They can no longer be assigned to the sql_mode system variable or used as permitted values for the mysqldump --compatible option.

Removal of MAXDB means that the TIMESTAMP data type for CREATE TABLE or ALTER TABLE is no longer treated as DATETIME.

For MySQL 5.7 applications that use SQL modes removed in MySQL 8.0, statements may fail when replicated from a MySQL 5.7 master to a MySQL 8.0 slave, or may have different effects on master and slave. To avoid such problems, applications that use modes removed in MySQL 8.0 should be revised to avoid them.

The following features related to account management have been removed:

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.

Using GRANT to modify account properties other than privilege assignments. This includes authentication, SSL, and resource-limit properties. Instead, establish such properties at account-creation time with CREATE USER or modify them afterward with ALTER USER.

IDENTIFIED BY PASSWORD 'hash_string' syntax for CREATE USER and GRANT. Instead, use IDENTIFIED WITH auth_plugin AS 'hash_string' for CREATE USER and ALTER USER, where the 'hash_string' value is in a format compatible with the named plugin.

Additionally, because IDENTIFIED BY PASSWORD syntax has been removed, the log_builtin_as_identified_by_password system variable is superfluous and has been removed.

The PASSWORD() function. Additionally, PASSWORD() removal means that SET PASSWORD ... = PASSWORD('auth_string') syntax is no longer available.

The old_passwords system variable.

本帖由系统于 9个月前 自动加精
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
讨论数量: 21
朕略显ぼうっと萌

厉害啊,老哥,我也全遇到了,

10个月前

基本上全遇上了。。。

10个月前

遇到了第二个。

9个月前

mark

9个月前

全遇上了.jpg :+1:

8个月前

我觉得应该顶上去

8个月前

本人刚开始学习MySQL和php 我也遇到了这两个问题 :+1:。
但是第二个问题,我没有找到config/database.php这个文件,我是从官网下载的mysql 8.0.11 x64 windows版本,免安装的。还请帮忙前辈指导一下。

8个月前

@sunny-w Laravel 什么版本?

8个月前
Atzcl

遇到了第二点,,,

8个月前

赞,多分享这些

8个月前

在哪里修改密码认证方式

8个月前

。。。 今天刚好碰到这个两个问题,解决方法也是如此

8个月前
Andy_xiaojun

我遇见第一个问题了,心里苦~可是laravel还是连接不上,Heidisql能连接

7个月前

file

密码格式改了还是出现第一个问题怎么解

6个月前

@某某某 我这个只是针对用户设置的,不是全局使用mysql_native_password

6个月前

mark

5个月前
j475523225

Storage engine 'MyISAM' does not support system tables. [mysql.user]

3个月前

两种问题都遇到了,但是都没解决问题 依然连不上 客户端可以连接上,没有修改身份验证模式的时候 laravel可以连接,客户端不行,修改该后 客户端可以 laravel 不行。

3个月前

@redrain 解决了可以在下面说明一下解决方案

3个月前

老哥厉害,我用 laradock ,自带的 mysql 是最新的,搞了一天了,google 也找不到解决方案,已经打算降版本试试了。在老哥这本来不抱希望的,结果解决了。

3个月前
Chet-Li

ReflectionException (-1)
Class setEventDispatcher does not exist
老哥之前的项目根据你的教程已经可以run起来,不过有一个新的项目却一直报错找不到setEventDispatcher php7.3 mysql8.0 您有遇到过吗,google找了好久

2个月前

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!