运维首页 > 数据运维 > 实战——研究一个 MySQL8.0.32 优化器的严重 Bug

实战——研究一个 MySQL8.0.32 优化器的严重 Bug

2024年 2月 4日 80.7k 0

背景

近日，一位群友分享了关于 MySQL8.0.32 优化器的 Bug。我在 Bug 报告网站上详细研究了此 Bug，认为比较严重，故决定与更多人分享。以下为我对此 Bug 的详细分析过程，内容较为繁琐，建议挑重点阅读。

Bug report 的描述

https://bugs.mysql.com/bug.php?id=109699

以下是翻译:

描述：以下查询在 MySQL 8.0.31（以及最新的 MySQL 5.6）中预期返回 1 行：
['❤' => 'žlutý_😀']
但自 MySQL 8.0.32 起，没有返回任何行。
复现步骤:https://pastebin.com/qghkzwTu

CREATE TABLE `employee` (
`id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
`name` VARCHAR(255) DEFAULT NULL,
`surname` VARCHAR(255) DEFAULT NULL,
`retired` TINYINT(1) DEFAULT NULL,
PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

insert into `employee` (`id`, `name`, `surname`, `retired`) values (1, 'Oliver', 'Smith', 0);
insert into `employee` (`id`, `name`, `surname`, `retired`) values (2, 'Jack', 'Williams', 1);
insert into `employee` (`id`, `name`, `surname`, `retired`) values (3, 'Harry', 'Taylor', 1);
insert into `employee` (`id`, `name`, `surname`, `retired`) values (4, 'Charlie', 'Lee', 0);

select * from (select 'žlutý_😀' `❤`) `🚀` where `❤` = 'žlutý_😀' group by `🚀`.`❤` having (`❤` = 'žlutý_😀');

如下，经过我的验证，确实如报告所述，在 8.0.32 版本中，上述查询未返回结果，并出现一个警告，提示Invalid utf8mb4 character string: 'FD5F3F'

mysql> select * from (select 'žlutý_😀' `❤`) `🚀` where `❤` = 'žlutý_😀' group by `🚀`.`❤` having (`❤` = 'žlutý_😀');
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1300 | Invalid utf8mb4 character string: 'FD5F3F' |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)

在 8.0.31 和 8.0.33 版本中，相同的 SQL 查询均返回结果，但也出现警告，不过不是Invalid utf8mb4 character string: 'FD5F3F'

mysql> select * from (select 'žlutý_😀' `❤`) `🚀` where `❤` = 'žlutý_😀' group by `🚀`.`❤` having (`❤` = 'žlutý_😀');
+--------------+
| ❤            |
+--------------+
| žlutý_😀       |
+--------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------+
| Warning | 1300 | Cannot convert string 'xF0x9Fx9Ax80' from utf8mb4 to utf8mb3 |
| Warning | 1300 | Cannot convert string 'xF0x9Fx9Ax80' from utf8mb4 to utf8mb3 |
+---------+------+------------------------------------------------------------------+
2 rows in set (0.00 sec)

这些警告提示字符串'xF0x9Fx9Ax80'
（代表🚀）无法从 utf8mb4 转换为 utf8mb3。这并非 Bug 的直接原因，只是由于 MySQL 表别名不支持 utf8mb4，需转换为 utf8mb3，但 emoji 字符无法转换，从而引发警告。例如：

mysql> select * from employee as `🚀`;
+----+---------+----------+---------+
| id | name | surname | retired |
+----+---------+----------+---------+
| 1 | Oliver | Smith | 0 |
| 2 | Jack | Williams | 1 |
| 3 | Harry | Taylor | 1 |
| 4 | Charlie | Lee | 0 |
+----+---------+----------+---------+
4 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------+
| Warning | 1300 | Cannot convert string 'xF0x9Fx9Ax80' from utf8mb4 to utf8mb3 |
+---------+------+------------------------------------------------------------------+
1 row in set (0.01 sec)

这个简单的查询里，定义了 employee 别名为 🚀
(属于 utf8mb4)，就有这个 warnings，但不影响查询。而定义为中文字符(属于 utf8mb3)则没有 warnings

mysql> select * from employee as `表`;
+----+---------+----------+---------+
| id | name | surname | retired |
+----+---------+----------+---------+
| 1 | Oliver | Smith | 0 |
| 2 | Jack | Williams | 1 |
| 3 | Harry | Taylor | 1 |
| 4 | Charlie | Lee | 0 |
+----+---------+----------+---------+
4 rows in set (0.00 sec)

Cannot convert string 'xF0x9Fx9Ax80' from utf8mb4 to utf8mb3
这个 warnings 是题外话，本次 Bug 只会产生Invalid utf8mb4 character string: 'FD5F3F'
这一类 warnings。

Bug 的影响范围

这是一个特定版本（MySQL 8.0.32）的 Bug。在 8.0.31 及之前版本中未发现此 Bug，而在 8.0.33 版本中已得到官方修复。

Bug 的严重性

这个 Bug 的严重性主要体现在两个方面：

  • 一. 这个 Bug 会导致数据不正确。Bug 汇报者的案例可以看出，原本应该有结果的 SQL，最终结果为空了。
  • 二. 这个 Bug 的触发条件极为普遍。前面那个 Bug report 的模拟案例太复杂了，好像要列名还有 emoji 表情才会触发。其实不是！触发条件极为普遍，实际上并不需要特定的列名或 emoji 表情即可触发这个 Bug，后面会讲。

这个 Bug 的触发条件极为普遍，以下是证据：

  1. 刚发布的版本就有用户遇到 Bug。8.0.32 在 2023 年 1 月 17 日发布，Bug 报告在 1 月 19 日提交。

  2. 多人反馈了此 Bug，见https://bugs.mysql.com/bug.php?id=109699 。

  1. 从 Bug 的原理分析

这里就是我要重点展开的部分。

官方回应和 Bug 分析见同一链接。https://bugs.mysql.com/bug.php?id=109699

如图，我们知道这个 Bug 官方也很重视， 2 月 2 日声明 8.0.33 版本修复了此 Bug，并发布了修复详情。

更多信息见 MySQL 8.0.33 Release Notes。

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-33.html

（根据 Bug report ID 109699，我们可以找到这段话）

  • When cloning a condition to push down to a derived table, characters in strings representing conditions were converted to utfmb4
    correctly only for values less than 128 (the ASCII subset), and code points outside the ASCII subset were converted to invalid characters, causing the resulting character strings to become invalid. For derived tables without UNION, this led to problems when a column name from the derived table used characters outside the ASCII subset, and was used in the WHERE
    condition. For derived tables with UNION
    , it created problems when a character outside the ASCII subset was present in a WHERE
    condition.
  • We fix these issues by initializing the string used for representing the condition in such cases to the connection character set. (Bug #109699, Bug #34996488)

这里我们可以通过Bug report ID 109699
拿到内部 Bug id 为 34996488，之后，相关修复提交可以在 MySQL GitHub Repository 查看。

我们找到了一个 commit，修复了这个 Bug

https://github.com/mysql/mysql-server/commit/463608249c245c09b71aa55da3022eac95828a0f

这里展示了官方是如何描述这个 Bug 的。

下面，有具体的代码，展示如何修复的。十几个字符就修复了这个 Bug。

这个 Bug 只发生在 8.0.32，我们假定这个 Bug 的引入就是内核开发人员上一次修改 sql_derived.cc 源码时引入的。那么我们可以这样调查原因。
首先，进入 sql_derived.cc 文件

然后，点击 history 查看代码历史修改记录

这里能看到谁在什么时候修改过代码。

我对 Bug 的总结

  • Bug 链接：[MySQL Bug #105969](https://bugs.mysql.com/bug.php?id=105969)
  • 官方修复代码：[MySQL Commit](https://github.com/mysql/mysql-server/commit/463608249c245c09b71aa55da3022eac95828a0f)
  • 官方修复公告：[MySQL 8.0.33 Release Notes](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-33.html)

前面提到了，我找到和 Bug 有关的描述页面就这三个，我基于这三个页面看到的做了总结。

问题描述

在 MySQL 中，创建派生表是一个常见操作，尤其是在执行涉及 JOIN 操作或特定 WHERE 条件的复杂查询时。这个 Bug 涉及到字符集的处理问题，具体表现在：

  1. 字符集问题：
  • 在处理派生表的 WHERE 条件时，MySQL 默认使用二进制字符集，仅能正确处理 ASCII 字符集（英文字母和常见符号）。
  • 当 WHERE 条件字符串包含非 ASCII 字符时（如中文、阿拉伯文或特殊符号），MySQL 无法正确处理。
  1. 非 ASCII 字符问题：

  • 派生表无 UNION 操作：如果 WHERE 条件用到派生表列名，并且列名包含非 ASCII 字符，则会出现问题。

  • 官方给出的测试案例：

SELECT * FROM (SELECT 'å' AS x) AS dt WHERE x = 'å';

  • 派生表使用 UNION 操作：如果 WHERE 条件本身包含非 ASCII 字符，也会出现问题。

  • 官方给出的测试案例：

CREATE TABLE t1 (f1 VARCHAR(10));
INSERT INTO t1 VALUES('å');
SELECT * FROM (SELECT f1 FROM t1 UNION SELECT f1 FROM t1) AS dt WHERE f1 = 'å';

我补充一个我的测试案例

可能有读者觉得官方的例子，不太贴近实际。我这个例子，更贴近实际应用。

假设我有一张日志表，由于太大了，我按月做了分表处理。
表名: log202401、log202402

我们造一下数据:

SET NAMES utf8mb4;

DROP TABLE IF EXISTS log202401;
CREATE TABLE log202401 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  timestamp DATETIME NOT NULL,
  level VARCHAR(10) NOT NULL,
  message TEXT NOT NULL,
  source VARCHAR(255)
) CHARSET=utf8mb4;

DROP TABLE IF EXISTS log202402;
CREATE TABLE log202402 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  timestamp DATETIME NOT NULL,
  level VARCHAR(10) NOT NULL,
  message TEXT NOT NULL,
  source VARCHAR(255)
) CHARSET=utf8mb4;

-- 插入数据到 log202401
INSERT INTO log202401 (timestamp, level, message, source) VALUES
('2024-01-01 00:00:00', '错误', '系统出现错误。', 'server1'),
('2024-01-05 12:30:00', '信息', '用户登录成功。', 'auth_module'),
('2024-01-10 08:45:00', '调试', '收到用户请求。', 'api_gateway'),
('2024-01-15 14:20:00', '警告', '磁盘使用率超过80%。', 'monitoring_system'),
('2024-01-20 17:10:00', '错误', '数据库连接失败。', 'db_server'),
('2024-01-25 09:30:00', '信息', '新用户注册。', 'user_service'),
('2024-01-27 11:00:00', '调试', '支付事务开始。', 'payment_module'),
('2024-01-29 16:00:00', '警告', 'CPU使用率超过90%。', 'monitoring_system'),
('2024-01-30 20:00:00', '错误', '邮件服务无响应。', 'email_service'),
('2024-01-31 23:59:59', '信息', '日常备份完成。', 'backup_module');

-- 插入数据到 log202402
INSERT INTO log202402 (timestamp, level, message, source) VALUES
('2024-02-01 00:00:00', '错误', '系统出现错误。', 'server1'),
('2024-02-01 12:30:00', '信息', '用户登录成功。', 'auth_module'),
('2024-02-01 08:45:00', '调试', '收到用户请求。', 'api_gateway'),
('2024-02-01 14:20:00', '警告', '磁盘使用率超过80%。', 'monitoring_system'),
('2024-02-01 17:10:00', '错误', '数据库连接失败。', 'db_server'),
('2024-02-02 09:30:00', '信息', '新用户注册。', 'user_service'),
('2024-02-02 11:00:00', '调试', '支付事务开始。', 'payment_module'),
('2024-02-02 16:00:00', '警告', 'CPU使用率超过90%。', 'monitoring_system'),
('2024-02-02 20:00:00', '错误', '邮件服务无响应。', 'email_service'),
('2024-02-02 23:59:59', '信息', '月度报告生成。', 'reporting_module');

现在我们需要统计日志表，梳理今年 1、2 月份发生了多少次"错误"级别的报错。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

mysql> select * from (SELECT level FROM log202401 UNION ALL SELECT level FROM log202402) as `log_level`  WHERE level = '错误';
Empty set, 2 warnings (0.00 sec)

很好，系统没啥事，我可以安心放年假了。

而实际上,真实情况是：

mysql> select * from (SELECT level FROM log202401 UNION ALL SELECT level FROM log202402) as `log_level`  WHERE level = '错误';
+--------+
| level  |
+--------+
| 错误   |
| 错误   |
| 错误   |
| 错误   |
| 错误   |
| 错误   |
+--------+
6 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

实际上，发生这个 Bug 的先决条件，按前面的分析需要同时满足如图红线的三个：

  • 使用了派生表
  • 派生表上使用了 where 条件
  • 条件列值使用了非 ASCII 字符(除了英文字母和常见符号外的都是)

那么我要在 8.0.32 避免这个问题，我可以这么做，例如把 level 字段全换成英文，从[信息,调试,警告,错误]换成[info,debug,warning,error]。或者避免使用派生表条件下推。而避免派生表条件下推又有两个方法：

  • 等价改写，例如上述需求，我可以 SQL 改写为

mysql> select  level FROM log202401  WHERE level = '错误' UNION ALL SELECT level FROM log202402  WHERE level = '错误';
+--------+
| level  |
+--------+
| 错误   |
| 错误   |
| 错误   |
| 错误   |
| 错误   |
| 错误   |
+--------+
6 rows in set (0.00 sec)

  • 关闭对应优化器

mysql> set optimizer_switch="derived_condition_pushdown=off";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from (SELECT level FROM log202401 UNION ALL SELECT level FROM log202402) as `log_level`  WHERE level = '错误';
+--------+
| level  |
+--------+
| 错误   |
| 错误   |
| 错误   |
| 错误   |
| 错误   |
| 错误   |
+--------+
6 rows in set (0.00 sec)

mysql> explain select * from (SELECT level FROM log202401 UNION ALL SELECT level FROM log202402) as `log_level`  WHERE level = '错误';
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     |  | NULL       | ref  |    |  | 42      | const |    2 |   100.00 | Using index |
|  2 | DERIVED     | log202401  | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |   10 |   100.00 | NULL        |
|  3 | UNION       | log202402  | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |   10 |   100.00 | NULL        |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

可以看到执行计划变了，在 DERIVED 中没有使用 where了。

解决方案

  • 关闭对应的优化器

set optimizer_switch="derived_condition_pushdown=off";

不建议，因为这可能会影响 SQL 性能。

  • 官方在 MySQL 8.0.33 版本修复了这个 Bug。推荐升级到最新版本 8.0.36 解决。

彩蛋

我们知道 MySQL 的国产分支 GreatSQL 是基于 MySQL 的分支 Percona 8.0.32 版本开发的，那么他是否也命中 8.0.32 派生表条件下推优化器 Bug？我直接给结论

版本 是否命中 Bug 备注
MySQL8.0.31 否
MySQL8.0.32 是
MySQL8.0.33 否
Percona8.0.32 是 我猜的，没测
GreatSQL8.0.32-24 是
GreatSQL8.0.32-25 否 令我惊讶

快速测试和验证工具推荐：https://gitee.com/fanderchan/dbops/releases/tag/1.1.20240201

他是一个 ansible playbook 工具，允许我在各类国产操作系统下，多实例混合部署各类 MySQL。

三掌柜
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

三掌柜

这个人很懒，什么都没有留下～

文章8286 评论0