Bug #6068 Traditional: CREATE TABLE with invalid storage engine should result in an error
Submitted: 13 Oct 2004 17:02 Modified: 4 Aug 2005 22:47
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.2-alpha-debug OS:Linux (SuSE 9.1)
Assigned to: CPU Architecture:Any

[13 Oct 2004 17:02] Trudy Pelzer
Description:
When sql_mode='traditional', we would like to return an error
in situations where syntax is used that is invalid for the MySQL
version.

For example, in default mode, a CREATE TABLE statement 
that specifies a storage engine which is not compiled in results
in a MyISAM table. In "traditional" mode, this should return an
error and the table should not be created. That is (assume BDB
tables are not available):
SET SQL_MODE='TRADITIONAL';
CREATE TABLE t1 (col1 INT) ENGINE=BDB;
-- should return SQLSTATE HY000 BDB storage engine not compiled in
(or a similar message)

How to repeat:
Assume BDB tables are not compiled in.

mysql> create table t1 (col1 int) engine=bdb;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1266 | Using storage engine MyISAM for table 't1' |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode='traditional';

mysql> create table t2 (col1 int) engine=bdb;
Query OK, 0 rows affected, 1 warning (0.11 sec)
-- This is the incorrect result. The table should not be created.

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1266 | Using storage engine MyISAM for table 't2' |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
[4 Aug 2005 22:47] Jim Winstead
The NO_ENGINE_SUBSTITUTION SQL mode does this.