Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

why this mysql event can't get run?

Tags:

mysql

events

mysql> show create event online_event;
+--------------+-----------------------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Event        | sql_mode                                | time_zone | Create Event                                                                                                                                                                                        | character_set_client | collation_connection | Database Collation |
+--------------+-----------------------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| online_event | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER | SYSTEM    | CREATE EVENT `online_event` ON SCHEDULE EVERY 1 SECOND STARTS '2009-06-03 06:54:16' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM online where webserver_id is null and jabber_server_id is null | utf8                 | utf8_general_ci      | utf8_general_ci    |
+--------------+-----------------------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql>

It's created this way:

CREATE EVENT online_event
    ON SCHEDULE EVERY 1 SECOND
    DO 
      DELETE FROM online where webserver_id is null and jabber_server_id is null;

and after quite a period,I found:

mysql> select *from online;
+----+------------+---------------------+--------------+------------------+
| id | account_id | since               | webserver_id | jabber_server_id |
+----+------------+---------------------+--------------+------------------+
|  1 |         30 | 2009-06-03 06:24:38 |         NULL |             NULL |
+----+------------+---------------------+--------------+------------------+
1 row in set (0.00 sec)

So I find that the event is not run at all.

like image 788
omg Avatar asked Jun 02 '09 23:06

omg


People also ask

How do I run a MySQL event?

To turn the Event Scheduler ON , run the following command: SET GLOBAL event_scheduler = ON; The value ON is interchangeable with 1 . OFF : The Event Scheduler thread is not running, and it does not show up in the output of SHOW processlist .

How do you check MySQL event is running or not?

SELECT * FROM INFORMATION_SCHEMA. events; You can also use SHOW CREATE EVENT yourevent to see what it does.

What is on completion preserve?

Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE . Using ON COMPLETION NOT PRESERVE merely makes the default nonpersistent behavior explicit. You can create an event but prevent it from being active using the DISABLE keyword.


2 Answers

Is your event scheduler running? Check with SHOW PROCESSLIST.

If you don't have a process "Daemon" by user "event_scheduler" then it's not running.

Start the event scheduler thus:

SET GLOBAL event_scheduler = ON;

See also http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html

like image 198
Bill Karwin Avatar answered Sep 16 '22 16:09

Bill Karwin


First you must check using :

SHOW VARIABLES LIKE 'event_scheduler';

if it's OFF you must run event scheduler using :

SET GLOBAL event_scheduler = ON;

My you must save this variable in your config (my.ini / my.conf) , if you don't events will not start when mysql restart .

[mysql]
event_scheduler=ON
like image 32
belaassal Avatar answered Sep 19 '22 16:09

belaassal