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,
