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.
