Recreate partition in mysql

Question

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 < '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!!

Answer 1

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.