Bug #87603 compression/tablespace ignored in create/alter table when not using InnoDB
Submitted: 30 Aug 2017 12:19 Modified: 30 Aug 2017 13:16
Reporter: Tomislav Plavcic Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.19 OS:Any
Assigned to: CPU Architecture:Any

[30 Aug 2017 12:19] Tomislav Plavcic
Description:
Firsty I have seen bugs like: https://bugs.mysql.com/bug.php?id=67727 and https://bugs.mysql.com/bug.php?id=72033 which are similar but for different functionality.
My examples are for innodb transparent compression and tablespaces, while for example for encryption option it seems fine.

Compression is just ignored when using MyISAM even in create table. Alter table is possible for MyISAM table into InnoDB tablespace but it's just ignored.

How to repeat:
COMPRESSION (create table issue - silently ignored):
mysql [localhost] {msandbox} (test) > create table t2(a int primary key) engine=myisam compression="zlib";
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > create table t7(a int primary key) engine=myisam compression="xxx";
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > create table t6(a int primary key) engine=innodb compression="xxx";
ERROR 1031 (HY000): Table storage engine for 't6' doesn't have this option

mysql [localhost] {msandbox} (test) > create table t6(a int primary key) engine=innodb compression="zlib";
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > alter table t7 engine=innodb;
ERROR 1031 (HY000): Table storage engine for '#sql-4936_5' doesn't have this option

TABLESPACES (alter issue - silently ignored):
mysql [localhost] {msandbox} (test) > create table t1(a int primary key) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) > alter table t1 tablespace=ts1;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > create table t2(a int primary key) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > alter table t2 tablespace=ts1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > show create table t2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) /*!50100 TABLESPACE `ts1` */ ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > drop tablespace ts1;
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (test) > show create table t2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) /*!50100 TABLESPACE `ts1` */ ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > alter table t2 engine=innodb;
ERROR 1812 (HY000): InnoDB: A general tablespace named `ts1` cannot be found.

ENCRYPTION option seems to work fine (at least for MyISAM):
mysql [localhost] {msandbox} (test) > create table t1(a int primary key) engine=innodb encryption='Y';
Query OK, 0 rows affected (0.05 sec)

mysql [localhost] {msandbox} (test) > create table t2(a int primary key) engine=myisam encryption='Y';
ERROR 1031 (HY000): Table storage engine for 't2' doesn't have this option

mysql [localhost] {msandbox} (test) > alter table t1 engine=myisam;
ERROR 1031 (HY000): Table storage engine for '#sql-4aa5_3' doesn't have this option

Suggested fix:
Fix the options handling in create/alter table for different SE's so that user experience is better.
[30 Aug 2017 13:16] Umesh Shastry
Hello Tomislav,

Thank you for the report and test case.
Verified as described with 5.7.19.

Thanks,
Umesh