Recreate partition in mysql I have a table having datetime column partitioned by RANGE 


  (DAYOFYEAR(datetime_col))


By this I want to drop oldest of 4th week partition data and recreate immediately for next year similar week inserts.

I.E. I use Mysql 5.6.19-67.0, the partitions are by week

(PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (71) ENGINE = InnoDB,
...
....
 PARTITION p49 VALUES LESS THAN (344) ENGINE = InnoDB,
 PARTITION p50 VALUES LESS THAN (351) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);


Now I want to retain the structure by removing 4th week prior DATA ONLY . But I want to see the structure there WITHOUT data for that partition.


  1st Option: alter table t1 truncate partition p4 
  
  2nd Option: Drop the partition and add partition to recreate it.


Issue in 1st Option: I'm afraid to issue this, as this doesn't drop the partition and recreate it. It goes for delete from partition where datetime_col &lt; '2013-11-12 00:00:00'. Not sure how much CPU or undo buffers this will create in a 500G partition table. 

Issue in 2nd Option: No issues, but it just won't work. 

ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition 

Anyother recommendations are most welcome!!
Running TRUNCATE PARTITION works fine

DROP DATABASE IF EXISTS partest;
CREATE DATABASE partest;
USE partest
CREATE TABLE rolando (
  id int not null auto_increment,
  LogDate DATE,
  PRIMARY KEY (id,LogDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
(PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
ALTER TABLE rolando TRUNCATE PARTITION p4;


Here is the output

mysql&gt; DROP DATABASE IF EXISTS partest;
Query OK, 1 row affected (1.55 sec)

mysql&gt; CREATE DATABASE partest;
Query OK, 1 row affected (0.00 sec)

mysql&gt; USE partest
Database changed
mysql&gt; CREATE TABLE rolando (
    -&gt;   id int not null auto_increment,
    -&gt;   LogDate DATE,
    -&gt;   PRIMARY KEY (id,LogDate)
    -&gt; ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -&gt; PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
    -&gt; (PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
    -&gt;  PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
    -&gt;  PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
    -&gt;  PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
    -&gt;  PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
    -&gt;  PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
    -&gt;  PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
    -&gt;  PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
    -&gt;  PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
    -&gt;  PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
    -&gt;  PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (2.60 sec)

mysql&gt; ALTER TABLE rolando TRUNCATE PARTITION p4;
Query OK, 0 rows affected (0.25 sec)

mysql&gt;


OK, fine. It works. However, doing this is logically unsafe. Why ? The partitioned table is based on DAYOFYEAR(datetime_col)). Since each partition contains a week for multiple years, running alter table t1 truncate partition p4; would wipe out week 4 from multiple years.

OK, let's try dropping and adding the partition

mysql&gt; ALTER TABLE rolando DROP PARTITION p4;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql&gt; ALTER TABLE rolando ADD PARTITION
    -&gt; (PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB);
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
mysql&gt;


This makes sense since MAXVALUE already exists in the definition. This prevents you from adding (appending) a partition if MAXVALUE is present.

While you could play games with ALTER TABLE ... REORGANIZE PARTITION, the complexity isn't worth it and you won't achieve the result of wedging in a partition.

Rather that explanining further partition machinations, just do the following


Make sure an index exists on the date field
Perform the DELETE SQL already mentioned


An alternative would be to create a table with a weekly partition then drop the exact week.