Tuesday 2 December 2014

mysql VALUES LESS THAN value must be strictly increasing for each partition SOLVED!

In case that you are doing reorganization of your mysql table (I talk about it in my previous posts) you may run in to this error message

ERROR 1493 (HY000) at line 1: VALUES LESS THAN value must be strictly increasing for each partition

When I reorganise partitions I like to do in from OS, something like this

server# mysql Syslog < reorganize.txt

where reorganise.txt is mysql commands that look like this

server# cat reorganise.txt
alter table SystemEvents reorganize partition p2014 into
 ( partition p20141201 values less than (to_days('2014-12-01')),
   partition p20141202 values less than (to_days('2014-12-02')),
   partition p20141203 values less than (to_days('2014-12-03')),
   partition p20141204 values less than (to_days('2014-12-04')),
   partition p20141205 values less than (to_days('2014-12-05')),
   partition p20141206 values less than (to_days('2014-12-06')),
   partition p20141207 values less than (to_days('2014-12-07')),
   partition p20141208 values less than (to_days('2014-12-08')),
   partition p20141209 values less than (to_days('2014-12-09')),
   partition p20141210 values less than (to_days('2014-10-10')),
   partition p20141211 values less than (to_days('2014-12-11')),
   partition p20141212 values less than (to_days('2014-12-12')),
   partition p20141213 values less than (to_days('2014-12-13')),
   partition p20141214 values less than (to_days('2014-12-14')),
   partition p20141215 values less than (to_days('2014-12-15')),
   partition p20141216 values less than (to_days('2014-12-16')),
   partition p20141217 values less than (to_days('2014-12-17')),
   partition p20141218 values less than (to_days('2014-12-18')),
   partition p20141219 values less than (to_days('2014-12-19')),
   partition p20141220 values less than (to_days('2014-12-20')),
   partition p20141221 values less than (to_days('2014-12-21')),
   partition p20141222 values less than (to_days('2014-12-22')),
   partition p20141223 values less than (to_days('2014-12-23')),
   partition p20141224 values less than (to_days('2014-12-24')),
   partition p20141225 values less than (to_days('2014-12-25')),
   partition p20141226 values less than (to_days('2014-12-26')),
   partition p20141227 values less than (to_days('2014-12-27')),
   partition p20141228 values less than (to_days('2014-12-28')),
   partition p20141229 values less than (to_days('2014-12-29')),
   partition p20141230 values less than (to_days('2014-12-30')),
   partition p2014 values less than (MAXVALUE));

It look good right? But when I execute it error message appears.

server# mysql Syslog < reorganize.txt
ERROR 1493 (HY000) at line 1: VALUES LESS THAN value must be strictly increasing for each partition

So I check again my reorganize.txt file. It looks good.
I could not see the error. But there was an error!
server# cat reorganize.txt
alter table SystemEvents reorganise partition p2014 into
 ( partition p20141201 values less than (to_days('2014-12-01')),
   partition p20141202 values less than (to_days('2014-12-02')),
   partition p20141203 values less than (to_days('2014-12-03')),
   partition p20141204 values less than (to_days('2014-12-04')),
   partition p20141205 values less than (to_days('2014-12-05')),
   partition p20141206 values less than (to_days('2014-12-06')),
   partition p20141207 values less than (to_days('2014-12-07')),
   partition p20141208 values less than (to_days('2014-12-08')),
   partition p20141209 values less than (to_days('2014-12-09')),
   partition p20141210 values less than (to_days('2014-10-10')),
   partition p20141211 values less than (to_days('2014-12-11')),
   partition p20141212 values less than (to_days('2014-12-12')),
   partition p20141213 values less than (to_days('2014-12-13')),
   partition p20141214 values less than (to_days('2014-12-14')),
   partition p20141215 values less than (to_days('2014-12-15')),
   partition p20141216 values less than (to_days('2014-12-16')),
   partition p20141217 values less than (to_days('2014-12-17')),
   partition p20141218 values less than (to_days('2014-12-18')),
   partition p20141219 values less than (to_days('2014-12-19')),
   partition p20141220 values less than (to_days('2014-12-20')),
   partition p20141221 values less than (to_days('2014-12-21')),
   partition p20141222 values less than (to_days('2014-12-22')),
   partition p20141223 values less than (to_days('2014-12-23')),
   partition p20141224 values less than (to_days('2014-12-24')),
   partition p20141225 values less than (to_days('2014-12-25')),
   partition p20141226 values less than (to_days('2014-12-26')),
   partition p20141227 values less than (to_days('2014-12-27')),
   partition p20141228 values less than (to_days('2014-12-28')),
   partition p20141229 values less than (to_days('2014-12-29')),
   partition p20141230 values less than (to_days('2014-12-30')),
   partition p2014 values less than (MAXVALUE));

When I change value from 2014-10-11 to 2014-12-11(as it should be in the first place) everything worked perfectly!!!

Problem was like error messages said that there was partition that did not have increasing value to_days from previous partition. If this could happen then it would be possible to have not time consistent values in your partition tables.

No comments: