📋

Description

MySQL Error 1114, 'The table '%s' is full', indicates that a specific database table cannot accept new data or grow further. This typically occurs when the table reaches its maximum storage capacity, often due to physical disk space limitations, file system constraints, or database engine-specific limits.

💬

Error Message

The table '%s' is full

🔍

Known Causes

3 known causes

⚠️

Insufficient Disk Space

The physical disk drive where MySQL stores its data files has run out of free space, preventing the table from expanding.

⚠️

Table File Size Limit Reached

The individual data file for the specific table has reached its maximum allowed size, which can be imposed by the operating system, file system, or MySQL's configuration.

⚠️

InnoDB/MyISAM Tablespace Full

For InnoDB, the shared tablespace or a file-per-table tablespace is full. For MyISAM, the data or index file has reached its maximum size limit.

🛠️

Solutions

4 solutions available

1. Increase Table Size Limit (if applicable) medium

Increase the maximum size of the table, often by adjusting storage engine settings.

1

Check the current `max_heap_table_size` and `tmp_table_size` variables. These are particularly relevant for MEMORY storage engine tables, which can become full if their size exceeds these limits.

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

2

If the table is of type MEMORY, you can increase these values. For a temporary fix, you can set them dynamically. For a permanent fix, edit your MySQL configuration file (`my.cnf` or `my.ini`).

SET GLOBAL max_heap_table_size = 256 * 1024 * 1024; -- Example: 256MB
SET GLOBAL tmp_table_size = 256 * 1024 * 1024; -- Example: 256MB

3

To make the change permanent, edit your MySQL configuration file (e.g., `/etc/my.cnf` or `C:\ProgramData\MySQL\MySQL Server X.Y\my.ini`). Add or modify the following lines under the `[mysqld]` section:

[mysqld]
max_heap_table_size = 256M
tmp_table_size = 256M

4

Restart the MySQL server for the permanent changes to take effect.

sudo systemctl restart mysql  # For systemd-based systems (e.g., Ubuntu, CentOS 7+)
sudo service mysql restart      # For older init.d systems
# On Windows, restart the MySQL service via the Services management console.

2. Optimize and Clean Up Table Data easy

Remove unnecessary data and optimize the table structure to free up space.

1

Identify and delete old or redundant data from the table. This is crucial if the table has grown excessively over time.

DELETE FROM your_table_name WHERE some_condition_for_old_data;
-- Example: DELETE FROM logs WHERE log_date < '2023-01-01';

2

After deleting data, it's highly recommended to run `OPTIMIZE TABLE`. This defragments the table, reclaims unused space, and updates index statistics, which can resolve 'Table Full' errors for InnoDB and MyISAM tables.

OPTIMIZE TABLE your_table_name;

3

For MEMORY tables, `OPTIMIZE TABLE` does not reclaim space in the same way as disk-based tables. If you are using a MEMORY table and it's full, you might need to drop and recreate it, or increase the `max_heap_table_size` as described in the previous solution.

3. Check Disk Space on the Filesystem easy

Verify that the underlying filesystem where MySQL data files reside has sufficient free space.

1

Determine the directory where your MySQL data files are stored. This is usually defined by the `datadir` variable.

SHOW VARIABLES LIKE 'datadir';

2

Use a system command to check the free space on the filesystem where the `datadir` is located.

df -h /var/lib/mysql/  # Replace /var/lib/mysql/ with your actual datadir

3

If the filesystem is full or nearly full, you need to free up space on that disk. This might involve deleting old files, moving data to another disk, or expanding the disk capacity.

4. Increase Disk Quotas (if applicable) medium

If disk quotas are enforced on the server, ensure they are sufficient for MySQL data.

1

Check if disk quotas are enabled and configured for the user running the MySQL process or the filesystem containing the data.

sudo repquota -a

2

If quotas are too low, you will need to increase them. This process varies depending on the operating system and quota configuration. Consult your system administrator or OS documentation for specific commands.

# Example for Linux (requires root privileges and quota package):
sudo edquota -u mysql_user
# Then edit the file to increase the quota limits.

🔗

Related Errors

5 related errors

MySQL Error 1114: Table Full Error