Search code examples
mysqldatabaseinnodbmyisam

Changing Table Engine in MySQL

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.

Solution

  • 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?