Solution for DBD::mysql::db do failed: MySQL server has gone away at mysqlhotcopy line 528.

I have found that when running mysqlhotcopy to backup a database, it always throws an ominous looking error when finished.

DBD::mysql::db do failed: MySQL server has gone away at mysqlhotcopy line 528.

This type of error would lead you to believe the backup failed, when in actuality it worked just fine. I have restored from backups that resulted in this error just fine.

The solution to this error is to modify the system variables interactive_timeout and wait_timeout from the default settings. Inside your my.cnf file (usually located at /etc/my.cnf) put the following lines.

interactive_timeout=3600
wait_timeout=3600

You may have to adjust up for larger databases. These particular settings work for me with 15-20GB databases. Here is the result I get upon completion.

Copying 691 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 230 tables (691 files) in 416 seconds (417 seconds overall).
[root@mysql2 scripts]#

Posted in Sysadmin

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.

Posted in Sysadmin