October 11, 2022 in Classic self-hosted technical help
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
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.
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.
Started 1 hour ago
Started 5 hours ago
Started 3 hours ago