So, apparently, MySQL has an amazing ability to make InnoDB engines vanish.
If that sentence means nothing to you, I apologise, but this is going to be an exceptionally geeky post, even for me.
If you use MySQL with InnoDB tables, it seems that they can very easily stop working because the InnoDB engine just goes away. It can be for a number of reasons, but they all seem to revolve around the "ib_logfile" files.
If, for whatever reason, you change the size of the InnoDB log in the config, and don't delete the existing log file - MySQL will start perfectly happily, but you won't be able to access anything InnoDBish. Similarly if the ib_logfile(s) get corrupted in anyway.
Doing a "show engines \G" in the MySQL prompt will show you what's wrong - there is no InnoDB engine running.
You won't get any error messages. No warnings will be displayed. The only clue will be when you try to access the table and it will give you a "No InnoDB engine" error.
The solution is to delete or move the existing ib_logfiles from the data folder, restart the MySQL service and it will create new ones. Everything should now be back to normal.
Apparently this can also happen if the log file setting is too high and the memory can't be allocated - so that's worth keeping an eye on.
Subscribe to:
Post Comments (Atom)
-
I'm fascinated by random tech, always have been. It's the sort of odd electronic things that you get in those tacky shops on holida... -
Time for another bunch of random links to stuff that I heard about this week. Historic watermill on the Thames reinstates an old turbine t...
This is fixed in 5.6.8 - See http://dev.mysql.com/doc/refman/5.6/en/news-5-6-8.html
ReplyDelete"InnoDB: On startup, MySQL would not start if there was a mismatch between the value of the innodb_log_file_size configuration option and the actual size of the ib_logfile* files that make up the redo log. This behavior required manually removing the redo log files after changing the value of innodb_log_file_size. The fix causes MySQL to write all dirty pages to disk and re-create the redo log files during startup if it detects a size mismatch. (Bug #14596550)"
Excellent, thanks for the heads up!
Delete