Bug #77772 If DATA DIRECTORY is defined for a partition or table, exchange is not possible
Submitted: 17 Jul 2015 23:32 Modified: 21 Jul 2015 21:41
Reporter: Geoff Montee Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.6.23 OS:CentOS
Assigned to: CPU Architecture:Any

[17 Jul 2015 23:32] Geoff Montee
Description:
If DATA DIRECTORY is defined for a partition, it is not possible to exchange the partition with the non-partitioned table, even if DATA DIRECTORY is set to the same value for each.

The error results in this misleading error message:

ERROR 1731 (HY000): Non matching attribute 'DATA DIRECTORY' between partition and table

There is no mention of this limitation here:

https://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html

How to repeat:
First, create a partitioned table with DATA DIRECTORY defined and insert some data:

create table test_partitioned (
id int not null
) PARTITION BY HASH ( id )
( partition p0 engine=InnoDB data directory = '/disk2/mysql/data',
partition p1 engine=InnoDB data directory = '/disk2/mysql/data',
partition p2 engine=InnoDB data directory = '/disk2/mysql/data',
partition p3 engine=InnoDB data directory = '/disk2/mysql/data',
partition p4 engine=InnoDB data directory = '/disk2/mysql/data') ;

insert into test_partitioned
values ( 0 ) , ( 1) , ( 2 ) , ( 3 ) , ( 4 ) , ( 5 ) , ( 6 ) , ( 7 ) , ( 8 ) , ( 9 ) ;

Then, create a non-partitioned table with DATA DIRCTORY defined:

create table test_nonpartitioned ( id int not null ) engine=InnoDB data directory = '/disk2/mysql/data' ;

Then try to exchange the partitions:

alter table test_partitioned exchange partition p0 with table test_nonpartitioned ;

At this point, you will receive an error:

mysql> alter table test_partitioned exchange partition p0 with table test_nonpartitioned ;
ERROR 1731 (HY000): Non matching attribute 'DATA DIRECTORY' between partition and table

Suggested fix:
Allow partitions and tables to be exchanged, even if DATA DIRECTORY is set.
[21 Jul 2015 21:41] Miguel Solorzano
Thank you for the bug report. Related to http://bugs.mysql.com/bug.php?id=73084.