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.
