MySQL 5.1 and InnoDB Hot Backup Gotcha

Posted in: Technical Track

Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup, the following error occurred when we ran mysql_upgrade:

mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
ERROR 13 (HY000) at line 311: Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
ERROR 13 (HY000) at line 316: Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
FATAL ERROR: Upgrade failed

The problem is that in MySQL 5.1, it is possible to log the slow query log and general log to tables in the mysql schema (source: Selecting General Query and Slow Query Log Output Destinations). These tables are created by default as CSV tables for performance reasons, and are created even if MySQL is set not to log to tables.

CSV tables, however, are not copied as part of the InnoDB Hot Backup process (by the wrapper script, thus creating this error. The fix to get the slave working was to copy the .CSV and .CSM files (each CSV table has three files: a .FRM file, a .CSV file, and a .CSM file), and re-run mysql_upgrade.

To avoid having to fix the CSV table issue every time, we looked at patching to backup CSV tables properly. We found it involved a simple change. To fix it for InnoDB Hot Backup, simply replace line 1449 from the original:

    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,opt}';


    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,CSM,CSV,opt}';

Xtrabackup, with its wrapper, also has the same issue with not backing up CSV tables. Similarly, the fix is to change line 1811 of from:

    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}';


    my $wildcard = '*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}';

We submitted this issue to Percona (innobackupex fails to backup CSV tables causing mysql_upgrade to fail) and to Innobase ( fails to backup CSV tables causing mysql_upgrade to fail).

Interested in working with Singer? Schedule a tech call.

3 Comments. Leave new

Problema MySQL 5.1 e Innodb HotBackup | TooManySecrets HeadQuarters
September 2, 2009 4:37 am

[…] toomany on Sep.02, 2009, under MySQL Leo la siguiente entrada en Pythian, donde indican esta “feature”. El problemilla en cuestión ha sido descubierto cuando […]

Log Buffer #160: a Carnival of the Vanities for DBAs | Pythian Group Blog
September 4, 2009 12:59 pm

[…] Here on the Pythian Blog, Singer Wang unearthed a MySQL 5.1 and InnoDB hot backup gotcha. […]


Another shortcut for this problem is to create an empty CSV file by missing name under mysql schema directory – assuming you are logging to a file. Server will report a warning that log table is crashed, just ignore it.


Leave a Reply

Your email address will not be published. Required fields are marked *