PHP连接MySQL 8.0报错的解决办法

Wesley13
• 阅读 1165

MySQL升级到8.0之后,PHP连接报错怎么解决?

作为资深的MySQL票友,最近我总在给8.0吹水,我们知数堂的MySQL课程去年也早就升级到8.0版本了。我的VPS老早就想升级了,无奈PHP版本太低,担心升级后有问题,所以一直没去折腾。这两天稍微得空,就开始折腾升级MySQL版本。

为了解锁最香新功能,我直接升级到了最新的MySQL 8.0.19版本:

[yejr@imysql.com]> \s
..
Using delimiter:    ;
Server version:        8.0.19 MySQL Community Server - GPL)
Protocol version:    10
...
Binary data as:        Hexadecimal

注意到上面的 Binary data as: Hexadecimal 了吗,我前几天的文章 MySQL 8.0.19客户端的一个小变化 中介绍了这个新特性。

由于我的PHP还是相对较低的版本,MySQL 8.0相对于5.7版本,对PHP这些应用程序影响最大的就是账号认证方式,由此前的 mysqlnativepassword 变成了 cachingsha2password。除了账号授权方式要保持使用 native 认证插件外,还有个要注意的默认使用的字符集还得保持用 utf8,否则可能会报告下面两种错误:

SQLSTATE[HY000] [2054] Server sent charset unknown to the client

或者是

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

看到上面的报错不用慌,只需要修改下面两个MySQL参数即可:

#MySQL 8.0起默认字符集改成了utf8mb4,这造成PHP PDO连接报错
#只需要重新指定默认字符集为utf8即可,但这并不影响在MySQL里创建utf8mb4字符集的表
character-set-server = utf8

#指定账号认证plugin还使用native模式,否则PHP PDO连接握手时会报错
default_authentication_plugin = mysql_native_password

在MySQL 8.0下创建新账号时,需要先 CREATE USER 并指定密码,然后再 GRANT 授权,不能再像以前那样,可以直接用一条 GRANT 语句搞定 创建账号+指定密码+授权 这三件事了,例如:

[yejr@imysql.com]> CREATE USER yejr@localhost identified with mysql_native_password by 'c97721c63c9fc077';
[yejr@imysql.com]> GRANT ALL ON yejr.* to yejr@localhost;

把MySQL从5.7到8.0的升级方案,建议如下:

  1. 先将当前的MySQL 5.7升级到最新子版本,例如5.7.29。
  2. 检查错误日志,确认没有任何表在升级过程中报错。
  3. 设置innodb_fast_shutdown=0,关闭5.7.29版本实例。
  4. 修改my.cnf参数,使之适应8.0版本,例如去掉 internal_tmp_disk_storage_engine 参数。
  5. 在my.cnf中增加参数 upgrade = AUTO,使得MySQL 8.0启动后能自动升级系统表和用户表。
  6. 检查确认MySQL 8.0实例启动过程中无其他报错。

在升级过程中,我还发现如果使用drupal系统的话,可能还会有个报错:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' in lock_may_be_available() (line 167 of xx/includes/lock.inc).

那是因为在drupal代码中,连接mysql时顺便设置了SQLMODE,其中 NOAUTOCREATEUSER 这个模式在8.0之后不复存在了,只需要微调下面的代码:

[root@yejr.me]# vim includes/database/mysql/database.inc

    $connection_options['init_commands'] += array(
      #注释掉本行代码,或者删除 NO_AUTO_CREATE_USER 模式即可
      #'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'",
      'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'",
    );

好了,现在全站都升级到MySQL 8.0了,yeah✌️

enjoy MySQL 8.0。

全文完

由我主讲的知数堂「MySQL优化课」第17期已发车,我们的课程从第15期就升级成MySQL 8.0版本了,现在上车刚刚好,一起开启MySQL 8.0的修行之旅吧

另外,我在腾讯课堂MySQL性能优化》精编课程已完结,本课程讲解读几个MySQL性能优化的核心要素:合理利用索引,降低锁影响,提高事务并发度

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
MySQL的各种JOIN
主题:MySQL的各种JOIN大纲:1、徐老师从事多年官方MySQL工作,为众多企业提供MySQL帮助时,企业比较关心的问题是什么呢?2、随着MySQL8.0的成熟和推广,相信越来越多的公司希望升级MySQL8.0,但又会担心低版本到高版本的升级会不会有兼容问题,徐老师能否分享下相关经验?3、徐
Wesley13 Wesley13
3年前
PHP创建多级树型结构
<!lang:php<?php$areaarray(array('id'1,'pid'0,'name''中国'),array('id'5,'pid'0,'name''美国'),array('id'2,'pid'1,'name''吉林'),array('id'4,'pid'2,'n
Wesley13 Wesley13
3年前
MySQL5.7升级到8.0过程详解
前言:不知不觉,MySQL8.0已经发布好多个GA小版本了。目前互联网上也有很多关于MySQL8.0的内容了,MySQL8.0版本基本已到稳定期,相信很多小伙伴已经在接触8.0了。本篇文章主要介绍从5.7升级到8.0版本的过程及注意事项,有想做版本升级的小伙伴可以参考下。1.升级前准备及注意事项首先
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这