MySQL Bugs: #76022: Wrong row format reported on an InnoDB table

Bug #76022	Wrong row format reported on an InnoDB table
Submitted:	24 Feb 2015 9:46	Modified:	25 Feb 2015 12:47
Reporter:	Saverio Miroddi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.20	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I have an InnoDB table in the system, whose row format is reported inconsistently across the system.

This is the extracted result of the SHOW CREATE TABLE:

CREATE TABLE `<table>` (
  <fields>
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC

This is the information in INFORMATION_SCHEMA:

+---------------------------+--------+---------+------------+--------------------+
| table_name                | engine | version | row_format | create_options     |
+---------------------------+--------+---------+------------+--------------------+
| <table>                   | InnoDB |      10 | Compact    | row_format=DYNAMIC |
+---------------------------+--------+---------+------------+--------------------+

The above shows inconsistent row format (declared vs. effective)

The following are the innodb table-related variables:

+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_max   | Antelope |
+--------------------------+----------+

Which shows that only Antelope tables have been opened (such tables are in fact Antelope).

The results shown in the SHOW CREATE TABLE and (in part of) INFORMATION_SCHEMA above are misleading, since the underlying row format is Compact.

How to repeat:
Just create an InnoDB table specifying ROW_FORMAT=DYNAMIC.

Warnings will be raised:

mysql> show warnings;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message                                                            |
+---------+------+--------------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                               |
+---------+------+--------------------------------------------------------------------+

And the table will be created with Compact row format, but it will still show up as ROW_FORMAT=Dynamic.

Hello Saverio M,

Thank you for the report.
Imho this is intended behavior. 
Please see the note in Bug #26214 which explains this behavior which has been documented as well [9 Feb 2007 14:58] Sergei Golubchik 

Quoting the note for your reference:

"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."

Also - http://dev.mysql.com/doc/refman/5.6/en/create-table.html

Thanks,
Umesh

// This confirms 

mysql> use test
Database changed
mysql> CREATE TABLE `bug_i_s` (
    ->   id int
    -> ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message                                                            |
+---------+------+--------------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                               |
+---------+------+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: bug_i_s
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact <=============== Actual format which is used
   ..
   
    CREATE_TIME: 2015-02-25 13:18:43
..
 CREATE_OPTIONS: row_format=DYNAMIC <============ CREATE TABLE definition is retained 
  TABLE_COMMENT:
1 row in set (0.00 sec)

mysql> show create table bug_i_s\G
*************************** 1. row ***************************
       Table: bug_i_s
Create Table: CREATE TABLE `bug_i_s` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC  <============ CREATE TABLE definition is retained 
1 row in set (0.00 sec)

mysql> show table status\G
*************************** 1. row ***************************
           Name: bug_i_s
         Engine: InnoDB
        Version: 10
     Row_format: Compact
..

 Create_options: row_format=DYNAMIC
        Comment:
1 row in set (0.00 sec)

mysql> show variables like 'innodb_file_format';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| innodb_file_format | Antelope |
+--------------------+----------+
1 row in set (0.00 sec)

mysql> set global innodb_file_format=barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.00 sec)

mysql> ALTER TABLE bug_i_s engine=innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: bug_i_s
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
   ..
   
 CREATE_OPTIONS: row_format=DYNAMIC
  TABLE_COMMENT:
1 row in set (0.00 sec)

mysql> show create table bug_i_s\G
*************************** 1. row ***************************
       Table: bug_i_s
Create Table: CREATE TABLE `bug_i_s` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> show table status\G
*************************** 1. row ***************************
           Name: bug_i_s
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
 ..
 
 Create_options: row_format=DYNAMIC
        Comment:
1 row in set (0.00 sec)

Maybe that this behaviour is documented, but it's big nonsense! This is a bug, documented or not.