MySQL Bugs: #78197: COMPRESSION column in innodb_sys

Bug #78197	COMPRESSION column in innodb_sys_tablespaces is not correct
Submitted:	25 Aug 2015 2:05	Modified:	2 Oct 2015 12:17
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.7.8, 5.7.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Create a compressed table with COMPRESSION attribute, and then restart the server:

mysql> select * from information_schema.innodb_sys_tablespaces where name like '%test%t6%';
+-------+---------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME    | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+---------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|    86 | test/t6 |    0 | Antelope    | Compact or Redundant |     16384 |             0 | Single     |          4096 | 104857600 |       33308672 | None        |
+-------+---------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
1 row in set (0.01 sec)

mysql> show create table test.t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMPRESS='zlib'
1 row in set (0.25 sec)

mysql> select * from information_schema.innodb_sys_tablespaces where name like '%test%t6%';
+-------+---------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME    | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+---------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|    86 | test/t6 |    0 | Antelope    | Compact or Redundant |     16384 |             0 | Single     |          4096 | 104857600 |       33308672 | Zlib        |
+-------+---------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+

Because the attribute is stored in frm, the table need to be opened from server layer. 

How to repeat:
described above

Suggested fix:
display the compress information from a i_s table of server layer rather than innodb engine

Hello Zhai,

Thank you for the report and test case.
Verified as described with 5.7.9 build.

Thanks,
Umesh

// 5.7.9

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> use db1;
Database changed
mysql> CREATE TABLE `t1` (   `a` int(11) DEFAULT NULL,   `b` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 compression='zlib';
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMPRESSION='zlib'
1 row in set (0.01 sec)

mysql> select * from information_schema.innodb_sys_tablespaces where name like '%t1%';
+-------+--------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME   | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+--------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|    23 | db1/t1 |    0 | Antelope    | Compact or Redundant |     16384 |             0 | Single     |          4096 |     98304 |          61440 | Zlib        |
+-------+--------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
1 row in set (0.00 sec)

mysql>

-- restart mysqld

mysql> select * from information_schema.innodb_sys_tablespaces where name like '%t1%';
+-------+--------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME   | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+--------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|    23 | db1/t1 |    0 | Antelope    | Compact or Redundant |     16384 |             0 | Single     |          4096 |     98304 |          61440 | None        |
+-------+--------+------+-------------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
1 row in set (0.00 sec)

Posted by developer:
 
    Bug#21687636 COMPRESSION COLUMN IN INNODB_SYS_TABLESPACES
    IS NOT CORRECT
    
    Problem:
    ========
    INNODB_SYS_TABLESPACES gives incorrect information about the
    COMPRESSION attribute when queried on a compressed table,
    after restarting the server.
    
    The reason behind this is that the COMPRESSION attribute is
    stored in .frm file and not stored in innodb dictionary
    (INNODB_SYS_TABLESPACES).
    
    Currently we return the value from
    fil_space_t->compression_type. It is filled when we do a
    ha_innobase::open().
    
    So after a restart, fil_space_t will have invalid value
    until we do a ha_innobase::open(). So if user executes
    select * from t1 (which causes ha_innobase::open()), then
    I_S.INNODB_SYS_TABLESPACES shows correct value else it will
    show incorrect value.
    
    Fix:
    ----
    Remove the COMPRESSION attribute altogether from
    I_S.INNODB_SYS_TABLESPACES and rely on SHOW CREATE TABLE to
    get the COMPRESSION attribute information.

Posted by developer:
 
Fixed as of the upcoming 5.7.10, 5.8.0 release, and here's the changelog entry:

After restarting the server, the COMPRESSION column of the
INNODB_SYS_TABLESPACES table displayed incorrect data. The COMPRESSION
column was removed from INNODB_SYS_TABLESPACES. To view the current
setting for page compression, use SHOW CREATE TABLE. 

Thank you for the bug report.