myISAM to INNOdb

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

myISAM to INNOdb

Post 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
deltawing
Forum Commoner
Posts: 46
Joined: Tue Jun 14, 2005 2:55 pm

Post 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.
Post Reply