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:
None 
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
Triage: D4 (Minor)

[2 Apr 2008 8:02] Jon Stephens
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)
[2 Apr 2008 12:01] Miguel Solorzano
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.