MySQL Bugs: #49828: mysqlchk fails to update statistics when parallel recovering

Bug #49828	mysqlchk fails to update statistics when parallel recovering
Submitted:	19 Dec 2009 17:14	Modified:	12 Mar 2010 8:13
Reporter:	David Stevenson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.0.89, 5.1.41, 5.1.43-bzr	OS:	Any (Windows, Mac OS X)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	MyISAMchk

Description:
When using mysqlchk to rebuild indices after compression using myisampack, myisamchk fails to update statistics when the -p option is selected. Removing the -p disables the parallel rebuild and stats are updated successfully

How to repeat:
1. Create a large table with distinct values in an index.
2. pack using myisampack <table>
3. rebuild indices using myisamchk -rapq and stats are not updated
4. rebuild indices using myisamchk -raq and stats are updated

Suggested fix:
just omit the -p option and use the slower but safe option.

Thank you for the bug report. Verified just as described with recent 5.1.43 from bzr on Mac OS X:

77-52-7-73:5.1 openxs$ bin/mysql -uroot test
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 1
Server version: 5.1.43-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tmi(c1 int auto_increment primary key, c2 int, key(c2)) engine=MyISAM;
Query OK, 0 rows affected (0.44 sec)

mysql> insert into tmi(c2) values(rand()*100000);
Query OK, 1 row affected (0.36 sec)

mysql> insert into tmi(c2) select rand()*100000 from tmi;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tmi(c2) select rand()*100000 from tmi;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
...

mysql> insert into tmi(c2) select rand()*100000 from tmi;
Query OK, 131072 rows affected (7.99 sec)
Records: 131072  Duplicates: 0  Warnings: 0

mysql> analyze table tmi;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test.tmi | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.19 sec)

mysql> show indexes from tmi;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tmi   |          0 | PRIMARY  |            1 | c1          | A         |      262144 |     NULL | NULL   |      | BTREE      |         |
| tmi   |          1 | c2       |            1 | c2          | A         |       87381 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> exit
Bye
77-52-7-73:5.1 openxs$ bin/myisampack var/test/tmi.MYI
Compressing var/test/tmi.MYD: (262144 records)
- Calculating statistics
- Compressing file
26.57%     
Remember to run myisamchk -rq on compressed tables
77-52-7-73:5.1 openxs$ bin/myisamchk -rapq var/test/tmi.MYI
- check record delete-chain
- parallel recovering (with sort) MyISAM-table 'var/test/tmi.MYI'
Data records: 262144
- Fixing index 1
- Fixing index 2
77-52-7-73:5.1 openxs$ bin/mysql -uroot test
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 2
Server version: 5.1.43-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> show table status like 'tmi'\G
*************************** 1. row ***************************
           Name: tmi
         Engine: MyISAM
        Version: 10
     Row_format: Compressed
           Rows: 262144
 Avg_row_length: 6
    Data_length: 1732531
Max_data_length: 281474976710655
   Index_length: 3998720
      Data_free: 0
 Auto_increment: 262145
    Create_time: 2009-12-20 16:19:06
    Update_time: 2009-12-20 16:20:23
     Check_time: 2009-12-20 16:22:07
      Collation: latin1_swedish_ci
       Checksum: 1705075407
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> show indexes from tmi;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tmi   |          0 | PRIMARY  |            1 | c1          | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tmi   |          1 | c2       |            1 | c2          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> exit
Bye
77-52-7-73:5.1 openxs$ bin/myisamchk -raq var/test/tmi.MYI
- check record delete-chain
- recovering (with sort) MyISAM-table 'var/test/tmi.MYI'
Data records: 262144
- Fixing index 1
- Fixing index 2
77-52-7-73:5.1 openxs$ bin/mysql -uroot test
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 3
Server version: 5.1.43-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> show table status like 'tmi'\G
*************************** 1. row ***************************
           Name: tmi
         Engine: MyISAM
        Version: 10
     Row_format: Compressed
           Rows: 262144
 Avg_row_length: 6
    Data_length: 1732531
Max_data_length: 281474976710655
   Index_length: 3998720
      Data_free: 0
 Auto_increment: 262145
    Create_time: 2009-12-20 16:19:06
    Update_time: 2009-12-20 16:20:23
     Check_time: 2009-12-20 16:23:39
      Collation: latin1_swedish_ci
       Checksum: 1705075407
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> show indexes from tmi;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tmi   |          0 | PRIMARY  |            1 | c1          | A         |      262144 |     NULL | NULL   |      | BTREE      |         |
| tmi   |          1 | c2       |            1 | c2          | A         |       87381 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Maybe this is what our manual means by the following note:

"--parallel-recover, -p

Use the same technique as -r and -n, but create all the keys in parallel, using different threads. This is beta-quality code. Use at your own risk! "

Hi Valeriy,

You may well be right about the comment in the manual, but it's been in Beta for at least two years, to the best of my knowledge. It would be good to get it into RTM quality.

At least I've logged it now, so that other people don't spend too long scratching their head about what is going wrong with their index rebuild.

Best Regards,
David

David

A duplicate of BUG#47444.