Bug #18582 event_scheduler not taking immediate effect

Description:
When switching off event scheduling, events are still being executed for a short time.

How to repeat:
root@localhost [test]> SELECT * FROM INFORMATION_SCHEMA.EVENTS\G
*************************** 1. row ***************************
 EVENT_CATALOG: NULL
  EVENT_SCHEMA: test
    EVENT_NAME: myevent
       DEFINER: root@localhost
    EVENT_BODY: insert into test.eventtable values (NULL)
    EVENT_TYPE: RECURRING
    EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: SECOND
      SQL_MODE: 
        STARTS: 0000-00-00 00:00:00
          ENDS: 0000-00-00 00:00:00
        STATUS: ENABLED
 ON_COMPLETION: NOT PRESERVE
       CREATED: 2006-03-23 20:29:25
  LAST_ALTERED: 2006-03-23 20:29:25
 LAST_EXECUTED: NULL
 EVENT_COMMENT: 
1 row in set (0.01 sec)                                                         

root@localhost [test]> SET GLOBAL event_scheduler=1;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> SELECT COUNT(*) FROM eventtable;
+----------+
| COUNT(*) |
+----------+
|       42 |
+----------+
1 row in set (0.00 sec)

root@localhost [test]> SET GLOBAL event_scheduler=0;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> SELECT COUNT(*) FROM eventtable;
+----------+
| COUNT(*) |
+----------+
|       44 |
+----------+
1 row in set (0.00 sec)

root@localhost [test]> SELECT COUNT(*) FROM eventtable;
+----------+
| COUNT(*) |
+----------+
|       45 |
+----------+
1 row in set (0.00 sec)

Verified at

ChangeSet@1.2265, 2006-03-30 11:12:24+02:00, knielsen@mysql.com

One execution of event is done after event scheduler is turned off.

mysql> show create event blah;
+-------+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Event | sql_mode | Create Event                                                                                                                    |
+-------+----------+---------------------------------------------------------------------------------------------------------------------------------+
| blah  |          | CREATE EVENT `blah` ON SCHEDULE EVERY 1 SECOND ON COMPLETION NOT PRESERVE ENABLE DO insert into test.eventtable values (uuid()) |
+-------+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table eventtable;
+------------+----------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                       |
+------------+----------------------------------------------------------------------------------------------------+
| eventtable | CREATE TABLE `eventtable` (
  `a` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------+----------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> delete from eventtable;  
Query OK, 2 rows affected (0.00 sec)

mysql> set global event_scheduler=on;select sleep(0.01);set global event_scheduler=off;
Query OK, 0 rows affected (0.01 sec)

+-------------+
| sleep(0.01) |
+-------------+
| 0           |
+-------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from eventtable;
+--------------------------------------+
| a                                    |
+--------------------------------------+
| 46797bbe-1149-1029-963e-692fb1e97cb0 |
| 47124542-1149-1029-963e-692fb1e97cb0 |
+--------------------------------------+
2 rows in set (0.00 sec)

Stopping and starting is asynchronous. As part of fix for bug#17619 there will be synchronous start and stop.