How to fix MySQL ERROR 1114 the table is full issue

The MySQL ERROR 1114 can be triggered when you try to perform an INSERT statement on a table.

The following example shows how the error happens when I try to insert data into the users table:

mysql> INSERT INTO `users` VALUES (15, "Nathan", "Sebhastian")

ERROR 1114 (HY000): The table users is full

To fix this error, you need to first check the disk space in where your MySQL server is installed and see if the partition is really full.

You can do so by running the df -h command from the Terminal. Here’s an example partitions listed from my server:

$ df -h

Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1       200G   67G  134G  34% /
tmpfs            16G   34M   16G   1% /dev/shm
/dev/vdb1       800G  446G  354G  56% /tmp
tmpfs            16G  1.6G   15G  11% /run/dbus

If you see any disk on the list with the Use% value reaching around 90%, then you need to check if your mysql is installed on that disk.

Most likely you will have mysql located in /var/www/mysql directory, so you need to make sure the main mounted partition at / has the Use% lower than 80%.

But if you’re Use% values are low like in the example above, then the error is not caused by the disk partition.

You need to check on your MySQL configuration file next.

Fix MySQL table is full error from the configuration file

You need to open your MySQL config file and look at the configuration for innodb_data_file_path.

The default value may be as follows:

innodb_data_file_path = ibdata1:12M:autoextend:max:256M

The values of innodb_data_file_path option above will create an ibdata1 directory that stores all critical information for your InnoDB-based tables.

The maximum size of data you can store in your InnoDB tables are 256MB as shown in the autoextend:max:256M in the option above.

To resolve the MySQL table is full issue, try increasing the size of your autoextend parameter to 512M like this:

innodb_data_file_path = ibdata1:12M:autoextend:max:512M

Alternatively, you can also just write autoextend without specifying the maximum size to allow InnoDB tables to grow until the disk size is full:

innodb_data_file_path = ibdata1:12M:autoextend

Once done, save your configuration file and restart your MySQL server:

sudo service mysql stop
sudo service mysql start

Try to connect and insert the data into your database table again. It should work this time.

If you’re using the MyISAM engine for your tables, then MySQL permits each MyISAM table to grow up to 256TB by default.

The MyISAM engine limit can still be increased up to 65,536TB if you need to. Check out the official MySQL documentation on table size limits on how to do that.

Good luck resolving the issue! 👍

Take your skills to the next level ⚡️

I'm sending out an occasional email with the latest tutorials on programming, web development, and statistics. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.