MySQL Bugs: #19446: Events : Scheduler goes crazy.

Bug #19446	Events : Scheduler goes crazy.
Submitted:	30 Apr 2006 13:58	Modified:	1 Jun 2006 12:31
Reporter:	Jeff C	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.9-BETA	OS:	FreeBSD (Freebsd 6.0)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Expecting one row per execution of an event.
First run, 1 row
Second, 33.

It continues to grow each iteration.

I have provided every statement I ran.. more information, the better.
Note in #10, i used 'now' instead of 'now()'... It generated an error log entry... Try every statement I ran to duplicate. 

How to repeat:
1) (root@localhost) [test]> set global event_scheduler = ON;
Query OK, 0 rows affected (0.23 sec)

2) show global variables like 'event%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

3) (root@localhost) [test]> create table emps(emp_id int NOT NULL,
    ->              emp_name varchar(30),
    ->              dept_id int,
    ->              salary decimal(5,2),
    ->              primary key(emp_id)) ;
Query OK, 0 rows affected (0.19 sec)

4) (root@localhost) [test]> insert into emps (emp_id,emp_name,dept_id,salary) values (1,'Roger',1,2000.00),(2,'John',2,2500.00),(3,'Alan',1,2100.00);
Query OK, 3 rows affected, 3 warnings (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 3

5) (root@localhost) [test]> select * from emps;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
| 1      | Roger    | 1       | 999.99 |
| 2      | John     | 2       | 999.99 |
| 3      | Alan     | 1       | 999.99 |
+--------+----------+---------+--------+
3 rows in set (0.00 sec)

6) (root@localhost) [test]> create table dept (dept_id int NOT NULL,
    ->              description varchar(30),
    ->              primary key(dept_id));
Query OK, 0 rows affected (0.38 sec)

7) (root@localhost) [test]> insert into dept (dept_id,description) values (1,'Information Techonology'),(2,'Sales');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

8) (root@localhost) [test]> create table emp_count
    -> (created timestamp NOT NULL default CURRENT_TIMESTAMP,row_count int);
Query OK, 0 rows affected (0.19 sec)

9) (root@localhost) [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2006-04-30 07:39:00 |
+---------------------+
1 row in set (0.00 sec)

10) (root@localhost) [test]> create event e_count
    -> on schedule at '2006-04-30 07:42:00'
    -> do insert into emp_count select now,count(*) from emps;
Query OK, 1 row affected (0.02 sec)

11) (root@localhost) [test]> show events\G
*************************** 1. row ***************************
            Db: test
          Name: e_count
       Definer: root@localhost
          Type: ONE TIME
    Execute at: 2006-04-30 12:42:00
Interval value: NULL
Interval field: NULL
        Starts: NULL
          Ends: NULL
        Status: ENABLED
1 row in set (0.00 sec)

12) (root@localhost) [test]> sELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.01 sec)

13) (root@localhost) [test]> create event e_count on schedule at '2006-04-30 07:55:00' do insert into emp_count select now(),count(*) from emps;
Query OK, 1 row affected (0.00 sec)

14) (root@localhost) [test]> show events;
+------+---------+----------------+----------+---------------------+----------------+----------------+--------+------+---------+
| Db   | Name    | Definer        | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status  |
+------+---------+----------------+----------+---------------------+----------------+----------------+--------+------+---------+
| test | e_count | root@localhost | ONE TIME | 2006-04-30 12:55:00 |                |                |        |      | ENABLED |
+------+---------+----------------+----------+---------------------+----------------+----------------+--------+------+---------+
1 row in set (0.01 sec)

15) (root@localhost) [test]> select * from emp_count;
+---------------------+-----------+
| created             | row_count |
+---------------------+-----------+
| 2006-04-30 07:55:00 | 3         |
+---------------------+-----------+
1 row in set (0.00 sec)

16) (root@localhost) [test]> show events;
Empty set (0.01 sec)

17) (root@localhost) [test]> create event e_count
    -> on schedule every 1 minute starts '2006-04-30 07:59:00'
    -> do insert into emp_count select now(),count(*) from emps;
Query OK, 1 row affected (0.00 sec)

18) (root@localhost) [test]> show events\G
*************************** 1. row ***************************
            Db: test
          Name: e_count
       Definer: root@localhost
          Type: RECURRING
    Execute at: NULL
Interval value: 1
Interval field: MINUTE
        Starts: 2006-04-30 12:59:00
          Ends: NULL
        Status: ENABLED
1 row in set (0.00 sec)

19) (root@localhost) [test]> select * from emp_count;
+---------------------+-----------+
| created             | row_count |
+---------------------+-----------+
| 2006-04-30 07:55:00 | 3         |
| 2006-04-30 07:59:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
| 2006-04-30 08:00:00 | 3         |
+---------------------+-----------+
33 rows in set (0.00 sec)

Error Log;
060430  7:42:00 [Note] SCHEDULER: Executing event test.e_count of root@localhost [EXPR:0]
060430  7:42:00 [ERROR] SCHEDULER: [`root`@`localhost`][`test`.`e_count`] Unknown column 'now' in 'field list']
060430  7:42:00 [ERROR] SCHEDULER: [`root`@`localhost`][`test`.`e_count`] Unknown error]
060430  7:42:00 [Note] SCHEDULER: Executed event test.e_count of root@localhost  [EXPR:0]. RetCode=1
060430  7:55:00 [Note] SCHEDULER: Executing event test.e_count of root@localhost [EXPR:0]
060430  7:55:00 [Note] SCHEDULER: Executed event test.e_count of root@localhost  [EXPR:0]. RetCode=0
060430  7:59:00 [Note] SCHEDULER: Executing event test.e_count of root@localhost [EXPR:1]
060430  7:59:00 [Note] SCHEDULER: Executed event test.e_count of root@localhost  [EXPR:1]. RetCode=0
060430  8:00:00 [Note] SCHEDULER: test.e_count in execution. Skip this time.
060430  8:00:00 [Note] SCHEDULER: test.e_count in execution. Skip this time.
060430  8:00:00 [Note] SCHEDULER: test.e_count in execution. Skip this time.
060430  8:00:00 [Note] SCHEDULER: test.e_count in execution. Skip this time.
060430  8:00:00 [Note] SCHEDULER: test.e_count in execution. Skip this time.
....
  8:00:00 [Note] SCHEDULER: Executing event test.e_count of root@localhost [EXPR:1]
<snip>

I suspect this is bug#19170 which was released in 5.1.9 but is fixed in the current tree.
Valeriy, please try to reproduce.

Duplicate of bug#19170