Event scheduler in Waiting for next activation state for long time

Question

I created an event and now when use SHOW PROCESSLIST command on my MyQLWorkbench window, I see the following message for my event.Please see the following Image enter image description here :

The above fields in the image corresponds to Id,Host,db,Command,time,State,Info respectively.

It's in "Waiting for next activation" state for ever. I don't know why and I believe that's the reason my event isn't running.

Read the documentation here but it doesn't seems to explain much about troubleshooting or any solution.

I already ran the following command:

SET GLOBAL event_scheduler = ON;

Here is the result of the Query SHOW EVENTS FROM MyDB

enter image description here

Result of SELECT * FROM mysql.event (Since the result was so long, I am adding the image in two parts as below)

Part 1 enter image description here

Part2: enter image description here

What's the result of `SELECT @@event_scheduler;`? If it is `DISABLED` you can't start the event scheduler at runtime. — VMai, Jul 11 '14 at 19:15
@VMai Result is `ON` for the command `SELECT @@event_scheduler;` — John, Jul 11 '14 at 19:15
How should I check for that? I did follow the create event syntax mentioned over here http://stackoverflow.com/questions/24542475/working-with-events-in-mysql — John, Jul 11 '14 at 19:20
I think event has been created because when I tried to create the event with the same name, I wasn't able to and got the message that event already exist — John, Jul 11 '14 at 19:21
You can get that with `SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='your_event_name_goes_here' AND EVENT_SCHEMA='your_database_name'`. Add the result to your question please. — VMai, Jul 11 '14 at 19:24
I used `SELECT * FROM MyDB.events WHERE EVENT_NAME = myeventname` But I got the message " Error Code: 1146 Table 'MyDB.events' doesn't exist. — John, Jul 11 '14 at 19:28
The result is similar to the more complicated statement of me. But you've got to post the result. Edit it into your question please. — VMai, Jul 11 '14 at 19:41
Well it's a recurrent one, and it isn't executed, while it should be executed every 10 seconds. You could look at the error log, but you could tell me the output of `SELECT EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='myeventname' AND EVENT_SCHEMA='MyDB'`. That's the SQL statement that should be executed every 10 seconds. — VMai, Jul 11 '14 at 19:52
@VMai I ran the query you mentioned and got a result under `EVENT_DEFINITION` as follows: `BEGIN CALL MyDB.mystoredproc() END` . Please let me know if it looks good. — John, Jul 11 '14 at 21:06
That leads us to the definition of your stored procedure. You'll get that with `SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'mystoredproc';` — VMai, Jul 11 '14 at 21:42
@VMai That gives me everything from `BEGIN INSERT INTO .... END` just like mentioned here http://stackoverflow.com/questions/24212273/gettting-an-error-while-defining-the-event-name-on-mysqlworkbench-5-5 between `BEGIN` and `END`. May I know why are we following these steps? — John, Jul 11 '14 at 21:48
We want to find out, why your event doesn't get executed, don't we? — VMai, Jul 11 '14 at 21:53
What's the result of `SELECT * FROM mysql.event`? Please regard it's not `mysql.events` as you wrote earlier. — VMai, Jul 11 '14 at 21:59
@Vmai Please find the results updated in my question above (Two images: Part 1 & Part 2) — John, Jul 11 '14 at 22:09
Although it said, last executed not so log ago, it doesn't look like my event is running because I manually ran the SP long time back and got some results inserted into some table from another table and I still see same number of records inserted into it. — John, Jul 11 '14 at 22:43
My mistake. I had the name of the stored procedure misspelled while running the event and that's why the event wasn't doing anything :) Thanks for your help @VMai — John, Jul 11 '14 at 23:19

Event scheduler in Waiting for next activation state for long time

0 Answers0