MySQL Bugs: #23755: ALTER TABLE accepts unknown engines without issuing an error

Bug #23755	ALTER TABLE accepts unknown engines without issuing an error
Submitted:	29 Oct 2006 21:12	Modified:	2 Jun 2007 8:04
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.1.3 to 5.1.20	OS:	Any (Linux,Mac OSX)
Assigned to:	Antony Curtis	CPU Architecture:	Any
Tags:	ALTER TABLE, engine

Description:
ALTER TABLE ... ENGINE=unknown is accepted without error.
Previous versions reject the statement with a clear error message. 
In 5.1.12 and 5.1.13, the statement is accepted with only a warning.

select version();
+-------------+
| version()   |
+-------------+
| 5.1.13-beta |
+-------------+

drop table if exists t1;
Query OK, 0 rows affected, 0 warning (0.00 sec)

create table t1 (i int) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

alter table t1 engine = donald_duck;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

show warnings;
+-------+------+------------------------------------+
| Level | Code | Message                            |
+-------+------+------------------------------------+
| Error | 1286 | Unknown table engine 'donald_duck' |
+-------+------+------------------------------------+

Notice that the level of the warning is 'error', and it is the same error that previously rejected the statement.

How to repeat:
select version();
drop table if exists t1;
create table t1 (i int) engine=MyISAM;
alter table t1 engine = donald_duck;
show warnings;

Suggested fix:
Revert to the previous behavior, when a non-existing engine causes an error.

Hi Giuseppe,

What sql_mode are you running in?
i.e. mysql> select @@sql_mode;

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

NO_ENGINE_SUBSTITUTION

Prevents automatic substitution of the default storage engine when a statement such as CREATE TABLE specifies a storage engine that is disabled or not compiled in. (Implemented in MySQL 5.0.8)

Hi Morgan,
Thanks for pointing me to this directive.

Using this specific SQL mode, the server issues an error instead of a warning.

However, there are three points to note here:

- the action of NO_ENGINE_SUBSTITUTION, which was implemented in 5.0.8, 
  is more than what the manual says. There is no engine substitution in this
  case, no matter what the SQL mode is set to. The only difference is that
  in one case the statement is refused, and in the other case the statement is 
  accepted but ignored. So this directive is not "preventing" anything.

- this unexpected behavior exists only with ALTER TABLE, while CREATE TABLE 
  rejects the statement with an error, regardless of the SQL mode.

- there is still a difference between 5.0.x and 5.1.x (I tested with versions 
  as early as 5.0.17 and 5.1.3) which is unaccounted for.
  In 5.0.x, the server refuses the statement with an error, even in "empty" SQL
  mode. in 5.1.x it rejects the statement only if SQL mode is set to
  NO_ENGINE_SUBSTITUTION.

If this is expected behavior, then the manual entry for ALTER TABLE should be updated, and the description for NO_ENGINE_SUBSTITUTION should be enhanced to inform of this side effect.

Easily fixed but will result in an incompatible change of behaviour.
However, when sqlmode NO_ENGINE_SUBSTITUTION is in effect, ALTER TABLE will abort if the specified storage engine does not exist.

Reopening this bug, because it breaks compatibility with 5.0.

When changing a storage engine (via ALTER TABLE) there are several cases, depending on the SQL_MODE. The result depends on the requested engine, which can be a valid but unavailable (e.g. InnoDB) one or a totally non-existent engine (e.g. XXXX).

The behavior changes from 5.0 to 5.1. In 5.0 the server will always reject, with an error a non-existing engine.
For existing but non-available engines, 5.0 uses the default engine, while 5.1 ignores it and issues an error.

The following table shows the behavior in detail.

Default engine = MyISAM
InnodB = not available

behavior of 5.0
+---------+------------------------+------------+--------+---------+
|  base   | SQL_MODE               |  requested | given  | result  |
|  engine |                        |  engine    | engine |         |
+---------+------------------------+------------+--------+---------+
| MyISAM  | ''                     | XXXX       | MyISAM | error   | !
| CSV     | ''                     | XXXX       | CSV    | error   | !
| MyISAM  | ''                     | InnoDB     | MyISAM | warning | 
| CSV     | ''                     | InnoDB     | MyISAM | warning | !
| MyISAM  | NO_ENGINE_SUBSTITUTION | XXXX       | MyISAM | error   |
| CSV     | NO_ENGINE_SUBSTITUTION | XXXX       | CSV    | error   |
| MyISAM  | NO_ENGINE_SUBSTITUTION | InnoDB     | MyISAM | error   |
| CSV     | NO_ENGINE_SUBSTITUTION | InnoDB     | CSV    | error   |
+---------+------------------------+------------+--------+---------+

behavior of 5.1
+---------+------------------------+------------+--------+---------+
|  base   | SQL_MODE               |  requested | given  | result  |
|  engine |                        |  engine    | engine |         |
+---------+------------------------+------------+--------+---------+
| MyISAM  | ''                     | XXXX       | MyISAM | warning | ?
| CSV     | ''                     | XXXX       | CSV    | warning | ?
| MyISAM  | ''                     | InnoDB     | MyISAM | warning |
| CSV     | ''                     | InnoDB     | CSV    | warning | ?
| MyISAM  | NO_ENGINE_SUBSTITUTION | XXXX       | MyISAM | error   |
| CSV     | NO_ENGINE_SUBSTITUTION | XXXX       | CSV    | error   |
| MyISAM  | NO_ENGINE_SUBSTITUTION | InnoDB     | MyISAM | error   |
| CSV     | NO_ENGINE_SUBSTITUTION | InnoDB     | CSV    | error   |
+---------+------------------------+------------+--------+---------+

test cases for 5.0 (desirable) and 5.1 (current wrong behavior)

Attachment: bug23755_test.tar.gz (application/x-tar, text), 1.21 KiB.

This is an intentional change.

MySQL used to treat "unknown" storage engine and "know but not compiled in" differently. As storage engines can be now pluggable run-time, this distinction makes no sense anymore. Now MySQL treats all "not available" storage engines identically and consistently.

I have updated:

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

to provide additional behavior on the effect of NO_ENGINE_SUBSTITUTION
for CREATE/ALTER TABLE, particularly the way in which the effect
changes in 5.1.12.