Got error 28 from storage engine (disk full) but my disk are not full

Question

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

Answer 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.

Answer 2

Two other possibilities:

1. Filesystems often reserve 5% of the space and return an error when it reaches 95% full. Your /home is at 93%.
2. 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).