MySQL Bugs: #80182: Unsupported extension error for new features while 'create table like'

Bug #80182	Unsupported extension error for new features while 'create table like'
Submitted:	28 Jan 2016 7:31	Modified:	16 Mar 2016 15:36
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.7.10	OS:	CentOS (7)
Assigned to:		CPU Architecture:	Any

Description:
Got the weird error message here:

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 '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) /*!50100 TABLESPACE `ts6` */ ENGINE=InnoDB AUTO_INCREMENT=808237 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

mysql> create table dbtest2.sbtest1 like dbtest.sbtest1;
ERROR 1112 (42000): Table 'sbtest1' uses an extension that doesn't exist in this MySQL version

http://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html#error_er_unsupported_ext...

Well, in fact exists :)

Of course with GTID enabled:

mysql> create table dbtest2.sbtest1 as select * from dbtest.sbtest1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

So the result is unabel to use create table like with page compression+general tablespace enabled table.

How to repeat:
See description

Suggested fix:
If it is a limitation, please provide much more verbose and informative error message

Hello Shahriyar,

Thank you for the report.
Verified as described with 5.7.10.

Thanks,
Umesh

Posted by developer:
 
This bug is really a problem with allowing a table to be assign compression='lz4' when that table is in a shared general tablespace.  Transparent Page Compression was intended to only work with File-Per_table Tablespaces.  The metadata and the SQL used to assign page compression is on a single table, but it is applied to all pages of the tablespace.  So Things get confused when there can be multiple tables in a tablespace.

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

Validation code for transparent page compression incorrectly permitted
innodb_strict_mode=OFF, which allowed the COMPRESSION attribute to be
applied to a general tablespace. Page compression is only supported with
file-per-table tablespaces. 

Thank you for the bug report.

Bug#80389 Irrelevant warning while altering tablespace of compression='none' table

was marked as a duplicate.

Bug#80820 and Bug#80745 were marked as duplicates.