MySQL Bugs: #26138: REPAIR TABLE with option USE_FRM erases all records in ARCHIVE table

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

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.

Stealing this one per discussion with Ingo.

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.

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.

Was approved by Serg via e-mail.

Approved by me too.

Pushed into 5.1.18-beta

Pushed into 5.0.40

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.