Bug #476 "myisamchk -r" results in loss of index "repair table" restores index

Description:
I ran myisamchk on all the tables in the database after executing "FLUSH TABLES" to close any open tables, and shutting it down.   

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
[Wed May 21 23:51:53]
*root@yoda:~ $ /sbin/service mysql stop
Killing mysqld with pid 20640
[Wed May 21 23:51:58]
*root@yoda:~ $ 030521 23:51:58  mysqld ended

[Wed May 21 23:51:59]
*root@yoda:~ $ myisamchk --fast /var/lib/mysql/some_company/*.MYI
Checking MyISAM file: /var/lib/mysql/some_company/table1.MYI
Data records: 1062734   Deleted blocks:       0
myisamchk: warning: 5 clients is using or hasn't closed the table properly
- 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 record links
MyISAM-table '/var/lib/mysql/some_company/table1.MYI' is usable but should be fixed

---------

MyISAM file: /var/lib/mysql/some_company/administrator.MYI is already checked

---------

MyISAM file: /var/lib/mysql/some_company/batch_status.MYI is already checked

-

A number of them produced a "MyISAM-table '/x/x.MYI' is usable but should be fixed" warnings. 

myisamchk /var/lib/mysql/some_company/table2.MYI
Data records:   70875   Deleted blocks:       0
myisamchk: warning: 12 clients is using or hasn't closed the table properly
- 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
MyISAM-table '/var/lib/mysql/some_company/table2.MYI' is usable but should be fixed

For the tables where those warnings were issued, I ran myisamchk -r.  Everything appeared fine. Until I started using the tables and discovered that queries were painfully slow.  Then, after seeing some really unbelievable explain results, I discovered that fo revery table which I ran myisamchk -r on every index (even the primary index) lost their cardinality values.  They were NULL.

mysql> show index from table3;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| table3 |          0 | PRIMARY  |            1 | id          | A         |        NULL |     NULL | NULL   |         |
| table3 |          0 | h        |            1 | handle      | A         |        NULL |     NULL | NULL   |         |
| table3 |          1 | priority |            1 | priority    | A         |        NULL |     NULL | NULL   |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
3 rows in set (0.00 sec)

Running REPAIR table restored the cardinality. (I thought REPAIR table and myisamchk -r were supposed to be the same.)

mysql> repair table table3;
+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| some_company.table3 | repair | status   | OK       |
+-------------------+--------+----------+----------+
1 row in set (0.00 sec)

mysql> show index from table3;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| table3 |          0 | PRIMARY  |            1 | id          | A         |          74 |     NULL | NULL   |         |
| table3 |          0 | h        |            1 | handle      | A         |          74 |     NULL | NULL   |         |
| table3 |          1 | priority |            1 | priority    | A         |           6 |     NULL | NULL   |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
3 rows in set (0.00 sec)

How to repeat:
- flush tables

- shut down mysql 

- run myisamchk --fast on a few tables until you get one that warns "[table] ... is usable but should be fixed"

- run myisamchk -r on those tables.

- do a "SHOW INDEX" on those tables before and after.  The cardinality values in the resulting index should be NULL.

- do a "REPAIR TABLE" cardinality should be restored,

myisamchk doesn't automaticly update statistics, as in some cases you
may not want that. This could be when your are debugging or when the
statistics doesn't correspond to how the keys in the table are used.

REPAIR TABLE in MySQL is "kind of smart" and updates statistics when
it can gets them 'for free' (= doing repair by sorting).  It's however
not guaranteed that the statics is updated when doing a REPAIR.  To be
sure you should do ANALYZE TABLE after the repair.  (On MyISAM tables
ANALYZE is 'instant' if the statistics is already up to date).

When using myisamchk and you want to have the statistics up to date you should use:

myisamchk -r -a table_name

Regards,
Monty