ERROR 1114 (HY000): The table is full

Question

I'm trying to add a row to an InnoDB table with a simply query:

INSERT INTO zip_codes (zip_code, city) VALUES ('90210', 'Beverly Hills');

But when I attempt this query, I get the following:

ERROR 1114 (HY000): The table `zip_codes` is full

Doing a "SELECT COUNT(*) FROM zip_codes" gives me 188,959 rows, which doesn't seem like too many considering I have another table with 810,635 rows in that same database.

I am fairly inexperienced with the InnoDB engine and never experienced this issue with MyISAM. What are some of the potential problems here?

EDIT: This only occurs when adding a row to the zip_codes table.

Answer 1

Unless you enabled innodb_file_per_table option, InnoDB keeps all data in one file, usually called ibdata1.

Check the size of that file and check you have enough disk space in the drive it resides on.

Answer 2

In my case the server memory was full so the DB could not write the temp data. To solve it you just have to make some place on your drive.

Answer 3

Another possible reason is the partition being full - this is just what happened to me now.

Answer 4

You will also get the same error ERROR 1114 (HY000): The table '#sql-310a_8867d7f' is full

if you try to add an index to a table that is using the storage engine MEMORY.

Answer 5

In my case, this was because the partition hosting the ibdata1 file was full.

Answer 6

To quote the MySQL Documents.

The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB.

If you are using InnoDB tables and run out of room in the InnoDB tablespace. In this case, the solution is to extend the InnoDB tablespace. See Section 13.2.5, [“Adding, Removing, or Resizing InnoDB Data and Log Files”.]