MadMaxMangos Posted January 16, 2023 Posted January 16, 2023 Is there a script or quick way to switch all the DB tables to InnoDB ?
MadMaxMangos Posted January 16, 2023 Author Posted January 16, 2023 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 Posted January 16, 2023 Posted January 16, 2023 (edited) 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 and MeMaBlue 1 1
MadMaxMangos Posted January 16, 2023 Author Posted January 16, 2023 (edited) 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 1
MeMaBlue Posted April 6, 2023 Posted April 6, 2023 hello🙏, can perhaps someone do this for me / provide me with a quote to do it for me ?
Recommended Posts