MySQL Bugs: #40863: REPAIR TABLE ... USE

Bug #40863	REPAIR TABLE ... USE_FRM can corrupt data
Submitted:	19 Nov 2008 18:31	Modified:	21 Mar 2014 19:33
Reporter:	Vladimir Kolesnikov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
REPAIR TABLE with USE_FRM option tries to rename table's files without even checking if table's engine supports repair or the TT_USEFRM repair option. This can cause all kinds of problems. In the best case it just doesn't work as in many cases closing table doesn't really close the file, so e.g. rename on windows fails. But in general it can cause data loss as table engine is unaware of rename. 

How to repeat:
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (1), (2), (3), (4);
REPAIR TABLE t1 USE_FRM;

Suggested fix:
some ideas...

1. ask the engine if it supports such operation (with given flags) (and likely hear "no" in response)
2. don't rename "manually" - utilize engine's table rename
3. cancel the prepare step of repair (where the rename actually happens) and just let the engine handle the whole repair operation passing it all the options

Thank you for a problem report. I think this is relevant only for third-party pluggable storage engines:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.30-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tinno (c1 int) engine=InnoDB;
Query OK, 0 rows affected (0.83 sec)

mysql> repair table tinno;2
+------------+--------+----------+----------------------------------------------
-----------+
| Table      | Op     | Msg_type | Msg_text
           |
+------------+--------+----------+----------------------------------------------
-----------+
| test.tinno | repair | note     | The storage engine for the table doesn't supp
ort repair |
+------------+--------+----------+----------------------------------------------
-----------+
1 row in set (0.11 sec)

mysql> repair table tinno use_frm;
+------------+--------+----------+----------------------------------------------
-----------+
| Table      | Op     | Msg_type | Msg_text
           |
+------------+--------+----------+----------------------------------------------
-----------+
| test.tinno | repair | note     | The storage engine for the table doesn't supp
ort repair |
+------------+--------+----------+----------------------------------------------
-----------+
1 row in set (0.00 sec)

Problems and dangers of USE_FRM for MyISAM and other builtin engines that supports REPAIR are described in the manual.

Am I right?

Valeriy,

I tried this with PBXT engine, but the problem is in the way MySQL processes the command. It's just by a happy accident that InnoDB is not affected by this problem (it keeps some of it's files outside of MySQL data directory, and thus doesn't have to report their extensions via bas_ext() ). Consider the following lines from sql_table.cc

  /*
    Check if this is a table type that stores index and data separately,
    like ISAM or MyISAM. We assume fixed order of engine file name
    extentions array. First element of engine file name extentions array
    is meta/index file extention. Second element - data file extention. 
  */
  ext= table->file->bas_ext();
  if (!ext[0] || !ext[1])
    goto end;					// No data file

InnoDB is just lucky to return ext[1] == 0 and make code "goto end". Later MySQL calls engine's repair() method and gets "not supported" (what you actually see on client). PBXT reports > 1 in bas_ext(), so it get its files renamed. I can see no way PBXT can prevent MySQL from destroying user's data. Also it's not actually a "Storage Engines API" problem. The problem is in the way MySQL preforms particular SQL command - REPAIR in this case.

Thank you for the feedback.

This code does not exist in version 5.5. Please check if problem is still repeatable with current versions 5.5 or 5.6

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".