Bug #34671 No error message for invalid storage engine entry.

• Submitted: 19 Feb 2008 16:54
• Modified: 19 Feb 2008 19:11
• Reporter: Hema Sridharan
• Status: Not a Bug
• Category: Tests: Engine
• Severity: S3 (Non-critical)
• Version: 6.0.5-alpha-debug
• OS: Any
• CPU Architecture: Any

[19 Feb 2008 16:54] Hema Sridharan

Description:
1) I created a database, table and inserted few rows in it.
2) I assigned a invalid engine name while creating table.
3) The SQL query accepts but if we check in show create table table_name , the default storage engine is taken.

While creating table if we specify invalid storage engine, it should error out saying "enter valid storage engine" or "invalid entry of storage engine"

How to repeat:
1)Create table bt7

mysql> create table bt7(C1 int,C2 char)engine=yyyyyy;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

The sql query is positive and the table is created.

2)

mysql> show create table bt7;
+-------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------+
| bt7   | CREATE TABLE `bt7` (
  `C1` int(11) DEFAULT NULL,
  `C2` char(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

From the above command ,we can check that MyIsam default storage engine is assigned for the table bt7.

3)Though we enter the invalid storage engine, the sql query does not error out and instead gets assigned with default storage engine.

Suggested fix:
The command should error out while giving invalid storage engine name.

[19 Feb 2008 19:11] MySQL Verification Team

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

http://dev.mysql.com/doc/refman/6.0/en/server-sql-mode.html

"#

NO_ENGINE_SUBSTITUTION

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:

With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable."

mysql> set sql_mode=NO_ENGINE_SUBSTITUTION;
Query OK, 0 rows affected (0.03 sec)

mysql> create table bt7(C1 int,C2 char)engine=yyyyyy;
ERROR 1286 (42000): Unknown table engine 'yyyyyy'
mysql>