In case you don’t recall or weren’t reading when it was put up, I did an article on why a downloaded my.cnf is a bad idea. It’s time for a bit of appending to that. I am cranking out my usual support tickets helping little old ladies with their sites about their pinochle groups (no not really) when I get a support ticket from a client who is having issues with their databases not working properly.
Naturally, being the curious sort I am and them being a paying client, I log into the server and start examining things. The first thing I do is try to do a database check. This doesn’t go over too well, I get a bunch of errors about “corrupt .frm files.” Naturally my curiosity is piqued at this point, so I go and start looking through /var/lib/mysql/hosts.err file. What is extremely odd here is that there it is super short, and there is nothing there really. At this point I decide to log into mysql and try rotating out the log file. It indicated that the operation had completed successfully, so I am a bit baffled at this point. Looking in the my.cnf, it appears that someone downloaded a web based my.cnf and stuck it on the server. It also appears that the logs are going somewhere else at this point. After examining the correct logs, I find a bunch of errors about the .frms being corrupted. I then start looking for the MYIs so that I can see if I can rebuild the .frms with them. To my dismay I find out there are no MYIs for the DBs here just the .frms.
After updating the client on what I’d found, I start looking over the net for an answer. It turns out that if InnoDB doesn’t have the engine started it will default to MyISAM databases, which is where the .frms came from. Definitely getting closer at this point, so I start looking at the engine statuses and of course InnoDB is disabled.
After this the picture is starting to come into focus a bit more, something in the my.cnf was changed and it broke everything. But what? I had noticed that the server was running MySQL as root when I had done “ps aux | grep mysql” earlier so I had a suspicion. Referencing another server I confirmed what I thought and found out that a line was missing.
user=mysql
The net downloaded configuration didn’t have a set MySQL user, root didn’t have permission to access the file and therefore it wouldn’t load the InnoDB files and in turn the InnoDB engine. A single line was missing and this production server was likely down for hours while they tried to figure out what happened. The internet is not a substitute for knowing what you are doing. Yes it can help you in this regard, but at the same time it is not a substitute for knowledge or competency. I once had an instructor tell me there is no crib sheet for life, it rings as true now as it ever did when he said it.