Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

MySQL ERROR 1114 (HY000): The table is full: disk, MEMORY table limit or tablespace

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

ERROR 1114 (HY000): The table 'orders' is full has two causes in the manual's own words: "it may be that the disk is full or that the table has reached its maximum size". Start with free space on the filesystem that holds the data directory. If the disk is fine, find the table's storage engine, because each engine has its own limit: max_heap_table_size for MEMORY tables, the tablespace settings for InnoDB, and file and pointer sizes for MyISAM.

What the error means

The server error reference lists error 1114 with the symbol ER_RECORD_FILE_FULL, SQLSTATE HY000, and the message The table '%s' is full. The manual's Limits on Table Size page says "The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits". It lists the reasons for a full-table error:

  • The disk might be full.
  • You are using InnoDB tables and have run out of room in an InnoDB tablespace file. "The maximum tablespace size is also the maximum size for a table."
  • You have hit an operating system file size limit, for example MyISAM tables on a system that supports files only up to 2GB.
  • You are using a MyISAM table and the space required exceeds what the internal pointer size permits.

MEMORY tables are a separate case. The MEMORY engine page says their maximum size "is limited by the max_heap_table_size system variable, which has a default value of 16MB". The default of 16777216 bytes is 16 × 1024 × 1024. An internal temporary table that grows too large is different: the Internal Temporary Table page says the server "automatically converts" an in-memory internal temporary table to an on-disk table at the tmp_table_size limit. For a MEMORY table created with CREATE TABLE, the same page says "only the max_heap_table_size variable determines how large a table can grow, and there is no conversion to on-disk format".

Likely causes

Cause What you see How to confirm
Filesystem holding the data directory is full Writes to several tables fail, not one df -h and df -i on the data directory's filesystem
A MEMORY table reached max_heap_table_size Only that table fails; the disk has room SHOW TABLE STATUS shows Engine MEMORY, and Data_length (the page says it approximates allocated memory) or rows times the per-row formula from the MEMORY page is near the limit
InnoDB system tablespace has no room Tables stored in the system tablespace fail SELECT @@GLOBAL.innodb_data_file_path; has no autoextend, or its max is reached
MyISAM data or index file at a size limit Only a large MyISAM table fails SHOW TABLE STATUS shows Data_length near Max_data_length
Operating system file size limit or unsuitable filesystem The data file stops at one size The manual says FAT and VFAT (FAT32) are not suitable for production use with MySQL on Windows

Prove the cause before changing anything

All of these are read-only.

1. Which table and which engine?

SHOW TABLE STATUS FROM shop LIKE 'orders';

Read the Engine, Data_length, Index_length and Max_data_length columns. For MyISAM, the manual says Max_data_length is the most data the pointer size can address. For InnoDB it says Max_data_length is unused.

2. Where is the data directory, and is it full?

SELECT @@GLOBAL.datadir;

Then, on the server, run df -h and df -i with that path as the argument. The df manual describes -h as printing sizes in powers of 1024 and -i as listing inode information instead of block usage. A filesystem at 100% in either listing explains the error and ends the search. The manual notes that a deleted file can stay open: MySQL removes temporary files by unlinking them, so a big one does not appear in directory listings, and lsof +L1 "may be helpful in identifying large files associated with mysqld".

3. Is it a MEMORY table limit?

SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';

If Engine was MEMORY and the disk has room, compare the table's size with this value in bytes. The SHOW TABLE STATUS page says that for MEMORY tables the Data_length, Max_data_length and Index_length values "approximate the actual amount of allocated memory", so compare Data_length with the limit. For a second estimate, the MEMORY page gives the memory one row needs as SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4) + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2) + ALIGN(length_of_row+1, sizeof(char*)). Multiply that by the Rows value.

4. Is it the InnoDB system tablespace?

SELECT @@GLOBAL.innodb_data_file_path;

The manual's default is ibdata1:12M:autoextend. The syntax is file_name:file_size[:autoextend[:max:max_file_size]]. A value with no autoextend, or with max reached, can be full. File-per-table tablespaces are the default type for new InnoDB tables, so a table in its own file is not limited by the system tablespace.

Fix

Disk or filesystem full

The remedy for a full disk is to free or add space on that filesystem; identify what is using it first. No command here deletes anything. Inodes and quotas are covered under the title "Linux Says No Space Left but Bytes Are Free: Check Inodes and Quotas". Do not delete or move files inside the data directory by hand while the server runs.

For writes to MyISAM tables and the binary log, the manual's full-disk page says the server checks once every minute whether there is enough space, continues if there is, and writes a warning to the log every 10 minutes.

A MEMORY table at max_heap_table_size

The manual says the value in effect for CREATE TABLE, or a later ALTER TABLE or TRUNCATE TABLE, "is the value used for the life of the table", and that a server restart also sets existing MEMORY tables to the global value. So raising the variable alone does not change a table that already exists.

First print and keep the current value:

SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';

Risk: the next two statements rebuild the live MEMORY table shop.page_cache with a 64 MiB limit, so it can then use up to 64 MiB of server RAM. The manual does not say how long a rebuild holds the table. It also says not to change the global value unless you intend it to apply to MEMORY tables created by all clients, so these statements use the session value only. Choose a limit that your free memory can cover.

Set the session value and rebuild only the table you need:

SET max_heap_table_size = 64*1024*1024;
ALTER TABLE shop.page_cache ENGINE=MEMORY;

Here 64 × 1024 × 1024 = 67108864 bytes. The manual describes ALTER TABLE ENGINE=MEMORY as forcing a table rebuild. Using the session value, as the MEMORY page shows, changes the limit only for that table. A restart returns the table to the global value.

To undo it: set the session value back to the saved number and rebuild the table again, then print the variable.

SET max_heap_table_size = 16777216;
ALTER TABLE shop.page_cache ENGINE=MEMORY;
SHOW VARIABLES LIKE 'max_heap_table_size';

This works only if your saved value was 16777216. Use the number you saved. If the table already holds more data than the lower limit allows, the rebuild will fail with this same error, so check Data_length first.

InnoDB system tablespace without room

The manual says "The easiest way to increase the size of the system tablespace is to configure it to be auto-extending": specify the autoextend attribute for the last data file in innodb_data_file_path and restart the server. A file with autoextend grows in 8MB increments as space is required. The manual also warns that you cannot change the size number of an existing file: changing ibdata1:10M:autoextend to ibdata1:12M:autoextend stops the server starting with error MY-012263.

First find the option file the server reads. The manual says that for mysqld you run it with --verbose and --help, and the help message "indicates which files it looks for". Of those files, edit the one that holds your [mysqld] settings. On Unix the manual lists /etc/my.cnf and /etc/mysql/my.cnf among the candidates.

mysqld --verbose --help

Then save that file. Replace <option_file> with the file you found. First check that it exists:

ls -l <option_file>

If it does not exist, stop: there is nothing to save, and this step needs an existing file. If it exists, save it:

cp -p <option_file> <option_file>.2026-11-22

Then record the current setting and the current size of the data file. Run the SQL statement in the mysql client:

SELECT @@GLOBAL.innodb_data_file_path;

Run the file check in a shell, replacing <data_directory> with the datadir value from the earlier check:

ls -l <data_directory>/ibdata1

Disruptive step: the next change takes effect only when the MySQL service restarts, which disconnects every session and interrupts running work. A wrong size number stops the server from starting. Do it in a quiet window and make sure the disk has room for growth.

In [mysqld] of that file, keep the existing file name and size exactly as saved and add :autoextend to the last file, so ibdata1:12M becomes ibdata1:12M:autoextend. Then validate and restart:

mysqld --validate-config

The manual says it exits with 0 when no errors are found. Restart the MySQL service with your platform's tool.

To undo it: the right action depends on whether the data file has grown since you saved its size. Run ls -l <data_directory>/ibdata1 again.

If the size is unchanged, first prove the saved copy exists with ls -l <option_file>.2026-11-22. Only then put it back under its original name, validate it, and restart:

cp -p <option_file>.2026-11-22 <option_file>
mysqld --validate-config

If the file has grown, do not restore the saved file. A setting whose size differs from the file on disk makes the server refuse to start with error MY-012263. The manual's rule for removing autoextend is to set the size attribute to the current data file size, rounded down to the closest MB, where a MB is 1024 × 1024 bytes. For example, a file of 1,300,000,000 bytes is 1,300,000,000 ÷ 1,048,576 = 1239.77, so the size is 1239M, and the setting becomes ibdata1:1239M. Edit the setting to that value without :autoextend, run mysqld --validate-config, and restart. The file then stays at that size and the manual gives no way to shrink it; it says decreasing the system tablespace is not supported.

After the restart, run SELECT @@GLOBAL.innodb_data_file_path; and compare it with what you saved or calculated.

MyISAM or operating system limits

The Limits on Table Size page describes AVG_ROW_LENGTH and MAX_ROWS for CREATE TABLE and ALTER TABLE, and myisam_data_pointer_size for new tables. Changing a large MyISAM table's limits rebuilds a production table. Plan it with a tested backup and read that page's section before acting.

Example (illustrative): On db01, an application fails with ERROR 1114 (HY000): The table 'page_cache' is full. df -h on the data directory shows 41% used, so the disk is not the cause. SHOW TABLE STATUS FROM shop LIKE 'page_cache'; reports Engine MEMORY and Rows of 139800. The MEMORY page's formula gives 120 bytes per row for this table, and 139800 × 120 = 16776000 bytes, just under the 16777216 bytes (16 MiB) default. The administrator saves the variable, raises the session limit to 64 MiB (67108864 bytes), rebuilds that table with ALTER TABLE shop.page_cache ENGINE=MEMORY;, and the inserts succeed.

Confirm the fix

  1. The statement that failed now succeeds.
  2. SHOW TABLE STATUS shows the table's Rows growing, and your per-row estimate times Rows stays below the new limit.
  3. df -h on the data directory shows free space and no filesystem at 100%.
  4. After any restart, the error log shows the server started with the intended settings.

What to watch afterwards

  • Track free space on the data directory's filesystem and its inode use. Alert well before 100%.
  • Watch MEMORY tables that grow with traffic. After a restart each returns to the global limit, so a table that needed a higher session value needs the same step again.
  • If you added autoextend, remember the file grows until the disk is full or max is reached. Decreasing a system tablespace is not supported; the manual's route is restoring from a backup to a new instance.
  • Check the error log after a restart that changes tablespace settings.

Risks and limits

  • These commands have not been run or lab-tested.
  • The manual's page for this error is one paragraph. The engine-specific advice comes from the engine pages (MEMORY, InnoDB system tablespace, Limits on Table Size), which do not name error 1114.
  • The full-disk page's description of waiting for space applies to MyISAM writes and the binary log; it does not describe InnoDB.

Sources