innobackupex fails to backup CSV tables causing mysql_upgrade to fail

[SingerWang]

In MySQL 5.1, there are two CSV tables in the mysql schema (general_log and slow_log) which are not backuped by innobackupex.pl at all. So when the backup is used to build a slave and 'mysql_upgrade' is run, you get the following error:

<pre>
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
</pre>

Solution:
Patch innobackupex.pl to include CSV tables, specifically line 1811 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}';

[Marcus Bointon (marcus-synchromedia)]

Why is this bug being downgraded and put off to later milestones? I'd say that for a backup program to fail to backup something it is supposed to is about as serious as it could possibly get? Especially since restoring from a backup results in a partially corrupt and apparently unfixable database. Also it seems that the posted fix is completely trivial - are there other factors preventing it from being used?

[Vadim Tkachenko (vadim-tk)]

Marcus,

CSV is not documented as supported storage engine, that's why I consider it as feature request, not as bug
that affects production.
1.5 milestone is now in release cycle and closed for non-critical fixes.

[Marcus Bointon (marcus-synchromedia)]

Well it does affect production since mysql seems to use it internally by default. Though I'm using normal external slow query logs in Percona Server 11.4 , Revision 111, these files are still present, and missing them out results in errors on restore, prevents mysql_upgrade from running and stops slow query logs from working. I currently have a production database that I can't get slow query logs on because it was originally built from a backup in this way and thus suffers from this bug.
I don't know if there is some workaround - I've certainly never asked for anything to use the CSV storage engine - I didn't even know there was one until I ran into this problem! I'm not interested in CSV storage, but I do want slow query logs!
I tried copying the CSV and CSM files from the original server they were from, but it didn't like them, didn't fix anything.

[Marcus Bointon (marcus-synchromedia)]

mysql> set global log_slow_queries = 'ON';
ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
mysql> set global slow_query_log = 'ON';
ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)

[Peter Zaitsev (pz-percona)]

Marcus,

What MySQL Version are you using ?

I think "table" for log was default in some very early MySQL 5.1 version
when it was reverted
to file because of huge performance overhead for logging in table.

Because people are typically concerned about logging performance overhead
and because most
of tools out there work with slow query log I have not seen using logging
to the table used that much.
At least you're the first one to run into this problem with XtraDB.

As in some configurations MySQL may use CSV tables by default we should fix
this issue still.

On Tue, Nov 30, 2010 at 12:57 AM, Marcus Bointon
<email address hidden>wrote:

> mysql> set global log_slow_queries = 'ON';
> ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
> mysql> set global slow_query_log = 'ON';
> ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
>
> --
> innobackupex fails to backup CSV tables causing mysql_upgrade to fail
> https://bugs.launchpad.net/bugs/420181
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona-XtraBackup.
>
> Status in Open source backup tool for InnoDB and XtraDB: Opinion
>
> Bug description:
> In MySQL 5.1, there are two CSV tables in the mysql schema (general_log and
> slow_log) which are not backuped by innobackupex.pl at all. So when the
> backup is used to build a slave and 'mysql_upgrade' is run, you get the
> following error:
>
>
> <pre>
> 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
> </pre>
>
> Solution:
> Patch innobackupex.pl to include CSV tables, specifically line 1811 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}';
>
>
>

--
Peter Zaitsev, CEO, Percona Inc.
Tel: [phone] ext 501 Skype: peter_zaitsev
24/7 Emergency Line [phone] ext 911

Percona Training Workshops
http://www.percona.com/training/

[Andrew Kent (andrew-sprybts)]

I've just been caught by the same problem.

Restoring a backup from a master server running: Percona-Server-server-51-5.1.50-rel11.4.111.rhel5 onto a slave server running the same.

The db restores correctly, but the /mysql/slow_log.CSV and /mysql/slow_log.CSM files did not come across as part of the restore.

When I went to enable slow logging on the new slave, I received the following error:

mysql> set global slow_query_log = 1;
ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
mysql>

However, my slow log configuration is (and always has been):

| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/sqls05-slow.log |

I have never configured the server to use TABLES for the slow log, nor have I used a mysql or percona version that defaulted to TABLE output.

For some reason mysql requires the slow_log.CSV table to be around even in order to enable slow logging to a file.

Copying the slow_log.CSV and slow_log.CSM tables into place on the new server allowed me to enable slow logging.

(The same issue may also impact the general_log table - it is also an automatically created CSV table. I don't use it so cannot confirm the impact).

Andrew

[SingerWang (wang)]

The original submitter here,

Basically even if MySQL doesn't log to the CSV file it still expects them to be there.

[Swany (greenlion)]

Does mysql_fix_privilege_tables fix the problem?

Sent from my iPhone

On Nov 30, 2010, at 3:08 PM, Andrew Kent <email address hidden> wrote:

> I've just been caught by the same problem.
>
> Restoring a backup from a master server running: Percona-Server-
> server-51-5.1.50-rel11.4.111.rhel5 onto a slave server running the same.
>
> The db restores correctly, but the /mysql/slow_log.CSV and
> /mysql/slow_log.CSM files did not come across as part of the restore.
>
> When I went to enable slow logging on the new slave, I received the
> following error:
>
> mysql> set global slow_query_log = 1;
> ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)
> mysql>
>
> However, my slow log configuration is (and always has been):
>
> | slow_query_log | OFF |
> | slow_query_log_file | /var/lib/mysql/sqls05-slow.log |
>
> I have never configured the server to use TABLES for the slow log, nor
> have I used a mysql or percona version that defaulted to TABLE output.
>
> For some reason mysql requires the slow_log.CSV table to be around even
> in order to enable slow logging to a file.
>
> Copying the slow_log.CSV and slow_log.CSM tables into place on the new
> server allowed me to enable slow logging.
>
> (The same issue may also impact the general_log table - it is also an
> automatically created CSV table. I don't use it so cannot confirm the
> impact).
>
> Andrew
>
> --
> innobackupex fails to backup CSV tables causing mysql_upgrade to fail
> https://bugs.launchpad.net/bugs/420181
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona-XtraBackup.

[Marcus Bointon (marcus-synchromedia)]

I tried copying the CSM and CSV files into place from the master, but it complained:

Error 'Incorrect key file for table 'general_log'; try to repair it' on query.

For both tables, the CSV file is empty and the CSM file is 35 bytes.

I just upgraded my passive master to percona server 5.1.52-rel11.6-log (Percona Server (GPL), 11.6 , Revision 140), which was actually my original master and has the files intact. My active master is currently running 5.1.50-rel11.4-log (Percona Server (GPL), 11.4 , Revision 111), was built from a backup of the passive master adn is missing the files. The upgrade on the slave has meant that it broke replication because it wanted to do an alter table on those two tables.

[Marcus Bointon (marcus-synchromedia)]

Skipping those two updates allowed replication to continue, but obviously that's not a happy situation.

[Marcus Bointon (marcus-synchromedia)]

Swany, I can see that mysql_fix_privilege_tables creates those tables but only if they are missing. If I do "show tables from mysql;", it shows that the slow_log and general_log tables exist (xtrabackup does copy the .frm files), so I suspect it won't work. It probably would if I dropped them first, but I'm not keen to run it on a live server to find out...

[Michael H. Oshita (ijinpublic+lp)]

I ran into the same problem with Percona Server 5.1.50-rel11.4-log backed up and restored with xtrabackup.

mysql> set global general_log = 'ON';
ERROR 13 (HY000): Can't get stat of './mysql/general_log.CSV' (Errcode: 2)

The default log destination is FILE, but it does seem like MySQL expects the CSV to be there, as SingerWang mentioned.

[Mark Schouten (mark-prevented)]

There needs to be another change. Only the $wildcard variable isn't enough, there is a grep somewhere that has the same list of extensions. See a full patch attached. This seems to work for me. The mysql logs say:

Dec 17 09:53:17 mysql02 /etc/mysql/debian-start[8468]: mysql.general_log
Dec 17 09:53:17 mysql02 /etc/mysql/debian-start[8468]: Error : You can't use locks with log tables.
Dec 17 09:53:17 mysql02 /etc/mysql/debian-start[8468]: status : OK
Dec 17 09:53:17 mysql02 /etc/mysql/debian-start[8468]: mysql.slow_log
Dec 17 09:53:17 mysql02 /etc/mysql/debian-start[8468]: Error : You can't use locks with log tables.
Dec 17 09:53:17 mysql02 /etc/mysql/debian-start[8468]: status : OK

Which seems to be a mysql-bug.

(Tested with:
mysql02:/srv/mysql/data/mysql# mysql -V
mysql Ver 14.14 Distrib 5.1.51, for debian-linux-gnu (x86_64) using readline 5.2)

[rombob (rombob)]

Can someone give advice how to fix existing problem with restored server?
After i tried to create CSV files manually i got error:
ERROR 1034 (HY000) at line 730: Incorrect key file for table 'slow_log'; try to repair it

[Alexey Kopytov (akopytov)]

rombob: I think you can fix it by removing general_log.frm and slow_log.frm, then running FLUSH TABLES (if you are removing FRMs without stopping the server) and then running mysql_upgrade --force.

[rombob (rombob)]

Alexey Kopytov: Thank you!

worked perfect:
- created empty slow_log.CSV and general_log.CSV
- removed general_log.frm and slow_log.frm
- FLUSH TABLES
- mysql_upgrade --force

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-267