[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1792
  • Last Modified:

Any way to: Alter table to engine=InnoDB?

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
ACSIPaul
Asked:
ACSIPaul
1 Solution
 
Steve BinkCommented:
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
 
ACSIPaulAuthor Commented:
Thank you.  Its not what I hoped for, but its exactly right, and it works.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now