Setting default MySQL storage engine (InnoDB or MyISAM) in PhpMyAdmin

I ran into an issue today when our slave MySQL server was lagging behind the master. The error was that a certain table didn’t exist. I went into the master and looked for the table causing the issue. Normally there are 3 files for each database table when you’re using the MyISAM storage engine.

table_name.frm
table_name.MYD
table_name.MYI

The master machine only had 1 file

table_name.MYI

Hmmmmmm… what gives? I fired up PhpMyAdmin on the Master DB and noticed the table was using InnoDB storage instead of MyISAM. Ultimately I found this was caused by one of our devs adding a new table to the database using PhpMyAdmin, which was defaulting to InnoDB. My database backup method of choice (mysqlhotcopy) doesn’t function if any of the tables in the DB are using a different storage engine than MyISAM.

The solution: Edit you my.cnf file (typically located at /etc/my.cnf). Add the following line to set the default storage engine to MyISAM.

default-storage-engine=MYISAM

Tables already created with InnoDB will not be changed, but your “new table” dropdown in PhpMyAdmin should now show MyISAM as the default engine.

No related posts.

Posted in Sysadmin