Description:
Show table status randomly reports incorrect Row_format for compressed myisam tables. I have several compressed tables that were all compressed using the same operation:
./myisampack ../data/dbname/tableName
./myisamchk -rq --sort-index --analyze ../data/dbname/tableName
After compressing the tables I issued a flush tables command from the mysql client. Some tables reported a Row_format of "Dynamic" prior to issuing a flush tables command and then reported the correct Row_format of "Compressed" after flushing - some did not need to be flushed to show "Compressed" as the Row_format.
In short, I am positive all the tables are actually compressed as attempting to run myisampack results in an error stating that the table is, in fact, already compressed. I am also positive that mysql should be using the new tables as I ran flush tables as well as flush table [tableName] on each table multiple times attempting to get the correct output from show table status.
How to repeat:
I have several tables like this one with names ending in YYYY_MM representing year and month for which the table contains data.
mysql> CREATE TABLE keyword_manage_log_2004_04 (
`id` int(10) unsigned NOT NULL auto_increment,
`keyword_id` int(10) unsigned NOT NULL default '0',
`managed_date` date NOT NULL default '0000-00-00',
`type` enum('reviewed','managed') NOT NULL default 'managed',
`managed_timestamp` datetime default NULL,
`forced` tinyint(1) unsigned NOT NULL default '0',
`prev_bid` double(4,2) unsigned default NULL,
`prev_rank` double(3,1) unsigned default NULL,
`new_bid` double(4,2) unsigned default NULL,
`new_rank` double(3,1) unsigned default NULL,
`pause` tinyint(3) unsigned NOT NULL default '0',
`entered` tinyint(1) NOT NULL default '-1',
`error_level` tinyint(1) unsigned NOT NULL default '0',
`error_message` varchar(100) default NULL,
`rule` varchar(100) default NULL,
PRIMARY KEY (`id`),
INDEX (`keyword_id`),
INDEX (`managed_timestamp`),
INDEX (`managed_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
shell# ./myisampack ../data/dbname/tableName
shell# ./myisamchk -rq --sort-index --analyze ../data/dbname/tableName
This one appears to be correct:
mysql> SHOW TABLE STATUS FROM dbname LIKE 'keyword_manage_log_2004_04'\G
*************************** 1. row ***************************
Name: keyword_manage_log_2004_04
Engine: MyISAM
Version: 9
Row_format: Compressed
Rows: 193884
Avg_row_length: 24
Data_length: 4662014
Max_data_length: 4294967295
Index_length: 7128064
Data_free: 0
Auto_increment: 930582
Create_time: 2005-04-29 12:40:26
Update_time: 2005-04-29 12:40:48
Check_time: 2005-05-02 13:50:35
Collation: latin1_swedish_ci
Checksum: 2323408218
Create_options:
Comment:
1 row in set (0.02 sec)
this one is not correct:
mysql> SHOW TABLE STATUS FROM dbmame LIKE 'keyword_manage_log_2004_05'\G
*************************** 1. row ***************************
Name: keyword_manage_log_2004_05
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 121908
Avg_row_length: 75
Data_length: 9202180
Max_data_length: 4294967295
Index_length: 4493312
Data_free: 0
Auto_increment: 1081204
Create_time: 2005-04-29 12:42:42
Update_time: 2005-04-29 12:43:15
Check_time: 2005-04-29 12:43:18
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.02 sec)
make sure it is actually compressed:
shell# ./myisampack ../data/keywordmax/keyword_manage_log_2004_05
../data/keywordmax/keyword_manage_log_2004_05 is already compressed
error message from myisampack indicates the table was already compressed, however show table status does not:
mysql> FLUSH TABLE keyword_manage_log_2004_05;
Query OK, 0 rows affected (1.15 sec)
mysql> SHOW TABLE STATUS FROM dbname LIKE 'keyword_manage_log_2004_05'\G
*************************** 1. row ***************************
Name: keyword_manage_log_2004_05
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 121908
Avg_row_length: 75
Data_length: 9202180
Max_data_length: 4294967295
Index_length: 4493312
Data_free: 0
Auto_increment: 1081204
Create_time: 2005-04-29 12:42:42
Update_time: 2005-04-29 12:43:15
Check_time: 2005-04-29 12:43:18
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.03 sec)
Suggested fix:
Displaying the correct Row_format would be great! :-) Thank you.
Description: Show table status randomly reports incorrect Row_format for compressed myisam tables. I have several compressed tables that were all compressed using the same operation: ./myisampack ../data/dbname/tableName ./myisamchk -rq --sort-index --analyze ../data/dbname/tableName After compressing the tables I issued a flush tables command from the mysql client. Some tables reported a Row_format of "Dynamic" prior to issuing a flush tables command and then reported the correct Row_format of "Compressed" after flushing - some did not need to be flushed to show "Compressed" as the Row_format. In short, I am positive all the tables are actually compressed as attempting to run myisampack results in an error stating that the table is, in fact, already compressed. I am also positive that mysql should be using the new tables as I ran flush tables as well as flush table [tableName] on each table multiple times attempting to get the correct output from show table status. How to repeat: I have several tables like this one with names ending in YYYY_MM representing year and month for which the table contains data. mysql> CREATE TABLE keyword_manage_log_2004_04 ( `id` int(10) unsigned NOT NULL auto_increment, `keyword_id` int(10) unsigned NOT NULL default '0', `managed_date` date NOT NULL default '0000-00-00', `type` enum('reviewed','managed') NOT NULL default 'managed', `managed_timestamp` datetime default NULL, `forced` tinyint(1) unsigned NOT NULL default '0', `prev_bid` double(4,2) unsigned default NULL, `prev_rank` double(3,1) unsigned default NULL, `new_bid` double(4,2) unsigned default NULL, `new_rank` double(3,1) unsigned default NULL, `pause` tinyint(3) unsigned NOT NULL default '0', `entered` tinyint(1) NOT NULL default '-1', `error_level` tinyint(1) unsigned NOT NULL default '0', `error_message` varchar(100) default NULL, `rule` varchar(100) default NULL, PRIMARY KEY (`id`), INDEX (`keyword_id`), INDEX (`managed_timestamp`), INDEX (`managed_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; shell# ./myisampack ../data/dbname/tableName shell# ./myisamchk -rq --sort-index --analyze ../data/dbname/tableName This one appears to be correct: mysql> SHOW TABLE STATUS FROM dbname LIKE 'keyword_manage_log_2004_04'\G *************************** 1. row *************************** Name: keyword_manage_log_2004_04 Engine: MyISAM Version: 9 Row_format: Compressed Rows: 193884 Avg_row_length: 24 Data_length: 4662014 Max_data_length: 4294967295 Index_length: 7128064 Data_free: 0 Auto_increment: 930582 Create_time: 2005-04-29 12:40:26 Update_time: 2005-04-29 12:40:48 Check_time: 2005-05-02 13:50:35 Collation: latin1_swedish_ci Checksum: 2323408218 Create_options: Comment: 1 row in set (0.02 sec) this one is not correct: mysql> SHOW TABLE STATUS FROM dbmame LIKE 'keyword_manage_log_2004_05'\G *************************** 1. row *************************** Name: keyword_manage_log_2004_05 Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 121908 Avg_row_length: 75 Data_length: 9202180 Max_data_length: 4294967295 Index_length: 4493312 Data_free: 0 Auto_increment: 1081204 Create_time: 2005-04-29 12:42:42 Update_time: 2005-04-29 12:43:15 Check_time: 2005-04-29 12:43:18 Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.02 sec) make sure it is actually compressed: shell# ./myisampack ../data/keywordmax/keyword_manage_log_2004_05 ../data/keywordmax/keyword_manage_log_2004_05 is already compressed error message from myisampack indicates the table was already compressed, however show table status does not: mysql> FLUSH TABLE keyword_manage_log_2004_05; Query OK, 0 rows affected (1.15 sec) mysql> SHOW TABLE STATUS FROM dbname LIKE 'keyword_manage_log_2004_05'\G *************************** 1. row *************************** Name: keyword_manage_log_2004_05 Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 121908 Avg_row_length: 75 Data_length: 9202180 Max_data_length: 4294967295 Index_length: 4493312 Data_free: 0 Auto_increment: 1081204 Create_time: 2005-04-29 12:42:42 Update_time: 2005-04-29 12:43:15 Check_time: 2005-04-29 12:43:18 Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.03 sec) Suggested fix: Displaying the correct Row_format would be great! :-) Thank you.