Description:
REPAIR TABLE tablename USE_FRM, when operating on a compressed table, issues an error saying that the table is compressed, but after that operation the table is unusable.
BACKUP / RESTORE apparently don't check if a table is compressed, and the restored table is unusable;
Also, after executing 'myisampack [table].MYI', if one doesn't execute myisamchk -rq table, the table will be corrupted.
How to repeat:
NOTE: 'jorge-/home/jorge> ./t | mysql pack' inserts 1001 records in pack.t1
BACKUP / RESTORE:
mysql> CREATE TABLE `t1` (
-> `a` bigint(20) NOT NULL auto_increment,
-> `b` bigint(20) default NULL,
-> `c` varchar(250) default NULL,
-> `d` int(11) default NULL,
-> PRIMARY KEY (`a`)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
jorge-/home/jorge> ./t | mysql pack
jorge-/home/jorge> myisampack /usr/local/mysql/data/pack/t1.MYI
Compressing /usr/local/mysql/data/pack/t1.MYD: (1001 records)
- Calculating statistics
- Compressing file
73.44%
Remember to run myisamchk -rq on compressed tables
jorge-/home/jorge> myisamchk -rq t1
- check key delete-chain
- check record delete-chain
- recovering (with sort) MyISAM-table 't1'
Data records: 1001
- Fixing index 1
jorge-/home/jorge> myisamchk -dvv t1
MyISAM file: t1
Record format: Compressed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2004-05-26 14:44:29
Recover time: 2004-05-26 14:45:25
Status: checked,optimized keys
Checksum: 2863444542
Data records: 1001 Deleted blocks: 0
Datafile parts: 1001 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 2
Datafile length: 9560 Keyfile length: 14336
Max datafile length: 4294967294 Max keyfile length: 67107839
Recordlength: 271
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 8 unique longlong 0 13312 1024
Field Start Length Nullpos Nullbit Type Huff tree Bits
1 1 1 constant 1 0
2 2 8 zerofill(6) 2 9
3 10 8 1 1 constant 3 0
4 18 250 1 2 no endspace 4 5
5 268 4 1 4 zerofill(2) 2 9
jorge-/home/jorge> mysql pack
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 to server version: 4.1.1-alpha-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> backup table t1 to '/home/jorge/tmp';
+---------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------+----------+----------+
| pack.t1 | backup | status | OK |
+---------+--------+----------+----------+
1 row in set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> restore table t1 from '/home/jorge/tmp';
+---------+---------+----------+------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+------------------------------------------+
| pack.t1 | repair | info | Wrong bytesec: 254-254- 8 at 0; Skipped |
| pack.t1 | repair | info | Wrong bytesec: 254-254- 8 at 0; Skipped |
| pack.t1 | restore | status | OK |
+---------+---------+----------+------------------------------------------+
3 rows in set (0.00 sec)
mysql> exit
Bye
jorge-/home/jorge> myisamchk -dvv t1
MyISAM file: t1
Record format: Packed
Character set: latin1_swedish_ci (8)
File-version: 1
Creation time: 2004-05-26 14:47:53
Recover time: 2004-05-26 14:47:53
Status: checked
Auto increment key: 1 Last value: 0
Data records: 0 Deleted blocks: 0
Datafile parts: 0 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
Datafile length: 0 Keyfile length: 1024
Max datafile length: 4294967294 Max keyfile length: 4398046510079
Recordlength: 272
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 8 unique longlong 1 1024
Field Start Length Nullpos Nullbit Type
1 1 1
2 2 8 no zeros
3 10 8 1 1 no zeros
4 18 250 1 2 no endspace
5 268 4 1 4 no zeros
NOT EXECUTING 'myisamchk -rq' RIGHT AFTER 'mysqlpack':
mysql> CREATE TABLE `t1` (
-> `a` bigint(20) NOT NULL auto_increment,
-> `b` bigint(20) default NULL,
-> `c` varchar(250) default NULL,
-> `d` int(11) default NULL,
-> PRIMARY KEY (`a`)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> exit
shell> ./t | mysql pack
shell> myisampack /usr/local/mysql/data/pack/t1.MYI
Compressing /usr/local/mysql/data/pack/t1.MYD: (1001 records)
- Calculating statistics
- Compressing file
73.44%
Remember to run myisamchk -rq on compressed tables
shell> mysql pack
mysql> explain select * from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1001 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
mysql> check table t1;
+---------+-------+----------+----------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+----------------------------------------------------+
| pack.t1 | check | error | Size of indexfile is: 1024 Should be: 14336 |
| pack.t1 | check | error | Corrupt |
+---------+-------+----------+----------------------------------------------------+
mysql> check table t1;
+---------+-------+----------+----------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+----------------------------------------------------+
| pack.t1 | check | warning | Table is marked as crashed |
| pack.t1 | check | error | Size of indexfile is: 1024 Should be: 14336 |
| pack.t1 | check | error | Size of datafile is: 9567 Should be: 35988 |
| pack.t1 | check | error | Corrupt |
+---------+-------+----------+----------------------------------------------------+
mysql> explain select * from t1;
ERROR 1016 (HY000): Can't open file: 't1.MYI' (errno: 145)
Suggested fix:
Make BACKUP / RESTORE aware if the table is compressed or not.
Description: REPAIR TABLE tablename USE_FRM, when operating on a compressed table, issues an error saying that the table is compressed, but after that operation the table is unusable. BACKUP / RESTORE apparently don't check if a table is compressed, and the restored table is unusable; Also, after executing 'myisampack [table].MYI', if one doesn't execute myisamchk -rq table, the table will be corrupted. How to repeat: NOTE: 'jorge-/home/jorge> ./t | mysql pack' inserts 1001 records in pack.t1 BACKUP / RESTORE: mysql> CREATE TABLE `t1` ( -> `a` bigint(20) NOT NULL auto_increment, -> `b` bigint(20) default NULL, -> `c` varchar(250) default NULL, -> `d` int(11) default NULL, -> PRIMARY KEY (`a`) -> ); Query OK, 0 rows affected (0.00 sec) mysql> exit Bye jorge-/home/jorge> ./t | mysql pack jorge-/home/jorge> myisampack /usr/local/mysql/data/pack/t1.MYI Compressing /usr/local/mysql/data/pack/t1.MYD: (1001 records) - Calculating statistics - Compressing file 73.44% Remember to run myisamchk -rq on compressed tables jorge-/home/jorge> myisamchk -rq t1 - check key delete-chain - check record delete-chain - recovering (with sort) MyISAM-table 't1' Data records: 1001 - Fixing index 1 jorge-/home/jorge> myisamchk -dvv t1 MyISAM file: t1 Record format: Compressed Character set: latin1_swedish_ci (8) File-version: 1 Creation time: 2004-05-26 14:44:29 Recover time: 2004-05-26 14:45:25 Status: checked,optimized keys Checksum: 2863444542 Data records: 1001 Deleted blocks: 0 Datafile parts: 1001 Deleted data: 0 Datafile pointer (bytes): 4 Keyfile pointer (bytes): 2 Datafile length: 9560 Keyfile length: 14336 Max datafile length: 4294967294 Max keyfile length: 67107839 Recordlength: 271 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 8 unique longlong 0 13312 1024 Field Start Length Nullpos Nullbit Type Huff tree Bits 1 1 1 constant 1 0 2 2 8 zerofill(6) 2 9 3 10 8 1 1 constant 3 0 4 18 250 1 2 no endspace 4 5 5 268 4 1 4 zerofill(2) 2 9 jorge-/home/jorge> mysql pack 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 to server version: 4.1.1-alpha-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> backup table t1 to '/home/jorge/tmp'; +---------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+--------+----------+----------+ | pack.t1 | backup | status | OK | +---------+--------+----------+----------+ 1 row in set (0.00 sec) mysql> drop table t1; Query OK, 0 rows affected (0.00 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> restore table t1 from '/home/jorge/tmp'; +---------+---------+----------+------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+------------------------------------------+ | pack.t1 | repair | info | Wrong bytesec: 254-254- 8 at 0; Skipped | | pack.t1 | repair | info | Wrong bytesec: 254-254- 8 at 0; Skipped | | pack.t1 | restore | status | OK | +---------+---------+----------+------------------------------------------+ 3 rows in set (0.00 sec) mysql> exit Bye jorge-/home/jorge> myisamchk -dvv t1 MyISAM file: t1 Record format: Packed Character set: latin1_swedish_ci (8) File-version: 1 Creation time: 2004-05-26 14:47:53 Recover time: 2004-05-26 14:47:53 Status: checked Auto increment key: 1 Last value: 0 Data records: 0 Deleted blocks: 0 Datafile parts: 0 Deleted data: 0 Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4 Datafile length: 0 Keyfile length: 1024 Max datafile length: 4294967294 Max keyfile length: 4398046510079 Recordlength: 272 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 8 unique longlong 1 1024 Field Start Length Nullpos Nullbit Type 1 1 1 2 2 8 no zeros 3 10 8 1 1 no zeros 4 18 250 1 2 no endspace 5 268 4 1 4 no zeros NOT EXECUTING 'myisamchk -rq' RIGHT AFTER 'mysqlpack': mysql> CREATE TABLE `t1` ( -> `a` bigint(20) NOT NULL auto_increment, -> `b` bigint(20) default NULL, -> `c` varchar(250) default NULL, -> `d` int(11) default NULL, -> PRIMARY KEY (`a`) -> ); Query OK, 0 rows affected (0.00 sec) mysql> exit shell> ./t | mysql pack shell> myisampack /usr/local/mysql/data/pack/t1.MYI Compressing /usr/local/mysql/data/pack/t1.MYD: (1001 records) - Calculating statistics - Compressing file 73.44% Remember to run myisamchk -rq on compressed tables shell> mysql pack mysql> explain select * from t1; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1001 | | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ mysql> check table t1; +---------+-------+----------+----------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+----------------------------------------------------+ | pack.t1 | check | error | Size of indexfile is: 1024 Should be: 14336 | | pack.t1 | check | error | Corrupt | +---------+-------+----------+----------------------------------------------------+ mysql> check table t1; +---------+-------+----------+----------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+----------------------------------------------------+ | pack.t1 | check | warning | Table is marked as crashed | | pack.t1 | check | error | Size of indexfile is: 1024 Should be: 14336 | | pack.t1 | check | error | Size of datafile is: 9567 Should be: 35988 | | pack.t1 | check | error | Corrupt | +---------+-------+----------+----------------------------------------------------+ mysql> explain select * from t1; ERROR 1016 (HY000): Can't open file: 't1.MYI' (errno: 145) Suggested fix: Make BACKUP / RESTORE aware if the table is compressed or not.