As an amateur DBA I recently made a classic amateur DBA mistake: I restarted my computer without first shutting down MySQL. (The restart in this case was the result of updating macOS to 10.12.4.) After the reboot I couldn’t work out why XAMPP wasn’t launching MySQL again – apache started up fine – so it was time to dig into the log files.
A quick check of
var/mysql/hostnameredacted.local.err in the XAMPP installation directory revealed a string of errors and a stacktrace. Reading it carefully I realized the key error message was this:
InnoDB: Operating system error number 2 in a file operation
The other clue was a few lines earlier in the logfile:
InnoDB: Database was not shutdown normally!
As ever, Google to the rescue which led me to a handy blog post which suggested adding the following line to the
[mysqld] section of
innodb_force_recovery = 1
I saved the file, and held my breath as I attempted to restart MySQL. Success!
I went back and removed that line again (actually just commented it out – there’s an outside chance I’ll repeat this error at some point in the future!) and re-restarted MySQL. Firing up SequelPro and I was relieved to be able to see all my tables as expected, and ran a few test queries to check it out. Everything looks OK. Phew!
Lesson learned: remember to shutdown MySQL properly before a reboot.