Description:
An event created with "on schedule at now() + interval 10 seconds" is executed at the intended time, but the time shown in the status is wrong. Checking in information_schema.events, the creation time is consistent with the system time, but the schedule time is not.
for example:
EVENT_SCHEMA: test
EVENT_NAME: justonce
EXECUTE_AT: 2006-02-13 23:10:04
CREATED: 2006-02-14 00:09:54
The system is working correctly. Attempting to create an event in the past will fail, as expected:
create event wrongone on schedule at now() - interval 1 second do drop table aa;
ERROR 1522 (HY000): Activation (AT) time is in the past
Here is a session of the test case as executed in my server:
+----------------+
| version() |
+----------------+
| 5.1.7-beta-log |
+----------------+
1 row in set (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
*************************** 1. row ***************************
Db: test
Name: justonce
Definer: gmax@%
Type: ONE TIME
Execute at: 2006-02-13 23:18:50
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: ENABLED
1 row in set (0.00 sec)
Empty set (0.00 sec)
+--------------------+
| waiting 11 seconds |
+--------------------+
| waiting 11 seconds |
+--------------------+
1 row in set (0.00 sec)
+-----------+
| sleep(11) |
+-----------+
| 0 |
+-----------+
1 row in set (11.01 sec)
*************************** 1. row ***************************
Db: test
Name: justonce
Definer: gmax@%
Type: ONE TIME
Execute at: 2006-02-13 23:18:50
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: DISABLED
1 row in set (0.00 sec)
+----+---------------------+---------------------+---------------------+
| id | descr | TS | inspection time |
+----+---------------------+---------------------+---------------------+
| 1 | from event justonce | 2006-02-14 00:18:50 | 2006-02-14 00:18:51 |
+----+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
*************************** 1. row ***************************
EVENT_CATALOG: NULL
EVENT_SCHEMA: test
EVENT_NAME: justonce
DEFINER: gmax@%
EVENT_BODY:
insert into test.t1
set descr = 'from event justonce'
EVENT_TYPE: ONE TIME
EXECUTE_AT: 2006-02-13 23:18:50
INTERVAL_VALUE: NULL
INTERVAL_FIELD: NULL
SQL_MODE: NULL
STARTS: NULL
ENDS: NULL
STATUS: DISABLED
ON_COMPLETION: PRESERVE
CREATED: 2006-02-14 00:18:40
LAST_ALTERED: 2006-02-14 00:18:40
LAST_EXECUTED: NULL
EVENT_COMMENT:
1 row in set (0.00 sec)
How to repeat:
select version();
use test;
drop table if exists t1;
create table t1
(
id int not null auto_increment primary key,
descr varchar(50),
TS timestamp
);
drop event if exists justonce;
create event justonce
on schedule at now() + interval 10 second
on completion preserve
do
insert into test.t1
set descr = 'from event justonce';
show events\G
select t1.*, now() as "creation time" from t1;
select "waiting 11 seconds";
select sleep(11);
show events\G
select t1.*, now() as "inspection time" from t1;
select * from information_schema.events where event_name='justonce' and event_schema='test'\G
Description: An event created with "on schedule at now() + interval 10 seconds" is executed at the intended time, but the time shown in the status is wrong. Checking in information_schema.events, the creation time is consistent with the system time, but the schedule time is not. for example: EVENT_SCHEMA: test EVENT_NAME: justonce EXECUTE_AT: 2006-02-13 23:10:04 CREATED: 2006-02-14 00:09:54 The system is working correctly. Attempting to create an event in the past will fail, as expected: create event wrongone on schedule at now() - interval 1 second do drop table aa; ERROR 1522 (HY000): Activation (AT) time is in the past Here is a session of the test case as executed in my server: +----------------+ | version() | +----------------+ | 5.1.7-beta-log | +----------------+ 1 row in set (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) *************************** 1. row *************************** Db: test Name: justonce Definer: gmax@% Type: ONE TIME Execute at: 2006-02-13 23:18:50 Interval value: NULL Interval field: NULL Starts: NULL Ends: NULL Status: ENABLED 1 row in set (0.00 sec) Empty set (0.00 sec) +--------------------+ | waiting 11 seconds | +--------------------+ | waiting 11 seconds | +--------------------+ 1 row in set (0.00 sec) +-----------+ | sleep(11) | +-----------+ | 0 | +-----------+ 1 row in set (11.01 sec) *************************** 1. row *************************** Db: test Name: justonce Definer: gmax@% Type: ONE TIME Execute at: 2006-02-13 23:18:50 Interval value: NULL Interval field: NULL Starts: NULL Ends: NULL Status: DISABLED 1 row in set (0.00 sec) +----+---------------------+---------------------+---------------------+ | id | descr | TS | inspection time | +----+---------------------+---------------------+---------------------+ | 1 | from event justonce | 2006-02-14 00:18:50 | 2006-02-14 00:18:51 | +----+---------------------+---------------------+---------------------+ 1 row in set (0.00 sec) *************************** 1. row *************************** EVENT_CATALOG: NULL EVENT_SCHEMA: test EVENT_NAME: justonce DEFINER: gmax@% EVENT_BODY: insert into test.t1 set descr = 'from event justonce' EVENT_TYPE: ONE TIME EXECUTE_AT: 2006-02-13 23:18:50 INTERVAL_VALUE: NULL INTERVAL_FIELD: NULL SQL_MODE: NULL STARTS: NULL ENDS: NULL STATUS: DISABLED ON_COMPLETION: PRESERVE CREATED: 2006-02-14 00:18:40 LAST_ALTERED: 2006-02-14 00:18:40 LAST_EXECUTED: NULL EVENT_COMMENT: 1 row in set (0.00 sec) How to repeat: select version(); use test; drop table if exists t1; create table t1 ( id int not null auto_increment primary key, descr varchar(50), TS timestamp ); drop event if exists justonce; create event justonce on schedule at now() + interval 10 second on completion preserve do insert into test.t1 set descr = 'from event justonce'; show events\G select t1.*, now() as "creation time" from t1; select "waiting 11 seconds"; select sleep(11); show events\G select t1.*, now() as "inspection time" from t1; select * from information_schema.events where event_name='justonce' and event_schema='test'\G