Dknelson Posted October 11, 2022 Share Posted October 11, 2022 I have a mix of Inno and MySam tables. A LOT of MySam. I know how to change them one at a time but don't know how to change them all at once. Can somebody give me a step by step on how to change them all to Inno? I know just enough to destroy everything. I tried the following query but it didn't work, Said something about formatting errors. Also exactly where in PHP do I run it? root@host [~]# mysqlshow -i dknelson_ipb2 | grep -ic innodb 62 Link to comment Share on other sites More sharing options...
Randy Calvert Posted October 11, 2022 Share Posted October 11, 2022 If you don't know what you're doing, I would highly recommend against trying to "mass run" anything. You also don't run anything from PHP. It's run from within mySQL itself. Before you do anything, make sure you have a full backup, etc. From within mySQL, to generate a list of everything to convert: SET @DATABASE_NAME = 'YOURDBNAME'; SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name DESC; It will give you output like: ALTER TABLE 'something' ENGINE=InnoDB; ALTER TABLE 'blah' ENGINE=InnoDB; ALTER TABLE 'another_name' ENGINE=InnoDB; ALTER TABLE 'sometable' ENGINE=InnoDB; Just copy/paste all of those ALTER TABLE lines in a single copy/paste. Again... make sure you have a backup if you do this. I take no responsibility if you break your site or for unintended consequences, etc. (It is a relatively minor change, so it should be fine... but generally I don't suggest for people to play in the database unless they're very comfortable with what they're doing. Dknelson and LemonGrenade 1 1 Link to comment Share on other sites More sharing options...
Dknelson Posted October 13, 2022 Author Share Posted October 13, 2022 Just wanted to come back and thank you. That worked great. Had to do it a few times to keep things from timing out but much faster than changing them one at a time. Thanks much. Randy Calvert 1 Link to comment Share on other sites More sharing options...
Recommended Posts