I created my Events to handle adding new partitions (and removing old ones) from my log table and they seem to be working. However, today I noticed that the events ran a few hours later than they ran yesterday.
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: DAY
SQL_MODE:
STARTS: 2008-02-13 05:36:21
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2008-02-13 05:36:21
LAST_ALTERED: 2008-02-13 05:36:21
LAST_EXECUTED: 2008-02-14 07:32:22My addpartition event is current date dependent: it creates a partition for tomorrow hopefully before tomorrow comes so we don't miss a partition and so my INSERTs don't get "No partition" errors. So if it waits a few extra hours more each day, gradually it will push past midnight and screw things up.
I couldn't find any information about how this computation is actually made in the online documentation. I was expecting this to be a little more exact.
Can anyone shed a little more light on this?
Trackback URL for this post:
http://mysqlguy.net/trackback/11
Hi,CREATED, LAST_ALTERED,
Hi,
CREATED, LAST_ALTERED, are in your local timezone, actually the SYSTEM TZ of the server.
OTOH, STARTS, ENDS, LAST_EXECUTED are always UTC and then the `time_zone` field will be used to start them at the right time.
Here is what happened here:
- I createad an event with scheduler enabled
- Then I shut mysqld down
- day++ and let the clock be few minutes earlier
- Started the server and enabled the scheduler
- Waited till the event got exexucuted again
Here is what I had in mysql.event :
- After the first execution
db: test
name: xyz
body: select 1
definer: root@localhost
execute_at: NULL
interval_value: 1
interval_field: DAY
created: 2008-02-14 18:25:01
modified: 2008-02-14 18:25:01
last_executed: 2008-02-14 16:25:01
starts: 2008-02-14 16:25:01
ends: NULL
status: ENABLED
on_completion: DROP
sql_mode:
comment:
originator: 0
time_zone: SYSTEM
- After the second execution:
db: test
name: xyz
body: select 1
definer: root@localhost
execute_at: NULL
interval_value: 1
interval_field: DAY
created: 2008-02-14 18:25:01
modified: 2008-02-14 18:25:01
last_executed: 2008-02-15 16:25:01
starts: 2008-02-14 16:25:01
ends: NULL
status: ENABLED
on_completion: DROP
sql_mode:
comment:
originator: 0
time_zone: SYSTEM
My timezone is EET (UTC+2)
mysql> show variables like 'system_time_zone';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| system_time_zone | EET |
+------------------+-------+
1 row in set (0.00 sec)
created + modified are local, last_executed and starts are UTC (use the internal THD::
Were there any changes to the server configuration? Time switch?
If the docs are not clear let me state something else again - the events are not guaranteed to execute at the same time scheduler - like if scheduled at 12:01:02 the event might be started at 12:01:03 - few secs difference can happen. Kernel scheduling or server load can lead to it. To be safe schedule few secs or a minute before the time you want and then create the appropriate partition. Another event should do something with the old partition, if needed.
You might also drop me an email with your findings.
I'd create the new partitions
I'd create the new partitions well ahead of needing them- there isn't a big overhead in having (a few) superfluous ones. Your system should try to create at least the next couple of partitions, and should be run more than once a day. Moreover, you should probably have something monitoring its success.
We use something similar (albeit with tables rather than partitions) which runs every 10 minutes trying to create tomorrow's table. This gives it about 144 chances to succeed before we need it.
Likewise, the process which drops old partitions should drop ALL of the obsolete ones, not just one, that way if it's skipped it can catch up.
Is there a reliable way of trapping errors and writing to logs from a stored procedure (i.e. event) ?
Mark
Post new comment