Bug #31931 Partitions: unjustified 'mix of handlers' error message
Submitted: 29 Oct 2007 23:47 Modified: 28 Mar 2008 15:56
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:6.0.4-alpha-debug/5.1 OS:Linux (SUSE 10 64-bit)
Assigned to: Mattias Jonsson CPU Architecture:Any

[29 Oct 2007 23:47] Peter Gulutzan
Description:
I create a table with two partitions, defining one of
the partitions with engine=myisam, which is the default.
Success.

I alter the table to have two partitions, defining one of
the partitions with engine=myisam, which is the default
-- that is, exactly the same clause as I used for CREATE.
Failure.

How to repeat:
mysql> set @@storage_engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tf (s1 int) partition by list (s1) (partition p1 values in (1) engine=myisam, partition p2 values in (2));
Query OK, 0 rows affected (0.05 sec)

mysql> alter table tf partition by list (s1) (partition p1 values in (1) engine=myisam, partition p2 values in (2));
ERROR 1496 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL
[29 Oct 2007 23:56] MySQL Verification Team
Thank you for the bug report. Verified as described.
[20 Dec 2007 16:21] Trudy Pelzer
Workaround is to create the table with the
desired partitions.
[7 Jan 2008 14:43] Mattias Jonsson
Will not fix the problem with error messages for specified/not specified engines on subpartitions, moved that to Bug#33722.
[9 Jan 2008 12:16] 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/40749

ChangeSet@1.2650, 2008-01-09 13:15:50+01:00, mattiasj@client-10-129-10-137.upp.off.mysql.com +14 -0
  Bug#31931 Partitions: unjustified 'mix of handlers' error message
  Problem was that the mix of handlers was not consistent between
  CREATE and ALTER
  
  changed so that it works like:
      - All partitions must use the same engine
        AND it must be the same as the table.
      - if one does NOT specify an engine on the table level
        then one must either NOT specify any engine on any
        partition/subpartition OR for ALL partitions/subpartitions
  
  Note: that after a table have been created, the storage engine
  is specified for all parts of the table (table/partition/subpartition)
  and so when using alter, one does not need to specify it (unless one
  wants to change the storage engine, then one have to specify it on the
  table level)
[24 Feb 2008 20:19] Mattias Jonsson
pushed into mysql-5.1-engines and mysql-6.0-engines
[25 Feb 2008 20:19] 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/42953

ChangeSet@1.2528, 2008-02-25 21:18:50+01:00, mattiasj@witty. +12 -0
  Post push fix
  
  Fixed a missed case in the patch for Bug#31931.
  Also makes Bug#33722 a duplicate of Bug#31931.
  Added tests for better coverage.
  Replaced some legacy function calls.
[27 Mar 2008 11:21] Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 17:53] Bugs System
Pushed into 6.0.5-alpha
[28 Mar 2008 10:07] Jon Stephens
Documented bugfix in the 5.1.24 and 6.0.5 changelogs as follows:

        Currently, all partitions of a partitioned table must use the same
        storage engine. One may optionally specify the storage engine on a 
        per-partition basis; however, where this is the done, the storage
        engine must be the same as used by the table as a whole. ALTER TABLE did
        not enforce these rules correctly, the result being that incaccurate 
        error messages were shown when trying to use the statement to change the
        storage engine used by an individual partition or partitions.

Also updated partitioning-limitations-storage-engines section of the manual to clarify the limitation using info supplied in developer comments.
[31 Mar 2008 14:26] Jon Stephens
Also noted fix in 5.1.23-ndb-6.3.11 changelog.