MySQL Bugs: #61598: REPAIR and OPTIMIZE lead to table corruption

Bug #61598	REPAIR and OPTIMIZE lead to table corruption
Submitted:	22 Jun 2011 18:04	Modified:	12 Jan 2013 17:32
Reporter:	Danny Ross	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.1.57, 5.5.21, 5.6.5	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	corruption, Optimize, owner, regression, Repair

Description:
Since mysql version 5.1.50 the execution of REPAIR and OPTIMIZE table leads to MyISAM table corruption if the system uid of the files .frm .MYD .MYI is different from the one used by mysqld itself.

===== Behaviour until 5.1.49

FS before repair:

-rw-rw---- 1 user mysql      8610 Jun 22 18:35 test.frm
-rw-rw---- 1 user mysql  16854536 Jun 22 18:35 test.MYD
-rw-rw---- 1 user mysql  13456384 Jun 22 18:35 test.MYI

Repair output:

+--------------------+--------+----------+----------+
| Table              | Op     | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| test               | repair | status   | OK       |
+--------------------+--------+----------+----------+

FS after repair:

-rw-rw---- 1 user mysql      8610 Jun 22 18:35 test.frm
-rw-rw---- 1 mysql mysql  16854536 Jun 22 18:35 test.MYD
-rw-rw---- 1 mysql mysql  13456384 Jun 22 18:35 test.MYI

===== Behaviour since 5.1.50 and until the current 5.1 GA release (5.1.57)

FS before repair:

-rw-rw---- 1 user mysql      8610 Jun 22 18:35 test.frm
-rw-rw---- 1 user mysql  16854536 Jun 22 18:35 test.MYD
-rw-rw---- 1 user mysql  13456384 Jun 22 18:35 test.MYI

Repair output:

+------+--------+----------+---------------------------------------------+
| test | repair | error    | 1 when fixing table                         |
| test | repair | Error    | Can't change ownership of the 
file '/var/lib/mysql/database/test.MYD' (Errcode: 1)    |
| test | repair | status   | Operation failed                         |
+------+--------+----------+--------------------------------------------

FS after repair:

-rw-rw---- 1 user mysql      8610 Jun 22 18:35 test.frm
-rw-rw---- 1 user mysql  16854536 Jun 22 18:35 test.MYD
-rw-rw---- 1 user mysql  13456384 Jun 22 18:35 test.MYI
-rw-rw---- 1 mysql mysql  16854536 Jun 22 18:35 test.TMD

=====

This is troublesome for shared environment where setting the table uid as user uid is useful to handle efficently user quotas.

How to repeat:
See above

This happened with me today ... After a lot of looking around, I was able to fix the corrupted table using:

myisamchk -ofv tablename

Thank you for the report.

Verified as described, although table is not corrupted: just run query CHECK TABLE 2 times and crash mark would be removed:

mysql> check table t1;
+---------+-------+----------+---------------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                          |
+---------+-------+----------+---------------------------------------------------+
| test.t1 | check | warning  | Table is marked as crashed and last repair failed |
| test.t1 | check | status   | OK                                                |
+---------+-------+----------+---------------------------------------------------+
2 rows in set (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)

I would not say changing permissions of files is safe operation, so current behavior looks more proper than old one, but this change should be announced and documented.

5.5.20 this BUG(lets call it right name ;)) occurs too.

As stated in the title.. after running "optimize" or "repair" Mysql changes file owner.

Table needs to be repaired
 1. running repair 
 1. fail (throws "Can't change ownership of the file" error)
 2. manually changing file owner to right one(mysql)
 2. success 
 3. trying repair again 
 3. fail, mysql __automatically changes owner again__.. OMG WHY?!!

I had the same problem when the original table has rwxrwxrwx mode, i just changed it to rw-rw---- owner mysql ansd group mysql and now it works fine

This is literally annoying. My solution on Debian is remove the package 5.1 and install 5.0.

Noted in 5.1.50, 5.5.6 changelogs.

REPAIR TABLE and OPTIMIZE TABLE table now catch and throw any errors 
that occur while copying table statistics from the old corrupted file
to newly created file. For example. if the user ID of the owner of
the .frm, .MYD, or .MYI file is different from the user ID of the
mysqld process, REPAIR TABLE and OPTIMIZE TABLE generate a "cannot
change ownership of the file" error unless mysqld is started by the
root user.