Solved

Any way to: Alter table to engine=InnoDB?

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

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…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 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