stopping the madness: convert mysql myisam tables to innodb


draft post. I would say I’ll test this some more and make it a funny oneliner.

use with caution, i don’t know much about mysql. I know I can’t understand why it defaults to “risk dataloss engine”. I know I can’t understand why DTC does also use MyISAM. This is supposed to be quality hosting software and it uses MyISAM? Quality my ass.

I found a good starting point at Ryans tech notes and will loop over that to handle all databases.

maybe flush tables here? my server is so idle I felt safe like it is.

for db in $dbs ; do
    mysql --defaults-extra-file=/root/.my.cnf -e "show tables in $db" | tail --lines=+2 | xarg
s -i echo "ALTER TABLE {} ENGINE=INNODB;" > tables_$db.sql
    mysql --defaults-extra-file=/root/.my.cnf --database=$db < tables_$db.sql
done

errors that I saw:
ERROR 1044 (42000) at line 1: Access denied for user ‘root’@’localhost’ to database ‘information_schema’
ERROR 1025 (HY000) at line 2: Error on rename of ‘./mysql/#sql-5cf_23c7a’ to ‘./mysql/db’ (errno: -1)

– ok should’ve not even tried on the information schema anyway – internal database.

the second one appears to be a race condition?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s