Bug #68535 data move from a partition of a partitioned table to a non partitioned table
Submitted: 1 Mar 2013 10:26 Modified: 25 Mar 2013 11:31
Reporter: rajnish kumar Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: data move from a partition of a partitioned table to a non partitioned table (N

[1 Mar 2013 10:26] rajnish kumar
Description:
Hi Team ,

Thanks for adding such a powerfull stuff in mysql , I just want to knew is there is any way so that  we can do only data move from a partition  of a partitioned table into non-partitioned table (Not Swaping) . For eg table e have 4 partitioned e1,e2,e3,e4 each has 100 rows now i just want to move e1 data to a non-partitioned(similar table structure of e) table e_cc , so that we can regural archive data from partitioned table to a non partitioned table .

How to repeat:
it is not a bug , it is just a feature enhancement request.
[1 Mar 2013 19:04] Davi Arnaut
http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html
[5 Mar 2013 9:19] rajnish kumar
Hi Davi ,
Thanks For reply ,the link you send is not relevant for my post .

I an asking for  EXCHANGE PARTITION p5 WITH TABLE t22 , this option will interchange data from table(non partition table ) t22 with partition p5 of table t11 (table t11 have partition p1 to p10).

I am asking not swap data instead of that only data move from p4 to t22 not vice ~ versa  :)
[15 Mar 2013 12:32] Shane Bester
Not sure why this won't work?
http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html
[16 Mar 2013 13:56] rajnish kumar
Hi Team,

exchanges is working , read post again to understand it.
[16 Mar 2013 17:29] Shane Bester
So, use an empty table, then you move data.  Like this:

mysql> create table t1(a int,key(a))partition by range(a)
    -> (
    ->  partition p1 values less than (10),
    ->  partition p2 values less than (20),
    ->  partition p3 values less than (30)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> create table t2(a int,key(a));
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> insert into t1 values (1),(2),(3),(11),(12),(13),(23),(24),(25);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> alter table t1 exchange partition p2 with table t2;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|   23 |
|   24 |
|   25 |
+------+
6 rows in set (0.00 sec)

mysql> select * from t2;
+------+
| a    |
+------+
|   11 |
|   12 |
|   13 |
+------+
3 rows in set (0.00 sec)
[25 Mar 2013 11:31] rajnish kumar
Hi Shane ,
Thanks for your reply , what you said is working  for first time , what about when we do it second time or more . I already mentioned theses scenario . 

I clearly mentioned that What is the solution when we want to do at a regular interval.