Description:
Using the ROW_FORMAT=FIXED option with a CREATE TABLE that has VARCHAR fields does not force them to become CHAR fields as the manual says it should:
http://dev.mysql.com/doc/refman/5.0/en/myisam-table-formats.html
This *may* be a documentation error, as the 5.1 manual does not say that the ROW_FORMAT option causes field type changes.
The behaviour seems quite ambiguous if use of the option is *not* intended to change the column types (as in 5.1).
I am left wondering, however, what is the point of being able to specify a ROW_FORMAT that contradicts the column types?
The behaviour seems really ambiguous when it is allowed.
The following example shows how the behaviour allows illogical table definitions and gives confusing/contradictory results:
mysql> create table t1 (a varchar(20), b varchar(30), c text) ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0.04 sec)
mysql> show table status like 't1'\G
*************************** 1. row ***************************
Name: t1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2006-10-18 11:54:38
Update_time: 2006-10-18 11:54:38
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: row_format=FIXED
Comment:
1 row in set (0.01 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` varchar(20) default NULL,
`b` varchar(30) default NULL,
`c` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
1 row in set (0.00 sec)
* I can create a table with ROW_FORMAT=FIXED that has a text field in the definition, without any warnings.
* I look at the table status and it shows the row format is actually dynamic, however, it also shows that the row_format=fixed is an extra option.
* The show create table includes the ROW_FORMAT=FIXED option, however, it has no effect.
Also:
mysql> CREATE TABLE t2 (a varchar(20), b varchar(10)) ROW_FORMAT=FIXED;
ERROR 1050 (42S01): Table 't2' already exists
mysql> DROP TABLE t2;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t2 (a varchar(20), b varchar(10)) ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t2 VALUES ('abcd','efghi'),('a','b');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SHOW TABLE STATUS LIKE 't2'\G
*************************** 1. row ***************************
Name: t2
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 2
Avg_row_length: 33
Data_length: 66
Max_data_length: 9288674231451647
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2006-10-18 11:56:55
Update_time: 2006-10-18 11:57:25
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: row_format=FIXED
Comment:
1 row in set (0.01 sec)
A table with variable width fields, containing data is still apparently a fixed format? How is that possible?
How to repeat:
In the simplest sense:
mysql> create table t1 (a varchar(20)) ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0.02 sec)
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.02 sec)
Suggested fix:
Fix documentation to accurately reflect behaviour or fix behaviour to work as documented.
