Friday, 16 November 2012

MySQL and vanishing InnoDB engines

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.

Concatenating CSVs the easy way

I was recently asked how to 'merge' a few CSV files into one, and if there was a script or tool that could do that. Lets say you h...