1114 database resource_error ai_generated partial

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

ID: database/mysql-temp-table-full

Also available as: JSON · Markdown · 中文
88%Fix Rate
86%Confidence
1Evidence
2023-12-01First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
MySQL 8.0 active — — —
MySQL 5.7 active — — —
MariaDB 10.6 active — — —

Root Cause

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

中文

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

Official Documentation

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

Workarounds

  1. 90% success 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. 
    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. 85% success Optimize the query to avoid large temporary tables, e.g., by adding indexes, using LIMIT, or rewriting JOINs to reduce intermediate result sets. 
    Optimize the query to avoid large temporary tables, e.g., by adding indexes, using LIMIT, or rewriting JOINs to reduce intermediate result sets.

中文步骤

  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.

Dead Ends

Common approaches that don't work:

  1. Increasing only tmp_table_size without checking max_heap_table_size 80% fail

    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% fail

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