Description:
Hi,
I have a moment ago imported through mysqldump a table on my test linux box. I went to compress the table and I got and error that there's too many keys.
I use perl script to automatically compress tables and recreate index files:
Info: Executing 'mysqldump --quote -C --extended-insert --host=kulan --add-drop-table Escherichia_coli_CFT073 intragenome_data | mysql --host=127.0.0.1 Escherichia_coli_CFT073' ...succeeded.
Info: Executing 'mysqldump --quote -C --extended-insert --host=kulan --add-drop-table Escherichia_coli_CFT073 kluster | mysql --host=127.0.0.1 Escherichia_coli_CFT073' ...succeeded.
Info: Executing 'mysqldump --quote -C --extended-insert --host=kulan --add-drop-table Escherichia_coli_CFT073 known3d | mysql --host=127.0.0.1 Escherichia_coli_CFT073' ...succeeded.
You see I got no error messages when importing the dump.
Info: Escherichia_coli_CFT073.kluster PACKED ... will be COMPRESSED
Info: /usr/local/mysql/bin/myisampack --backup -f --tmpdir=/var/tmp /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI
Compressing /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYD: (2920 records)
- Calculating statistics
- Compressing file
59.58%
Remember to run myisamchk -rq on compressed tables
Info: /usr/local/mysql/bin/myisamchk -O sort_buffer_size=256M -O key_buffer_size=512M -O read_buffer_size=8M -O write_buffer_size=8M -rq -f -a -S --tmpdir=/var/tmp /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI --backup
- check key delete-chain
- check record delete-chain
- recovering (with sort) MyISAM-table
'/var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI'
Data records: 2920
- Fixing index 1
myisamchk: error: Key 1 - Found too many records; Can't continue
MyISAM-table '/var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option
Error: [/var/lib/mysql/Escherichia_coli_CFT073/kluster.*] REAIRING INDEX FAILED. RESTORING OLD INDEX FILE.
Error: Cannot find /var/lib/mysql/Escherichia_coli_CFT073/kluster.TMM, cannot restore /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI.
mysql@vrapenec Anabaena_sp_PCC7120 $
I've backep up these files and put them on the web for you.
mysql@vrapenec Anabaena_sp_PCC7120 $ ls -la /var/lib/mysql/Escherichia_coli_CFT073/kluster.*
-rw-rw---- 1 mysql mysql 30961 Aug 8 12:53 /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYD
-rw-rw---- 1 mysql mysql 1024 Aug 8 18:56 /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI
-rw-rw---- 1 mysql mysql 76580 Aug 8 12:53 /var/lib/mysql/Escherichia_coli_CFT073/kluster.OLD
-rw-rw---- 1 mysql mysql 8646 Aug 8 12:53 /var/lib/mysql/Escherichia_coli_CFT073/kluster.frm
mysql@vrapenec Anabaena_sp_PCC7120 $
How to repeat:
I report here the weir error message "Found too many records":
Please download the compressed version of table from http://pedant.gsf.de/public/Escherichia_coli_CFT073.kluster.tar.bz2
Then, repeat what I've done below to fix the table and think of the various error messages you get:
mysql@vrapenec mysql $ myisamchk /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI
Checking MyISAM file: /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI
Data records: 2815 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Can't read indexpage from filepos: -1
- check record links
myisamchk: error: Record-count is not ok; is 2920 Should be: 2815
myisamchk: warning: Found 2920 parts Should be: 2816 parts
MyISAM-table '/var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI' is corrupted
Fix it using switch "-r" or "-o"
mysql@vrapenec mysql $ myisamchk -r /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI'
Data records: 2815
- Fixing index 1
myisamchk: error: Key 1 - Found too many records; Can't continue
MyISAM-table '/var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option
mysql@vrapenec mysql $ myisamchk -o /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI
- recovering (with keycache) MyISAM-table '/var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI'
Data records: 2815
Data records: 2920
mysql@vrapenec mysql $ myisamchk /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI
Checking MyISAM file: /var/lib/mysql/Escherichia_coli_CFT073/kluster.MYI
Data records: 2920 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check record links
mysql@vrapenec mysql $
