Description:
myisamchk --help says:
-q, --quick Faster repair by not modifying the data file.
One can give a second '-q' to force myisamchk to
modify the original datafile in case of duplicate keys.
NOTE: Tables where the data file is currupted can't be
fixed with this option.
The how-to-repeat shows a case where the MYD is good but can't be repaired with -q.
This is an important bug as mysqlhotcopy --no-indices copies only the first 2 kilobytes of the MYI, and the MyISAM online backup is planned to do this too (because with the frm, MYD and the first 2kb of MYI you can rebuild your table entirely in the same state as the original).
How to repeat:
MASTER> create table t(a int, b int, c int not null auto_increment primary key);
Query OK, 0 rows affected (0.00 sec)
MASTER> load data infile '/m/grosse_table5' into table t;
Query OK, 40000 rows affected (1.07 sec)
Records: 40000 Deleted: 0 Skipped: 0 Warnings: 120000
MASTER> delete from t limit 1000;
Query OK, 1000 rows affected (0.01 sec)
MASTER> flush tables;
Query OK, 0 rows affected (0.00 sec)
[guilhem@gbichot2 test]$ ll t.*
-rw-rw---- 1 guilhem qq 8584 Dec 10 22:12 t.frm
-rw-rw---- 1 guilhem qq 520000 Dec 10 22:13 t.MYD
-rw-rw---- 1 guilhem qq 329728 Dec 10 22:13 t.MYI
[guilhem@gbichot2 test]$ dd bs=2048 count=1 if=t.MYI > t.MYItrunc
1+0 records in
1+0 records out
[guilhem@gbichot2 test]$ mv t.MYI t.MYIgood
[guilhem@gbichot2 test]$ mv t.MYItrunc t.MYI
[guilhem@gbichot2 test]$ ll t.*
-rw-rw---- 1 guilhem qq 8584 Dec 10 22:12 t.frm
-rw-rw---- 1 guilhem qq 520000 Dec 10 22:13 t.MYD
-rw-r--r-- 1 guilhem qq 2048 Dec 10 22:13 t.MYI
-rw-rw---- 1 guilhem qq 329728 Dec 10 22:13 t.MYIgood
[guilhem@gbichot2 test]$ /m/mysql-4.0/myisam/myisamchk -rq t.MYI
- check key delete-chain
myisamchk: error: record delete-link-chain corrupted
myisamchk: error: Quick-recover aborted; Run recovery without switch 'q'
Updating MyISAM file: t.MYI
myisamchk: error: 126 when reading last record
MyISAM-table 't.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
So it fails (myisamchk -r succeeds). Whereas MYD was good, so -q should have worked.
I could not find a shorter testcase (tried with 128 rows in 't', but no error then).
Suggested fix:
Quoting Serg, there are 2 problems:
1. myisamchk should not check key delete chain on repair --quick
2. wrong error message
Description: myisamchk --help says: -q, --quick Faster repair by not modifying the data file. One can give a second '-q' to force myisamchk to modify the original datafile in case of duplicate keys. NOTE: Tables where the data file is currupted can't be fixed with this option. The how-to-repeat shows a case where the MYD is good but can't be repaired with -q. This is an important bug as mysqlhotcopy --no-indices copies only the first 2 kilobytes of the MYI, and the MyISAM online backup is planned to do this too (because with the frm, MYD and the first 2kb of MYI you can rebuild your table entirely in the same state as the original). How to repeat: MASTER> create table t(a int, b int, c int not null auto_increment primary key); Query OK, 0 rows affected (0.00 sec) MASTER> load data infile '/m/grosse_table5' into table t; Query OK, 40000 rows affected (1.07 sec) Records: 40000 Deleted: 0 Skipped: 0 Warnings: 120000 MASTER> delete from t limit 1000; Query OK, 1000 rows affected (0.01 sec) MASTER> flush tables; Query OK, 0 rows affected (0.00 sec) [guilhem@gbichot2 test]$ ll t.* -rw-rw---- 1 guilhem qq 8584 Dec 10 22:12 t.frm -rw-rw---- 1 guilhem qq 520000 Dec 10 22:13 t.MYD -rw-rw---- 1 guilhem qq 329728 Dec 10 22:13 t.MYI [guilhem@gbichot2 test]$ dd bs=2048 count=1 if=t.MYI > t.MYItrunc 1+0 records in 1+0 records out [guilhem@gbichot2 test]$ mv t.MYI t.MYIgood [guilhem@gbichot2 test]$ mv t.MYItrunc t.MYI [guilhem@gbichot2 test]$ ll t.* -rw-rw---- 1 guilhem qq 8584 Dec 10 22:12 t.frm -rw-rw---- 1 guilhem qq 520000 Dec 10 22:13 t.MYD -rw-r--r-- 1 guilhem qq 2048 Dec 10 22:13 t.MYI -rw-rw---- 1 guilhem qq 329728 Dec 10 22:13 t.MYIgood [guilhem@gbichot2 test]$ /m/mysql-4.0/myisam/myisamchk -rq t.MYI - check key delete-chain myisamchk: error: record delete-link-chain corrupted myisamchk: error: Quick-recover aborted; Run recovery without switch 'q' Updating MyISAM file: t.MYI myisamchk: error: 126 when reading last record MyISAM-table 't.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 So it fails (myisamchk -r succeeds). Whereas MYD was good, so -q should have worked. I could not find a shorter testcase (tried with 128 rows in 't', but no error then). Suggested fix: Quoting Serg, there are 2 problems: 1. myisamchk should not check key delete chain on repair --quick 2. wrong error message