Monday, September 29, 2008

Suddenly mysql won't start

Suddenly is a bit of an exaggeration. This happened on my desktop -- I use mysql on my laptop almost daily, but only once every few months my desktop. They are similar environments: latest Mac OSX 10.5 patched intel based macs. The mysql on the desktop was transferred from my previous PowerPc based desktop, but has been used a few times since the transfer.

In any case, the normal startup action
sudo /usr/local/mysql/bin/mysqld_safe



failed with the following output.


Starting mysqld daemon with databases from /usr/local/mysql/data

/usr/local/mysql/bin/mysqld_safe: line 395: /usr/local/var/: Is a directory


/usr/local/mysql/bin/mysqld_safe: line 401: /usr/local/var/: Is a directory


STOPPING server from pid file /usr/local/mysql/data/rdf-8-Tower.local.pid

tee: /usr/local/var/: Is a directory

080910 15:39:05 mysqld ended

tee: /usr/local/var/: Is a directory



At this point I said to myself "well this hasn't been upgraded in a while, I should upgrade mysql" !!bad idea!!


The upgrade didn't solve the problem. After tracing through the script in more detail I did a


sudo ./my_print_defaults



which reminded me that


Default options are read from the following files in the given order:

/etc/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf




/etc/my.cnf contained



[mysqld]

log = /usr/local/var/mysqlLOG.log

#If no specific storage engine/table type is defined in an SQL-Create statement the default type will be used.

default-storage-engine=myisam

max_allowed_packet = 16M

#Enter a name for the error log file. Otherwise a default name will be used.

log-error=/usr/local/var/

#Enter a name for the slow query log. Otherwise a default name will be used.

log-slow-queries=/usr/local/var/



Which I changed to



[mysqld]

log = /usr/local/var/mysqlLOG.log

#If no specific storage engine/table type is defined in an SQL-Create statement the default type will be used.

default-storage-engine=myisam

max_allowed_packet = 16M

#Enter a name for the error log file. Otherwise a default name will be used.

log-error=/usr/local/mysql_ERROR_LOG.log

#Enter a name for the slow query log. Otherwise a default name will be used.

log-slow-queries=/usr/local/var/mysql_SLOW_l_LOG.log




/usr/local/mysql/etc/my.cnf
didn't exist, nor did
~/.my.cnf

This allowed the db to start but I couldn't log in with any of the user accounts normally available (including root). It appears that using the mysql-5.0.67-osx10.5-x86.dmg file to update caused the db user information to get hosed.



Fortunately I'm pretty neurotic about backups and so I easily recovered just by copying /usr/local/* from my last backup.


NOTE: this backup was to a separate disk performed using SuperDuper -- TimeMachine isn't going to get files in /usr (which helps explain why the disk space used by TimeMachine is smaller than I expected).

No comments: