Take the 2-minute tour ×
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. It's 100% free, no registration required.

We are trying some partitioning as below.

ALTER TABLE tblreceipt PARTITION BY RANGE (MONTH(receiptDate)) 
( 
PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-10-20')), 
PARTITION p2 VALUES LESS THAN (TO_DAYS('2012-10-21')), 
PARTITION p3 VALUES LESS THAN (TO_DAYS('2012-10-22')) 
); 

but end up with this Error Code: 1503 A PRIMARY KEY must include all columns in the table's partitioning function

I have done the changes now.

ALTER TABLE tblreceipt PARTITION BY RANGE (receiptDate) 
( 
PARTITION p1 VALUES LESS THAN '2012-10-20', 
PARTITION p2 VALUES LESS THAN '2012-10-21', 
PARTITION p3 VALUES LESS THAN '2012-10-22' 
);

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2012-10-20', 
PARTITION p2 VALUES LESS THAN '2012-10-21', 
PARTITION p3 VALUES ' at line 3
share|improve this question
    
The problem is that the function (MONTH()) and the LESS THAN values are incompatible. Apart from the fact that I don't see the value of using TO_DAYS() (what is the advantage there compared to a simple date?), you should use TO_DAYS(receiptDate). Or, more simply (if you want a day-by-day partitioning), just RANGE (receiptDate) ... LESS THAN '2012-10-20'. –  dezso Oct 21 '12 at 9:24
    
@dezso I have done the changes accordingly but I get a syntax error now? –  newbie14 Oct 21 '12 at 12:43
    
Then let's look at the manual, as the error message advises. Then it becomes clear that I missed the parentheses... I'll compile an answer now. –  dezso Oct 21 '12 at 12:55

1 Answer 1

The problem is that the function (MONTH()) and the LESS THAN values are incompatible.

Apart from the fact that I don't see the value of using TO_DAYS() (what is the advantage there compared to a simple date?), you should use (TO_DAYS(receiptDate)) instead of (MONTH(receiptDate)).

Or, more simply (if you want a day-by-day partitioning), I'd write just

RANGE COLUMNS (receiptDate) 
... 
LESS THAN ('2012-10-20')

See a very simple working example at SQLFiddle.

share|improve this answer
    
@dezo I tried RANGE (receiptDate) ..... LESS THAN ('2012-10-20') it gives me this error Error Code: 1064 VALUES value must be of same type as partition function near '), PARTITION p2 VALUES LESS THAN ('2012-10-21'), PARTITION p3 VALUES LESS THAN' at line 3 –  newbie14 Oct 21 '12 at 14:16
    
What type receipDate is? The code chunk I included works for date type. –  dezso Oct 21 '12 at 14:22
    
The receiptDate is of date type only not datetime. –  newbie14 Oct 21 '12 at 14:36
    
Well, I confused things once again. Edited my answer, look at the COLUMNS keyword. –  dezso Oct 21 '12 at 15:06
    
Unfortunately is still the same error Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMNS (receiptDate) ( PARTITION p1 VALUES LESS THAN ('2012-10-20'), PARTITI' at line 1 –  newbie14 Oct 21 '12 at 15:22

Your Answer

 
discard

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.