MySQL Losing its Socket! (Shallow Thoughts)

MySQL Losing its Socket!

This morning I was all ready to continue working on an ongoing web project when I discovered that mysql wasn't running.

That's funny, it was running fine yesterday! I tried /etc/init.d/mysql start, but it failed. The only error message was, "Please take a look at the syslog."

So I hied myself over to /var/log, to discover that mysql.log and mysql.err were both there, but empty.

Some poking around /etc/mysql/my.cnf revealed that logging is commented out by default, because: "# Be aware that this log type is a performance killer."

I uncommented logging and tried again, but /var/log/mysql.err remained blank, and all that was in mysql.log was three lines related basically giving its runtime arguments and the name of the socket file.

Back to the drawing board. I was well aware that I had changed the mysql settings yesterday. See, mysqld on Ubuntu likes to create its socket as /var/run/mysqld/mysqld.sock, but other apps, like Ruby, all expect to find it in /tmp/mysql.sock. It's easy enough to change Ruby's expectations. But then I found out that although the cmdline client mysql also expects the socket in /var/run/mysqld, it depends on something called mysqladmin that wants the socket in /tmp. (I may have those two reversed. No matter: the point is that you can't use the client to talk to the database because it and the program it depends on disagree about the name of the socket. This is probably a Dapper bug.)

Okay, so I had to pick one. I decided that /tmp/mysql.sock was easier to remember and more standard with non-Debian setups. I knew where to change it in the server (/etc/mysql/my.cnf is there and well commented) but the mysql client doesn't use that, and it took some googling and help from clueful friends to find out that what it wanted was a new file called /etc/my.cnf (how's that for a nice clear configuration file name?) containing one line:

socket          = /tmp/mysql.sock

That done, mysql started and ran and everything worked. Woo!

Except that it didn't the following morning after a reboot, and didn't give any error messages as to why.

Off I went on a merry chase across init files: /etc/init.d/mysql calls /etc/mysql/debian-start (which made me realize that debian has added yet another config file, debian.cnf, which has yet another copy of the line specifying the socket filename) which calls /usr/share/mysql/debian-start.inc.sh as well as calling various other programs. But those were all red herrings: the trick to debugging the problem was to run mysqld directly (not via /etc/init.d/mysql start: it actually does print error messages, but they were being hidden by using the init.d script.

The real problem turned out to be that I had changed the location of the socket file, but not the pid file, in /etc/mysql/my.cnf, which was also located by default in /var/run/mysqld. Apparently that directory is created dynamically at each boot, and it isn't created unless it's needed for the socket file (whether the pid file needs it doesn't matter). So since I'd moved the socket file to /tmp, /var/run/mysqld wasn't created, mysqld couldn't create its pid file and it bailed. Solution: edit my.cnf to use /tmp for the pid file.

Tags: programming
[ 13:29 Mar 17, 2006    More programming | permalink to this entry | ]