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:
PLAIN TEXT
CODE:
- [FONT='Courier New', Courier, monospace]class mysqlx extends mysqli {[/font]
- [FONT='Courier New', Courier, monospace] [/font]
- [FONT='Courier New', Courier, monospace]...[/font]
- [FONT='Courier New', Courier, monospace] [/font]
- [FONT='Courier New', Courier, monospace] function deadlock_query($query) {[/font]
- [FONT='Courier New', Courier, monospace] $MAX_ATTEMPS = 100;[/font]
- [FONT='Courier New', Courier, monospace] $current = 0;[/font]
- [FONT='Courier New', Courier, monospace] while ($current++ query($query);[/font]
- [FONT='Courier New', Courier, monospace] [/font]
- [FONT='Courier New', Courier, monospace] if(!$res && ( $this->errno== '1205' || $this->errno == '1213' ) )[/font]
- [FONT='Courier New', Courier, monospace] continue;[/font]
- [FONT='Courier New', Courier, monospace] else [/font]
- [FONT='Courier New', Courier, monospace] break;[/font]
- [FONT='Courier New', Courier, monospace] }[/font]
- [FONT='Courier New', Courier, monospace] } [/font]
- [FONT='Courier New', Courier, monospace]...[/font]
- [FONT='Courier New', Courier, monospace]} [/font]
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
PLAIN TEXT
CODE:
- [FONT='Courier New', Courier, monospace]DBNAME="dbname"[/font]
- [FONT='Courier New', Courier, monospace]DBUSER="user"[/font]
- [FONT='Courier New', Courier, monospace]DBPWD="password"[/font]
- [FONT='Courier New', Courier, monospace]for t in $(mysql -u$DBUSER -p$DBPWD --batch --column-names=false -e "show tables" $DBNAME);[/font]
- [FONT='Courier New', Courier, monospace]do[/font]
- [FONT='Courier New', Courier, monospace]echo "Converting table $t"[/font]
- [FONT='Courier New', Courier, monospace]mysql -u$DBUSER -p$DBPWD -e "alter table $t type=InnoDB" $DBNAME;[/font]
- [FONT='Courier New', Courier, monospace]done [/font]
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.
Pitfalls of converting to InnoDB - Read More...