Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have created one event schedule which looks like this:

mysql> show create event event1
    -> ;
+--------+----------+-----------+--------------------------------------------------------------------------------------------+
| Event  | sql_mode | time_zone | Create Event  | character_set_client | collation_connection | Database Collation |
+--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+

| event1 |          | SYSTEM    | CREATE DEFINER=`root`@`localhost` EVENT `event1` ON SCHEDULE EVERY 1 MONTH STARTS '2013-02-02 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
 update samp set col1 =col1  + 1; END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+---------
-----------+
1 row in set (0.00 sec)

This events was not called on 1st of the month. So I tried show processlist\g to find it was running or not; it showed following:

mysql> show processlist\g;
+-----+-----------------+-----------------+---------------+---------+------+---   
| Id  | User            | Host            | db            | Command | Time | State                       | Info             |
+-----+-----------------+-----------------+---------------+---------+------+---
| 136 | event_scheduler | localhost       | NULL          | Daemon  | 1855 | Waiting for next activation | NULL    |

Does the NULL in the db column means that no DB is assigned to it?

Please help me solve this.

share|improve this question

migrated from stackoverflow.com Mar 30 '13 at 2:22

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

Events are not "running" unless they are actually in the process of executing.

What you see in your processlist is the actual event scheduler, not the event itself. You will always have this entry when the event scheduler is enabled.

mysql> show processlist;
+---------+-----------------+---------------------------+-----------------+---------+-------+-----------------------------+------------------+
| Id      | User            | Host                      | db              | Command | Time  | State                       | Info             |
+---------+-----------------+---------------------------+-----------------+---------+-------+-----------------------------+------------------+
|      48 | event_scheduler | localhost                 | NULL            | Daemon  |     1 | Waiting for next activation | NULL             |

Nothing shows in the processlist for scheduled events unless the actual query spawned by the event is currently running.

When that happens, this row will still be in the processlist, plus you will see a new thread that will appear while the event's query is actually running, and disappear when the event's query has finished. The next time the event fires, it will have a new thread and a new thread ID.

To check on an event:

mysql> use the_event_db; # your database name
Database changed

mysql> show events;
+------------------+------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db               | Name                   | Definer   | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+------------------+------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| the_event_db     | scheduled_stats_update | tehuser@% | SYSTEM    | RECURRING | NULL       | 15             | MINUTE         | 2012-12-14 12:41:16 | NULL | ENABLED |         11 | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------------------+------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
mysql>

Note the Starts value. In the event above, this event doesn't fire at :00, :15, :30, and :45 minutes of every hour -- it runs every 15 minutes after 2012-12-14 12:41:16... so each hour it runs at XX:11:16, and XX:26:16, XX:41:16, and XX:56:16.

Your original question shows this:

ON SCHEDULE EVERY 1 MONTH STARTS '2013-02-02 00:00:00' 

This would run on the second day of every month, not the first.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.