Bug #23403 ROW_FORMAT=FIXED at table creation time does not force fixed column types
Submitted: 18 Oct 2006 2:01 Modified: 8 Dec 2006 11:23
Reporter: Lachlan Mulcahy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.24a OS:Any (Any)
Assigned to: MC Brown CPU Architecture:Any
Tags: bfsm_2006_11_02
Triage: D4 (Minor)

[18 Oct 2006 2:01] Lachlan Mulcahy
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.
[8 Dec 2006 11:23] MC Brown
The documentation has been updated. We now explicitly mention the BLOB/TEXT limitation and the effects on the length pf VARCHAR/VARBINARY columns (even though they retain their type).