MySQL Bugs: #58060: Inconsistency in row format reported through I

Bug #58060	Inconsistency in row format reported through I_S
Submitted:	8 Nov 2010 19:06	Modified:	18 Nov 2010 20:26
Reporter:	Nirbhay Choubey	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.5.7-rc-debug	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb, mysql-5.5

Description:
Creating a table using the following two
statements gives the resultant row_format
as 'COMPRESSED'.

CREATE TABLE t1 (c1 INT) ROW_FORMAT=REDUNDANT;
ALTER TABLE test.t1 KEY_BLOCK_SIZE = 8;

mysql> SELECT row_format FROM INFORMATION_SCHEMA.tables WHERE table_name='t1';
+------------+
| Compressed |
+------------+

However, if the same table is created using the
following single CREATE TABLE statement, row_format
is reported as REDUNDANT, and hence inconsistent.

CREATE TABLE `t1` (
    `c1` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
    ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected, 1 warning (0.21 sec)

SHOW WARNINGS;
Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED. |

SELECT row_format FROM INFORMATION_SCHEMA.tables WHERE table_name='t1';
+------------+
| Redundant  |
+------------+

Note. This scenario can be reproduced using DYNAMIC row format too.

How to repeat:
USE test;
CREATE TABLE t1 (c1 INT) ROW_FORMAT=REDUNDANT;
ALTER TABLE test.t1 KEY_BLOCK_SIZE = 8;
SELECT row_format FROM INFORMATION_SCHEMA.tables WHERE table_name='t1';
#Notice the row_format.

SHOW CREATE TABLE t1;

DROP TABLE t1;
CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8;
SELECT row_format FROM INFORMATION_SCHEMA.tables WHERE table_name='t1';
# Notice the row_format.

Thank you for the bug report.

Hi,

The second scenario looks better to me as, at least, it
throws a warning. While in the first case, 'ALTER TABLE'
execution goes normally, changing the row format without
even notifying about this change.

It *might* be a bug in InnoDB, but I am not sure.

Most likely I_S schema problem. See below:

SELECT row_format FROM INFORMATION_SCHEMA.tables WHERE table_name='t1';
row_format
Compressed
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT KEY_BLOCK_SIZE=8