A Working Solution To “ERROR 1114: The table ‘x’ is full” MySQL Error

It’s rare I get stuck on anything for more than an hour before I’m able to find a solution. Today was a different story however. I must’ve been stuck on this problem for about 4 hours so when I finally solved it I had to write about it to help others that might find themselves in the same situation.

So let’s look at the problem…

I was doing what I’d done hundreds of times in the past. Exporting an SQL file using phpMyAdmin and re-importing it into another database, again using phpMyAdmin. When I imported it however I started getting the following error:

1. ERROR 1114 (HY000) at line 12345: The table 'x' is full  

What’s odd was that the table it was complaining about was empty and contained no rows. Yes, the SQL files was fairly large, but not the largest I’ve imported before.

POTENTIAL SOLUTIONS

I tried a few things to try and resolve this issue:

Remove the problematic table from the SQL file – When i did this it then just complained that the next table referenced was empty. Again this had no rows so it looks like it wasn’t specific to a particular table.

Change innodb settings – There are a number of settings that can be configured in the MySQL my.cnf configuration file. Changing these, restarting MySQL, and trying the import again always resulted in the same error.

THE SOLUTION

Four hours later and at 3am in the morning I finally got it. I noticed that the error was occurring at the point near the end of the SQL file where the indexes and keys were added. I was playing around with the settings when exporting the SQL from phpMyAdmin and noticed this field:

After ticking that option and re-exporting the SQL I noticed that the keys and indexes were now part of the ‘CREATE TABLE’ statement. I re-imported it and voila! It imported successfully with no errors.

I’ve no idea whatsoever why creating the indexes at a different point would have anything to do with a table being full. If you do the know the reason I’d love to hear it in the comments below.