How to make partitioning in existing mysql table?

Question

I have a table called tbl_rtdata. I was dropped the all partitions in that table and create the new partition in the below manner.

ALTER TABLE tbl_rtdata  PARTITION BY RANGE (Month(fld_date))

SUBPARTITION BY HASH (Day(fld_date)) SUBPARTITIONS 12(   
PARTITION Apr_0 VALUES LESS THAN (2012-05-01),
PARTITION May_1 VALUES LESS THAN (2012-06-01),    
PARTITION Jun_2 VALUES LESS THAN (2012-07-01),  
PARTITION Jul_3 VALUES LESS THAN (2012-08-01),
PARTITION Aug_4 VALUES LESS THAN (2012-09-01),
PARTITION Sep_5 VALUES LESS THAN (2012-10-01),
PARTITION Oct_6 VALUES LESS THAN (2012-11-01),  
PARTITION Nov_7 VALUES LESS THAN (2012-12-01),
PARTITION Dec_8 VALUES LESS THAN MAXVALUE );

But I got an Error : VALUES LESS THAN value must be strictly increasing for each partition.

If I remove the subpartition in query it shows the error VALUES value for partition 'Apr_0' must have type INT

What I should do to recover from this ?

which version of MySQL are you using?

Omesh
– Omesh

2012-08-08 07:26:05 +00:00
Commented Aug 8, 2012 at 7:26 — Omesh
– Omesh, Commented Aug 8, 2012 at 7:26

Dhileepan · Accepted Answer · 2012-08-10 11:58:11Z

6

I found the answer which is very simple, use to_days function

ALTER TABLE tbl_rtdata  PARTITION BY RANGE (Month(fld_date))
(   
PARTITION p_Apr VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p_May VALUES LESS THAN (TO_DAYS('2012-06-01')), 
PARTITION p_Jun VALUES LESS THAN (TO_DAYS('2012-07-01')),  
PARTITION p_Jul VALUES LESS THAN (TO_DAYS('2012-08-01')),
PARTITION p_Aug VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION p_Sep VALUES LESS THAN (TO_DAYS('2012-10-01')),
PARTITION p_Oct VALUES LESS THAN (TO_DAYS('2012-11-01')),  
PARTITION p_Nov VALUES LESS THAN (TO_DAYS('2012-12-01')),
PARTITION p_Dec VALUES LESS THAN MAXVALUE );

edited Aug 10, 2012 at 11:58

answered Aug 10, 2012 at 11:48

Dhileepan

2,0579 gold badges24 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Janis Peisenieks Over a year ago

Are you sure that this works as intended? By what I can see, All of the values would (almost) always be put in p_Apr since Month(fld_date) would always return a value that is less than TO_DAYS('2012-05-01')

Rick James Over a year ago

It should be BY RANGE(TO_DAYS(fld_date)).

Rick James Over a year ago

If you will be dropping old partitions, see mysql.rjweb.org/doc.php/partitionmaint for details on how to do it.

Omesh · Accepted Answer · 2012-08-08 07:55:33Z

2

I think it's not supported in MySQL < 5.5 try this: Manual here

ALTER TABLE tbl_rtdata PARTITION BY RANGE COLUMNS (fld_date) (
  PARTITION Apr_0 VALUES LESS THAN ('2012-05-01'),
  PARTITION May_1 VALUES LESS THAN ('2012-06-01'),
  PARTITION Dec_8 VALUES LESS THAN (MAXVALUE)
  );

edited Aug 8, 2012 at 7:55

answered Aug 8, 2012 at 7:18

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

1 Comment

Dhileepan Over a year ago

It's returning Error Code: 1697. VALUES value for partition 'Apr_0' must have type INT

Collectives™ on Stack Overflow

How to make partitioning in existing mysql table?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related