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: | |
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 |
[29 Oct 2006 21:12]
Giuseppe Maxia
[30 Oct 2006 11:26]
Morgan Tocker
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)
[30 Oct 2006 12:22]
Giuseppe Maxia
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.
[30 May 2007 3:53]
Antony Curtis
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.
[1 Jun 2007 21:12]
Giuseppe Maxia
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 | +---------+------------------------+------------+--------+---------+
[1 Jun 2007 21:15]
Giuseppe Maxia
test cases for 5.0 (desirable) and 5.1 (current wrong behavior)
Attachment: bug23755_test.tar.gz (application/x-tar, text), 1.21 KiB.
[2 Jun 2007 8:04]
Sergei Golubchik
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.
[6 Jun 2007 15:37]
Paul DuBois
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.