Converting your existing MaiaMailguard?'s MySQL database to InnoDB from MyISAM

If you are using MySQL4.x or MySQL3.x-max, you can upgrade your database to use InnoDB, fixing various performance/locking issues.

From the MySQL Documentation:

InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine with commit, rollback, and crash recovery capabilities. InnoDB does locking on the row level and also provides an Oracle-style consistent non-locking read in SELECT statements. These features increase multi-user concurrency and performance. There is no need for lock escalation in InnoDB because row-level locks in InnoDB fit in very little space. InnoDB also supports FOREIGN KEY constraints. In SQL queries you can freely mix InnoDB type tables with other table types of MySQL, even within the same query.

The following "shell script" will convert an existing Maia database's tables to InnoDB:

for table in `echo show tables|mysql maia|tail +2` ; do echo  alter table $table type='InnoDB'\;; done |mysql maia

It works for me.

But doesn't for people with passwords required to access their db. The long manual way to do it is:

$ mysql -u root -p maia
mysql> show tables;
mysql> alter table <insert table names one by one> type='InnoDB';
mysql> quit

Back to FAQ

Last modified 18 years ago Last modified on Dec 7, 2004, 12:30:53 PM