Bug #26214 CREATE TABLE 'create options'
Submitted: 9 Feb 2007 8:16 Modified: 30 Aug 2007 14:39
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:any (to my knowledge) OS:Any (any (to my knowledge))
Assigned to: MC Brown CPU Architecture:Any
Tags: qc

[9 Feb 2007 8:16] Peter Laursen
Description:
This is basically a documentation request!

The information about MySQL table row_formats is available from 3 different places

1) SHOW TABLE STATUS column `Row_format`
2) SHOW TABLE STATUS column `Create_Options`
3) SHOW CREATE TABLE

How to repeat:
1)
-- MyIsam and Falcon engines

drop database if exists test2;
create database test2;
use test2;
drop table if exists testtab;
create table testtab (`id` integer) engine = innodb;
insert into testtab values ('1'),('2');
show table status from `test2`;
-- reports `Row_format` as 'fixed'. `Create_Options` is empty.
alter table testtab row_format=COMPACT;
show table status from `test2`;
-- still reports Row_format as 'fixed'.  However `Create Options` is now 'COMPACT'
show create table testtab;  
-- returns 
-- CREATE TABLE `testtab` ( `id` int(11) DEFAULT NULL
-- ) ENGINE=Falcon DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

2)
-- InnoDB behaves opposite

drop database if exists test2;
create database test2;
use test2;
drop table if exists testtab;
create table testtab (`id` integer) engine = innodb;
insert into testtab values ('1'),('2');
show table status from `test2`;
-- reports Row_format as 'Compact'. `Create_Options` is empty.
alter table testtab row_format=FIXED;
show table status from `test2`;
-- still reports Row_format as 'Compact'.  However `Create Options` is now 'Fixed'
show create table testtab;  
-- returns 
-- CREATE TABLE `testtab` ( `id` int(11) DEFAULT NULL ) ENGINE=Falcon DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

Suggested fix:
Explanation? 
Is this a bug? 
Some docs available on this ... ??

I am very uncertain whether specifying ROW_FORMAT (and other 'create options' as well) have any effect at all!
[9 Feb 2007 8:19] Peter Laursen
I think I made a mistake when constructing this topic!

FALCON should be INNODB one place with 2) .
But I think you get the point!
[9 Feb 2007 14:58] Sergei Golubchik
It's a documentation request, indeed.
There is a set of row formats that a storage engine supports.
If you specify a row format in CREATE TABLE that storage engine does not support, it'll use some other row format, one that it supports.

Row_format column in SHOW TABLE STATUS shows the actual row format of the table.
Create_options in SHOW TABLE STATUS and SHOW CREATE TABLE show the options (including row format) that you specified at CREATE TABLE time.

The original options are preserved because you may do ALTER TABLE ... ENGINE= and change the storage engine of the table, and a new storage engine may support the row format that you specified back then during CREATE TABLE.
[30 Apr 2007 18:17] Valeriy Kravchuk
Thank you for a reasonable documentation request.
[30 Aug 2007 14:39] MC Brown
The documentation has been updated to note the differences in the column output (under SHOW TABLE STATUS), and a note has been added to CREATE TABLE that explicitly describes the retention of the information and where to obtain the current (active) and original specifications.