1114 database resource_error ai_generated partial

错误 1114 (HY000): 表 '/tmp/#sql_xxxx' 已满

ERROR 1114 (HY000): The table '/tmp/#sql_xxxx' is full

ID: database/mysql-temp-table-full

其他格式: JSON · Markdown 中文 · English
88%修复率
86%置信度
1证据数
2023-12-01首次发现

版本兼容性

版本状态引入弃用备注
MySQL 8.0 active — — —
MySQL 5.7 active — — —
MariaDB 10.6 active — — —

根因分析

MySQL 的临时表存储（内存或磁盘）空间不足，通常是由于大型查询创建的临时表超出了 tmp_table_size 或 max_heap_table_size 的限制。

English

MySQL's temporary table storage (either in-memory or on disk) has run out of space, often due to a large query that creates an oversized temporary table exceeding tmp_table_size or max_heap_table_size.

generic

官方文档

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmp_table_size

解决方案

  1. Increase both tmp_table_size and max_heap_table_size in MySQL configuration:
[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M
Then restart MySQL.
  2. Optimize the query to avoid large temporary tables, e.g., by adding indexes, using LIMIT, or rewriting JOINs to reduce intermediate result sets.

无效尝试

常见但无效的做法:

  1. Increasing only tmp_table_size without checking max_heap_table_size 80% 失败

    MySQL uses the smaller of tmp_table_size and max_heap_table_size for in-memory temp tables; both must be increased.

  2. Deleting files from /tmp directory manually 90% 失败

    Temporary tables are managed by MySQL; manual deletion can cause corruption or errors, and space will not be freed immediately.