MySQL Bugs: #31931: Partitions: unjustified 'mix of handlers' error message

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

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

Thank you for the bug report. Verified as described.

Workaround is to create the table with the
desired partitions.

Will not fix the problem with error messages for specified/not specified engines on subpartitions, moved that to Bug#33722.

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)

pushed into mysql-5.1-engines and mysql-6.0-engines

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.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

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.

Also noted fix in 5.1.23-ndb-6.3.11 changelog.