MySQL 'The table is full' (Error 1114): You Are Checking the Wrong Filesystem

An insert fails at 2am with a message that sends everyone in the wrong direction:

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

So the on-call engineer looks at orders. They check its row count, its size, whether it hit some row limit, whether a column type is too small. All wasted, because the table named in that error is usually innocent. Error 1114 is MySQL telling you a write could not find room, and the room it needed is almost never "inside that specific table". It is disk, or temp space, or a tablespace file that was configured years ago to stop growing.

The thesis: 1114 is a storage error wearing a table's name. The single most common mistake is fixating on the table in the message instead of checking, in order, three unrelated storage limits: the datadir partition, the tmpdir partition, and the InnoDB system tablespace's configured maximum.

The exact error, and the three storages behind it

You will see one of these forms depending on client and version:

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

That second form is the giveaway most people miss. A table name that looks like /tmp/#sql_... or #sql-temptable is not one of your tables at all. It is an internal on-disk temporary table MySQL created to run a query, and the message is telling you the temp storage filled, not your data. If you ever see that path, skip straight to the tmpdir section.

Step 1: check the datadir partition

The most common cause, by a wide margin, is that the disk holding MySQL's data directory is simply full. Find the datadir and check its partition, not just the root filesystem.

# Where does MySQL keep its data?
mysql -e "SHOW VARIABLES LIKE 'datadir';"
# +---------------+-----------------+
# | Variable_name | Value           |
# | datadir       | /var/lib/mysql/ |
# +---------------+-----------------+

# Is that partition full?
df -h /var/lib/mysql

If Use% is 100% (or within a few percent, since MySQL reserves headroom), that is your answer. The named table was just the unlucky write that ran out of room first. Free space by rotating logs, moving binary logs off the volume, or archiving old data. If your database has grown steadily into this wall, the real fix is a data lifecycle plan; I walk through exactly that for one common case in what to delete, archive and partition when your database hits 40GB.

One trap: binary logs. If log_bin is on and expire_logs_days (or binlog_expire_logs_seconds) is unset, binlogs accumulate on the datadir volume forever and fill it while your actual tables are modest. Check with SHOW BINARY LOGS; and du -sh on the datadir.

Step 2: check tmpdir, the separate limit nobody remembers

If df on the datadir shows plenty of space, the write that failed was probably a temporary table, not a permanent one. MySQL builds on-disk temporary tables under tmpdir when a query's intermediate result is too large to keep in memory: large GROUP BY, ORDER BY without an index, DISTINCT, UNION, and many joins. By default tmpdir is often /tmp, which on a lot of servers is a small or separate partition.

mysql -e "SHOW VARIABLES LIKE 'tmpdir';"
# tmpdir = /tmp

df -h /tmp

A full /tmp gives you 1114 with a #sql temp-table name even though your data disk is nearly empty. The permanent fix is to point tmpdir at a directory on a large filesystem:

# /etc/mysql/mysql.conf.d/mysqld.cnf  (or your distro's config)
[mysqld]
tmpdir = /var/lib/mysql-tmp

sudo mkdir -p /var/lib/mysql-tmp
sudo chown mysql:mysql /var/lib/mysql-tmp
sudo systemctl restart mysql

Then attack the cause, not just the symptom. Raise the thresholds that decide when a temp table is allowed to stay in memory rather than spilling to disk:

[mysqld]
tmp_table_size    = 256M
max_heap_table_size = 256M

Both matter: MySQL uses the smaller of the two as the in-memory limit, so raising only one does nothing. But do not set them recklessly high, because that memory is per-connection for a query that needs it, and a busy server can multiply it into an out-of-memory event. If a single query is spilling gigabytes to tmpdir, the honest fix is an index so it stops building a giant temp table at all. Find that query with the slow query log.

Step 3: the fixed-size InnoDB tablespace ceiling

Now the confusing case: df shows free space everywhere, tmpdir is fine, and you still get 1114 on a normal insert. This is almost always an old-style shared InnoDB system tablespace whose file was configured with a fixed maximum and no autoextend.

mysql -e "SHOW VARIABLES LIKE 'innodb_data_file_path';"

A dangerous value looks like this, a fixed 12MB file with no growth clause:

innodb_data_file_path = ibdata1:12M

A safe value ends with :autoextend, so InnoDB grows the file against real disk:

innodb_data_file_path = ibdata1:12M:autoextend

Only the last file in the list may carry :autoextend. If yours lacks it, the shared tablespace stops growing at its stated size and every table that lives inside it (which, without file-per-table, is all of them) reports "full" together. Changing this setting requires care and a clean shutdown, so back up first and follow the MySQL manual for your version. The better long-term posture is to stop using the shared tablespace for user tables entirely:

[mysqld]
innodb_file_per_table = ON

With innodb_file_per_table on (the default in modern MySQL and MariaDB), each table gets its own .ibd file that grows against the datadir's actual free space, and you are back to Step 1's simple "is the disk full" question instead of a hidden per-tablespace ceiling. Existing tables move out of the shared tablespace when you rebuild them with ALTER TABLE ... FORCE or OPTIMIZE TABLE.

Verification

Prove which storage was the culprit and that it now has room.

# Datadir and tmpdir both have headroom
df -h /var/lib/mysql /var/lib/mysql-tmp

# The failing write now succeeds
mysql yourdb -e "INSERT INTO orders (customer_id, total) VALUES (1, 100);"
# Query OK, 1 row affected

To confirm the tmpdir case specifically, run the exact query that failed and watch the created-tmp-tables counters climb without erroring:

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
-- run your heavy query --
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

A rising Created_tmp_disk_tables confirms the query spills to tmpdir; if it now completes instead of throwing 1114, the tmpdir fix worked. If that counter climbs fast in normal operation, that is your cue to raise the temp-table sizes or add the missing index.

What people get wrong

Investigating the named table. The table in the message is the write that happened to fail, not the thing that is full. Reading its schema and row count is time spent looking at the wrong object. Check storage first, always.

Assuming df / is enough. The root filesystem can be 20% used while a dedicated /var/lib/mysql or /tmp mount is at 100%. Always df the specific partition that holds datadir and tmpdir, which may be different mounts.

Setting tmp_table_size to something enormous. That memory is allocated per query that needs a temp table, so on a server with hundreds of connections you can turn a disk problem into an OOM kill. Raise it moderately and fix the query that spills.

Deleting rows to "make space" without reclaiming it. With the shared tablespace, deleting rows frees space inside the ibdata1 file but does not return it to the OS, so df does not move. Only innodb_file_per_table plus a table rebuild actually shrinks files on disk.

When it is still broken

• Disk has space, tmpdir has space, tablespace autoextends, still 1114. Check for a filesystem inode exhaustion with df -i; you can run out of inodes with bytes to spare, which also blocks new files.
• 1114 only under load. Concurrent heavy queries each build a temp table under tmpdir at once and collectively fill it, even though any one is small. Reduce concurrency of the offending report or give it an index.
• It happens on a managed database (RDS, Cloud SQL). You cannot always see the host disk, but the console exposes free-storage and temp-space metrics. A managed instance can also hit a hard storage cap that behaves exactly like a full partition.
• The error moved to a different message. If freeing space turns 1114 into MySQL server has gone away or a lock timeout, you have cleared the storage wall and uncovered the next bottleneck. That is progress, and each has its own fix.

The mental model to keep: MySQL says "the table is full" because from its point of view a write had nowhere to go, and "nowhere to go" has three possible addresses. Check the datadir, the tmpdir, and the tablespace config, and the named table stops being a distraction.