Alter all database tables engine to InnoDB

Question

I want to use an sql query, to alter all my database tables engine from MyISAM to InnoDB.
I used the query below. Although it gives me a success message, it doesn t work. Still my table s Storage Engine is MyISAM.

SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as ExecuteTheseSQLCommands
FROM information_schema.tables WHERE table_schema = 'name_of_the_database' 
ORDER BY table_name DESC;

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

If you have knowledge in PHP then this script will help you:

How to convert all tables from MyISAM into InnoDB?
If you have knowledge in Shell script then this link will help you:

http://shrubbery.homeip.net/c/display/W/Shell+and+MySQL#ShellandMySQL-ConvertMyISAMtablestoInnoDB (domain name changed by wiki owner)

edited May 23 '17 at 12:03

Community

1
1

answered Jan 22 '13 at 17:08

Jirilmon

1,924
1
12
13

Yes, that s the same like taking the results of my query and copy/paste them for execution. Thank you – mallix Jan 22 '13 at 17:11

ion · Answer 2 · 2020-03-14T09:10:54.690

1

My solution - launch your mysql shell and paste:

SELECT CONCAT('ALTER TABLE ',CONCAT(TABLE_SCHEMA,'.',TABLE_NAME),'     
ENGINE=InnoDB;') 
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND TABLE_SCHEMA NOT IN ('MYSQL','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA','SYS')
INTO OUTFILE '/tmp/mysql.conversions';
SOURCE /tmp/mysql.conversions;

Source 1: https://computingforgeeks.com/how-to-convert-all-mysql-tables-from-myisam-into-innodb-storage-engine/

Source 2: https://stackoverflow.com/a/16014411/3882707

edited Mar 14 '20 at 09:10

answered Mar 13 '20 at 21:53

ion

540
7
20

1

You should makes sure to exclude tables in system schemas `mysql`, `information_schema`, `performance_schema`, `sys`. In MySQL 5.x, most of the `mysql.*` system tables MUST use the MyISAM engine. In MySQL 8.0, they're InnoDB already, so no need to alter them. – Bill Karwin Mar 13 '20 at 22:01
Also you should put back-ticks around the schema names and table names, just in case someone has named their table the same as a reserved word. For example a table named `order` is pretty common. – Bill Karwin Mar 13 '20 at 22:03

score 0 · Accepted Answer · answered Jan 22 '13 at 16:58

0

See http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html

ALTER TABLE t1 ENGINE=InnoDB;

edit: oh wait, you generated this kind of statements, but they did not work?

answered Jan 22 '13 at 16:58

Bastien Jansen

8,756
2
35
53

if I execute your sql statement (per table), it works. I want all tables at once. Using my sql query, I get a success, but no change to the Storage Engine – mallix Jan 22 '13 at 17:01
3

You statement only generates a list of results which look like SQL statements, but these statements are not actually executed. You should copy/paste the output of your SELECT to a `mysql` command, which will run them one by one. – Bastien Jansen Jan 22 '13 at 17:03
Yes that s correct. So is there an sql query, to do this at once, no matter the number and names of database tables ? – mallix Jan 22 '13 at 17:07
Like I said in my previous answer, the easiest solution would be to copy/paste the output of your previous query in a file, then do: `mysql -u user -p pwd < yourfile.sql` – Bastien Jansen Jan 23 '13 at 08:42

Alter all database tables engine to InnoDB

3 Answers3