Bug #88843 Unhelpful error when moving from table compression to page compression
Submitted: 10 Dec 2017 9:55 Modified: 11 Dec 2017 5:19
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.20 OS:Any
Assigned to: CPU Architecture:Any

[10 Dec 2017 9:55] Daniël van Eeden
Description:
When moving from table compression to page compression the error message says that InnoDB doesn't support COMPRESSION if you don't supply KEY_BLOCK_SIZE=0.

Related: Bug #70534 	Removing table compression leaves compressed keys

How to repeat:
This fails:
CREATE TABLE t1 (c1 INT) ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE=8;
ALTER TABLE t1 ROW_FORMAT=DYNAMIC, COMPRESSION="zlib";
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'COMPRESSION'

This works:
CREATE TABLE t1 (c1 INT) ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE=8;
ALTER TABLE t1 ROW_FORMAT=DYNAMIC, KEY_BLOCK_SIZE=0, COMPRESSION="zlib";

Suggested fix:
Change the error message to indicate:
1. InnoDB does support COMPRESSION
2. That table options are not compatible
3. How to change this

For example:
- Table storage engine 'InnoDB' reports that incompatible create options are used. See https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html for details.
[11 Dec 2017 5:19] MySQL Verification Team
Hello Daniël,

Thank you for the report and feedback!

Thanks,
Umesh
[6 May 2019 0:11] Benjamin Morel
Just been bitten by the same misleading error message; this should be a quick fix to make and would be really helpful. At least Google pointed me to this bug, thanks Daniël for providing the solution!