MySQL ERROR 1114 "The table is full": Causes and Fixes

MySQL ERROR 1114 "The table is full" sounds like a hard limit on one table, but it rarely is. The same message covers a full disk, an InnoDB tablespace that is not allowed to grow, a temporary table created behind the scenes by a heavy query, and a couple of engine limits. The fix depends entirely on which of these you have, and the first ten minutes are best spent finding out.

The error itself looks like this:

ERROR 1114 (HY000): The table 'order_items' is full

In the server error reference it is error 1114, symbol ER_RECORD_FILE_FULL, SQLSTATE HY000, with the message "The table '%s' is full". The manual's own troubleshooting entry is two sentences long: the disk may be full, or the table may have reached its maximum size. The page on limits on table size expands that into the list below.

First look at the table name

The name in the message is the most useful clue you get.

• One of your real tables (order_items above): the problem is where that table is stored, a disk, a tablespace or an engine limit.
• A generated name, usually starting with #sql, that you never created: it is an internal temporary table MySQL built while executing a query: for a UNION, a derived table or CTE, some GROUP BY and ORDER BY combinations, DISTINCT with ORDER BY, COUNT(DISTINCT), window functions and similar cases. The table is not the problem; the query and the temporary storage are.

Then get the facts about storage in one go:

-- Which engine and which tablespace does the table live in?
SELECT table_schema, table_name, engine, create_options
FROM information_schema.TABLES
WHERE table_name = 'order_items';

SELECT name, space_type, file_size / 1024 / 1024 AS size_mb
FROM information_schema.INNODB_TABLESPACES
WHERE name LIKE '%order_items%' OR space_type = 'System';

SELECT @@innodb_file_per_table, @@innodb_data_file_path,
       @@innodb_temp_data_file_path, @@datadir, @@tmpdir;

df -h /var/lib/mysql /tmp
df -i /var/lib/mysql             # inodes can run out before bytes do
du -sh /var/lib/mysql/'#innodb_temp' /var/lib/mysql/ibtmp1

Cause 1: the disk is actually full

Still the most common case, and the first one the documentation lists. Check every volume involved: the data directory, the location of innodb_data_file_path files if they live elsewhere, tmpdir, and the directory with the session temporary tablespaces (#innodb_temp in the data directory by default). Look at inodes too.

The usual space eaters are binary logs, the slow and general logs, old dumps somebody left in the data directory, and ibtmp1 that grew during one huge query. If binlogs are the culprit, purge them properly rather than with rm; our guide to PURGE BINARY LOGS and freeing disk space walks through that safely.

Cause 2: an InnoDB tablespace is not allowed to grow

With innodb_file_per_table enabled (the default), each table has its own .ibd file that grows as needed until the disk or the file system says no. The tables that can hit an artificial ceiling are the ones in the system tablespace: tables created while innodb_file_per_table was off, old installations upgraded over many years, or tables explicitly placed there.

The error reference spells this case out: InnoDB reports error 1114 "when the system tablespace runs out of free space" and suggests adding a data file. The default innodb_data_file_path is ibdata1:12M:autoextend, with no cap. If someone added :max:, or the last file has no autoextend at all, the system tablespace stops at that size.

The system tablespace documentation gives two ways out, both requiring a restart:

1. Make the last data file auto-extending (and drop the max).
2. Add another data file. Set the size of the existing file to its current size, rounded down to whole megabytes, and append the new file:

# before: ibdata1 has grown to 988M and hit its max
innodb_data_file_path = ibdata1:12M:autoextend:max:1G

# after: keep ibdata1 at its current size, add a second file
innodb_data_file_path = ibdata1:988M;ibdata2:50M:autoextend

Do not just change the size of the existing file in the setting: the manual shows that MySQL then refuses to start with an error about the data file being "of a different size" than specified in the .cnf file. In the longer run, consider moving large tables out of the system tablespace into file-per-table tablespaces, because ibdata1 never shrinks.

Cause 3: temporary tables outgrew their space

This is the case behind most #sql names. In MySQL 8.4, an internal temporary table starts in memory in the TempTable engine. Per the internal temporary tables documentation:

• One in-memory temporary table may use up to tmp_table_size (16 MiB by default). Past that, MySQL converts it to an InnoDB on-disk temporary table.
• All TempTable tables together may use temptable_max_ram. If not set explicitly, that is 3% of the server's memory, at least 1 GB and at most 4 GB.
• temptable_max_mmap defaults to 0 in 8.4, so memory-mapped overflow files are off and overflow goes straight to InnoDB on disk. MySQL 8.0 was different: temptable_use_mmap defaulted to ON and temptable_max_mmap to 1 GiB, so check which version and settings you actually run.

On-disk temporary tables live in session temporary tablespaces, .ibt files in innodb_temp_tablespaces_dir (by default #innodb_temp in the data directory). Each session gets up to two of them from a pool, and they are truncated and returned to the pool when the session disconnects. Long-lived pooled connections therefore can keep a big .ibt file around long after the query that inflated it. The temporary tablespace documentation also describes the global temporary tablespace ibtmp1, which holds rollback segments for user-created temporary tables, auto-extends by default and only shrinks on restart. It can be capped with innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M, which protects the disk, but statements that need more room there will fail once the cap is reached.

Find who holds the space:

SELECT ID AS session_id, PATH, SIZE / 1024 / 1024 AS size_mb, STATE, PURPOSE
FROM information_schema.INNODB_SESSION_TEMP_TABLESPACES
ORDER BY SIZE DESC
LIMIT 10;

SELECT FILE_NAME, TOTAL_EXTENTS * EXTENT_SIZE / 1024 / 1024 AS size_mb, MAXIMUM_SIZE
FROM information_schema.FILES
WHERE TABLESPACE_NAME = 'innodb_temporary';

The ID column of INNODB_SESSION_TEMP_TABLESPACES is the session ID, so you can match it against the process list. Ending that session releases its space. Then find the queries that keep spilling to disk:

SELECT query, exec_count, disk_tmp_tables, tmp_tables_to_disk_pct
FROM sys.statements_with_temp_tables
ORDER BY disk_tmp_tables DESC
LIMIT 10;

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

The sys.statements_with_temp_tables view shows, per normalized statement, how many on-disk temporary tables it created and what share of its temporary tables were converted to disk. The real fix is usually in the query: a missing index for the GROUP BY or ORDER BY, a report that sorts millions of wide rows, a SELECT DISTINCT over a join that multiplies rows. Our slow query log guide helps find those statements in the first place. Raising tmp_table_size keeps more in RAM but does not make a bad query use less space overall.

Cause 4: a MEMORY table hit max_heap_table_size

Tables you created with ENGINE=MEMORY are not converted to disk; they simply stop accepting rows. The MEMORY engine documentation sets the limit with max_heap_table_size, 16 MB by default, and adds a detail that trips people up: the value in effect at CREATE TABLE, ALTER TABLE or TRUNCATE TABLE is used for the life of the table, and a server restart resets existing MEMORY tables to the global value.

SELECT @@max_heap_table_size;

-- the limit is fixed when the table is created, altered or truncated
SET SESSION max_heap_table_size = 256 * 1024 * 1024;
ALTER TABLE session_cache ENGINE = MEMORY;

Raising the global value affects every MEMORY table created afterwards, so do it deliberately. And if the data matters, keep in mind that MEMORY tables are empty after a restart anyway; an InnoDB table is often the better answer.

Cause 5: MyISAM and file-system limits

Rare today, but the limits page still lists it. A MyISAM table can grow to 256 TB by default, governed by its internal pointer size (myisam_data_pointer_size, default 6 bytes). A table created with small MAX_ROWS or AVG_ROW_LENGTH values can hit its ceiling much earlier; ALTER TABLE ... MAX_ROWS=... AVG_ROW_LENGTH=... raises it. Very old or unusual file systems with small maximum file sizes produce the same symptom. In both cases moving the table to InnoDB is usually the better long-term fix.

How to avoid the next one

• Alert on free space for every MySQL path: data directory, tmpdir, the temporary tablespace directory and the binlog location, plus inodes.
• Watch temporary-table spills: the rate of Created_tmp_disk_tables and the size of #innodb_temp. A sudden jump usually points to one new report or one missing index.
• Know your caps. If innodb_data_file_path or innodb_temp_data_file_path has a max, write down why and monitor how close you are to it.
• Recycle pooled connections now and then, so a session temporary tablespace inflated by one query does not hold its space for days.