毅窝疯

遇到 MySQL 8.0.11 的一些坑

By hybnx 2019-04-17 sql相关 来源:https://learnku.com/articles/10736/some-craters-in-mysql-8011 访问:223

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.