Bug #80731 Could run alter statements for innodb, with myisam engine without error

Description:
this is not a critical situation but it is quite weird.

mysql> show create table sbtest1;

| sbtest1 | 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_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=685441 DEFAULT CHARSET=utf8 MAX_ROWS=1000000 |

mysql> alter table sbtest1 engine=myisam;
Query OK, 685440 rows affected (19.06 sec)
Records: 685440  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 encryption='y';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 compression='zlib';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 tablespace=innodb_file_per_table;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

How to repeat:
See description

Suggested fix:
Prevent running InnoDB related alter statements.
converting back to innodb and trying again will fail:

mysql> alter table sbtest1 engine=innodb;
Query OK, 685440 rows affected (1 min 31.86 sec)
Records: 685440  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 engine=myisam;
ERROR 1031 (HY000): Table storage engine for '#sql-24ec_2' doesn't have this option

Again this is not a best way to explain situation.

Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh

Posted by developer:
 
This is a legacy issue, where engine attributes are ignored when ALTER is changing engine.