Bug #73084 Exchanging partitions defined with DATA DIRECTORY and INDEX DIRECTORY options
Submitted: 23 Jun 2014 18:10
Reporter: John Dzilvelis Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.6 OS:Linux (3.5.0-17-generic #28-Ubuntu SMP Tue Oct 9 19:31:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to: CPU Architecture:Any

[23 Jun 2014 18:10] John Dzilvelis
Description:
Table partitions defined with DATA DIRECTORY and INDEX DIRECTORY options cannot be exchanged with identically defined, non-partitioned tables. Attempting to do so results in the error: "ERROR 1736 (HY000) at line 40: Tables have different definitions"  

The only workaround that I am aware of is to use ALTER TABLE...REORGANIZE PARTITION to move individual partitions to the schema's default data directory. 

In very large environments, a single storage device is not feasible. The added flexibility of allowing partition exchanges in the same non-default data directory would minimize unnecessary data movements during maintenance tasks. 

How to repeat:
use dzdb  ;
drop table if exists jd_test_partition ;
drop table if exists jd_test ;

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

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

create table jd_test ( id int not null ) engine=MyISAM  data directory = '/disk2/mysql/data'  index directory = '/disk2/mysql/data'  ;

alter table jd_test_partition exchange partition p0 with table jd_test ;
-- ERROR 1736 (HY000) at line 39: Tables have different definitions
-- ----------------------------
-- This is the work around : 
-- ----------------------------

drop table if exists jd_test ;
create table jd_test ( id int not null ) engine=MyISAM  ;
alter table jd_test_partition reorganize partition p0 into ( partition p0 engine=MyISAM ) ;
alter table jd_test_partition exchange partition p0 with table jd_test ;
select * from jd_test ;

select table_schema , table_name , partition_name , table_rows
from information_schema.partitions where table_name = "jd_test_partition" ;

+----+
| id |
+----+
|  0 |
|  5 |
+----+
+--------------+-------------------+----------------+------------+
| table_schema | table_name        | partition_name | table_rows |
+--------------+-------------------+----------------+------------+
| dzdb         | jd_test_partition | p0             |          0 |
| dzdb         | jd_test_partition | p1             |          2 |
| dzdb         | jd_test_partition | p2             |          2 |
| dzdb         | jd_test_partition | p3             |          2 |
| dzdb         | jd_test_partition | p4             |          2 |
+--------------+-------------------+----------------+------------+

Suggested fix:
Allow partition exchanges on otherwise identical tables when the DATA DIRECTORY / INDEX DIRECTORY options are used.
[21 Jul 2015 21:42] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=77772 marked as duplicate of this one.