Bug #80386 alter table compression='none' rebuilds table each time with ENCRYPTION='N'
Submitted: 16 Feb 2016 8:40 Modified: 31 Mar 2016 4:53
Reporter: Shahriyar Rzayev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S1 (Critical)
Version:5.7.11 OS:CentOS (7)
Assigned to: CPU Architecture:Any

[16 Feb 2016 8:40] Shahriyar Rzayev
Description:
Here is the table:

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `pad_c` int(11) GENERATED ALWAYS AS ((char_length(`pad`) + char_length(`c`))) STORED,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=1843230 DEFAULT CHARSET=latin1 COMPRESSION='none' ENCRYPTION='N'

mysql> alter table sbtest1 compression='none';
Query OK, 1843229 rows affected (18,03 sec)
Records: 1843229  Duplicates: 0  Warnings: 0

mysql> show processlist;
+----+------+-----------+--------+---------+------+-------------------+----------------------------------------+
| Id | User | Host      | db     | Command | Time | State             | Info                                   |
+----+------+-----------+--------+---------+------+-------------------+----------------------------------------+
|  2 | root | localhost | dbtest | Query   |   18 | copy to tmp table | alter table sbtest1 compression='none' |
|  3 | root | localhost | NULL   | Query   |    0 | starting          | show processlist                       |
+----+------+-----------+--------+---------+------+-------------------+----------------------------------------+
2 rows in set (0,00 sec)

Running several times:

mysql> alter table sbtest1 compression='none';
Query OK, 1843229 rows affected (19,05 sec)
Records: 1843229  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 compression='none';
Query OK, 1843229 rows affected (17,50 sec)
Records: 1843229  Duplicates: 0  Warnings: 0

How to repeat:
See description

Suggested fix:
To disable page compression as doc states:

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

To disable page compression, set COMPRESSION=None using ALTER TABLE. Writes to the tablespace that occur after setting COMPRESSION=None no longer use page compression. To uncompress existing pages, you must rebuild the table using OPTIMIZE TABLE after setting COMPRESSION=None.

ALTER TABLE t1 COMPRESSION="None";
OPTIMZE TABLE t1;

With regular table there is no table rebuild:

mysql> alter table sbtest4 compression='none';
Query OK, 0 rows affected (0,01 sec)
Records: 0  Duplicates: 0  Warnings: 0
[16 Feb 2016 9:45] Shahriyar Rzayev
Same thing with alter tablespace:

CREATE TABLE `sbtest5` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `pad_c` int(11) GENERATED ALWAYS AS ((char_length(`pad`) + char_length(`c`))) STORED,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=1843230 DEFAULT CHARSET=latin1

mysql> alter table sbtest5 encryption='N';
Query OK, 1843229 rows affected (17,16 sec)
Records: 1843229  Duplicates: 0  Warnings: 0

mysql> alter table sbtest5 tablespace=sbtest1;
Query OK, 1843229 rows affected (15,62 sec)
Records: 1843229  Duplicates: 0  Warnings: 0

mysql> alter table sbtest5 tablespace=sbtest1;
Query OK, 1843229 rows affected (15,59 sec)
Records: 1843229  Duplicates: 0  Warnings: 0
[17 Feb 2016 11:24] MySQL Verification Team
Hello Shahriyar,

Thank you for the report and test case.

Thanks,
Umesh
[31 Mar 2016 4:53] Erlend Dahl
[23 Mar 2016 7:09] Daniel T Price

Fixed as of the upcoming 5.7.13, 5.8.0 release, and here's the changelog
entry:

Online DDL operations like ALTER TABLE ... ADD INDEX were not permitted
for tables created with the ENCRYPTION attribute.

(Under the heading of Bug#22897921)