Bug #71395 Using ".MYI" in table name w/ myisamchk & using symlinks results in corruption
Submitted: 16 Jan 2014 1:27 Modified: 16 Jan 2014 10:23
Reporter: Chris Calender Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.6, All OS:Any (Those that support symbolic links)
Assigned to: CPU Architecture:Any
Tags: .MYI, corruption, delete-chain, MyISAMchk, Wrong bytesec

[16 Jan 2014 1:27] Chris Calender
Description:
In a nutshell, if you create a table with the .MYI and .MYD files in a different directory, using symbolic links - either manually or using CREATE TABLE .. INDEX DIRECTORY="" DATA DIRECTORY="", and then run myisamchk on the table and specify .MYI, you will corrupt the table.

Using 5.6.14:

mysql> create table t1 (id int) engine=myisam index directory="/home/chris/Desktop/Instances/mysql-5.6.14/data-real/" data directory="/home/chris/Desktop/Instances/mysql-5.6.14/data-real";

In shell, cd to the datadir and run myisamchk:

If you do not specify the .MYI, it runs fine:

chris@chris-linux:~/Desktop/Instances/mysql-5.6.14/data/test$ ../../bin/myisamchk -rqa t1
- check record delete-chain
- recovering (with keycache) MyISAM-table 't1'
Data records: 0

If you do specify the .MYI, it fails *and* corrupts the table:

chris@chris-linux:~/Desktop/Instances/mysql-5.6.14/data/test$ ../../bin/myisamchk -rqa t1.MYI
- check record delete-chain
- recovering (with keycache) MyISAM-table 't1.MYI'
Data records: 0
myisamchk: error: Couldn't fix table with quick recovery: Found wrong number of deleted records
myisamchk: error: Run recovery again without -q
MyISAM-table 't1.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

mysql> select * from t1;
ERROR 144 (HY000): Table './test/t1' is marked as crashed and last (automatic?) repair failed

This works as expected if sumbolic links are not involved:

mysql> create table t2 (id int) engine=myisam;

chris@chris-linux:~/Desktop/Instances/mysql-5.6.14/data/test$ ../../bin/myisamchk -rqa t2
- check record delete-chain
- recovering (with keycache) MyISAM-table 't2'
Data records: 0

chris@chris-linux:~/Desktop/Instances/mysql-5.6.14/data/test$ ../../bin/myisamchk -rqa t2.MYI
- check record delete-chain
- recovering (with keycache) MyISAM-table 't2.MYI'
Data records: 0

For the record, I see the same behavior in 5.1.71.

The manual does not indicate there should be any problem with this (though there does not appear to be 5.5 nor 5.6 versions of this manual page):

http://dev.mysql.com/doc/refman/5.1/en/symbolic-links-to-tables.html

Also, myisamchk --help output shows one should be able to specify .MYI.

How to repeat:
Ensure a valid path exists for the "index directory" and "data directory" for the below command.

create table test.t1 (id int) engine=myisam index directory="/home/chris/Desktop/Instances/mysql-5.6.14/data-real/" data directory="/home/chris/Desktop/Instances/mysql-5.6.14/data-real";

cd $datadir

../../bin/myisamchk -rqa t1

../../bin/myisamchk -rqa t1.MYI

(Last command is the one that fails and causes corruption - even to an empty table.)
[16 Jan 2014 10:21] Umesh Shastry
Hello Chris,

Thank you for the bug report and test case.
Verified as described on 5.6.15

Thanks,
Umesh
[12 Jun 2018 14:32] Jesús Uzcanga
Still happening in MySQL 8.0.11