Table rebuild with alter compression=’none’

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

So the rebuild will be on optimize table run.

But if you want table rebuild each time when you run compression=’none’ you can add encryption=’N’ option 😛

Sample 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

Yes it is from BUG report -> http://bugs.mysql.com/bug.php?id=80386

Leave a Reply

Your email address will not be published. Required fields are marked *