Description:
The SHOW CREATE TABLE statement and the information_schema.TABLES table offer contradicting information about the ROW_FORMAT used by a MyISAM table.
The information-schema seems to give the actual format whereas SHOW CREATE TABLE returns the last explicitly specified row format (set using a ROW_FORMAT table option in ALTER TABLE or CREATE TABLE)
How to repeat:
mysql> show create table row_format;
+------------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-------------------------------------------------------------------------------------------------------------------------------+
| row_format | CREATE TABLE "row_format" (
"id" int(11) DEFAULT NULL,
"c" text
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC |
+------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select row_format from information_schema.tables where table_name = 'row_format';
+------------+
| row_format |
+------------+
| Dynamic |
+------------+
1 row in set, 19 warnings (0.00 sec)
mysql> alter table row_format row_format fixed;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select row_format from information_schema.tables where table_name = 'row_format';
+------------+
| row_format |
+------------+
| Dynamic |
+------------+
1 row in set, 19 warnings (0.00 sec)
mysql> show create table row_format;
+------------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------+
| row_format | CREATE TABLE "row_format" (
"id" int(11) DEFAULT NULL,
"c" text
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED |
+------------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table row_format row_format compressed;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table row_format;
+------------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+----------------------------------------------------------------------------------------------------------------------------------+
| row_format | CREATE TABLE "row_format" (
"id" int(11) DEFAULT NULL,
"c" text
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED |
+------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select row_format from information_schema.tables where table_name = 'row_format';
+------------+
| row_format |
+------------+
| Dynamic |
+------------+
1 row in set, 19 warnings (0.00 sec)
Suggested fix:
Make them reflect the same info or fix the documentation to explain this
