Page 1 of 1

myISAM to INNOdb

Posted: Thu Oct 20, 2005 9:00 am
by waskelton4
Hey group,

I'm starting to do some things with my database that will be best done in transactions and I have a question.

all of my major tables are using the myISAM engine currently and I'd like to change them over to INNOdb.

I know that i can use code like so..

Code: Select all

alter table `dbName`.`tblName` ENGINE=INNOdb;
and change them over.

My questions are..

Does the database need to be taken down and all connections terminated before I run these commands?
Are there any problems that might/will/should arise after the change? Will INNOdb tables react the same as the myISAM tables to my querys?
Will all of the built in table functions (autoincrement and such) work the same?
Anything else I should be careful of?

Thanks
Will

Posted: Thu Oct 20, 2005 11:16 am
by deltawing
As far as I know, it should all work the same. I can't see any problems during the changeover even if there are open connections.

When you use ALTER, it actually (i) creates a new table with a temporary name (in this case, an InnoDB table), (ii) copies the data from the original table to the new table, (iii) renames the original table another temporary name, (iv) renames the first temporary table to the original name, then (v) deletes the original table. The chances of a query between iii and iv (when the table with the requested name doesn't exist) are very low, so I wouldn't be too concerned.