MySQL Bugs: #84370: It is possible to enable compression for partitioned table in general tablespace

Bug #84370	It is possible to enable compression for partitioned table in general tablespace
Submitted:	29 Dec 2016 13:42	Modified:	30 Dec 2016 4:12
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi dear all,

As per documentation:

https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html

Page compression is not supported for tables that reside in shared tablespaces, which include the system tablespace, the temporary tablespace, and general tablespaces. 

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `numbers` */ DEFAULT NULL,
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) /*!50100 TABLESPACE `ccc` */ ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000

If you try to enable compression:

mysql [localhost] {msandbox} (dbtest) > alter table sbtest1 compression='lz4';
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'

But for partitioned table:

CREATE TABLE `sbtest2` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED */ DEFAULT NULL,
  `pad` char(60) NOT NULL DEFAULT ''
) /*!50100 TABLESPACE `ccc` */ ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (k)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (10001) ENGINE = InnoDB) */

mysql [localhost] {msandbox} (dbtest) > alter table sbtest2 compression='lz4';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE `sbtest2` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED */ DEFAULT NULL,
  `pad` char(60) NOT NULL DEFAULT ''
) /*!50100 TABLESPACE `ccc` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COMPRESSION='lz4'
/*!50100 PARTITION BY RANGE (k)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (10001) ENGINE = InnoDB) */

How to repeat:
See description.

Hi Shahriyar,

Thank you for the report.

Thanks,
Umesh

Related - Bug #84356

-- 5.7.17

##### Partitioned table

[umshastr@hod03]/export/umesh/server/source/bugs/84173/5.7.17:
[umshastr@hod03]/export/umesh/server/source/bugs/84173/5.7.17: cat data/log.err|grep -i "PUNCH"
2016-12-30T04:01:33.596002Z 0 [Note] InnoDB: PUNCH HOLE support available
[umshastr@hod03]/export/umesh/server/source/bugs/84173/5.7.17: bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 Source distribution

Copyright (c) 2000, 2016, 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.

root@localhost [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use test
Database changed
root@localhost [test]> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

root@localhost [test]> create table t1(a int,b int) engine = innodb
    ->   partition by range (a)
    ->   (PARTITION p1 VALUES LESS THAN (100),
    ->   PARTITION p2 VALUES LESS THAN (1000),
    ->   PARTITION p3 VALUES LESS THAN (10000),
    ->   PARTITION p4 VALUES LESS THAN (10001)
    ->   );
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> CREATE TABLESPACE `t1_gen` ADD DATAFILE 't1_gen`.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]>  alter table t1 tablespace t1_gen;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `t1_gen` */ ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (a)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (10001) ENGINE = InnoDB) */
1 row in set (0.00 sec)

root@localhost [test]> alter table t1 compression='lz4';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `t1_gen` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 COMPRESSION='lz4'
/*!50100 PARTITION BY RANGE (a)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (10001) ENGINE = InnoDB) */