MySQL Forums
Forum List  »  Partitioning

EXCHANGE PARTITION and DATA DIRECTORY errors
Posted by: Ekundayo Lasode
Date: August 16, 2014 09:47AM

I've recently run into a problem while using the new EXCHANGE PARTITION feature with the DATA DIRECTORY CLAUSE while moving partitions (version 5.6.17). I'm not sure if this is a bug or I've missed a trick.

Given 3 tables:

1)
test_live.a_live => partitioned, residing in default data directory /var/lib/mysql

2)
test_stg.a_stg => non-partitioned to enable partition switch with "EXCHANGE PARTITION" keyword .This table is also in the default data directory

3)
test_arc.a_arc => partitioned with defined DATA DIRECTORY clause '/mysql/data' to enable storing data on a dedicated disk for archive


The below sequence of commands to move a partition p0 from table test_live.a_live, via table test_stg.a_stg to table test_arc.a_arc results in an error (see below)

mysql>ALTER TABLE test_live.a_live EXCHANGE PARTITION p0 WITH TABLE test_stg.a_stg;
Query OK, 0 rows affected (0.03 sec)

mysql>ALTER TABLE test_arc.a_arc EXCHANGE PARTITION p0 WITH TABLE test_stg.a_stg;
ERROR 1731 (HY000): Non matching attribute 'DATA DIRECTORY' between partition and table

It seems you can't move a partition from table x to table y if either table resides in a separate data directory (using the DIRECTORY DIRECTORY clause). Unfortunately, the motivation for using this functionality is to be able to move archive partitions to storage on separate disks based on defined retention policies.

Is there a work around for this or is it a bug in this version?

Options: ReplyQuote


Subject
Views
Written By
Posted
EXCHANGE PARTITION and DATA DIRECTORY errors
4142
August 16, 2014 09:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.