Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am keep adding daily base partition for a table as e.g.

ALTER TABLE tblData PARTITION BY RANGE (to_days(`dataInsertDateTime`)) (
PARTITION p20 VALUES LESS THAN (to_days('2012-12-25')),
PARTITION p21 VALUES LESS THAN (to_days('2012-12-26')),
PARTITION p22 VALUES LESS THAN (to_days('2012-12-27')),
PARTITION p23 VALUES LESS THAN (to_days('2012-12-28')),
PARTITION p24 VALUES LESS THAN (to_days('2012-12-29'))

When I run this show create table tblData is only shows me the partition p20 to p24. I am wondering where is all my previous p0-p19? Is it get dissolve when each time I run the alter table to add partitions?

share|improve this question
I suspect you're actually repartitioning the entire table like that (and making the p20 partition very big). From the documentation, I think you need a REORGANIZE PARTITION clause. I'm sure a MySQL DBA will answer it soon and take my credit :) – Phil Dec 24 '12 at 1:54
@phil so what should I be doing to keep adding new partition when I need them? – newbie14 Dec 24 '12 at 2:17
As @Phil's comment, see the documentation: Management of RANGE and LIST Partitions, and the paragraph: "To add a new range or list partition to a previously partitioned table, use the ALTER TABLE ... ADD PARTITION statement. For tables ..." – ypercubeᵀᴹ Dec 24 '12 at 7:40
@ypercube so I guess I am missing ALTER TABLE tblData ADD PARTITION. Can I say I am missing the word ADD? – newbie14 Dec 25 '12 at 2:11
up vote 1 down vote accepted

You don't have plessthanmax part? When you set up a partition (that "moves" daily) the best practice is to set up a plessthanmax part to avoid errors. Each day you create a new partition and reorganise the plessthanmax like this :

For December 29

ALTER TABLE BtblData REORGANIZE PARTITION plessthanmax INTO (PARTITION p24 VALUES LESS THAN (to_days('2012-12-29')), partition plessthanmax values less than maxvalue)

For December 30

ALTER TABLE BtblData REORGANIZE PARTITION plessthanmax INTO (PARTITION p25 VALUES LESS THAN (to_days('2012-12-30')), partition plessthanmax values less than maxvalue)


share|improve this answer
so I am missing plessthanmax and reorganise right? Anything am I missing ? Sorry kind of new to partition so trying out and learning new stuff. – newbie14 Dec 25 '12 at 2:09

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.