Bug #90040 COMPRESSION='none' preventing creating temporary table

• Submitted: 13 Mar 2018 7:39
• Modified: 14 Mar 2018 10:54
• Reporter: Shahriyar Rzayev
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.7.20
• OS: Any
• CPU Architecture: Any

[13 Mar 2018 7:39] Shahriyar Rzayev

Description:
Hi dear experts.

Here is the situation which prevents creating temporary table with compression='none' added to create statement. I can use encryption='N' but can not use compression='none'.

CREATE temporary TABLE `sbtest1_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL,
  `json_test_s` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  `json_test_index` varchar(255) GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `json_test_index` (`json_test_index`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 COMPRESSION='none' ENCRYPTION='N'

ERROR 1112 (42000): Table 'sbtest1_temp' uses an extension that doesn't exist in this MySQL version

When I remove compression='none':

CREATE temporary TABLE `sbtest1_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `json_test_v` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) VIRTUAL,
  `json_test_s` json GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  `json_test_index` varchar(255) GENERATED ALWAYS AS (json_array(`k`,`c`,`pad`)) STORED,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `json_test_index` (`json_test_index`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 ENCRYPTION='N'

Query OK, 0 rows affected (0,00 sec)

How to repeat:
See description.

[14 Mar 2018 10:54] MySQL Verification Team

Hello Shahriyar,

Thank you for the report.
Imho user-created temporary tables and on-disk internal temporary tables are created in a shared temporary tablespace and per documented limitation under "Page Compression Limitations and Usage Notes" - Page compression is not supported for tables that reside in shared tablespaces, which include the system tablespace, the temporary tablespace, and general tablespaces. - https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html

Thanks,
Umesh