Saturday, April 08, 2006

Events work much better now

Today, I updated my development tree installation of MySQL 5.1 (which is already at 5.1.10-beta) and tested Event Scheduling again. In MySQL 5.1.7 there were still a lot of rough edges, but now a lot is much smoother.

Take for example this event:
DELIMITER //

DROP EVENT IF EXISTS copyGeneralLog //

CREATE EVENT copyGeneralLog
ON SCHEDULE EVERY 1 MINUTE
STARTS '2006-04-08 01:44:00'
ENDS '2006-04-08 01:50:00'
ON COMPLETION PRESERVE
ENABLE DO
BEGIN
INSERT INTO test.general_log
(event_time, user_host, thread_id, server_id,
command_type, argument)
SELECT event_time, user_host, thread_id, server_id,
command_type, argument
FROM general_log
WHERE command_type='Connect';

TRUNCATE TABLE general_log;

SELECT 1, 2, 3;
END //

DELIMITER ;

In 5.1.7, it was not guaranteed that this event runs at every starting minute (with :00 in the seconds), this seems to work now. Also that the query "SELECT 1, 2, 3" (which actually doesn't make sense here, it's just for testing purposes) is now being logged is new - in 5.1.7, queries inside events haven't appeared in the general log.

And the TRUNCATE TABLE command also failed in 5.1.7 and works now. So there's a whole lot better now.

One thing is still a bit disturbing. Take the output of information_schema.EVENTS:
mysql> select * from information_schema.events\G
*************************** 1. row ***************************
EVENT_CATALOG: NULL
EVENT_SCHEMA: mysql
EVENT_NAME: copyGeneralLog
DEFINER: mpopp@localhost
EVENT_BODY: ... [cut off]
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: MINUTE
SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
STARTS: 2006-04-07 23:44:00
ENDS: 2006-04-07 23:50:00
STATUS: ENABLED
ON_COMPLETION: PRESERVE
CREATED: 2006-04-08 01:43:42
LAST_ALTERED: 2006-04-08 01:43:42
LAST_EXECUTED: 2006-04-07 23:46:01
EVENT_COMMENT:
1 row in set (0.00 sec)

While CREATED and LAST_ALTERED are specified as of the local time zone, STARTS, ENDS and LAST_EXECUTED show the UTC time.

By the way, since last week, there are also nightly snapshots from MySQL 5.1.

No comments: