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
