Changing Table Engine in MySQL

Question

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.

Sharper

Give us more details. How did you try to change engine? How did you "inserted data from 5 MyISAM tables"? – Reunionist

Answer 1

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?

Olibanum

Answer 2

It's not obvious. If you edit the table and then select the column tab the engine widget is not immediately visible. On the upper right of the edit window you will see two down pointing chevrons. Select the arrow once and additional widgets will appear. In the upper right hand corner there will now be widgets for the schema and engine.

Cowpuncher

I edited the picture to put pointers and numbered 1 and 2 to show the sequence of action. – Tarriance