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
Triage: Needs Triage: D3 (Medium)

[22 Jun 2011 18:04] Danny Ross
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
[29 Nov 2011 18:32] BigProf Software
This happened with me today ... After a lot of looking around, I was able to fix the corrupted table using:

myisamchk -ofv tablename
[17 Jan 2012 18:39] Sveta Smirnova
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.
[22 Feb 2012 1:09] acido fil
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?!!
[23 Apr 2012 3:48] Juan Manuel Doren
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
[4 Aug 2012 10:47] Umut Akyol
This is literally annoying. My solution on Debian is remove the package 5.1 and install 5.0.
[12 Jan 2013 17:32] Paul Dubois
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.