Bug #90040 COMPRESSION='none' preventing creating temporary table
Submitted: 13 Mar 7:39 Modified: 14 Mar 10:54
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.20 OS:Any
Assigned to: CPU Architecture:Any

[13 Mar 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 10:54] Umesh Shastry
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