Bug #12633 myisampack and then unpack destroys auto_increment key

Description:
mysql server 4.1.13-log
myisampack Ver 1.22 for unknown-linux-gnu on x86_64
myisamchk  Ver 2.7 for unknown-linux-gnu at x86_64

Note that I marked this bug as Serious because the results are Serious, although I don't know how often people will actually run the steps necessary to make this happen.

Repro steps (summary):
pack a table with an auto_increment key using myisampack
unpack the table with myisamchk --unpack

Exected results:
the table should now be writable as usual

Actual results:
The table's auto-increment primary key fails to work. That is, you get an error because the auto_increment field refuses to increment. The result is that you can only insert new data into the table once; after that you get an error on every insert unless you explictly set the auto_increment field (hence the "Serious" severity).

Example below:
mysql> show table status like 't_history';
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| t_history | MyISAM |       9 | Fixed      | 178352 |             24 |     4280448 |       402653183 |      6158336 |         0 |         178365 | 2005-08-17 21:21:49 | 2005-08-17 21:37:11 | 2005-08-17 21:25:46 | latin1_swedish_ci |     NULL |                |         |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.14 sec)

mysql> describe t_history;
+------------+-------------+------+-----+---------------------+----------------+
| Field      | Type        | Null | Key | Default             | Extra          |
+------------+-------------+------+-----+---------------------+----------------+
| history_id | int(11)     |      | PRI | NULL                | auto_increment |
| file_id    | int(11)     |      | MUL | 0                   |                |
| user_id    | int(11)     |      | MUL | 0                   |                |
| entryType  | smallint(5) |      |     | 0                   |                |
| playdate   | datetime    |      |     | 0000-00-00 00:00:00 |                |
| cached     | tinyint(1)  |      | MUL | 0                   |                |
+------------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.00 sec)

mysql> select max(history_id) from t_history;
+-----------------+
| max(history_id) |
+-----------------+
|          178364 |
+-----------------+
1 row in set (0.35 sec)
mysql> insert into t_history ( file_id, user_id, entryType, playdate ) values ( 900000, 1, 1, now() );
Query OK, 1 row affected (0.18 sec)

mysql> insert into t_history ( file_id, user_id, entryType, playdate ) values ( 900001, 1, 1, now() );
ERROR 1062 (23000): Duplicate entry '178365' for key 1
mysql> show table status like 't_history';                   
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| t_history | MyISAM |       9 | Fixed      | 178353 |             24 |     4280472 |       402653183 |      6158336 |         0 |         178365 | 2005-08-17 21:21:49 | 2005-08-17 21:39:09 | 2005-08-17 21:25:46 | latin1_swedish_ci |     NULL |                |         |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> show indexes from t_history;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t_history |          0 | PRIMARY  |            1 | history_id  | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| t_history |          1 | idx      |            1 | file_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| t_history |          1 | idx2     |            1 | user_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| t_history |          1 | idx3     |            1 | user_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| t_history |          1 | idx3     |            2 | entryType   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| t_history |          1 | idx4     |            1 | cached      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.36 sec)

How to repeat:
Repro steps (detail):
tuba:/data/mysql/musica_evo/t# myisamchk -A t_history
Updating MyISAM file: t_history
tuba:/data/mysql/musica_evo/t# myisampack t_history
Compressing t_history.MYD: (178352 records)
- Calculating statistics
- Compressing file
71.32%     
Remember to run myisamchk -rq on compressed tables
tuba:/data/mysql/musica_evo/t# myisamchk -rq t_history
- check record delete-chain
- recovering (with sort) MyISAM-table 't_history'
Data records: 178352
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
tuba:/data/mysql/musica_evo/t# myisamchk --unpack t_history
- recovering (with sort) MyISAM-table 't_history'
Data records: 178352
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
tuba:/data/mysql/musica_evo/t# myisamchk -A t_history
Table: t_history doesn't have an auto increment key

Repeated just as described. Additional info on how to recreate the table. I used the following steps:

create table t_history (
 history_id int(11) primary key auto_increment,
 file_id    int(11),
 user_id    int(11),
 entryType  smallint(5), 
 playdate   datetime default '0000-00-00 00:00:00',
 cached     tinyint(1),
 key idx (file_id),
 key idx2 (user_id),
 key idx3 (file_id, entryType),
 key idx4 (cached)
) engine=MyISAM;

mysql> insert into t_history(file_id, user_id, entryType, cached) values(1,1,1,1
);
Query OK, 1 row affected (0.07 sec)

mysql> insert into t_history(file_id, user_id, entryType, cached) select file_id
, user_id, entryType, cached from t_history;
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

... repeat the above statement several times to populate table with sample data ...

mysql> insert into t_history(file_id, user_id, entryType, cached) select file_id
, user_id, entryType, cached from t_history;
Query OK, 65536 rows affected (42.92 sec)
Records: 65536  Duplicates: 0  Warnings: 0

mysql> select max(history_id) from t_history;
+-----------------+
| max(history_id) |
+-----------------+
|          131072 |
+-----------------+
1 row in set (0.00 sec)

Than stop the server (because keys are cached) and proceed as described in a "How to repeat" section.

This has been fixed together with Bug#10405 in 4.0.27, 4.1.20, 5.0.22, and 5.1.10.

Recorded in the changelogs for 4.0, 4.1, 5.0 and 5.1.