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.