Bug #27886 ALTER TABLESPACE ADD DATAFILE does not honor NO_ENGINE_SUBSTITUTION
Submitted: 17 Apr 2007 15:26 Modified: 5 Dec 2007 18:56
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Disk Data Severity:S2 (Serious)
Version:mysql-5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: 5.1.16, SQL_MODE
Triage: Triaged: D3 (Medium)

[17 Apr 2007 15:26] Roland Bouman
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.
[17 Apr 2007 15:35] Jon Stephens
Verified as described with latest 5.1.18-bk, Linux 32-bit and 64-bit.

In addition, it is horribly, horribly wrong to say "Query OK, 1 Warning" if the statement *fails* and the data file is not created.