MySQL 5.1 and InnoDB Hot Backup Gotcha

MySQL 5.1 and InnoDB Hot Backup Gotcha: "

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 innobackupex.pl), 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 innobackup.pl 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}';

to:

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

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

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

to:

    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 (innobackup.pl fails to backup CSV tables causing mysql_upgrade to fail).

PlanetMySQL Voting:
Vote UP /
Vote DOWN"