MySQL 8.0.25 Bug 報"MY-013132 The table ...is full"

1. 問題現象描述

環境概述：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

每個環境的記憶體消耗可能是不同的，觸發條件非常嚴格，一個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，select count(*) 操作，會使MySQL資料庫建立臨時表。當全域性臨時表大小超過 temptable_max_mmp值，則使用innodb臨時表從disk上分配空間，並將記憶體中的臨時表遷移到disk上。該過程在MySQL8.0.25觸發bug ，該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的臨時表的使用;