- 问题现象描述
环境概述：MySQL 8.0.25；
问题简述：2024/1/31 凌晨3点 应用批量报 The table ’/data_tmp/mysql_xx….’ is full 。
表的大小9GB,报错SQL语句如下:
SQL执行计划
2.问题处理过程
业务跑批失败的SQL语句，应用手工重新执行。
3. 问题分析
3.1 数据库错误日志：
数据库mysql.err日志发现有相应的报错信息：
错误信息是：tpmdir目录下的临时表 xxx is full
3.2 数据库关于临时表的相关配置信息：
3.3 SQL调用情况
4.问题原因
猜想1：tpmdir目录下临时表xxx is full ,是OS的磁盘空间满了吗？（排除）
tmpdir=/data_tmp 在sql报错期间 可用磁盘充足，故排除。
猜想2：innodb_temp_data_file_path 临时表限额max超了吗？（排除）
原因同上，如果限额max超了 MySQL error log 为 [ERROR] [MY-012639]，[ERROR] [MY-012640]，非 [ERROR] [MY-013132] ，故排除。
猜想3：碰到MySQL 临时表bug了吗?（已复现）
MySQL bug https://bugs.mysql.com/bug.php?id=99100
每个环境的内存消耗可能是不同的，触发条件非常严格，一个temptable内存溢出必须发生在table()->file->ha_update_row().
5. BUG复现
<code style="text-align: left;">会话1：
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)
mysql>
mysql> show variables like '%temp%';
+-----------------------------+-------------------------------+
| Variable_name | Value |
+-----------------------------+-------------------------------+
| avoid_temporal_upgrade | OFF |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:20M |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| show_old_temporals | OFF |
| temptable_max_mmap | 2097152 |
| temptable_max_ram | 2097152 |
| temptable_use_mmap | ON |
+-----------------------------+-------------------------------+
7 rows in set (0.01 sec)
mysql> show create table tt.sbtest1 G;
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` varchar(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb3
1 row in set (0.01 sec)
mysql> select count(*) from tt.sbtest1;
+----------+
| count(*) |
+----------+
| 5317 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) cnt, pad, c from tt.sbtest1 group by pad order by cnt desc limit 1,2;
+-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+
| cnt | pad | c |
+-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+
| 1 | 74781290517-41121402981-50604677924-34464478849-89102349959 | 42909700340-70078987867-62357124096-35495169193-85675377266-14643719347-30417020186-80900182681-50382374444-66260611196 |
| 1 | 05718751935-89098378275-78210260499-98875938393-07869813478 | 38843067987-81616962226-70977297724-90679933528-19965056701-17197660199-96971885251-66400408314-33753356648-14231252167 |
+-----+-------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)
mysql> explain select count(*) cnt, pad, c from tt.sbtest1 group by pad order by cnt desc limit 1,2;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 5317 | 100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
会话2：
mysql> select count(*) cnt, pad, c from tt.sbtest1 group by pad order by cnt desc limit 1,2;
ERROR 1114 (HY000): The table '/data/mysql/mysql3301/tmp/#sql5ac1_a_1' is full
MySQL错误日志
[root@test_greatsql_01 ~]# tail -f /data/mysql/mysql3301/logs/mysql.err
2024-02-04T16:41:50.401950+08:00 10 [ERROR] [MY-013132] [Server] The table '/data/mysql/mysql3301/tmp/#sql5ac1_a_2' is full!
- 6.问题总结
该SQL语句中含有嵌套查询（子查询中有group by）和join表的操作，会使MySQL数据库创建临时表。当全局临时表大小超过 temptable_max_mmp值，则使用innodb临时表从disk上分配空间，并将内存中的临时表迁移到disk上。该过程在MySQL8.0.25触发bug https://bugs.mysql.com/bug.php?id=99100，该BUG 在 8.0.27 被 Fixed。
7.优化建议
1> 升级MySQL版本，该BUG Fixed in 8.0.27；
2> 对该SQL进行优化，减少临时表使用大小；
3> 减少该SQL并发，错峰执行；
4> 当SQL执行报错 xxx is full，重跑任务；
5> 调整 internal_tmp_mem_storage_engine=MEMORY，但可能在特殊场景会影响其他SQL的临时表的使用;
