mysql停止计划事件,创建并运行第二个事件后，mysql计划的事件将停止工作

here is what I'd like to do:

I am working on a project where users can vote for either option A or B. For testing purposes, I need bots that automatically check for new questions in my MySQL database and vote 50/50 on either option. I've read about scheduled events, but I now I am stuck as soon as I add multiple events.

here is what I've done:

Since my bots do only work while there is just one bot (= one event) enabled but stops working as soon as a second bot (= a second event) gets enabled, I simplified the whole process. To demonstrate what the problem is, here is my first bot:

delimiter |

CREATE EVENT bot1

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 SECOND

ON COMPLETION PRESERVE

DO

BEGIN

## do something

## do something else

ALTER EVENT bot1

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 SECOND

ENABLE;

END |

delimiter ;

Now, this 'bot1' starts 30 seconds after it was created and then reschedules itself to every 5 seconds. As long as this bot1 is the only bot running, everything works perfectly. Within phpmyadmin, I can refresh 'show events' and can see that the 'execute at' schedule changes every 5 seconds:

what the problem is:

I need more of those bots, so I add another 'bot2':

delimiter |

CREATE EVENT bot2

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 MINUTE

ON COMPLETION PRESERVE

DO

BEGIN

# do something

# do something else

ALTER EVENT bot2

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 SECOND

ENABLE;

END |

delimiter ;

This bot is called 'bot2' and there is two differences: It starts only 2 minutes after creation, plus, it reschedules itself only every 30 seconds.

here is what I'd expect to happen:

I expect my bot1 to keep working like it did before the creation of bot2. So, bot1 shouldnt care at all that there is another bot involved, since they have different names.

here is what does happen though:

Right after creation of my bot2, bot1 stops rescheduling itself. It stops firing and waits until bot2 fired for the first time. Even then, bot1 forgot about its actual schedule of every 5 seconds and, from now on, fires only each time bot2 is executed. So, it looks like bot2 somehow is the real boss now and tells bot1 when to fire:

This behaviour is reproducable:

When I add another 'bot3' - event and tell bot3 to fire first time only 5 minutes after creation, now bot1 and bot2 wait for those full 5 minutes before they fire.

Does anyone have a clue what the problem is? Would appreciate any help :)

Oh, and yes, there is a reason why I dont use the 'every' schedule but rather reschedule it this way :)

解决方案

I found a workaround:

Instead of using one-time-events and re-enable them with a new starting time (which works perfectly as long as just one event is involved), I now create recurrent events and alter those within their body. So, for example, using

ALTER EVENT `bot1`

ON SCHEDULE EVERY 52 MINUTE;

within its body, the bot now goes to sleep for 52 minutes. With this method, even multiple events work as expected and independent from each other.

Still dont know what the problem with the first attempt is, but having a working solution is most important :)