0

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?

4
  • 1
    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 ..." 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
1

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)

Max.

1
  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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