Bug #26138 REPAIR TABLE with option USE_FRM erases all records in ARCHIVE table
Submitted: 7 Feb 2007 8:52 Modified: 11 Apr 2007 1:45
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S2 (Serious)
Version:5.0.32 OS:Linux (Linux)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: archive, data loss, repair table

[7 Feb 2007 8:52] Giuseppe Maxia
Description:
Using REPAIR_TABLE ... USE_FRM with an ARCHIVE table results in a total data loss.

mysql> create table t1 (i int) engine=archive;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> repair table t1;
+---------+--------+----------+----------+
| Table   | Op     | Msg_type | Msg_text |
+---------+--------+----------+----------+
| test.t1 | repair | status   | OK       |
+---------+--------+----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> repair table t1 use_frm;
+---------+--------+----------+----------+
| Table   | Op     | Msg_type | Msg_text |
+---------+--------+----------+----------+
| test.t1 | repair | status   | OK       |
+---------+--------+----------+----------+
1 row in set (0.06 sec)

mysql> select * from t1;
Empty set (0.00 sec)

I know that the documentation says to use this command only if the regular REPAIR TABLE fails, and that it applies to .MYI files (which archive does not have).
However, a command resulting in a complete loss of data should not be allowed. The parser should refuse this command  as unsupported.

How to repeat:
-- source include/have_archive.inc

--disable_warnings
drop table if exists t1;
--enable_warnings

create table t1 (i int) engine=archive;

insert into t1 values (1);
check table t1;
repair table t1;
select * from t1;
repair table t1 use_frm;
select * from t1;

Suggested fix:
The engine should refuse the USE_FRM option as unsupported, returning an error.
[28 Mar 2007 14:50] Sergey Vojtovich
Stealing this one per discussion with Ingo.
[28 Mar 2007 15:18] 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/23169

ChangeSet@1.2486, 2007-03-29 00:13:59+05:00, svoj@mysql.com +3 -0
  BUG#26138 - REPAIR TABLE with option USE_FRM erases all records in
              ARCHIVE table
  
  ARCHIVE table is truncated by REPAIR TABLE ... USE_FRM statement.
  This is fixed by reodering ARCHIVE engine extentions array, so now
  REPAIR TABLE ... USE_FRM works well with ARCHIVE tables.
[30 Mar 2007 8: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/23399

ChangeSet@1.2426, 2007-03-30 13:00:21+05:00, svoj@mysql.com +6 -0
  BUG#26138 - REPAIR TABLE with option USE_FRM erases all records in
              ARCHIVE table
  ARCHIVE table was truncated by REPAIR TABLE ... USE_FRM statement.
  The table handler returned its file name extensions in a wrong order.
  REPAIR TABLE believed it has to use the meta file to create a new table
  from it.
  
  With the fixed order, REPAIR TABLE does now use the data file to create
  a new table. So REPAIR TABLE ... USE_FRM works well with ARCHIVE engine
  now.
  
  This issue affects 5.0 only, since in 5.1 ARCHIVE engine stores meta
  information and data in the same file.
[30 Mar 2007 9:47] Sergey Vojtovich
Was approved by Serg via e-mail.
[30 Mar 2007 10:48] Ingo Strüwing
Approved by me too.
[8 Apr 2007 19:01] Bugs System
Pushed into 5.1.18-beta
[8 Apr 2007 19:02] Bugs System
Pushed into 5.0.40
[11 Apr 2007 1:45] Paul Dubois
Noted in 5.0.40 changelog.

REPAIR TABLE ... USE_FRM with an ARCHIVE table deleted all records
from the table.

No 5.1.x changelog entry; I assume the merge to 5.1 was a null
merge because the report states that 5.1 was not affected by
the problem.