Description:
ALTER/CREATE TABLE ... ROW_FORMAT=compressed does not lead into error. It only writes a comment into Create_options.
When documentation is not clearly read this can lead to misunderstandings.
How to repeat:
mysql> show table status like 'oli';
+------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| 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 |
+------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| oli | MyISAM | 10 | Fixed | 164558 | 35 | 5759530 | 9851624184872959 | 1024 | 0 | NULL | 2006-07-17 16:30:49 | 2006-07-17 16:31:00 | | utf8_general_ci | NULL | | |
+------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
mysql> system ls -la product/mysql-5.0.19/data/test/oli*
-rw-rw---- 1 mysql mysql 8864 2006-07-17 16:30 product/mysql-5.0.19/data/test/oli.frm
-rw-rw---- 1 mysql mysql 5759530 2006-07-17 16:31 product/mysql-5.0.19/data/test/oli.MYD
-rw-rw---- 1 mysql mysql 1024 2006-07-17 16:31 product/mysql-5.0.19/data/test/oli.MYI
mysql> alter table oli row_format=compressed;
Query OK, 164558 rows affected (0.18 sec)
Records: 164558 Duplicates: 0 Warnings: 0
product/mysql-5.0.19/data/test/oli.MYI
mysql> show table status like 'oli';
+------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+-----------------------+---------+
| 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 |
+------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+-----------------------+---------+
| oli | MyISAM | 10 | Fixed | 164558 | 35 | 5759530 | 9851624184872959 | 1024 | 0 | NULL | 2006-07-17 16:32:53 | 2006-07-17 16:32:53 | | utf8_general_ci | NULL | row_format=COMPRESSED | |
+------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+-----------------------+---------+
mysql> system ls -la product/mysql-5.0.19/data/test/oli*
-rw-rw---- 1 mysql mysql 8864 2006-07-17 16:32 product/mysql-5.0.19/data/test/oli.frm
-rw-rw---- 1 mysql mysql 5759530 2006-07-17 16:32 product/mysql-5.0.19/data/test/oli.MYD
-rw-rw---- 1 mysql mysql 1024 2006-07-17 16:32
mysql> create table oli2 ( id int , data varchar(255)) row_format=compressed;
Query OK, 0 rows affected (0.04 sec)
mysql> show table status like 'oli%';
+------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------+
| 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 |
+------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------+
| oli | MyISAM | 10 | Fixed | 164558 | 35 | 5759530 | 9851624184872959 | 1024 | 0 | NULL | 2006-07-17 16:32:53 | 2006-07-17 16:32:53 | | utf8_general_ci | NULL | row_format=COMPRESSED | |
| oli2 | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2006-07-17 16:38:20 | 2006-07-17 16:38:20 | | latin1_swedish_ci | NULL | row_format=COMPRESSED | |
+------+--------+---------+------------+--------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------+
Suggested fix:
Explicitly write in docu that alter table ... row_format=compress does NOT work.
Prohibit parser to accept this form of statement.
change syntax diagram in docu.
or
implement this feature.
