Changing Table Engine in MySQL

mysql database innodb myisam
AliBZ picture AliBZ · Nov 22, 2010 · Viewed 59.4k times · Source

I am using mysql and mysql workbench. I created 5 tables with innodb engine. I checked their engine and it was innodb before I insert data into them. I inserted data from 5 MyISAM tables and now my innodb tables are MyISAM. I can't change them. I used the alter table engine=innodb but it doesn't work.

Answer

Matt Healy picture Matt Healy · Nov 22, 2010

From the manual: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

For example, to convert a table to be an InnoDB table, use this statement:

ALTER TABLE t1 ENGINE = InnoDB;

The outcome of attempting to change a table's storage engine is affected by whether the desired storage engine is available and the setting of the NO_ENGINE_SUBSTITUTION SQL mode, as described in Section 5.1.11, “Server SQL Modes”.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_engine_substitution

When you create the table do you get any warning about the Engine type being unavailable?

Related questions

MyISAM versus InnoDB

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. …

Read More
What's the difference between MyISAM and InnoDB?

I understand that this question has been asked before, but most of the time it is asked in relation to a specific database or table. I cannot find an answer on this site that describes the two engines and their …

Read More
Why doesn't MySQL's MyISAM engine support Foreign keys?

I am writing a web-app for my studies which includes fulltext search and foreign keys. I have read somewhere, MyISAM engine is suitable for fulltext searching, and InnoDB for foreign keys. In this situation what engine should I use for …

Read More