1114 (HY000): The table is full

Wickethewok_30133 0
Viewed 284265

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.

innodb mysql
23 Answers

EDIT: First check, if you did not run out of disk-space, before resolving to the configuration-related resolution.

You seem to have a too low maximum size for your innodb_data_file_path in your my.cnf, In this example

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

you cannot host more than 512MB of data in all innodb tables combined.

Maybe you should switch to an innodb-per-table scheme using innodb_file_per_table.

user_192460

DOCKER USERS: This also happens when you have hit around 90% of your Docker image size limit (seems like 10% is needed for caching or so). The wording is confusing, as this simply means the amount of disk space Docker can use for basically everything.

To fix, go to your Docker desktop settings > Disk > move slider a bit more to the right > Apply.

enter image description here

user_11143200

You need to modify the limit cap set in my.cnf for the INNO_DB tables. This memory limit is not set for individual tables, it is set for all the tables combined.

If you want the memory to autoextend to 512MB 

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

If you don't know the limit or don't want to put a limit cap, you can modify it like this

innodb_data_file_path = ibdata1:10M:autoextend
user_1512440

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.

user_551590

in my case, it just because the mysql server runs together with an application , who write too many logs that the disk is full.

you can check if the disk has enough space use

df -h

if the disk usage percentage is 100%, you can use this command to find which directory is too large

du -h -d 1 /
user_56997840

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”.]

211lafur_Waage_224590

This disk is full at /var/www/mysql

user_8978890

For those of you whose issues still remain when trying increasing any of the various memory limits: by setting internal_tmp_mem_storage_engine=MEMORY solved the issue for me.

I'm on Ubuntu 20.04.2, using MySQL 8.0.25-0ubuntu0.20.04.1.

user_7043710

In my case, I was trying to run an alter table command and the available disk space was less than the size of table. Once, I increased the disk space the problem went away.

user_27900360

I fixed this problem by increasing the amount of memory available to the vagrant VM where the database was located.

user_52870840

This could also be the InnoDB limit for the number of open transactions:

http://bugs.mysql.com/bug.php?id=26590

at 1024 transactions, that have undo records (as in, edited any data), InnoDB will fail to work

Admin1
Related