Bug #1304 cannot fix index on freshly compressed table
Submitted: 16 Sep 2003 10:24 Modified: 22 Jun 2004 12:45
Reporter: Martin Mokrejs Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0.15 OS:Linux (Linux 2.4.20)
Assigned to: Michael Widenius

[16 Sep 2003 10:24] Martin Mokrejs
Description:
Hi, I cannot fix index on freshly compressed table:

mysql@kulan$ myisamchk /data/mysql/Pasteurella_multocida_PM70/blast_data.MYI
Checking MyISAM file: /data/mysql/Pasteurella_multocida_PM70/blast_data.MYI
Data records:    2014   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 data record references index: 5
- check record links
mysql@kulan$ myisampack /data/mysql/Pasteurella_multocida_PM70/blast_data.MYI
Compressing /data/mysql/Pasteurella_multocida_PM70/blast_data.MYD: (2014 records)
- Calculating statistics
- Compressing file
32.46%     
Remember to run myisamchk -rq on compressed tables
mysql@kulan$ myisamchk -rq /data/mysql/Pasteurella_multocida_PM70/blast_data.MYI
- check key delete-chain
- check record delete-chain
- recovering (with sort) MyISAM-table '/data/mysql/Pasteurella_multocida_PM70/blast_data.MYI'
Data records: 2014
- Fixing index 1
Found wrong record at 1132
MyISAM-table '/data/mysql/Pasteurella_multocida_PM70/blast_data.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option
mysql@kulan$

How to repeat:
download http://pedant.gsf.de/Pmultocida.tgz
gzip -dc Pmultocida.tgz | tat xvf -
myisamchk Pasteurella_multocida_PM70/blast_data.MYI
diff -b Pasteurella_multocida_PM70/blast_data.MYD \ Pasteurella_multocida_PM70/blast_data.OLD
myisampack Pasteurella_multocida_PM70/blast_data.MYI
myisamchk -rq Pasteurella_multocida_PM70/blast_data.MYI
---------------------------------^ now you should get an error

repair back(shoudl work):

mysqladmin flush-tables
mysql> truncate table blast_data;
mysqladmin flush-tables

cp Pasteurella_multocida_PM70/blast_data.OLD \ Pasteurella_multocida_PM70/blast_data.MYD
myisamchk -rq Pasteurella_multocida_PM70/blast_data.MYI
[16 Sep 2003 10:30] Martin Mokrejs
This bug is not present in 4.0.13. I used in both testings official binaries. mysqld from 4.0.15 marked the table as crashed:

$ mysql -hkulan Pasteurella_multocida_PM70
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5356 to server version: 4.0.15-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> check table blast_data;
+---------------------------------------+-------+----------+----------------------------+
| Table                                 | Op    | Msg_type | Msg_text                   |
+---------------------------------------+-------+----------+----------------------------+
| Pasteurella_multocida_PM70.blast_data | check | error    | Found wrong record at 1132 |
| Pasteurella_multocida_PM70.blast_data | check | error    | Corrupt                    |
+---------------------------------------+-------+----------+----------------------------+
2 rows in set (0.12 sec)

mysql>
[24 Sep 2003 3:49] Michael Widenius
This is a bug that seams to exist in all MySQL versions (at least myisampack from 3.23 also gives this error).

I have been able to repeat this, but it will take some time before I will be able to fix this as I will be traveling the rest of the week.
[24 Sep 2003 3:52] Michael Widenius
Just noticed that this is a bug in myisamchk and not in myisampack (myisamchk from 3.23 worked ok).

I should be able to make a fix for this before Sunday.

Regards,
Monty
[24 Sep 2003 4:23] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Fix will be in 4.0.16
[1 Oct 2003 0:55] Martin Mokrejs
The table can be compressed, index "fixed". This is fixed. Unfortunately, there;s still something:

mysql> check table blast_data;
+---------------------------------------+-------+----------+------------------------------------------------------------+
| Table                                 | Op    | Msg_type | Msg_text                                                   |
+---------------------------------------+-------+----------+------------------------------------------------------------+
| Pasteurella_multocida_PM70.blast_data | check | error    | Table 'Pasteurella_multocida_PM70.blast_data' is read only |
+---------------------------------------+-------+----------+------------------------------------------------------------+
1 row in set (0.36 sec)

mysql> 

It seems mysqld reports compressed table still is buggy, maybe just because it is readonly.

vrapenec mysql-bk # bin/myisamchk -dvv /var/lib/mysql/Pasteurella_multocida_PM70/blast_data.MYI

MyISAM file:         /var/lib/mysql/Pasteurella_multocida_PM70/blast_data.MYI
Record format:       Compressed
Character set:       latin1 (8)
File-version:        1
Creation time:       2003-09-16 19:17:23
Recover time:        2003-10-01  9:50:43
Status:              checked,optimized keys
Checksum:               2881157305
Data records:                 2014  Deleted blocks:                 0
Datafile parts:               2014  Deleted data:                   0
Datafile pointer (bytes):        4  Keyfile pointer (bytes):        2
Datafile length:          44826129  Keyfile length:            103424
Max datafile length:    4294967294  Max keyfile length:      67107839
Recordlength:               101390
[cut]
[6 Oct 2003 0:26] Sergei Golubchik
on the last 4.0 from bk CHECK TABLE returns Ok.

So, I assume it's fixed.
[16 Jun 2004 13:09] Michael Arndt
Fro me the same bug exists, using myisamchk from 4.0.20
the table repair works ok, but mysql still marks the table as bad !

Is there any chance to tell 4.0.15 to handle the compressed table ?

TIA
Micha
[22 Jun 2004 12:45] Sergei Golubchik
if it's still can see the bug, try to create a repeatable test case for us
[12 Oct 2005 14:47] Michael Wallner
Could it be that a sort of this bug is still around?

System:

MySQL 4.0.24-10 (Debian)
Linux s1-iw 2.4.26-1-686 #1 Tue Aug 24 13:46:05 JST 2004 i686 GNU/Linux

I did the following:

I extracted the data of a month from a logging table to compress it:

> CREATE TABLE stats_2005_08 SELECT * FROM stats_full WHERE mark LIKE "200508%";
> DELETE FROM stats_full WHERE mark LIKE "200508%";
> FLUSH TABLES;

$ myisampack -wv stats_2005_08

... succeeded, but it was the first time that I noticed the little sentence when myisampack exits: "Don't forget to run myisamchk -rq", so I ran it.  But obviously this wasn't the best idea, because it kinda corrupted the index file.  MySQL failed to retrieve the table status from now on, anything like CHECK, REPAIR didn't help.

$ myisamchk --unpack -r stats_2005_08

...even truncated the datafile to 0 bytes.

So I went ahead, restored the stats_2005_08 table "directly" from the dump of the full_stats table with some greps and seds and ran myisampack without myisamchk, and everything is fine, as it was the previous months :)

I think I'm lucky in this case because the table doesn't have any indices, but what if I'd need them?

Thanks for listening!
[13 Oct 2005 8:20] Sergei Golubchik
if you still can see the bug, try to create a repeatable test case for us