We often recommend to our clients to convert their current database from MyISAM tables to InnoDB.
The transfer by itself in most cases is almost plain, however the application can be broken by new unexpected errors
1205 (ER_LOCK_WAIT_TIMEOUT)
Lock wait timeout expired. Transaction was rolled back.
1213 (ER_LOCK_DEADLOCK)
Transaction deadlock. You should rerun the transaction.
It is not hard to handle these errors, but you should be aware of.
This is some thing we do in our PHP applications:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
class mysqlx extends mysqli { ... function deadlock_query($query) { $MAX_ATTEMPS = 100; $current = 0; while ($current++ < $MAX_ATTEMPS) { $res = $this->query($query); if(!$res && ( $this->errno== '1205' || $this->errno == '1213' ) ) continue; else break; } } ... } |
You may want to handle ER_LOCK_WAIT_TIMEOUT in different way, especially for web applications where long waiting is not good, you get the idea.
Also there is script we are using for converting databases with many tables, maybe it will be useful for you
|
1 2 3 4 5 6 7 8 |
DBNAME="dbname" DBUSER="user" DBPWD="password" for t in $(mysql -u$DBUSER -p$DBPWD --batch --column-names=false -e "show tables" $DBNAME); do echo "Converting table $t" mysql -u$DBUSER -p$DBPWD -e "alter table $t type=InnoDB" $DBNAME; done |
There is the standard script mysql_convert_table_format in the MySQL distribution, but it requires Perl and DBI package and sometimes they are absent on used server.
