Any way to: Alter table to engine=InnoDB?

Posted on 2008-06-16
Last Modified: 2010-04-21
My MySQL 4.x database is hosted at an ISP.  I have an entire database of tables with foreign keys that I just discovered are worthless because the ISP didn't create the database as InnoDB.

For example,

    msg          VARCHAR(5)

   test_id     int,
      foreign key (test_id) REFERENCES TEST1 (test1_id),
   msg        VARCHAR(6)

INSERT INTO TEST1 (msg) VALUES ('One'), ('Two'), ('Three'), ('Four'), ('Five');

INSERT INTO TEST2 (test_id, msg) VALUES (null, '2One');
INSERT INTO TEST2 (test_id, msg) VALUES (2, '2Two');
INSERT INTO TEST2 (test_id, msg) VALUES (8, '2Eight');

All the inserts work, i.e., the foreign key constraint fails to flag the last insert as a key violation.

But if I change the table creation statements to include " engine=InnoDB" after the last ")" and before the semicolon, the tables work as expected and the last insert gets flagged as violating the foreign key.

While this is by design of MySQL...... there a way, using an ALTER TABLE [table name] or similar to convert a table to InnoDB?

Obviously, I could export the whole database using something like SQL Administrator and then edit the backup file to add the required parameter, but since this is a production database, I'd like to avoid that method so I can change one or two, test to verify the functionality with the website that uses it, and move one.
Question by:ACSIPaul
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 50

Accepted Solution

Steve Bink earned 500 total points
ID: 21796583

Under the "Limitations" section, the manual explicitly states that changing the table engine online is not possible.

Because of the way ALTER TABLE operates, you're not doing much different by creating another copy of the table and swapping them.  Build your InnoDB table with references, import that data into it, then swap the two tables by renaming them.  If the InnoDB table is 100% ready-to-go when you do the swap, you should only have a 10-second downtime, if that.

Author Closing Comment

ID: 31467725
Thank you.  Its not what I hoped for, but its exactly right, and it works.

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to count in a table in php 22 57
MySQL Backup Strategy 15 62
Use Select Query to Return Results as a Form 9 56
Not listening to where 1 42
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In an interesting question ( here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question