, one of the sites I am responsible for, we recently took our mySql instance and put it on its own server instead of sharing the server with Apache (and several other things) as we had been previously. The migration went quite smooth and we turned the “on” switch with no major issues. After the switch, I decided to look at the my.cnf config file, which I had tuned for the previous one-server setup, to see if it could be better optimized for the new setup. I was excited when I found an example config that shipped with mysql named “my-innodb-heavy-4G.cnf”. The new database server is indeed a 4Gb (memory) machine. And the main databases on this server do make extremely heavy use of InnoDb. So, I figured this would be a great starting point.
First, I reviewed the my-innodb-heavy-4G.cnf file to ensure there were no crazy settings in there and that integral, non-performance related, things like file paths were setup correctly. I felt comfortable with the config. After taking a backup of my.cnf, I replaced it with the new config file. I restarted mysql and I was happy that it came back up. I immediately started looking at performance monitors to see what I could notice.
What I did not do was go to www.majorcommand.com
to see if it still worked. Of course with Murphy’s law and all, it did not. After a bit I noted no load at all and decided something was wrong. So I went to the site and sure enough it was down. I looked at the Apache log and saw the following never-before-seen error message:
Unknown table engine 'InnoDB'.
I immediate thought “oh shit, I probably left in the skip-innodb directive,” although I also wondered how that could have possibly been in a sample config file with InnoDb in it’s title! None-the-less, it seemed the most likely answer. I looked at the config file and there was the skip-innodb directive, happily commented out as it should be.
I Googled around a bit and found one other likely cause: if your InnoDb log files are corrupt this error will occur. You need to delete and recreate your InnoDb log files to fix it. However, I did not even try this because I knew my log files were not corrupt. After the failed attempt with the config file update, I replaced my previous config file and InnoDb again worked just fine; Therefore, the InnoDb logs had to be fine. I was stumped.
I just stared at the config file, hoping the answer would jump out at me. I was convinced it was something in the config file, based on the simple fact that with one config file everything worked fine and with the other it did not.
BOOM! It hit me.
I see a directive called “log-bin=mysql-bin” and I read the comment above it which starts out tellingly:
Enable binary logging
I thought to myself, “if it is looking for a binary log and finds an ascii one instead, it will surely consider that corrupt as well.” When the config was previously set to use ascii logging, the file was not corrupt. If it is set to binary, then for all intents and purposes the log file is corrupted.
I gave it a whirl. I commented out the log-bin line completely and also commented out the next, obviously related, directive called “binlog_format”. I restarted mysqld and innodb tables now load perfectly.
Can’t load Innodb tables in mySql due to “Unknown table engine” error
Possible Causes (and/or)
- You have the skip-innodb directive set in your my.cnf file
- Your innodb log files are corrupt (/var/lib/mysql/ib_logfile*)
- Your innodb log files had been previsouly stored in ascii, and now you’re trying to use them in binary because of the log-bin directive in my.cnf (or the other way around: your log files are binary and my.cnf is set to ascii by the omission of the log-bin directive)
Cause #3 is the topic of this blog entry, is not easily found on Google (I couldn’t find it anywhere) and it was the source of my particular issue.
Solutions (to the above causes, respectively)
- Comment out or remove the skip-innodb directive in my.cnf
- Delete your InnoDb log files and recreate them, as detailed here by the poster name HoKe (this is the post that clued me in to looking at the log directives): http://www.turnkeylinux.org/forum/support/20090111/drupal-6-problem-enable-innodb#comment-131
- Comment out or delete the log-bin directive in my.cnf (or add the directive, if your log files are store din binary already)
I hope this helps someone.