How to change storage to Inno

[Dknelson]

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

[Randy Calvert]

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]

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.