Recreate partition in mysql

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> DROP DATABASE IF EXISTS partest;
Query OK, 1 row affected (1.55 sec)

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

mysql> USE partest
Database changed
mysql> 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);
Query OK, 0 rows affected (2.60 sec)

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

mysql>

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> ALTER TABLE rolando DROP PARTITION p4;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

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.