Database corruption due to InnoDB?

Question

We had some weird situation last night where a EE site we manage fell over of it's own accord and is this morning reporting SQL errors:

Error Number: 1286

Unknown storage engine 'InnoDB'

Closer inspection reveals that all of the affected tables are set to use InnoDB whereas the unaffected tables are using MyISAM.

I'm not sure what best practice is but I'm guessing have different tables running on different engines is less than ideal and I'm not even sure how it happened but this is a fairly old site that's moved from host to host and started out on EE 1.x.

What are the repercussions of changing database engine for an ExpressionEngine table (if any) and why might this have happened in the first place (the table corruption)?

Answer 1

You probably need to check with your web host, as that error tells me that they made a change to MySQL server, and it's no longer configured to work with InnoDB. Or that you just moved from a host that supported InnoDB to one that does not.

There aren't any huge gotchas with running different engines on different tables as long as you or your web host are familiar with them (can't generate backups with MySQL's MyISAM-only tools, for instance, no FULLTEXT search in InnoDB).

We often recommend InnoDB for users on hosts who are well-versed in configuring MySQL for InnoDB, with the exception of the exp_cp_search table because it uses FULLTEXT search, which is not available to InnoDB.

Changing the engines should not be a problem either way, though of course you will want to backup first - and fairly certain that the server you do it on will need to have both storage engines available.