警惕参数变化，MySQL 8.0 升级避免再次踩坑

警惕参数变化，MySQL 8.0 升级避免再次踩坑

• 前言：
• 1. MySQL 8.0中废弃/移除的参数
  • 1.1 相关参数
    • 1.1.1 查询缓存相关参数
    • 1.1.2 事务相关参数
    • 1.1.3 日志相关参数
    • 1.1.4 数据类型格式
    • 1.1.5 sql_mode的变化
  • 1.2 MySQL 8.0升级踩坑 - 废弃的参数篇
• 2. MySQL 8.0中默认值改变的参数
  • 2.1 参数默认值变化
  • 2.2 一些重要参数的默认值变化说明
  • 2.3 MySQL 8.0升级踩坑，不同版本MySQL的参数默认值改变导致业务报错
    • 2.3.1 数据库升级之后，业务反馈导入数据报错
    • 2.3.2 问题分析解决
• 总结

前言：

上一篇博客sql_mode兼容性，MySQL 8.0 升级踩过的坑，提到了MySQL 8.0版本中sql_mode默认值的变化，导致升级之后业务访问报错。我们知道MySQL 8.0相对于MySQL 5.7加入了很多新特性，在功能和安全性上做了很多的优化和调整，这就不可避免地会修改一些参数或者函数。我们现有业务数据从MySQL 5.7及之前的版本升级到8.0，需要特别警惕这些参数的变化，这些变化可能会导致业务行为发生一些意料之外的结果！！！

最近MySQL 8.0 升级的过程中，我梳理了一些业务可能用到的、重要的参数变化，包括一些MySQL 8.0中已经废弃的参数和部分常见参数默认值的变化。对于一些innodb底层的参数变化，业务并无感知，就不用单独列出讨论。

1. MySQL 8.0中废弃/移除的参数

1.1 相关参数

1.1.1 查询缓存相关参数

查询缓存功能，在8.0.3版本中被整体移除，相关参数随之全部移除。

• query_cache_type
• query_cache_size
• query_cache_limit
• query_cache_min_res_unit
• query_cache_wlock_invalidate

1.1.2 事务相关参数

tx_isolation 和 tx_read_only 在8.0.3中移除，使用参数transaction_isolation 和transaction_read_only 替代

1.1.3 日志相关参数

expire_logs_days 设置binlog保留天数，从MySQL 8.0.11开始已经废弃，并把默认值修改为0，转而使用参数binlog_expire_logs_seconds替代。在MySQL 5.7中习惯使用expire_logs_days参数设置binlog保留时间的需要注意一下。

1.1.4 数据类型格式

date_format 和 datetime_formate 和 time_format 时间格式化函数，在MySQL 8.0.3中被移除。注意业务程序中使用这几个函数，在8.0中很可能会报错。

1.1.5 sql_mode的变化

sql_mode参数在MySQL 8.0中当然还是保留的，但是，要注意NO_AUTO_CREATE_USER 模式在8.0中被移除。

1.2 MySQL 8.0升级踩坑 - 废弃的参数篇

一套java程序的后端使用的数据库版本为MySQL 5.7。数据库升级到MySQL 8.0之后，业务代码连接数据库失败。报错信息为“java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)”

get connetion from pool error:ConnectionInfo{jdbcUrl='jdbc:mysql://9.9.9.9:3306/db?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8', host='9.9.9.9', port='3306', dbName='db', userName='appuser', userPasswd='appuser'} for 3 times
[WARN] 2022-03-29 09:56:28,389 [4c927ecf695a46d9a78afafa309885c6] com.tenpay.risk.ditto.core.common.data.ExceptionWarper.warper(ExceptionWarper.java:71) --> Exception=============com.tenpay.risk.ditto.core.common.exception.DbException
com.tenpay.risk.ditto.core.common.exception.DbException: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)

我们第一反应，这是不是jdbc的MySQL连接驱动版本不兼容？

首先，查看JAVA程序使用的MySQL连接驱动版本为5.1.38。如图所示：

然后，网上查询了mysql-connecter-java的版本兼容性，发现Connector/J version 5.1是可以部分兼容MySQL 8.0的。

接着，我们对MySQL Connector/J version 5.1的小版本（从5.1.1到5.1.49）都进行了测试，发现确实是部分兼容的。其中，5.1.44 - 5.1.49版本是支持MySQL 8.0的。也就是说5.1.44之后的版本都是支持MySQL 8.0的，而5.1.44之前的版本只有部分小版本是兼容，大部分不兼容。

最后，我们查阅一下mysql-connector-java-5.1.44版本的release note，发现这个版本修复了一个重要bug。使用参数transaction_isolation 和transaction_read_only 替代 tx_isolation 和 tx_read_only 。（参考官方文档：https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-44.html）

Important Change: Following the changes in MySQL Server 8.0.3, the system variables tx_isolation and tx_read_only have been replaced with transaction_isolation and transaction_read_only in the code of Connector/J. Users should update Connector/J to this latest release in order to connect to MySQL 8.0.3. They should also make the same adjustments to their own applications if they use the old variables in their codes. (Bug #26440544)

所以，解决java程序连接MySQL 8.0失败的方式，就是升级MySQL驱动版本到5.1.44以上版本，建议直接升级驱动到8.0版本。

2. MySQL 8.0中默认值改变的参数

2.1 参数默认值变化

MySQL 8.0中很多参数的默认值，相较于5.7来说发生了变化。

2.2 一些重要参数的默认值变化说明

1. MySQL 5.7之前系统默认字符集是latin1，相应的排序方式为latin1_swedish_ci；而在MySQL 8.0之后默认字符集改为utf8mb4，相应的排序方式为utf8mb4_0900_ai_ci。相关参数character_set_client、character_set_connection 、character_set_database 、character_set_results、 character_set_server 、collation_database 和 collation_server 的默认值都发生了改变。
2. MySQL 8.0之后推出了caching_sha2_password的认证插件，并且作为推荐使用的认证插件，增加了相应的认证插件参数default_authentication_plugin ，默认值为caching_sha2_password。
3. explicit_defaults_for_timestamp参数控制对timestamp列的default和null值的处理，默认值从OFF修改为ON。
4. event_scheduler 时间调度器默认值从OFF修改为ON，建议即使在MySQL 8.0中仍然显式设置为OFF。
5. innodb_autoinc_lock_mode 控制MySQL自增键的生成方式，MySQL 8.0中将默认值从1修改为2以提高部分场景下的并发性能。
6. innodb_flush_neighbors 脏块刷盘方式，当表数据存储在传统 HDD 存储设备上时，与在不同时间刷新单个page页相比，在一次操作中刷新此类相邻page页可减少 I/O 开销（主要用于磁盘寻道操作）。对于存储在 SSD 上的数据，寻道时间不再是影响IO性能的一个重要因素，所以在MySQL 8.0中将默认值设置为0以分散写入操作。
7. local_infile 参数决定能否使用load data local infile命令，在MySQL 5.7之前都是默认放开的，但是在MySQL 8.0之后，处于安全考虑，将默认值从ON修改为OFF。
8. log_error_verbosity 参数控制MySQL日志级别，默认值从3修改为2。
9. log_slave_updates 参数控制从库记录回放日志到binlog，MySQL 8.0中将默认值从OFF修改为ON，这里可能会增加IO负载压力。
10. sql_mode 模式的默认值中去掉了NO_AUTO_CREATE_USER ，MySQL 8.0的默认配置为sql_mode='ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION'。

2.3 MySQL 8.0升级踩坑，不同版本MySQL的参数默认值改变导致业务报错

2.3.1 数据库升级之后，业务反馈导入数据报错

数据库版本MySQL 5.7时，业务方一直使用LOAD DATA LOCAL INFILE的方式导入数据；升级到MySQL 8.0.23之后，反馈导数命令报错ERROR 1148 (42000)。

# 导数命令
MYSQL> LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE sbtest1 FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n' (id,name);

# 报错信息
ERROR 1148 (42000): The used command is not allowed with this MySQL version

2.3.2 问题分析解决

LOAD DATA LOCAL INFILE导入数据的权限是由参数 local_infile 控制的。在MySQL 5.7中，这个参数的默认值是ON，即允许客户端使用LOAD DATA LOCAL INFILE方式导入数据；而在MySQL 8.0中出于安全考虑，将这个参数的默认值设置成了OFF，即默认是禁止使用LOAD DATA LOCAL INFILE方式导入数据。

这里的可选解决方案有两种：

1. 在MySQL 8.0中显式设置local_infile=ON，保持与MySQL 5.7之前的默认配置一致；
2. 业务侧使用其他方式导入数据，譬如在shell中使用管道导入文件，或者在MySQL 客户端使用source导入sql文件。

很明显，方案1对于解决问题更简单直接；但是我更推荐使用方案2，客户端使用load data local infile本身是存在安全风险的，一般情况下还是禁用为好。

总结

MySQL 8.0增加了很多新的很有用的特性，相较于5.7的版本也有很多的变化。现有业务数据从MySQL 5.7及之前的版本升级到MySQL 8.0时，需要特别警惕一些参数的变化，这些变化可能会导致业务行为发生一些意料之外的结果。同时，规范数据库使用方式，也是避免踩坑中雷的有效方式。