Got error 28 from storage engine (disk full) but my disk are not full
Solution 1
It might be that your query makes MySQL create temporary tables. In the default configuration, these will be created alongside the other tables, which is likely on your /
partition which has only 1.6GB left, and these tables can become larget than that really quickly.
Watch your free space while you do such a query.
See this documentation about it.
Solution 2
Two other possibilities:
- Filesystems often reserve 5% of the space and return an error when it reaches 95% full. Your
/home
is at 93%. - You get the same error message when you run out of inodes, even if you still have disk space left. You can see inodes with
df -i
.
I think SvenW's answer is more likely to be correct. His reasoning is sound and I have run into exactly the same issue myself.
You can figure out whether the query is going to use a temporary table by running EXPLAIN <query>
in your MySQL instance, replacing <query>
with the actual query. You are looking for "Using temporary" in the Extra
section. Temporary tables will be written to disk if they are larger than max_heap_table_size
and/or tmp_table_size
in your my.cnf
.
You can find what directory MySQL is using for temporary tables by looking at the tmpdir
variable either in the running instance (mysql> SHOW VARIABLES LIKE 'tmpdir';
) or in your my.cnf
(grep tmpdir my.cnf
).
dynamic
Updated on September 18, 2022Comments
-
dynamic over 1 year
Basically MySQL is giving me many errors of "Got error 28 from storage engine" that means no more disk space is available.
The output of
df -h
is:File system Dim. Usati Disp. Uso% Montato su /dev/md1 10G 7,9G 1,6G 84% / tmpfs 2,0G 0 2,0G 0% /lib/init/rw udev 10M 176K 9,9M 2% /dev tmpfs 2,0G 0 2,0G 0% /dev/shm /dev/md2 683G 601G 48G 93% /home
Every filesystem here is not empty. Are there any other problem?
I am on
- a dedicated server (debian 64bit )
- and the errors happens doing an heavy query -
dynamic almost 12 yearsindeed that query are made on pretty large table... I will try to free some spaces in /
-
dynamic almost 12 yearsHm, Do mysql uses
/home
or/
? Anyway i will checkdf -i
too later! thanks you too -
Ladadadada almost 12 yearsMySQL uses whatever you have configured in
my.cnf
fordatadir
. The default is/var/lib/mysql
. -
dynamic almost 12 yearsI have explain-ed my query, there is "using temporary; Using filesort" in the extra field. My
tmpdir
var is/tmp
. But I have 1,6GB free on/
how is that possible