| Bug #35765 | ALTER TABLE produces wrong error when non-existent storage engine used | ||
|---|---|---|---|
| Submitted: | 2 Apr 2008 8:02 | Modified: | 7 Mar 2010 18:49 |
| Reporter: | Jon Stephens | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.1.24-bk (2008-03-30) | OS: | Linux (OpenSUSE 10.2 32-bit) |
| Assigned to: | Mattias Jonsson | CPU Architecture: | Any |
| Tags: | ALTER TABLE, engine, errors, partitioning | ||
[2 Apr 2008 12:01]
MySQL Verification Team
Thank you for the bug report.
c:\dbs>5.1\bin\mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.25-rc-nt-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database db1;
Query OK, 1 row affected (0.03 sec)
mysql> use db1
Database changed
mysql> CREATE TABLE `part1` (
-> `c1` int(11) NOT NULL AUTO_INCREMENT,
-> `c2` int(11) NOT NULL,
-> PRIMARY KEY (`c1`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=1409286290 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY
-> HASH (c1 div 3) PARTITIONS 4 */;
Query OK, 0 rows affected (0.13 sec)
mysql> ALTER TABLE part1 ENGINE = InnDB;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL
mysql>
[7 Jul 2008 17:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49119 2693 Mattias Jonsson 2008-07-07 Bug#35765 ALTER TABLE produces wrong error when non-existent storage engine used The behavior was because of 12345678901234567890123456789012345678901234567890123456789012345678901234567890
[7 Jul 2008 17:49]
Mattias Jonsson
Oops, missed the global commit comment :) will recommit...
[7 Jul 2008 17:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49121 2693 Mattias Jonsson 2008-07-07 Bug#35765 ALTER TABLE produces wrong error when non-existent storage engine used Problem was that the parser flagged that the engine was specified in the command, but it was not (it was still set to UNKNOWN engine). Solution was to not set the flag if a non existent engine was specified, which is consistent of the non partitioned alter.
[13 Aug 2008 16:06]
Mattias Jonsson
Pushed into mysql-6.0-bugteam
[13 Sep 2008 20:02]
Bugs System
Pushed into 6.0.6-alpha (revid:mattiasj@mysql.com-20080707175450-6pta89uvvbmlu0u3) (version source revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (pib:3)
[15 Sep 2008 14:11]
Jon Stephens
Documented in the 6.0.6 changelog as follows:
When an attempt is made to change a table to an unsupported storage
engine, the server normally uses the default storage engine in place of
the requested engine while issuing a warning. However, if the table was
partitioned, the same ALTER TABLE statement failed with the error, The
mix of handlers in the partitions is not allowed in this version of
MySQL. This happened even if the server was not running in
NO_ENGINE_SUBSTITUTION mode. Now the behavior for partitioned tables is
the same as for other MySQL tables; the substitution is made, and a
warning is issued.
[15 Sep 2008 14:12]
Jon Stephens
Will this be pushed to 5.1 as well?
[20 Nov 2009 12:59]
Mattias Jonsson
pushing to mysql-next-mr-bugfixing too
[24 Nov 2009 11:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/91397 2947 Mattias Jonsson 2009-11-24 merge of bug#35765 into mysql-next-mr-bugfixing @ mysql-test/r/log_tables.result Bug #35765 ALTER TABLE produces wrong error when non-existent storage engine used Updated result @ mysql-test/r/partition.result Bug #35765 ALTER TABLE produces wrong error when non-existent storage engine used Updated result @ mysql-test/r/partition_innodb.result Bug #35765 ALTER TABLE produces wrong error when non-existent storage engine used Updated result @ mysql-test/t/log_tables.test Bug #35765 ALTER TABLE produces wrong error when non-existent storage engine used Updated test @ mysql-test/t/partition.test Bug #35765 ALTER TABLE produces wrong error when non-existent storage engine used Added test case @ mysql-test/t/partition_innodb.test Bug #35765 ALTER TABLE produces wrong error when non-existent storage engine used Updated test @ sql/protocol.cc Bug #35765 ALTER TABLE produces wrong error when non-existent storage engine used (fix of bug#48939 to avoid test failures on my test build). @ sql/sql_yacc.yy Bug #35765 ALTER TABLE produces wrong error when non-existent storage engine used if no existing engine was given, don't set HA_CREATE_USED_ENGINE
[25 Nov 2009 9:42]
Mattias Jonsson
pushed to mysql-next-mr-bugfixing
[11 Dec 2009 6:02]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)
[11 Dec 2009 6:05]
Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)
[11 Dec 2009 16:59]
Jon Stephens
Documented bugfix in the 5.6.0 and 6.0.14 changelogs as follows:
When used on a partitioned table, ALTER TABLE produces the wrong
error message when the name of a nonexistent storage engine was
used in the ENGINE clause.
Closed.
[6 Mar 2010 11:09]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)
[7 Mar 2010 18:49]
Paul DuBois
Moved 5.6.0 changelog entry to 5.5.3.
Description: mysql> SHOW CREATE TABLE part1\G *************************** 1. row *************************** Table: part1 Create Table: CREATE TABLE `part1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=MyISAM AUTO_INCREMENT=1409286290 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1 div 3) PARTITIONS 4 */ 1 row in set (0.00 sec) mysql> ALTER TABLE part1 ENGINE = InnDB; ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL mysql> ALTER TABLE part1 ENGINE = InnoDB; Query OK, 443558 rows affected (30.51 sec) Records: 443558 Duplicates: 0 Warnings: 0 CREATE TABLE using partitioning and an unknown storage engine produces the expected behaviour: mysql> CREATE TABLE t2 (c1 INT) ENGINE=InnDB PARTITION BY HASH(c1) PARTITIONS 4; Query OK, 0 rows affected, 2 warnings (0.11 sec) mysql> SHOW WARNINGS; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1286 | Unknown table engine 'InnDB' | | Warning | 1266 | Using storage engine MyISAM for table 't2' | +---------+------+--------------------------------------------+ 2 rows in set (0.00 sec) See also Suggested fix. How to repeat: See Description. Suggested fix: The ALTER TABLE statement referencing an unknow storage should behave in the same way for partitioned tables as it does for non-partitioned tables, e.g.: mysql> CREATE TABLE a1 (c1 INT); Query OK, 0 rows affected (0.11 sec) mysql> SHOW CREATE TABLE a1\G *************************** 1. row *************************** Table: a1 Create Table: CREATE TABLE `a1` ( `c1` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> ALTER TABLE a1 ENGINE=InnDB; Query OK, 0 rows affected, 1 warning (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW WARNINGS; +---------+------+------------------------------+ | Level | Code | Message | +---------+------+------------------------------+ | Warning | 1286 | Unknown table engine 'InnDB' | +---------+------+------------------------------+ 1 row in set (0.00 sec)