Problem with MySQL partitioning :

[syndeysider]

Hi

Came across a fun ride today that I didn't want to get on....

Setup MySQL Partitioning as instructed here on 06/08/2014 (August) :

https://www.zabbix.org/wiki/Docs/howto/mysql_partition

Works like a charm... except now I'm getting

"Error Code: 1493 VALUES LESS THAN value must be strictly increasing for each partition"

whenever my cronjob runs partition maintenance.

RTFM...
"If the interval is decreased, you will most likely get an error like this => "ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition". My suggestion is to first delete all future partitions (outside the scope of this article) and then try and change the interval."

I tried this with no luck.

So I dug into the issue a bit more. Baring in mind this has been running :

CALL partition_maintenance(SCHEMA_NAME, 'history', 14, 24, 7);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 14, 24, 7);
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 14, 24, 7);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 14, 24, 7);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 14, 24, 7);
CALL partition_maintenance(SCHEMA_NAME, 'trends', 180, 24, 7);
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 180, 24, 7);

without an issue since 06/08/2014 (August).

What I found was interesting. This :

SELECT partition_name, table_rows, PARTITION_ORDINAL_POSITION, PARTITION_METHOD
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'zabbix' AND TABLE_NAME = 'history'

http://pastebin.com/0G73Ry7B

show's that on the 06/10, for some reason, my UnixTimestamp fastforwarded an hour...

This mean's any new partitions that are trying to be provisioned fail as they their limit (value < clock ; where clock is the limit) is within the 1 hour range of the previous partition.
or
The partition is trys to create starts 1 hour before the previous partition finishs.

Great!

I found manual working around by manually calculating +23 hours or the last partition_description and use that as my new limit for new partitions but this code looks like a train wreck.

Anyone worked on this before or know how to get off this ride?

Something smells like daylight savings....

[syndeysider]

Turns out it was an issue with MySQL and Daylight savings. To remedy I had to delete all existing partitions, manually create a single partition for each table with the correct "< clock" value and rerun partition_maintenance_all.

[coudy]

Hi,
I have same problem.
You have right, it has something common with DST.
This is my partition list for one table>

....
 PARTITION p201410230000 VALUES LESS THAN (1414101600) ENGINE = InnoDB,
 PARTITION p201410240000 VALUES LESS THAN (1414188000) ENGINE = InnoDB,
 PARTITION p201410250000 VALUES LESS THAN (1414274400) ENGINE = InnoDB,
 PARTITION p201410260000 VALUES LESS THAN (1414360800) ENGINE = InnoDB,
 PARTITION p201410262300 VALUES LESS THAN (1414447200) ENGINE = InnoDB,
 PARTITION p201410272300 VALUES LESS THAN (1414533600) ENGINE = InnoDB,
 PARTITION p201410282300 VALUES LESS THAN (1414620000) ENGINE = InnoDB,
 PARTITION p201410292300 VALUES LESS THAN (1414706400) ENGINE = InnoDB,
 PARTITION p201410302300 VALUES LESS THAN (1414792800) ENGINE = InnoDB,
....

at 26.10.2014 I have two tables (p201410260000 and p201410262300), and after this date all tables end with time ..2300..

How to solve this without loosing data ? I don't want to delete partitions and data.

[syndeysider]

workaround

hi

so if you look closely you'll notice that the partition_create meathod is quite straight forward. it creates a new partition based on calculating the name from the current data and passes in a unix timestamp for the "all rows where clock <"

I deleted all partitions in the future to start with. Than I manually calculated the unixtime stamp to bring me to exactly 12:00 am e.g. 1415275200

As I partition daily I :

CALL partition_create('zabbix', 'trends_uint', 'p201411050000', 1415275200);

On all the tables.

This created a single partition for a single day (11-05-2014) ending at 12:00am (11-06-2014). Once date/time of the system moved into this day (i left it till 8am in the morning) and ran the normal maintenance scripts everything worked again.

I think the original link is definitely flawed as in the partition_maintenance this line

IF DATE(NOW()) = '2014-04-01' THEN

is related to daylight savings.

Basically if you just left it. you'd loose data for a single hour before than being able to run the maintenance job to get your partitions recreated etc.

[coudy]

Hi,
you wrote 12:00 am, but when I convert your timestamp, I get 13:00/01:00pm

date --date=@1415275200                                                                              Thu Nov  6 13:00:00 CET 2014

So, if I have 24h partitioning, should I create partition to 24h or 25h ?

Is there a way how to update that script/docs to avoid this problem with DST ?

[kayjay]

syndeysider,

I have exactly this problem so I'm glad I saw your post. My last partitions run out at 00:00 today so I have no future partitions to delete. Is there any specific reason that you created your new partitions to end at 12:00am?

Thanks.

[kayjay]

Has anyone else managed to recover this? I have attempted this procedure but when I try and create the one day partition it fails with the ERROR 1493.

Thanks

[Crypty]

Hi,

so do I lose just one hour of data once/twice a year or do I lose one hour every day during e.g. "summer time"? (if I don't change anything...)

[syndeysider]

Hi

Sorry it's taken so long to get back to you all.

You loose one hour of data if you scheduled maintenance job is set to run every hour. You may loose a couple more hours depending on when your job is scheduled to run.

e.g.
8:00am you job runs. It tries to create partitions for tomorrow between 12:00am->12:00am a full 24 hours. The job can't run because it's trying to include a time which is included in today's partition which ends at +1:00hour because of the 1 hour gain i.e. 01:00am. It is broken because it's using the partition name to calculate the time range. By removing all partitions you effectively create a new, correctly named partition between the 12:00am->12:00am time stamps.

The issue with daylight savings is you loose/gain one hour. This is once off and ONLY if you jobs is set to run hourly. Why?
The issue with the code is that it uses the date/time to calculate partition names.

So not only are you getting the incorrect 'clock value >' for the partition but you also have wonky partition names.

I haven't had time to fix this properly as I'm busy migrating to a Multi Site Zabbix Cluster setup and Linux HA etc. has been taking up all my time. I'll have to get around to this eventually.

[Crypty]

Btw can I easily change the intervals? If (for testing) I keep history for 1 day and trends for 7 days... Can I easily change it to 1 month and 2 years for example? Thanks.

E.g. I had:

CALL partition_maintenance('zabbix', 'history_uint', 1, 24, 1);

Can I just call

CALL partition_maintenance('zabbix', 'history_uint', 28, 24, 7);

And that's it?