MySQL 8.0.25 Bug 報"MY-013132 The table ...is full"
- 問題現象描述
環境概述: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)和join表的操作,會使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的臨時表的使用;
來自 “ ITPUB部落格 ” ,連結:https://blog.itpub.net/31383567/viewspace-3006323/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 半同步複製報錯mysql8.0.25MySql
- ERROR 1114 (HY000) The table '' is fullError
- MySQL報錯Table 'plugin' is read only [ERROR] Can't open the mysql.plugin table.MySqlPluginError
- [BUG反饋]子查詢報錯,Base table or view not found: 1146 Table 'onethink.(' doesn't existView
- MySQL 之 only_full_group_byMySql
- MySQL 預設 only_full_group_byMySql
- ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_XX"ASTExport
- 【TUNE_ORACLE】列出走了TABLE ACCESS FULL的SQL參考OracleSQL
- MySQL:Analyze table導致'waiting for table flush'MySqlAI
- MYSQL報1022錯誤:Can't write;duplicate key in table '.....'MySql
- mysql sql_mode ONLY_FULL_GROUP_BYMySql
- MySQL table into a golang strucMySqlGolang
- MySQL 鎖bug?MySql
- 【mysql】table中新增列MySql
- mysql的ALTER TABLE命令MySql
- MySQL rename table方法大全MySql
- 踩坑系列:MySql only_full_group_by配置,竟導致所有應用報錯?MySql
- 【BUG記錄】MySQL插入Emoji表情報錯"Incorrect string value"MySql
- MySQL sql_mode=only_full_group_by 錯誤MySql
- laravel 解決 mysql only_full_group_by 問題LaravelMySql
- mysql 禁用 ONLY_FULL_GROUP_BY,暫時解決錯誤(sql_mode=only_full_group_by)MySql
- mysql之 OPTIMIZE TABLE整理碎片MySql
- MySQL5.7 Waiting FOR TABLE FLUSHMySqlAI
- MySQL:Table_open_cache_hits/Table_open_cache_misses/Table_open_cache_overflowsMySql
- MySQL:關於Bug #81119MySql
- MYSQL connector 的 NullReferenceException bugMySqlNullException
- MySQL:關於Bug #20939184MySql
- 【MySQL】七、再說MySQL中的 table_idMySql
- mysql5.6在匯入時報innodb_table_stats不存在MySql
- 修改MySQL資料型別報 Changing columns for table XXX 錯的問題MySql資料型別
- MySQL5.6 create table原理分析MySql
- MySQL 關於Table cache設定MySql
- Mysql用optimize table 最佳化MySql
- MySQL UDF 在 in ( subquery where ) bugMySql
- MySQL執行語句報Incorrect key file for table '/tmp/#sql_.MYI'; try to repair itMySqlAI
- MySQL-ALTER TABLE命令學習[20180503]MySql
- MySQL的create table as 與 like區別MySql
- MySQL新增索引偶遇waiting for table metadata lockMySql索引AI