MySQL Bugs: #30566: Can't use ALTER TABLE to rename a compressed MyISAM table

Bug #30566	Can't use ALTER TABLE to rename a compressed MyISAM table
Submitted:	22 Aug 2007 15:08	Modified:	28 Nov 2008 20:11
Reporter:	Sean Chighizola	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.1.20/5.0/5.1BK	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, compressed, myisam

Description:
On a compressed myisam table, one can't use ALTER TABLE ... REANME TO ...; but one can use RENAME TABLE ... TO ...;

mysql> show table status like 'table_1'\G
*************************** 1. row ***************************
Name: table_1
Engine: MyISAM
Version: 9
Row_format: Compressed
Rows: 100000
Avg_row_length: 116
Data_length: 26560928
Max_data_length: 4294967295
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2007-08-21 15:21:14
Update_time: 2007-08-21 15:21:16
Check_time: 2007-08-21 15:21:19
Collation: latin1_swedish_ci
Checksum: 2759676531
Create_options:
Comment:

mysql> ALTER TABLE table_1 RENAME TO table_2;
ERROR 1036 (HY000): Table 'table_1' is read only

mysql> RENAME TABLE table_1 TO table_2;
Query OK, 0 rows affected (0.00 sec)

mysql> show table status like 'table_2'\G
*************************** 1. row ***************************
Name: table_2
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 100000
Avg_row_length: 28
Data_length: 31369652
Max_data_length: 4294967295
Index_length: 50426880
Data_free: 0
Auto_increment: NULL
Create_time: 2007-08-21 11:40:10
Update_time: 2007-08-21 11:42:21
Check_time: 2007-08-21 11:42:21
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:

mysql> show variables like 'version';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| version       | 4.1.20-standard-log |
+---------------+---------------------+

How to repeat:
1. create a table
2. insert some records
3. compress it using myisampack
4. repair indexes using myisamchk -rq
5. try to rename the table using ALTER TABLE (receive an error)
6. try to rename the table using RENAME TABLE (this will work)

Suggested fix:
One can't modify the table structure of a compressed table, so perhaps inform the user to use RENAME TABLE or just let ALTER TABLE ... RENAME...; execute successfully.

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.22-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  show table status like 'table_1'\G
*************************** 1. row ***************************
           Name: table_1
         Engine: MyISAM
        Version: 10
     Row_format: Compressed
           Rows: 2097152
 Avg_row_length: 4
    Data_length: 8389258
Max_data_length: 281474976710655
   Index_length: 23700480
      Data_free: 0
 Auto_increment: 2097153
    Create_time: 2007-08-26 13:23:18
    Update_time: 2007-08-26 13:26:11
     Check_time: 2007-08-26 13:29:34
      Collation: latin1_swedish_ci
       Checksum: 1998802833
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

mysql> ALTER TABLE table_1 RENAME TO table_2;
ERROR 1036 (HY000): Table 'table_1' is read only
mysql> RENAME TABLE table_1 TO table_2;
Query OK, 0 rows affected (0.00 sec)

mysql> show table status like 'table_2'\G
*************************** 1. row ***************************
           Name: table_2
         Engine: MyISAM
        Version: 10
     Row_format: Compressed
           Rows: 2097152
 Avg_row_length: 4
    Data_length: 8389258
Max_data_length: 281474976710655
   Index_length: 23700480
      Data_free: 0
 Auto_increment: 2097153
    Create_time: 2007-08-26 13:23:18
    Update_time: 2007-08-26 13:26:11
     Check_time: 2007-08-26 13:29:34
      Collation: latin1_swedish_ci
       Checksum: 1998802833
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql>