MySQL Error Guide: 'ERROR 1114 (HY000)' The Table Is Full

Overview

ERROR 1114 (HY000): The table is full means MySQL could not add more data to a table because it hit a storage limit — not necessarily the disk, but a ceiling that applies to that table or operation:

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

The “table” being full can mean several different limits: the underlying filesystem is out of space, an in-memory temporary table exceeded tmp_table_size/max_heap_table_size, an explicit MEMORY table hit its row cap, or a tablespace/innodb_data_file_path reached a configured maximum. Because the same error covers all of these, the first job is identifying which limit you hit.

It appears on INSERT, ALTER TABLE, bulk LOAD DATA, and on large SELECT/GROUP BY that build internal temp tables.

Symptoms

The table '<name>' is full on insert, alter, or a large query.
Big GROUP BY/ORDER BY/DISTINCT queries fail while small ones work.
Failures appear suddenly when a partition fills.
df shows the datadir or tmp filesystem near 100%.

mysql -u app -p appdb -e "INSERT INTO events SELECT * FROM events_staging;"

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

Common Root Causes

1. The datadir filesystem is out of space

The most common cause: the disk holding /var/lib/mysql is full, so InnoDB cannot extend the tablespace.

df -h /var/lib/mysql

Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1       100G  100G   16K 100% /

100% use means no room to grow. Free space (purge old data, drop unused tables, add disk) and the inserts resume.

2. The temp-table filesystem (tmpdir) is full

Large sorts/joins/group-bys spill to on-disk temp tables in tmpdir. If that filesystem fills, the operation reports 1114.

mysql -u root -p -e "SHOW VARIABLES LIKE 'tmpdir';"
df -h /tmp

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           2.0G  2.0G     0 100% /tmp

A 2GB tmpfs /tmp filling on a big query yields 1114 even though the datadir has space.

3. In-memory temp table exceeded its size limit

Internal temp tables start in memory and are capped by the smaller of tmp_table_size and max_heap_table_size. Older versions converted to MyISAM on disk; if conversion also fails or the limit is hit on a MEMORY-bound op, you get 1114.

SELECT @@tmp_table_size, @@max_heap_table_size;

+------------------+-----------------------+
| @@tmp_table_size | @@max_heap_table_size |
+------------------+-----------------------+
| 16777216         | 16777216              |
+------------------+-----------------------+

A 16MB cap is easily exceeded by a wide GROUP BY; raising both (within RAM budget) helps.

4. Explicit MEMORY-engine table hit its row limit

A CREATE TABLE ... ENGINE=MEMORY table is bounded by max_heap_table_size; once full, inserts fail with 1114.

SELECT engine, table_rows, max_data_length
FROM information_schema.tables WHERE table_name = 'session_cache';

+--------+------------+-----------------+
| engine | table_rows | max_data_length |
+--------+------------+-----------------+
| MEMORY | 131072     | 16777216        |
+--------+------------+-----------------+

The MEMORY table reached max_data_length (16MB). Raise max_heap_table_size or move it to InnoDB.

5. innodb_data_file_path caps the system tablespace

With innodb_file_per_table=OFF, data goes into a shared system tablespace whose autoextend max can be configured. Hitting that max throws 1114 even with free disk.

SHOW VARIABLES LIKE 'innodb_data_file_path';
SHOW VARIABLES LIKE 'innodb_file_per_table';

+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend:max:2G |
+-----------------------+------------------------+
| innodb_file_per_table | OFF                    |
+-----------------------+------------------------+

max:2G caps the shared tablespace at 2GB; raising the max (or enabling file_per_table) resolves it.

6. A full partition in a partitioned table

A range/list-partitioned table can have one partition fill (or its MAX_ROWS/storage limit) while others have room.

SELECT partition_name, table_rows, data_length
FROM information_schema.partitions
WHERE table_name = 'events' AND partition_name IS NOT NULL
ORDER BY partition_ordinal_position;

+----------------+------------+-------------+
| partition_name | table_rows | data_length |
+----------------+------------+-------------+
| p2026_06       | 9842110    | 2147483648  |
+----------------+------------+-------------+

One partition holding the active month is the one filling — manage it independently.

Diagnostic Workflow

Step 1: Identify which limit the error refers to

Note whether the failing statement is an insert into a persistent table (likely disk/tablespace) or a large SELECT/GROUP BY/ALTER (likely temp space). This narrows the search immediately.

Step 2: Check disk on both datadir and tmpdir

df -h /var/lib/mysql
mysql -u root -p -e "SHOW VARIABLES LIKE 'tmpdir';"
df -h "$(mysql -u root -pXXX -N -e "SELECT @@tmpdir")"

A full datadir or tmpdir is the cause in the majority of 1114 cases.

Step 3: Inspect the table’s engine and limits

SELECT engine, table_rows, data_length, max_data_length
FROM information_schema.tables WHERE table_name = 'events';

A MEMORY engine or a non-zero max_data_length points to an in-memory/row-cap limit rather than disk.

Step 4: Check tablespace configuration

SHOW VARIABLES LIKE 'innodb_data_file_path';
SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'innodb_temp_data_file_path';

A capped max: in any of these is a configured ceiling independent of free disk.

Step 5: Free space or raise the limit, then retry

# Free datadir space (example: drop an old archive table after backup)
mysql -u root -p -e "DROP TABLE appdb.events_2024_archive;"

-- Or raise temp-table memory limits for large queries (session)
SET SESSION tmp_table_size = 268435456;       -- 256MB
SET SESSION max_heap_table_size = 268435456;  -- 256MB

Re-run the operation after freeing space or raising the relevant limit.

Example Root Cause Analysis

A reporting query that aggregates a year of events starts failing every morning:

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

The table name /tmp/#sql_... is a giveaway — this is an internal temporary table, not a user table. The DBA checks the temp filesystem:

df -h /tmp

Filesystem      Size  Used Avail Use% Mounted on
tmpfs           2.0G  2.0G     0 100% /tmp

/tmp is a 2GB tmpfs. The yearly aggregation builds a temp table larger than 2GB, exhausting it. The datadir has plenty of free space — the limit is the in-memory temp filesystem.

Fix: point MySQL’s tmpdir at a large on-disk path and size temp-table memory appropriately:

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
tmpdir = /var/lib/mysql-tmp        # a roomy on-disk path, not a small tmpfs
tmp_table_size = 268435456
max_heap_table_size = 268435456

After restart and pointing tmpdir at a 200GB volume, the report runs to completion. (Rewriting the query to reduce the temp-table size is the better long-term fix.)

Prevention Best Practices

Monitor free space on both the datadir and tmpdir filesystems and alert well before 100%; a full disk is the leading cause of 1114 and many other failures.
Put tmpdir on a generously sized on-disk volume, not a small tmpfs, if your workload runs large sorts, joins, or GROUP BY operations.
Use innodb_file_per_table=ON (the default) so each table has its own tablespace that can grow to the filesystem limit, instead of a capped shared ibdata1.
Size tmp_table_size/max_heap_table_size for your analytic queries, but balance against max_connections since these are per-connection allocations.
For ever-growing tables, use partitioning plus a retention job that drops old partitions, so storage is reclaimed predictably rather than filling the disk.
For quick triage of which limit a 1114 hit (disk vs temp vs tablespace), the free incident assistant can correlate the failing statement and df output. More in MySQL guides.

Quick Command Reference

# Disk on datadir and tmpdir
df -h /var/lib/mysql
mysql -u root -p -e "SHOW VARIABLES LIKE 'tmpdir';"
df -h /tmp

-- Engine and row/size limits of the table
SELECT engine, table_rows, data_length, max_data_length
FROM information_schema.tables WHERE table_name = 'events';

-- Tablespace configuration
SHOW VARIABLES LIKE 'innodb_data_file_path';
SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'innodb_temp_data_file_path';

-- Temp-table memory limits
SELECT @@tmp_table_size, @@max_heap_table_size;
SET SESSION tmp_table_size = 268435456;
SET SESSION max_heap_table_size = 268435456;

-- Per-partition sizes
SELECT partition_name, table_rows, data_length
FROM information_schema.partitions
WHERE table_name = 'events' AND partition_name IS NOT NULL;

Conclusion

ERROR 1114 (HY000) means MySQL hit a storage ceiling for a table or operation. The usual root causes:

The datadir filesystem is out of space.
The tmpdir filesystem filled during a large sort/join/group-by.
An internal temp table exceeded tmp_table_size/max_heap_table_size.
An explicit MEMORY-engine table hit its row/size cap.
A capped innodb_data_file_path shared tablespace (with file_per_table=OFF).
A single partition of a partitioned table filling up.

Look at whether the failing object is a persistent table or an internal #sql_... temp table, check df on both datadir and tmpdir, then free space or raise the specific limit you actually hit.