How to fix ERROR 1114: Table is full in MySQL

Start by checking how much free space is available on the partition containing your MySQL data directory.

On Linux/macOS:

df -h /var/lib/mysql

On Windows (PowerShell):

Get-Volume | Where-Object {$_.DriveLetter -eq 'C'} | Select-Object SizeRemaining, Size

If the available space shows 0% or very close to it, disk space is the problem. You need to free up disk space before MySQL can write again.

If disk space is the issue, remove unnecessary files to make room:

1. Clear old MySQL binary logs (if binary logging is enabled):

mysql -u root -p -e "SHOW BINARY LOGS;"
mysql -u root -p -e "PURGE BINARY LOGS BEFORE '2024-12-01';"

2. Remove old backup files:

rm -f /var/backups/mysql-*.sql.gz

3. Clear temporary files in /tmp:

rm -f /tmp/mysql*

4. Check and clean application logs:

find /var/log -name "*.log" -type f -mtime +30 -delete

5. Consider expanding the partition if possible, or migrating MySQL data to a larger disk.

After freeing space, test that MySQL can write to the table:

mysql -u root -p -e "USE yourdatabase; INSERT INTO testtable (column) VALUES ('test');"

If the INSERT succeeds, you have resolved the disk space issue.

If you have adequate disk space but still get the error, check your InnoDB configuration:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_data_file_path';"

If the output shows a max limit like:

ibdata1:12M:autoextend:max:512M

The table has hit the configured maximum. You need to increase or remove the max limit.

Edit your MySQL configuration file (/etc/mysql/my.cnf or /etc/my.cnf):

Find the line with innodb_data_file_path and either:

Option A: Remove the max limit (recommended):

[mysqld]
innodb_data_file_path = ibdata1:12M:autoextend

Option B: Increase the max limit:

[mysqld]
innodb_data_file_path = ibdata1:12M:autoextend:max:2048M

Then restart MySQL:

sudo systemctl restart mysql

Or on systems using mysqld_safe:

sudo /usr/bin/mysqld_safe &

Verify the setting took effect:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_data_file_path';"

If the error occurs on MEMORY (heap) tables, check the memory configuration:

mysql -u root -p -e "SHOW VARIABLES LIKE 'max_heap_table_size'; SHOW VARIABLES LIKE 'tmp_table_size';"

To increase these limits, edit your MySQL configuration:

[mysqld]
max_heap_table_size = 256M
tmp_table_size = 256M

Then restart MySQL and verify:

sudo systemctl restart mysql
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_heap_table_size';"