Bug #16264 MySQL hangs ugly if no space on device
Submitted: 6 Jan 2006 19:38 Modified: 9 Jan 2006 10:26
Reporter: Philip Stoev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.0.16-standard OS:Linux (Fedora Core 4 2.6.13 in VMWare)
Assigned to: CPU Architecture:Any

[6 Jan 2006 19:38] Philip Stoev
Description:
Hello,

If your hard drive is 100% full and you attempt to start mysql when innodb data files are not present, mysql appears to attempt to create them unsuccessfully and hangs at startup.

The system console displays a message

post_create: setxaddr failed, rc=28 (dev=dm-0 ino=585204)

The MySQL error log shows:

/usr/sbin/mysqd: Error writing file '/var/lib/mysql/hostname.pid` (Errcode: 28)
Can't start server: can't create PID file: No space left on device

The issue is that the safe_mysqld script is not prepared to handle that and keeps restarting the mysql server every second without any benefit (only the log file grows on an already full partition). Straightforward killing safe_mysqld does not work for some reason and kill -9 must be used. "mysqladmin shutdown" can not be used since mysqld never opens its listening sockets. /etc/rc.d/init.d/mysqld restart probably will not work as well.

All this appears to be very user-unfriendly, in particular the inability to kill safe_mysqld, the resulting high CPU usage and the annoying kernel message that keeps poping up over the console.

How to repeat:
1. Install MySQL
2. Fill the partition to 100%
3. Run safe_mysqld or /etc/rc.d/init.d/mysqld start
4. Try to make sense of the situation, in particular if #3 was executed automatically by your initscript
5. Attempt to kill safe_mysqld will not work

Suggested fix:
safe_mysqld should be made aware of the reason mysql exists and certain error situations should not result in a mysqld restart attempt every second.

A backoff algorithm in safe_mysqld should be used to make mysqld restart less frequently if it is evident that the problem that caused the restart does not go away.
[8 Jan 2006 11:12] Valeriy Kravchuk
Thank you for a problem report. It looks like a feature request for me. Do you agree?
[8 Jan 2006 12:33] Philip Stoev
Yes, I agree.

Please note however that from a public-relations standpoint any issues relating to incomplete/incorrect error reporting and inability to restart the server should have their prioirity increased a bit.
[9 Jan 2006 10:26] Valeriy Kravchuk
Let's ask for the following feature: mysqld_safe (and, maybe, other startup scripts) should be changed to handle problems like the following:

/usr/sbin/mysqd: Error writing file '/var/lib/mysql/hostname.pid` (Errcode: 28)
Can't start server: can't create PID file: No space left on device

properly. There is no need to keep restarting with the same "rate" until the problem is solved. So, some status code checks and/or error log checks should be added to the script.