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

ERROR 1114 sounds like a limit on the table itself, but in modern MySQL with InnoDB it almost always means some storage resource is exhausted: the filesystem holding the data directory, a tablespace with a fixed maximum, the volume tmpdir lives on, or the memory limit for an in memory temporary table. Writes fail, and depending on what filled up, the whole instance may be effectively down. The job is to identify which resource is full, free space safely (not with rm in the data directory), and add the guardrails that stop it recurring.

ERROR 1114 (HY000): The table is full

What this error means

InnoDB raises this error when it cannot allocate another page for the table or index it is writing. With default file per table tablespaces that autoextend, the usual reason is simply that the filesystem is full. Fixed size tablespaces (an ibdata1 configured with a max, or general tablespaces with caps) produce it even with disk free. Separately, the MEMORY engine and internal in memory temporary tables report the same error when they hit max_heap_table_size or tmp_table_size, and disk spilled temporary tables fail when the tmpdir volume fills. Same message, four different tanks that can run dry.

Common causes

• The filesystem holding datadir is full: data growth, binary logs, undo logs, or stray files consumed the volume.
• Binary logs with no expiry accumulating for months on the data volume.
• A shared ibdata1 or general tablespace configured without autoextend or with a max size that has now been reached.
• A big ALTER TABLE or index build that needs a temporary full copy of the table, doubling its footprint mid operation.
• Large queries spilling internal temporary tables to a small tmpdir volume.
• A MEMORY engine table or in memory temp table hitting max_heap_table_size.
• Undo or redo growth from very long transactions pinning space that cannot be reclaimed.

How to fix it

STABILIZE first. Find the full resource and free headroom through safe channels (purging binlogs via SQL, dropping known junk on the same volume) so writes resume; never delete files inside the data directory by hand.

1. DIAGNOSE: check every filesystem MySQL touches, and see what is consuming the data volume.

df -h
du -sh /var/lib/mysql/* | sort -rh | head -20

Check where tmpdir points, since it is often a different (and smaller) volume:

SHOW VARIABLES LIKE 'tmpdir';
SHOW VARIABLES LIKE 'datadir';

2. STABILIZE: if binary logs dominate, purge them through MySQL after confirming replicas are past them.

SHOW BINARY LOGS;
SHOW REPLICA STATUS\G
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;
SET GLOBAL binlog_expire_logs_seconds = 259200;

3. DIAGNOSE a tablespace cap: if the disk has space but writes still fail, check for a fixed size shared tablespace and confirm the table uses file per table.

SHOW VARIABLES LIKE 'innodb_data_file_path';
SHOW VARIABLES LIKE 'innodb_file_per_table';
SELECT name, file_size FROM information_schema.innodb_tablespaces ORDER BY file_size DESC LIMIT 10;

FIX by adding :autoextend to innodb_data_file_path (requires restart) or, better long term, moving large tables to file per table tablespaces via ALTER TABLE ... ENGINE=InnoDB once space allows.

4. FIX a temp table limit: if the error comes only from big GROUP BY or ORDER BY queries, raise the in memory limits for the session running them, and point tmpdir at a volume with real capacity.

SET SESSION tmp_table_size = 268435456;
SET SESSION max_heap_table_size = 268435456;

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
tmpdir = /data/mysqltmp

5. FIX the growth itself: reclaim space from bloated tables (deleted rows do not shrink the file until the table is rebuilt).

ALTER TABLE big_history ENGINE=InnoDB;

Run rebuilds when you have free space of roughly the table’s size available, and archive or drop data you no longer need first.

How to prevent it

• Alert on disk usage for datadir and tmpdir volumes at 80 percent, so this becomes a ticket, not an outage.
• Set binlog_expire_logs_seconds everywhere binary logging is on.
• Keep innodb_file_per_table on and avoid fixed caps on shared tablespaces.
• Plan ALTER TABLE operations with free space of at least the table size, or use an online schema change tool that works in chunks.
• Archive or partition unbounded history tables so growth is managed, not discovered.
• Test that monitoring covers every mount MySQL uses, including the often forgotten tmpdir.

When to call a senior engineer

Call for help when the volume is full and nothing obvious is safe to delete, when a fixed ibdata tablespace needs restructuring without extended downtime, or when a table rebuild needed to reclaim space will not fit on the disk you have. Erzon engineers can free space through safe channels under pressure, migrate tablespaces and archives without losing data, and set up capacity monitoring so the database never hits the wall again.