Description:
ALTER TABLESPACE ADD DATAFILE can omit the ENGINE clause.
MySQL will try and substitute the ENIGNE with the default engine even when NO_ENGINE_SUBSTITUTION is specified in the sql_mode. The error occurs only under specific circumstances:
The ENGINE clause must be omitted
The INITIAL_SIZE clause must be included.
Specifying a nonexistent ENGINE does show the desired behaviour (error message)
Ommitting both the INITIAL_SIZE clause as well as the ENGINE clause results in a syntax error
How to repeat:
mysql> select @@sql_mode;
+------------------------+
| @@sql_mode |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)
mysql> alter tablespace ts1 ADD datafile 'data02.dat' INITIAL_SIZE 1M ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------+
| Error | 1466 | Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' |
+-------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table k (i int)engine = k;
ERROR 1286 (42000): Unknown table engine 'k'
mysql> alter tablespace ts1 ADD datafile 'data02.dat' INITIAL_SIZE 1M engine = k;
ERROR 1286 (42000): Unknown table engine 'k'
mysql> alter tablespace ts1 ADD datafile 'data02.dat';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Suggested fix:
PLease generate an error when NO_ENGINE_SUBSTITUTION is included in the sql_mode and the ENGINE clause is omitted from the ALTER TABLESPACE statement.
Alternatively, make it an syntax error to omit the ENGINE clause from the ALTER TABLESPACE statement.
