| 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: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.7.8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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
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;