Invision Community 4: SEO, prepare for v5 and dormant account notifications Matt November 11, 2024Nov 11
Posted October 11, 20222 yr 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
October 11, 20222 yr Community Expert 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.
October 13, 20222 yr Author 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.