Sorry but things have now changed, and I found the following.
The tables was in fact restored as Innodb, however someone seems
to have gone and changed something causing innodb to be disabled, this
caused the tables to be defaulted back to MyIsam.
Should this not rather have just resulted in an error allowing
to fix the problem in the first place instead of changing the storage
Anyone have some thoughts on the best solution to fix this? I
will look into the innodb not working soon.
From: Machiel Richards <machielr@stripped>
To: mysql mailing list <mysql@stripped>
Subject: Changing database tables to different storage engine.
Date: Mon, 22 Nov 2010 11:59:03 +0200
Good day all
Hope all is well.
I have something to ask as someone might have done this as
well and may have a good solution on how to fix this.
During a database migration this weekend to move a MySQL
database from windows to linux, we created a backup and restore of the
However, form my part I made a mistake by overlooking the
fact that the windows database was configured to use default storage
engine as Innodb.
On the new server, the default was set to MyIsam.
This resulted in all the tables being restored to the new
system as MyIsam instead of Innodb.
In order to fix this, I know you can use alter table to
change the storage engine, however I need to know the following:
1. this is a production system and can't afford any
downtime or as little performance degration as possible.
What is the best way to do this in order to
have the least amount of effect on the database and it's performance?