Strange "/var/lib/mysql too full!" message, with almost empty partitions

Question

I've just encountered a strange problem on my server : it's a basic LAMP server I've just setup and when I try to restart MySQL, I get the following error.

/etc/init.d/mysql: ERROR: The partition with /var/lib/mysql is too full!

What's very strange is that I didn't set a special partition for the /var/ and I took a look at this using df and mount, but my partition are all almost empty !

>> df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2       254G  8.8G  232G   4% /
none            4.0K     0  4.0K   0% /sys/fs/cgroup
udev            3.9G  4.0K  3.9G   1% /dev
tmpfs           798M  452K  797M   1% /run
none            5.0M     0  5.0M   0% /run/lock
none            3.9G     0  3.9G   0% /run/shm
none            100M     0  100M   0% /run/user
/dev/sda1       180M   35M  133M  21% /boot
/dev/sda3       656G   70M  623G   1% /home

So I've google around and looked at older questions there, but I couldn't find anything except for a "bug report" in MySQL which wasn't confirmed (and whose workaround didn't work for me.)

So I'm at a loss there.

And now my server started to do strange things : when I did a LOAD DATA INFILE with a pretty big file (~1Go), it loaded for a good hour and then it just emptied the table I was loading it in... Just like it would have if it had rejected the datas because it hadn't enough space.

N.B : I also did a df -i and they are all at 1%... N.B : When trying to do it with "sudo", as root, I get the following

sudo /etc/init.d/mysql restart
* Stopping MySQL database server mysqld                   [ OK ]
* Starting MySQL database server mysqld                   [fail]

Which is even worse since I don't know why... But I guess it's the "too full" problem again.

[EDIT:] My filesystems :

>> df -hT
Filesystem     Type      Size  Used Avail Use% Mounted on
/dev/sda2      ext4      254G  8.9G  232G   4% /
none           tmpfs     4.0K     0  4.0K   0% /sys/fs/cgroup
udev           devtmpfs  3.9G  4.0K  3.9G   1% /dev
tmpfs          tmpfs     798M  460K  797M   1% /run
none           tmpfs     5.0M     0  5.0M   0% /run/lock
none           tmpfs     3.9G     0  3.9G   0% /run/shm
none           tmpfs     100M     0  100M   0% /run/user
/dev/sda1      ext4      180M   35M  133M  21% /boot
/dev/sda3      ext4      656G   70M  623G   1% /home

And here are the results of a "ps aux | grep mysql" :

mysql      851  0.2  2.1 619960 177832 ?       Sl   Apr14   2:53 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
mysql    20977  0.1  0.4 380728 34228 ?        Ssl  11:28   0:00 /usr/sbin/mysqld
ruuser      21111  0.0  0.0  11696   936 pts/1    S+   11:29   0:00 grep --color=auto mysql
root     32425  0.0  0.0   4440   748 ?        S    Apr14   0:00 /bin/sh /usr/bin/mysqld_safe

Answer 1

Did you check if MySQL is not already started ? For some reason, it sometimes says that the partition is too full only because a mysql deamon is already started.

You can check this with "ps aux | grep mysql". If you see some mysql processes running, try to stop them with "service mysql stop". If there are still mysql processes, kill them with "kill $PID" replacing $PID with their respective PID.

Try to start mysql again with "service mysql start".

Answer 2

The reason for the message is that (I suspect) a mysql instance opens a temporary file, and deletes it while retaining a handle to it. In this way, the file will automatically be deleted when the process dies, as the reference count will reach 0.

You can verify this using lsof (as root, or as the mysql service user). You will see mentions of (deleted) in the output if this is the case. The temporary file that it creates will be a useful way for mysql to pre-book some space (it probably makes a sparse file of some reasonably large size), which would explain the cause. Another possible cause of 'insufficient space' type messages can be a lack of inodes (df -hi will show it) -- but that is encountered typically only when dealing with a lot of small files, such as a proxy cache.

As others have indicated, it can be symptomatic of another MySQL process running. In which case, stop the service using the script; verify that there is no service running; kill any remaining if there are; and start using the script; verify that its up.