MySQL ERROR 1114 (HY000): The Table Is Full - Causes and Fixes

MySQL and MariaDB raise error 1114 when the storage engine cannot add more data to a table. Despite the wording, the table is rarely "full" in a logical sense. In most cases the disk behind it is out of space, or a memory-backed table hit a size cap you set yourself.

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

The table name in the message is your first clue. A real table name points at that table's storage. A name like /tmp/#sql... or #sql-... is an internal temporary table that a large GROUP BY, ORDER BY, UNION or ALTER TABLE created, so the problem is the temp location or the temp-table limits, not your data.

Step 1: check disk space and inodes

This is the cause more often than any other. Check the volume holding the data directory and the temp directory:

df -h /var/lib/mysql /tmp
df -i /var/lib/mysql /tmp

Find the paths MySQL actually uses:

SELECT @@datadir, @@tmpdir, @@innodb_file_per_table;

If a volume is at 100% use, or inodes are exhausted, free space first. The usual space hogs are:

• Binary logs. List them with SHOW BINARY LOGS;. Once replicas have read them, remove old ones with PURGE BINARY LOGS BEFORE '2026-09-01 00:00:00';. Do not delete the files by hand, because the index file will no longer match. In MySQL 8.0 the retention variable is binlog_expire_logs_seconds (default 2592000, 30 days); older versions use expire_logs_days.
• Slow, general and error logs that were never rotated.
• Old dumps and backups written to the same volume.
• A runaway ALTER TABLE that is copying a huge table and needs roughly the table's size again in free space.

Watch free space continuously. A disk filling slowly is a classic cause of a 3 a.m. outage, and a heartbeat monitor on a disk-check cron job can warn you early.

Step 2: temp tables on disk or in memory

If the table name looks like /tmp/#sql..., the query needed more temporary space than was available. Check where it goes and how large it can grow:

SHOW VARIABLES LIKE 'tmpdir';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';

On a small /tmp partition (many servers mount /tmp as a few hundred megabytes of tmpfs), point tmpdir at a larger disk in my.cnf, which needs a restart:

[mysqld]
tmpdir = /var/lib/mysql-tmp

Create the directory first and give the mysql user ownership. MySQL 8.0 internal temp tables use the TempTable engine by default, whose RAM and disk-mapped limits are controlled by temptable_max_ram and temptable_max_mmap. Check the manual for your exact version before changing them. The more durable fix is a better query: add an index that satisfies the ORDER BY, reduce the rows being grouped, or avoid selecting huge TEXT or BLOB columns in a sorted result. Use EXPLAIN and look for "Using temporary".

Step 3: MEMORY tables hit max_heap_table_size

A table created with ENGINE=MEMORY stops growing at max_heap_table_size, which defaults to 16 MB. Inserts beyond that fail with error 1114.

SHOW VARIABLES LIKE 'max_heap_table_size';
SET GLOBAL max_heap_table_size = 268435456;  -- 256 MB, affects new tables
ALTER TABLE sessions ENGINE=MEMORY;          -- rebuild an existing table with the new limit

The limit is read when a MEMORY table is created or rebuilt, so raising it does not help an existing table until you rebuild. Remember that MEMORY tables lose their rows on restart, and for anything you care about, InnoDB is the better choice.

Step 4: InnoDB tablespace limits

A normal install uses innodb_file_per_table=ON, so each table gets its own .ibd file and grows until the disk is full. An error here under that setting almost always means disk space, not a configured cap.

Old or unusual setups define the system tablespace with a fixed maximum. Look at it:

SHOW VARIABLES LIKE 'innodb_data_file_path';

The default is ibdata1:12M:autoextend. A value such as ibdata1:10G with no autoextend means the file cannot grow past 10 GB. Add :autoextend or add another data file, then restart. Note that ibdata1 never shrinks, even after you delete rows. If undo logs or an old shared tablespace ballooned, the clean fix is a logical dump and reload into a fresh data directory.

Step 5: find the biggest tables

SELECT table_schema, table_name,
       ROUND((data_length + index_length) / 1024 / 1024) AS size_mb
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC
LIMIT 10;

Archive or purge old rows, then run OPTIMIZE TABLE only if you have the free space to rebuild the table. Deleting rows alone does not return space to the filesystem for InnoDB.

Hosting limits

On shared hosting and managed databases, 1114 can mean a quota rather than a full disk: a per-account database size cap, or a storage limit on a managed instance that has not auto-scaled. Check your control panel's usage page, or ask the provider. Managed services typically need you to raise the storage allocation in the console.

Verify the fix

After freeing space or raising a limit, rerun the failed statement. Confirm the server is healthy with SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'; over time, and run the Website Health Check on a page that writes to the database. If the app showed a database error page, an Uptime Monitor with a keyword check would have flagged it.

Next steps

• If the server refuses connections instead, read MySQL error 2002.
• Connections piling up behind a stalled write? See too many connections.
• Long transactions holding space and locks: lock wait timeout.
• Diagnose a site that returns errors with the Website Health Check.
• Alert on failures with the Uptime Monitor.

FAQ

Does error 1114 mean the table reached a row limit?

Rarely. Modern InnoDB tables have no practical row limit. The usual causes are a full disk, a small temp directory, or a MEMORY table capped by max_heap_table_size.

Why did freeing space not fix it immediately?

Check that you freed it on the right volume, since data and tmpdir are often different mounts. Then retry; sessions that already failed do not recover automatically.

Will deleting rows shrink an InnoDB table file?

No. The space is reused for future inserts but the .ibd file stays the same size until you rebuild it with OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB, which itself needs free space.

Is error 1114 data loss?

Not by itself. The failing statement is rolled back or not applied, and existing rows are untouched. Fix capacity quickly, though, because a full disk can also stop the server.

This page is general information written by our editorial team and updated when documentation changes. Servers and providers differ, so test changes safely first. See our editorial policy and disclaimer.