Bug #21094 ALTER TABLE ... ROW_FORMAT=compressed does not lead into error
Submitted: 17 Jul 2006 14:50 Modified: 17 Jul 2006 15:11
Reporter: Oli Sennhauser Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1-BK, 5.0-BK, 5.0.19 OS:Linux (linux)
Assigned to:
Tags: ALTER TABLE, compressed, CREATE TABLE, ROW_FORMAT
Triage: Triaged: D3 (Medium)

[17 Jul 2006 14:50] Oli Sennhauser
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.
[17 Jul 2006 15:11] Valeriy Kravchuk
Thank you for a problem report. This is either a bug, or a documentation request, or a feature request. Whatever seems more appropriate for the developers. For me it looks like a bug.
[11 Dec 2008 17:46] Philip Stoev
Basically any ROW_FORMAT can be used with any engine type. Maria and Innodb ROW_FORMATs are accepted for MyISAM.
[10 Oct 2014 7:52] Daniƫl van Eeden
InnoDB strict mode should be used to prevent this. This is documented on http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_strict_mode

Related to Bug #73769