2010-01-13

Multiple table changes

So, I run into a problem with a java (ewwww) build, where the tables in a MySQL DB were being altered, but the underlying storage engine wouldn't support the request.

So, I'm faced with having to change the storage engine of multiple tables in a single DB. I wanted to do this the easy way, so I asked twitter for help. It even got a retweet, thanks MBGeek.

Sadly, Twitter hasn't yet reached the quickness of a man in action and I dove into the information_schema, where I quickly used this query to identify all the tables needing the change:

SELECT `TABLE_NAME`
FROM `TABLES`
WHERE `TABLE_SCHEMA` = '%DBOFINTEREST%'
AND `ENGINE` = 'MyISAM'


From there, I took the results and dumped them into the best editor around. Then I did the following commands:

VG # to select all the lines in the file
:s/^/ALTER TABLE`/ #Put "ALTER TABLE`" at the beginning

VG # to select all the lines again in the file
:s/$/` ENGINE = InnoDB;/ #Put "` ENGINE = InnoDB;" at the end



Then, C&P the result into a SQL window (set to the correct DB) and fired it off:

"Your SQL query has been executed successfully"


Hell yeah! It probably took me longer to write this than it did to figure it out...