Easiest way to switch all tables to InnoDB

[MadMaxMangos]

Is there a script or quick way to switch all the DB tables to InnoDB ?

[MadMaxMangos]

Edit:
I used this script

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;') 
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'

Which then generated a list of commands and tables I just ran the output as a new query and all tables show InnoDB.

However, the admincp still says I have MyISAM tables🤔

[teraßyte]

Try using this query instead (replace DBNAME as needed):

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=InnoDB ROW_FORMAT=DYNAMIC;') FROM information_schema.TABLES WHERE TABLE_SCHEMA='DBNAME' AND ENGINE = 'MyISAM';

This query doesn't check the table type and also forces the tables to use the DYNAMIC row format (in case your server defaults to Compact).

 

I always use it and once I run all the queries the ACP never complains about tables still being MyISAM.

Edited January 16, 2023 by teraßyte

[MadMaxMangos]

5 minutes ago, teraßyte said:

Try using this query instead (replace DBNAME as needed):

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=InnoDB ROW_FORMAT=DYNAMIC;') FROM information_schema.TABLES WHERE TABLE_SCHEMA='DBNAME' AND ENGINE = 'MyISAM';

This query doesn't check the table type and also forces the tables to use the DYNAMIC row format (in case your server defaults to Compact).

 

I always use it and once I run all the queries the ACP never complains about tables still being MyISAM.

This worked perfectly for me and admincp now showing no issues, thanks!

I'd mark your post as the solution but I dont seem to have the option to, maybe a mod/admin can do that when they see this.

Edited January 16, 2023 by MadMaxMangos

[MeMaBlue]

hello🙏, can perhaps someone do this for me / provide me with a quote to do it for me ?