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
Solved

Any way to: Alter table to engine=InnoDB?

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
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…

837 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