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...
No comments:
Post a Comment