Bug #78219 compression can be defined on a compressed table if innodb_strict_mode is OFF
Submitted: 26 Aug 2015 8:27 Modified: 27 Aug 2015 12:21
Reporter: zhai weixiang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.8 OS:Any
Assigned to: CPU Architecture:Any

[26 Aug 2015 8:27] zhai weixiang
Description:
mysql> use test
Database changed
mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int) row_format=compressed key_block_size = 4 compression='zlib';
Query OK, 0 rows affected (0.03 sec)

mysql> select * from information_schema.innodb_sys_tablespaces where name like 'test/t1';
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME    | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|   103 | test/t1 |   39 | Barracuda   | Compressed |     16384 |          4096 | Single     |          4096 |     65536 |          16384 | None        |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
1 row in set (0.00 sec)

Here the value of COMPRESSION column is 'None'

Then restart the server:

mysql> use test
Database changed
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 COMPRESS='zlib'
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_sys_tablespaces where name like 'test/t1';
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME    | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|   103 | test/t1 |   39 | Barracuda   | Compressed |     16384 |          4096 | Single     |          4096 |     65536 |          16384 | Zlib        |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
1 row in set (0.00 sec)

Now the value of COMPRESSION column is 'Zlib'

How to repeat:
Described above

Suggested fix:
Don't set compression attribute on compressed tableļ¼Œ for example, bellow is a simple fix (not fully tested)

diff --git a/storage/innobase/fil/fil0fil.cc b/storage/innobase/fil/fil0fil.cc
index 5ae66e6..1fc6242 100644
--- a/storage/innobase/fil/fil0fil.cc
+++ b/storage/innobase/fil/fil0fil.cc
@@ -6683,6 +6683,10 @@ fil_set_compression(
                err = DB_NOT_FOUND;
 
        } else {
+               if (fsp_flags_is_compressed(space->flags)) {
+                       space->compression_type = Compression::NONE;
+                       return (DB_SUCCESS);
+               }
 
                space->compression_type = compression.m_type;
[27 Aug 2015 11:47] MySQL Verification Team
Hello Zhai,

Thank you for the report.
I'm not seeing this issue at my end with 5.7.8 and 5.7.9 daily builds.
Are you using source build, if yes then what is the exact cmake command used? Or Am I missing anything here.

Thanks,
Umesh
[27 Aug 2015 11:48] MySQL Verification Team
// 5.7.8

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8-rc: cat docs/INFO_SRC
commit: ae3b133e5f7b13d1edf7acf7eee6af2c2b4014e2
date: 2015-07-20 14:02:16 +0200
build-date: 2015-07-20 14:16:07 +0200
short: ae3b133
branch: mysql-5.7.8-rc-release

MySQL source 5.7.8

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8-rc: bin/mysql  -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

Database changed
mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (a int) row_format=compressed key_block_size = 4 compression='zlib';
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from information_schema.innodb_sys_tablespaces where name like 'test/t1';
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME    | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|    23 | test/t1 |   39 | Barracuda   | Compressed |     16384 |          4096 | Single     |          4096 |     65536 |          65536 | None        |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
1 row in set (0.01 sec)

mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8-rc: bin/mysqladmin -uroot -S /tmp/mysql_ushastry.sock shutdown
[1]+  Done                    bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/mysql-5.7.8-rc --datadir=/export/umesh/server/binaries/mysql-5.7.8-rc/78085 --core-file --socket=/tmp/mysql_ushastry.sock --port=15000 --log-error=/export/umesh/server/binaries/mysql-5.7.8-rc/78085/log.err 2>&1
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8-rc: bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/mysql-5.7.8-rc --datadir=/export/umesh/server/binaries/mysql-5.7.8-rc/78085 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-5.7.8-rc/78085/log.err 2>&1 &
[1] 30388
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8-rc: 2015-08-27T11:32:31.985486Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-08-27T11:32:31.985592Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2015-08-27T11:32:31.985676Z 0 [Note] bin/mysqld (mysqld 5.7.8-rc) starting as process 30388 ...

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8-rc: bin/mysql  -uroot -S /tmp/mysql_ushastry.sock                                                                                       Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  select * from information_schema.innodb_sys_tablespaces where name like 'test/t1';
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME    | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|    23 | test/t1 |   39 | Barracuda   | Compressed |     16384 |          4096 | Single     |          4096 |     65536 |          65536 | None        |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
1 row in set (0.00 sec)

mysql> show create table test.t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 COMPRESS='zlib'
1 row in set (0.01 sec)

mysql>
[27 Aug 2015 11:48] MySQL Verification Team
// 5.7.9

commit: e4928d41773503a7b93ab0886a1f5efa88a4e4e4
date: 2015-08-26 21:01:11 +0530
build-date: 2015-08-26 18:05:18 +0200
short: e4928d4
branch: mysql-5.7

MySQL source 5.7.9

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085/log.err 2>&1 &
[1] 29903
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysql  -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set session innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

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

Database changed
mysql> create table t1 (a int) row_format=compressed key_block_size = 4 compression='zlib';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.innodb_sys_tablespaces where name like 'test/t1';
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME    | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|    23 | test/t1 |   39 | Barracuda   | Compressed |     16384 |          4096 | Single     |          4096 |     65536 |          16384 | None        |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
1 row in set (0.00 sec)

mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysqladmin -uroot -S /tmp/mysql_ushastry.sock shutdown
[1]+  Done                    bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085 --core-file --socket=/tmp/mysql_ushastry.sock --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085/log.err 2>&1
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9:
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.9 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.7.9/78085/log.err 2>&1 &
[1] 29940
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysql  -uroot -S /tmp/mysql_ushastry.sock                                                                                 Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from information_schema.innodb_sys_tablespaces where name like 'test/t1';
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME    | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|    23 | test/t1 |   39 | Barracuda   | Compressed |     16384 |          4096 | Single     |          4096 |     65536 |          16384 | None        |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_sys_tablespaces where name like 'test/t1';
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
| SPACE | NAME    | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | COMPRESSION |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
|    23 | test/t1 |   39 | Barracuda   | Compressed |     16384 |          4096 | Single     |          4096 |     65536 |          16384 | None        |
+-------+---------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+-------------+
1 row in set (0.00 sec)

mysql>
[27 Aug 2015 12:03] zhai weixiang
HI, Umesh 
After restarting the server, you should execute `SHOW CREATE TABLE` first and then query the innodb_sys_tablespace table...(there is  another bug#78197)
[27 Aug 2015 12:21] MySQL Verification Team
Thank you, Zhai.
Verified as described with 5.7.8 build.

Thanks,
Umesh