Fixing the “The table is full” Error in MySQL: When Hundreds of GBs are Still Free – ITFROMZERO

Imagine a day when the system is running smoothly, then suddenly the log reports Error 1114 (HY000): The table 'users' is full. You immediately type df -h to check and see that the hard drive still has over 200GB free. Why does MySQL report “full” when the actual storage space is still abundant?

This is an ironic situation that many system administrators (DBAs) often encounter. This error isn’t simply about running out of physical space. It relates directly to how the InnoDB Storage Engine allocates storage space through the concept of Tablespace.

Table of Contents

Three main culprits making a table “full”

Before intervening in the configuration, you need to identify the exact cause through these 3 scenarios:

Running out of actual disk space: The partition containing MySQL data (usually /var/lib/mysql) is exhausted.
System Tablespace limits: The ibdata1 file hits the maximum size (max size) specified in the configuration.
Temporary Table overflow: Heavy queries (like JOINing 5-6 large tables) create temporary files that exceed the tmp_table_size limit.

We will focus on the second cause – the issue with innodb_data_file_path. This is the most confusing error because it misleads the administrator regarding the server’s free capacity.

Choosing a Tablespace management strategy

InnoDB provides two ways to store data. Choosing the wrong strategy from the start can lead to a maintenance burden later on.

1. System Tablespace (Centralized ibdata1 file)

By default, MySQL can aggregate all data from all databases into a single file named ibdata1.

Risk: This file only grows; it doesn’t shrink. Even if you delete 100GB of data, the ibdata1 file remains the same size on the disk, creating meaningless gaps (fragmentation).

2. File-Per-Table (innodb_file_per_table)

Each table will have its own .ibd file. This is the gold standard for modern systems.

Benefits: When you perform a DROP or TRUNCATE on a table, the space is immediately returned to the operating system. Managing and moving data between servers is also much more flexible.

Fixing the innodb_data_file_path limit error

If you are using System Tablespace, check the my.cnf (Linux) or my.ini (Windows) configuration file. The “The table is full” error often appears due to a hard-coded configuration line.

Step 1: Check the current status

Run the following SQL command to see how the data file is defined:

SHOW VARIABLES LIKE 'innodb_data_file_path';

If the result returned is ibdata1:10M:autoextend:max:512M, it means your data file can never exceed 512MB. As soon as the database hits this mark, MySQL will refuse to write more data even if the hard drive has Terabytes free.

Step 2: Loosen the capacity barrier

Open the my.cnf file and find the innodb_data_file_path line. You have two ways to handle this:

Option 1: Increase the max limit to a safe level (e.g., 20GB)

innodb_data_file_path = ibdata1:10M:autoextend:max:20G

Option 2: Allow unlimited growth (Recommended)

innodb_data_file_path = ibdata1:10M:autoextend

After editing, you need to restart MySQL. Important note: The initial size value (10M here) must not be smaller than the current size of the ibdata1 file on disk, otherwise the service will fail to start.

Long-term solution: Switch to innodb_file_per_table

In an e-commerce project I once handled, the database reached a threshold of 100GB of data, making ibdata1 file backups extremely slow. The definitive solution was to enable innodb_file_per_table.

Add the following configuration to the [mysqld] section:

[mysqld]
innodb_file_per_table = 1

However, this setting only applies to newly created tables. To “rescue” old tables stuck in ibdata1, you need to run a rebuild command:

ALTER TABLE users ENGINE=InnoDB;

This operation will lift the users table out of the system tablespace and create a separate .ibd file for it.

Handling when Temporary Tables are full

If the tablespace configuration is fine but the error still occurs when running large reports, the main culprit is temporary tables. MySQL prioritizes using RAM for temporary tables; if it’s insufficient, it writes to disk.

Check the following two parameters:

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

If your system has plenty of RAM, increase these values to 256MB or 512MB to speed up processing and avoid table overflow errors:

SET GLOBAL tmp_table_size = 256 * 1024 * 1024;
SET GLOBAL max_heap_table_size = 256 * 1024 * 1024;

Small tips when cleaning up data

Many people mistakenly believe that the DELETE command will reduce the file size on the hard drive. In reality, MySQL only marks those memory cells as free space to be reused later. To actually reclaim space (shrink the file), you must use:

OPTIMIZE TABLE table_name;

Warning: This command will lock the table (Table Lock). For a large data table of about 50GB, this process can take 15-30 minutes depending on disk I/O speed. It’s best to perform this between 2-3 AM or use the pt-online-schema-change tool to avoid downtime for users.

Conclusion

The “The table is full” error is usually a consequence of default configurations being too low compared to the actual data scale. To keep the system running smoothly, you should prioritize enabling innodb_file_per_table and removing the max size limit in the ibdata1 configuration. Understanding InnoDB’s storage mechanism will help you handle incidents more calmly and accurately.