MySQL Bugs: #74804: Using myisamchk --unpack results in 'Row

Bug #74804	Using myisamchk --unpack results in 'Row_format: Fixed'
Submitted:	12 Nov 2014 3:02	Modified:	12 Nov 2014 7:57
Reporter:	Meiji Kimura	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	5.6.21, 5.6.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Bug #45217 reports, 'Using myisamchk --unpack results in small myisam_data_pointer_size'

And if 'Row_format: dynamic' before compression, Row_format alsways become 'Row_format: Fixed' after compression. It should be fixed with Bug #45217.

How to repeat:
We can reproduce use same procedure as Bug #45217, and add one operation 'myisamchk -rq --sort-index --analyze packed'. it's needed to access compressed table.

How to repeat:
-- create table
CREATE TABLE packed (a varchar(1000)) ENGINE=myisam;

-- populate it
insert into packed values (repeat('a', 100)), (repeat('b', 100)), (repeat('c', 100)),(repeat('d', 100)),(repeat('e', 100)),(repeat('f', 100)),(repeat('g', 100)),(repeat('h', 100)),(repeat('i', 100)),(repeat('j', 100));

-- look at max_data_length prior to packing/unpacking
mysql> SHOW TABLE STATUS LIKE 'packed'\G
*************************** 1. row ***************************
           Name: packed
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 10
 Avg_row_length: 108
    Data_length: 1080
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-11-12 11:51:30
    Update_time: 2014-11-12 11:51:30
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

-- pack with myisampack
shell> ll packed*
-rw-rw---- 1 mysql mysql 1080 11月 12 11:51 packed.MYD
-rw-rw---- 1 mysql mysql 1024 11月 12 11:51 packed.MYI
-rw-rw---- 1 mysql mysql 8554 11月 12 11:51 packed.frm
shell> myisampack packed
Compressing packed.MYD: (10 records)
- Calculating statistics
- Compressing file
51.57%     
shell> myisamchk -rq --sort-index --analyze packed
- check record delete-chain
- recovering (with keycache) MyISAM-table 'packed'
Data records: 10
- Sorting index for MyISAM-table 'packed'
shell> ll packed*
-rw-rw---- 1 mysql mysql  530 11月 12 11:51 packed.MYD
-rw-rw---- 1 mysql mysql 1024 11月 12 11:54 packed.MYI
-rw-rw---- 1 mysql mysql 8554 11月 12 11:51 packed.frm

-- check compressed table's status
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'packed'\G
*************************** 1. row ***************************
           Name: packed
         Engine: MyISAM
        Version: 10
     Row_format: Compressed
           Rows: 10
 Avg_row_length: 52
    Data_length: 523
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-11-12 11:51:30
    Update_time: 2014-11-12 11:51:30
     Check_time: 2014-11-12 11:54:00
      Collation: latin1_swedish_ci
       Checksum: 4257035281
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

-- unpack with myisamchk
shell> myisamchk -u packed
- recovering (with keycache) MyISAM-table 'packed'
Data records: 10
shell> ll packed*
-rw-rw---- 1 mysql mysql 10030 11月 12 11:55 packed.MYD
-rw-rw---- 1 mysql mysql  1024 11月 12 11:55 packed.MYI
-rw-rw---- 1 mysql mysql  8554 11月 12 11:51 packed.frm

-- flush tables and view max_data_length again
mysql> SHOW TABLE STATUS LIKE 'packed'\G
*************************** 1. row ***************************
           Name: packed
         Engine: MyISAM
        Version: 10
     Row_format: Fixed <- This bug.
           Rows: 10
 Avg_row_length: 1003
    Data_length: 10030
Max_data_length: 65732607 <- Bug #45217
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-11-12 11:51:30
    Update_time: 2014-11-12 11:55:51
     Check_time: 2014-11-12 11:55:51
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

Suggested fix:
Recover row_format before compression or Add an option to specify the row_format.

Hello Meiji-San,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

// 5.6.23

[root@cluster-repo mysql-advanced-5.6.23]# bin/mysql -uroot -p test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TABLE packed (a varchar(1000)) ENGINE=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into packed values (repeat('a', 100)), (repeat('b', 100)), (repeat('c', 100)),(repeat('d', 100)),(repeat('e', 100)),(repeat('f', 100)),(repeat('g', 100)),(repeat('h', 100)),(repeat('i', 100)),(repeat('j', 100));
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS LIKE 'packed'\G
*************************** 1. row ***************************
           Name: packed
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 10
 Avg_row_length: 108
    Data_length: 1080
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-11-14 14:55:03
    Update_time: 2014-11-14 14:55:12
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql>
mysql> \! ls -l /tmp/bug/test
total 20
-rw-rw---- 1 root root 8554 Nov 14 14:55 packed.frm
-rw-rw---- 1 root root 1080 Nov 14 14:55 packed.MYD
-rw-rw---- 1 root root 1024 Nov 14 14:55 packed.MYI
mysql>

root@cluster-repo test]# pwd
/tmp/bug/test
[root@cluster-repo test]# ls -l
total 20
-rw-rw---- 1 root root 8554 Nov 14 14:55 packed.frm
-rw-rw---- 1 root root 1080 Nov 14 14:55 packed.MYD
-rw-rw---- 1 root root 1024 Nov 14 14:55 packed.MYI
[root@cluster-repo test]#
[root@cluster-repo test]# myisampack packed
Compressing packed.MYD: (10 records)
- Calculating statistics
- Compressing file
51.57%

[root@cluster-repo test]# ls -l
total 20
-rw-rw---- 1 root root 8554 Nov 14 14:55 packed.frm
-rw-rw---- 1 root root 1080 Nov 14 14:55 packed.MYD
-rw-rw---- 1 root root 1024 Nov 14 14:55 packed.MYI
[root@cluster-repo test]#
[root@cluster-repo test]# myisampack packed
Compressing packed.MYD: (10 records)
- Calculating statistics
- Compressing file
51.57%
[root@cluster-repo test]# myisamchk -rq --sort-index --analyze packed
- check record delete-chain
- recovering (with keycache) MyISAM-table 'packed'
Data records: 10
- Sorting index for MyISAM-table 'packed'
[root@cluster-repo test]#
[root@cluster-repo test]# ls -l
total 20
-rw-rw---- 1 root root 8554 Nov 14 14:55 packed.frm
-rw-rw---- 1 root root  530 Nov 14 14:55 packed.MYD
-rw-rw---- 1 root root 1024 Nov 14 14:59 packed.MYI

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

mysql> SHOW TABLE STATUS LIKE 'packed'\G
*************************** 1. row ***************************
           Name: packed
         Engine: MyISAM
        Version: 10
     Row_format: Compressed
           Rows: 10
 Avg_row_length: 52
    Data_length: 523
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-11-14 14:55:03
    Update_time: 2014-11-14 14:55:12
     Check_time: 2014-11-14 14:59:15
      Collation: latin1_swedish_ci
       Checksum: 4257035281
 Create_options:
        Comment:
1 row in set (0.00 sec)

[root@cluster-repo test]# myisamchk -u packed
- recovering (with keycache) MyISAM-table 'packed'
Data records: 10

[root@cluster-repo test]# ls -l
total 28
-rw-rw---- 1 root root  8554 Nov 14 14:55 packed.frm
-rw-rw---- 1 root root 10030 Nov 14 15:00 packed.MYD
-rw-rw---- 1 root root  1024 Nov 14 15:00 packed.MYI

-- flush tables and view max_data_length again

mysql> SHOW TABLE STATUS LIKE 'packed'\G
*************************** 1. row ***************************
           Name: packed
         Engine: MyISAM
        Version: 10
     Row_format: Fixed <-----------
           Rows: 10
 Avg_row_length: 1003
    Data_length: 10030
Max_data_length: 65732607
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-11-14 14:55:03
    Update_time: 2014-11-14 15:00:32
     Check_time: 2014-11-14 15:00:32
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)