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.

Does the error occur when you try to insert into any table, or only the zip_codes one? — Chad Birch, Apr 08 '09 at 15:44

Martin C. · Accepted Answer · 2017-09-14T14:36:17.657

119

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.

edited Sep 14 '17 at 14:36

answered Apr 08 '09 at 15:45

Martin C.

12,140
7
40
52

C where we get this my.cnf file in ubuntu – Jun 16 '17 at 07:42
3

@Nadh In Ubuntu 16.04 it's part of `/etc/mysql/` and is partly splitted into additional files in `/etc/mysql/conf.d` – Martin C. Jun 17 '17 at 22:01
Mine worked after adding the `innodb_data_file_path` line to `/etc/mysql/mysql.conf.d/mysqld.cnf` and restarting the `mysql` and `apache2` services – Timmah Sep 26 '18 at 01:16
I realized that an 6 gb data dump required in equivalent amount of space in OS partition in windows to get imported in mysql – veritas May 16 '23 at 09:55

score 93 · Answer 2 · answered May 28 '11 at 15:24

93

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

answered May 28 '11 at 15:24

maaartinus

44,714
32
161
320

1

This should always be the first thing to check. Always go back to the power cord, I have stumbled across this many times. – Kyias Mar 09 '17 at 15:08
1

You saved me few hours trying to change the mysql configuration. The primary partition was full. Had to move mysql database to data partition and then create a soft link – Ganesh Krishnan Sep 02 '17 at 11:21
3

use `df -h` for check disk size – Amit Bera Nov 28 '19 at 11:00
This should always be the first thing to check. but usually when mysql give the error it deletes the data. and partition is not full. – Nick Sep 30 '20 at 20:13

score 46 · Answer 3 · answered Jun 09 '19 at 23:17

46

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.

answered Jun 09 '19 at 23:17

Sliq

15,937
27
110
143

1

That was a useful answer, I was running short on disk space and thought that was my issue, but it was indeed the virtual disk space setting that I had to worry about in the docker settings – delboy1978uk Feb 15 '23 at 15:54

score 27 · Answer 4 · edited Aug 10 '16 at 08:28

27

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

edited Aug 10 '16 at 08:28

Tarek

3,810
3
36
62

answered Mar 10 '10 at 16:01

Daniel Luca CleanUnicorn

1,087
1
12
30

1

We hosted our ddbb on Amazon and it was configured with autoextend. But we had the same problem that I assume was due to reaching the configured stored limit – borjab Nov 04 '16 at 09:03

score 27 · Answer 5 · answered May 19 '10 at 17:42

27

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.

answered May 19 '10 at 17:42

Green Card

369
4
2

This happened to me but it seemed like my client was using wrong syntax. When adding the same index with a simple `ALTER TABLE my_table ADD INDEX my_index (column_a, column_b);` it worked. – thephper Dec 08 '19 at 00:23

score 14 · Answer 6 · answered Nov 01 '11 at 17:15

14

This error also appears if the partition on which tmpdir resides fills up (due to an alter table or other

answered Nov 01 '11 at 17:15

fimbulvetr

742
7
8

score 12 · Answer 7 · answered Apr 28 '10 at 16:57

12

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

answered Apr 28 '10 at 16:57

skiphoppy

97,646
72
174
218

score 11 · Answer 8 · answered Jun 21 '12 at 16:58

You may be running out of space either in the partition where the mysql tables are stored (usually /var/lib/mysql) or in where the temporary tables are stored (usually /tmp).

You may want to: - monitor your free space during the index creation. - point the tmpdir MySQL variable to a different location. This requires a server restart.

Arun Kumar · Answer 9 · 2018-09-13T12:25:09.090

8

I too faced this error while importing an 8GB sql database file. Checked my mysql installation drive. There was no space left in the drive. So got some space by removing unwanted items and re-ran my database import command. This time it was successful.

edited Sep 13 '18 at 12:25

answered Aug 24 '16 at 05:27

Arun Kumar

81
1
2

score 7 · Answer 10 · answered Jan 07 '15 at 23:00

7

we had: SQLSTATE[HY000]: General error: 1114 The table 'catalog_product_index_price_bundle_sel_tmp' is full

solved by:

edit config of db:

nano /etc/my.cnf

tmp_table_size=256M max_heap_table_size=256M

restart db

answered Jan 07 '15 at 23:00

Sition

81
1
1

2

Those 512M settings are dangerous. They control the max memory size for temp tables in complex selects. It is not just "per connection", but also "per tmp table". So, those values could easily cause you to run out of RAM. – Rick James Jul 29 '16 at 22:29

score 7 · Answer 11 · answered Jan 11 '19 at 09:44

7

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 /

answered Jan 11 '19 at 09:44

Kai

679
9
11

this was the issue. not enough disk space +1 – Chris Apr 16 '23 at 19:25

score 7 · Answer 12 · answered Apr 08 '09 at 15:48

7

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.

answered Apr 08 '09 at 15:48

Quassnoi

413,100
91
616
614

score 7 · Answer 13 · edited Aug 22 '13 at 10:04

7

If you use NDBCLUSTER as storage engine, you should increase DataMemory and IndexMemory.

Mysql FQA

edited Aug 22 '13 at 10:04

Emil Davtyan

13,808
5
44
66

answered Dec 21 '11 at 14:13

metdos

13,411
17
77
120

score 5 · Answer 14 · answered Nov 27 '19 at 06:48

5

This disk is full at /var/www/mysql

answered Nov 27 '19 at 06:48

wukong

2,430
2
26
33

score 4 · Answer 15 · edited Jun 20 '20 at 09:12

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

score 3 · Answer 16 · answered Jun 17 '21 at 12:33

3

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.

answered Jun 17 '21 at 12:33

Johan Dettmar

27,968
5
31
28

1

This solution works also on Windows 10, MySQL 8.0.26. Thank you. – volkut Aug 19 '21 at 17:35
2

This is related to this [bug](https://bugs.mysql.com/bug.php?id=99100). It will be resolved in MySQL 8.0.27, according to the bugreport. – G Wimpassinger Sep 16 '21 at 16:30

score 2 · Answer 17 · answered Jan 26 '16 at 13:11

2

On CentOS 7 simply stopping and starting the MySQL service fixed this for me.

sudo service mysql stop

sudo service mysql start

answered Jan 26 '16 at 13:11

crmpicco

16,605
26
134
210

Strangely this worked for me as well.... Didn't have any partitions that were greater than 80% full and just restarting fixed it. – n0nag0n Nov 04 '19 at 16:53

score 2 · Answer 18 · answered Dec 07 '16 at 20:00

2

I faced same problem because of low disk space. And partition which is hosting the ibdata1 file which is the system tablespace for the InnoDB infrastructure was full.

answered Dec 07 '16 at 20:00

Saveendra Ekanayake

3,153
6
34
44

score 2 · Answer 19 · answered Jul 25 '17 at 13:32

2

I was experiencing this issue... in my case, I'd run out of storage on my dedicated server. Check that if everything else fails and consider increasing disk space or removing unwanted data or files.

answered Jul 25 '17 at 13:32

NotJay

3,919
5
38
62

score 2 · Answer 20 · answered Dec 30 '18 at 04:32

2

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.

answered Dec 30 '18 at 04:32

Pratik Singhal

6,283
10
55
97

im facing the same issue while altering table. but i dont have much space in vm. what should i do? can i do this from other vm? – Mujeebcpy Aug 25 '21 at 06:26

score 1 · Answer 21 · answered Jan 05 '16 at 18:59

1

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.

answered Jan 05 '16 at 18:59

GPY

3,832
1
16
11

score 1 · Answer 22 · answered Nov 28 '18 at 15:12

1

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

answered Nov 28 '18 at 15:12

yvoloshin

378
6
18

score 0 · Answer 23 · answered Jan 29 '10 at 13:42

0

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

answered Jan 29 '10 at 13:42

answer is way dated. – Evan Carroll Jul 13 '18 at 14:59

1114 (HY000): The table is full

23 Answers23

Linked

Related