Now this isn’t going to be a normal post where I pretend that I know what I’m doing. However it was time to upgrade our installation of Web Help Desk and I figured I would also upgrade MySQL, because they finally fixed a bug where the the server would not launch on boot. This caused more than a few heart attacks so let’s kick that bug in the guts.
You know how they say ‘do your upgrades one at a time and test in between’? Well, I got cocky and figured that I would just do it all at once. The downside of this is that you have no idea which bit is broken when it doesn’t work.
And thus it transpired. We were upgrading WHD from 12.2 to 12.3 and MySQL from 5.22 to 5.26.
Straight after the upgrades I launched WHD and it complained about the missing database. Well, actually it goes into a setup wizard that asks about databases, which makes you think that it has magically eaten all of your data. OK, no problem I’ll just add in the details of the MySQL database and it’ll be fine. But no, ‘can’t connect to database’ gives you an evil chill.
OK, let’s open MySQL Workbench and see what is going on. Doesn’t seem to have launched, so we navigate to the start/ stop button and hit it. No dice, and it seems to have crashed Workbench. About 30 seconds later Workbench recovers, and reading the log and console messages it is complaining that MySQL might already be launched. Stop taking crazy pills- the PrefPane says it is not running!
Then I started on Dr Google. This article from Sybaspot.com has some interesting pointers but is a bit out of date- some of the commands either aren’t correct or are unnecessary. Quite honestly, the docs at dev.mysql.com weren’t that great either- they rely on a certain amount of knowledge that I don’t have. I really needed to be told what happens automatically and what needs to be hand rolled.
So I played around for a while adding symlinks and changing the location of various bits of the install without success before finally seeing this command-
ps auxww|grep [m]ysqldI was looking for logs so I could find out what the hell was wrong. This command shows you where the system thinks various bits are. The output of this was-
_mysql 3231 0.0 1.4 3114568 57940 ?? Ss 5:58pm 0:00.79 /usr/local/mysql/bin/mysqld –user=_mysql –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –plugin-dir=/usr/local/mysql/lib/plugin –log-error=/usr/local/mysql/data/mysqld.local.err –pid-file=/usr/local/mysql/data/mysqld.local.pid –port=3307
See that last number? It’s running on port 3307……. ARE YOU KIDDING ME? Fire up MySQL Workbench, punch in the new details and BAM! It’s working. The standard port is 3306, so what happened? No idea and I probably never will.
Things I learned- you can no longer trust the MySQL Server Status PrefPane- it can tell you the server is not running even if it is. MySQL Workbench will tell you it can change the port number in the my.cnf file, but it doesn’t. If you ask Workbench to start a server that doesn’t exist it will act like an evil serial killer. The upgrades and symlink instructions may or may not have been needed.
I would have to restore from a backup (the one good thing I did do is make a backup before heading down the rabbit hole) and try out this stuff but I spent so long on it I no longer have the time. Next time I need to do an upgrade I’ll refer to these notes and do it a bit smarter.
Maybe one day I’ll dive back in and do some performance tuning too, I’m pretty sure I can make it faster. OK I mean it’s a bit slow and it’s running on some powerful hardware, it should be fast!