Solved

Any way to: Alter table to engine=InnoDB?

Posted on 2008-06-16
2
1,774 Views
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,

CREATE TABLE TEST1
(
    test1_id     INT AUTO_INCREMENT PRIMARY KEY,
    msg          VARCHAR(5)
);

CREATE TABLE TEST2
(
    test2_id     INT AUTO_INCREMENT PRIMARY KEY,
   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......

.....is 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.
0
Comment
Question by:ACSIPaul
2 Comments
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 21796583
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

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.
0
 
LVL 4

Author Closing Comment

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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

930 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now