Bug #46565 repair of partition fail for archive engine
Submitted: 5 Aug 2009 12:49 Modified: 18 Jun 2010 1:12
Reporter: Cristian Teodor Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S2 (Serious)
Version:5.1.35 OS:Linux
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: archive, partition, Repair
Triage: Triaged: D1 (Critical)

[5 Aug 2009 12:49] Cristian Teodor
Description:
I have a table with partitioned that uses the engine archive that has crashed and I'm unable to repair it.
 At select i get ERROR 1194 (HY000): Table 'ActivityLog20090801_old' is marked as crashed and should be repaired 
 When i try to repair:
 repair table ActivityLog20090801_old;
+---------------------------------+--------+----------+-----------------------------+
| Table                           | Op     | Msg_type | Msg_text                    |
+---------------------------------+--------+----------+-----------------------------+
| reports.ActivityLog20090801_old | repair | error    | Partition p3 returned error | 
| reports.ActivityLog20090801_old | repair | error    | Corrupt                     | 
+---------------------------------+--------+----------+-----------------------------+
alter table  ActivityLog20090801_old repair  partition p3;
+---------------------------------+--------+----------+-----------------------------+
| Table                           | Op     | Msg_type | Msg_text                    |
+---------------------------------+--------+----------+-----------------------------+
| reports.ActivityLog20090801_old | repair | error    | Partition p3 returned error | 
| reports.ActivityLog20090801_old | repair | error    | Corrupt                     | 
+---------------------------------+--------+----------+-----------------------------+

the files.
30081251  4.9M -rw-rw---- 1 mysql mysql  4.9M Aug  5 13:28 ActivityLog20090801_old#P#p3.ARN
30081948  1.7M -rw-rw---- 1 mysql mysql  1.7M Aug  3 15:32 ActivityLog20090801_old#P#p3.ARZ
30081232  1.7M -rw-rw---- 1 mysql mysql  1.7M Aug  5 11:27 ActivityLog20090801_old#P#p3#TMP#.ARZ

How to repeat:
create table partition and archive engine  brake one partition (try to write when no space is available) and try to repair after.
[5 Aug 2009 13:31] Cristian Teodor
there was no insert on that table so this is not a lock problem
[5 Aug 2009 13:51] Valeriy Kravchuk
Thank you for the problem report. Had you tried to repair that specific partition with

ALTER TABLE ActivityLog20090801_old REPAIR PARTITION p3;
[5 Aug 2009 13:53] Cristian Teodor
yes 

mysql> ALTER TABLE ActivityLog20090801_old REPAIR PARTITION p3;
+---------------------------------+--------+----------+-----------------------------+
| Table                           | Op     | Msg_type | Msg_text                    |
+---------------------------------+--------+----------+-----------------------------+
| reports.ActivityLog20090801_old | repair | error    | Partition p3 returned error | 
| reports.ActivityLog20090801_old | repair | error    | Corrupt                     | 
+---------------------------------+--------+----------+-----------------------------+
2 rows in set (1.00 sec)
[5 Aug 2009 14:06] Valeriy Kravchuk
Do you have enough free space on disk now?

Please, try to run ALTER TABLE ... CHECK PARTITION p3 and ALTER TABLE ...  REBUILD PARTITION p3 also and send the results.
[5 Aug 2009 14:14] Cristian Teodor
mysql> ALTER TABLE ActivityLog20090801_old CHECK PARTITION p3;
+---------------------------------+-------+----------+-----------------------------+
| Table                           | Op    | Msg_type | Msg_text                    |
+---------------------------------+-------+----------+-----------------------------+
| reports.ActivityLog20090801_old | check | error    | Partition p3 returned error | 
| reports.ActivityLog20090801_old | check | error    | Corrupt                     | 
+---------------------------------+-------+----------+-----------------------------+
2 rows in set (0.18 sec)

mysql> ALTER TABLE ActivityLog20090801_old REBUILD PARTITION p3;       
ERROR 1194 (HY000): Table 'ActivityLog20090801_old' is marked as crashed and should be repaired
mysql> 

I have 30G free and the files are under 10M
[8 Aug 2009 19:22] Sveta Smirnova
Thank you for the report.

Verified as described.

To repeat:

dd if=/dev/zero of=smallfs.dat bs=1024k count=3
mkfs.ext2 smallfs.dat
mount -o loop smallfs.dat mysqld-datadir

Start mysqld with new small datadir.

$ dd if=/dev/zero of=mysqld-datadir/smallfs.dat bs=256k count=3

CREATE TABLE t1 (
  pk INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (pk)
) engine=archive
PARTITION BY HASH (pk)
PARTITIONS 2;
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 select (NULL) from t1;
INSERT INTO t1 select (NULL) from t1;
....
Until you get error because disk is full.

Then run CHECK TABLE t1; REPAIR TABLE t1;

Remove file mysqld-datadir/smallfs.dat

Run CHECK TABLE t1; REPAIR TABLE t1; ALTER TABLE .... again

No way to repair the table.
[28 Dec 2009 15:05] Cyril SCETBON
Hi,

We met the same error but without a full filesystem log !

mysql> repair table logs_SPP_CPP;
+---------------------+--------+----------+----------------------------------+
| Table               | Op     | Msg_type | Msg_text                         |
+---------------------+--------+----------+----------------------------------+
| syslog.logs_SPP_CPP | repair | error    | Partition j_1_184 returned error |
| syslog.logs_SPP_CPP | repair | error    | Corrupt                          |
+---------------------+--------+----------+----------------------------------+

We didn't find a way to correct this error ...
[5 Jan 2010 10:16] Cyril SCETBON
for your information we are using 5.1.38
[19 Mar 2010 13:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103828

3412 Sergey Vojtovich	2010-03-19
      BUG#46565 - repair of partition fail for archive engine
      
      There was no way to repair corrupt ARCHIVE data file,
      when unrecoverable data loss is inevitable.
      
      With this fix REPAIR ... EXTENDED attempts to restore
      as much rows as possible, ignoring unrecoverable data.
      
      Normal REPAIR is still able to repair meta-data file
      only.
     @ mysql-test/r/archive.result
        A test case for BUG#46565.
     @ mysql-test/std_data/bug46565.ARZ
        A test case for BUG#46565.
     @ mysql-test/std_data/bug46565.frm
        A test case for BUG#46565.
     @ mysql-test/t/archive.test
        A test case for BUG#46565.
     @ storage/archive/ha_archive.cc
        Allow unrecoverable data loss when extended repair
        is requested.
[24 Mar 2010 11:26] Ingo Strüwing
Set back to "in progress" for now, as I have doubts about the architecture of the fix. Please see email.
[25 Mar 2010 19:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/104388

3412 Sergey Vojtovich	2010-03-25
      BUG#46565 - repair of partition fail for archive engine
      
      There was no way to repair corrupt ARCHIVE data file,
      when unrecoverable data loss is inevitable.
      
      With this fix REPAIR ... EXTENDED attempts to restore
      as much rows as possible, ignoring unrecoverable data.
      
      Normal REPAIR is still able to repair meta-data file
      only.
     @ mysql-test/r/archive.result
        A test case for BUG#46565.
     @ mysql-test/std_data/bug46565.ARZ
        A test case for BUG#46565.
     @ mysql-test/std_data/bug46565.frm
        A test case for BUG#46565.
     @ mysql-test/t/archive.test
        A test case for BUG#46565.
     @ storage/archive/ha_archive.cc
        Allow unrecoverable data loss when extended repair
        is requested.
[26 Mar 2010 9:20] Ingo Strüwing
Approved. The patch in itself is fine. I feel outvoted regarding the concept though. Let me state my concerns.

This fix changes the behavior so that REPAIR EXTENDED can drop a corrupted record and all the rest of the table. I understand that the manual warns about possible data loss with REPAIR TABLE. As such this change may be within documented behavior. OTOH losing half of a table due to one corrupted record doesn't sound that nice. On yet the other hand, this option opens the only chance to get part of the data back at all. However, I'd prefer if a special option would be used for repairing with data loss.

The data loss happens silently. The repair loop ends as if end of file had been reached. No indication is given that records have been dropped. I'd prefer to emit a warning when breaking the loop due to a broken record.
[26 Mar 2010 14:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/104459

4006 Sergey Vojtovich	2010-03-26
      An addition to fix for BUG#46565 - repair of partition
      fail for archive engine
      
      Adjusted a test case to 6.0 codebase.
     @ mysql-test/r/archive.result
        An addition to fix for BUG#46565 - repair of partition
        fail for archive engine
        
        Adjusted a test case to 6.0 codebase.
     @ mysql-test/std_data/bug46565.ARZ
        An addition to fix for BUG#46565 - repair of partition
        fail for archive engine
        
        Adjusted a test case to 6.0 codebase.
     @ mysql-test/std_data/bug46565.frm
        An addition to fix for BUG#46565 - repair of partition
        fail for archive engine
        
        Adjusted a test case to 6.0 codebase.
[6 Apr 2010 7:57] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:svoj@sun.com-20100326102010-01j36uhl2z0rxblh) (merge vers: 5.1.46) (pib:16)
[16 Apr 2010 17:13] Paul Dubois
Noted in 5.1.46 changelog.

REPAIR TABLE failed for partitioned ARCHIVE tables. 

Setting report to Need Merge pending push to Celosia.
[28 May 2010 5:53] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100422150750-vp0n37kp9ywq5ghf) (pib:16)
[28 May 2010 6:22] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)
[28 May 2010 6:50] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100403083753-yswcxwe63nyqeac4) (merge vers: 5.5.4-m3) (pib:16)
[28 May 2010 21:44] Paul Dubois
Noted in 5.5.5, 6.0.14 changelogs.
[17 Jun 2010 12:08] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:55] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:35] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)