MySQL: ERROR 1114 (HY000): The table is full

What it means

The storage engine couldn’t add the row because the table has no room left. InnoDB tables have no fixed size of their own; they grow until the file system or a tablespace limit stops them. A MEMORY table does have one, set when it’s created. So the first question is which table is full:

A table of yours with ENGINE=MEMORY: it hit its size limit (see below).
An InnoDB table: the disk the data directory is on is full, or the table lives in a tablespace with a fixed size.
A name like #sql… or /tmp/#sql…: an internal temporary table that a big GROUP BY, DISTINCT, sort or ALTER TABLE was building, and the space for it ran out.

Common causes

A MEMORY table reached max_heap_table_size. The limit is taken when the table is created, altered or truncated; raising the variable later doesn’t grow an existing table.
The disk is full: the data directory’s file system, or a hosted database at its storage limit.
A fixed-size system tablespace: innodb_data_file_path without :autoextend, with tables stored in it rather than in their own files.
No space for temporary tables: a query or ALTER TABLE that copies a large table needs room in the data directory or tmpdir for the copy.

How to fix it

MEMORY tables: raise the limit, then rebuild

Check the table’s engine and how close it is to its maximum:

SHOW TABLE STATUS LIKE 'cache';

Data_length next to Max_data_length shows how full it is. Raise max_heap_table_size in the session, then rebuild the table so it takes the new limit:

SET SESSION max_heap_table_size = 64 * 1024 * 1024;
ALTER TABLE cache ENGINE=MEMORY;

For it to survive a restart, set max_heap_table_size in the server’s configuration too. A MEMORY table also loses all its rows on restart; if the data matters, ALTER TABLE cache ENGINE=InnoDB removes the limit altogether.

MEMORY isn’t transactional: a statement that fails part-way keeps the rows it inserted before the table filled up.

A full disk

On the server, check the file system that holds the data directory:

SELECT @@datadir, @@tmpdir;

df -h /var/lib/mysql

Free space (old binary logs are often the largest files: PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY; on MySQL, if no replica still needs them) or grow the volume. On a hosted database, increase the allocated storage.

A fixed-size tablespace

SELECT @@innodb_data_file_path, @@innodb_file_per_table;

If the last file in innodb_data_file_path has no :autoextend and tables live in the system tablespace (innodb_file_per_table off), add a file or :autoextend in the configuration and restart.

Temporary tables

For an #sql… name, the same disk check applies to tmpdir and the data directory. Then make the query need less: filter before grouping, add an index that lets GROUP BY or ORDER BY read rows in order, or select fewer columns.

Reproduce it

On MySQL 8.4.11, with a 64 KB limit for the session and a MEMORY table:

SET SESSION max_heap_table_size = 65536;
CREATE TABLE cache (id int AUTO_INCREMENT PRIMARY KEY, payload varchar(200)) ENGINE=MEMORY;
INSERT INTO cache (payload) VALUES (REPEAT('x', 200));
INSERT INTO cache (payload) SELECT payload FROM cache;   -- repeated, doubling the rows

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

The table held 70 rows: the INSERT … SELECT that failed had kept the 6 rows it added before the limit. Raising max_heap_table_size to 16 MB in the same session didn’t help, as the next insert failed the same way; after ALTER TABLE cache ENGINE=MEMORY the table took 140 rows.

MariaDB 11.4.13 gave the same error and behaved the same way (72 rows). The full-disk and tablespace cases weren’t reproduced on the shared test servers; they’re described from the MySQL manual.

In Inlet

When an insert or a query fails, Inlet shows the server’s error and links to this page. Inlet’s Activity monitor for MySQL and MariaDB lists the server’s sessions, which helps find a long query that’s filling temporary space.

ERROR 1114 (HY000): The table is full