MySQL Bugs: #37666: Event set to DISABLED and ON COMPLETION NOT PRESERVE is deleted at server start

Bug #37666	Event set to DISABLED and ON COMPLETION NOT PRESERVE is deleted at server start
Submitted:	26 Jun 2008 13:58	Modified:	27 Mar 2012 17:51
Reporter:	Vemund Østgaard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.26	OS:	Any
Assigned to:	Magne Mæhre	CPU Architecture:	Any

Description:
I have discovered that when I do "ALTER EVENT <x> DISABLE" and restart the server, event <x> is deleted from mysql.events table.

I searched through the reference manual, but could not find any evidence of this behavior being documented. Two bug reports however indicate that this is likely intended behavior from the implementors side:
http://bugs.mysql.com/bug.php?id=34804
http://bugs.mysql.com/bug.php?id=34805

Further testing shows that if an event is set to DISABLED and ON COMPLETION NOT PRESERVE (the default), then it is deleted if the server is restarted. However if the event is set to DISABLED and ON COMPLETION PRESERVE, then it is not deleted. This is made a bit worse by the fact that just doing "ALTER EVENT <x> DISABLE" as I originally did, will set your ON COMPLETION setting to NOT PRESERVE independent of what it was set to before (known bug: http://bugs.mysql.com/bug.php?id=35981). So, if you want to DISABLE an event and not loose it if the server is restarted, you have to set DISABLE and ON COMPLETION PRESERVE in the same statement.

Based on reading the reference manual and doing some testing with events, I find it not intuitive that DISABLED events are deleted at server restart, no matter what your other settings are. If you want to loose the event, then you do DROP EVENT. DISABLE seems to me a way of temporarily putting that event to rest while having the option of turning it on again later. You do not want to loose it completely, but you would if the server intentionally or accidentally goes down and you start it again.

I also find it not intuitive that the ON COMPLETION setting has anything at all to do with this. I have learned from Konstantin Osipov that when an event completes (is ENDS timestamp is passed) it is set to DISABLED if ON COMPLETION is set to PRESERVE. If OM COMPLETION is set to NOT PRESERVE, then the event is dropped when it completes.

It seems clear that there is no distinction between an event being COMPLETED (its ENDS timestamp is in the past) and being DISABLED, which is confusing and IMO wrong, as an event can be DISABLED by an ALTER EVENT or CREATE EVENT statement even though it has not completed and its ENDS time is in the future.

The behavior should be changed so that DISABLED and COMPLETED are not the same thing, and DISABLED events are not deleted at server restart. Even if it is decided not to change todays behavior, it should be better documented.

How to repeat:
Create an event which is set to DISABLED and ON COMPLETION NOT PRESERVE, and stop the server. Observe that the event is deleted when the server is started again.

Setting to verified, easy to reproduce.

We discussed this as an Azalea runtime meeting. To us it seems like the this should probably be a I4, since it is rather unlikely to happen.

/Staale

If the END time of an event is passed the event goes into disabled state. Then on completion is checked. If you stop your server and then you start it and the END time is passed the event should be dropped, as it will be disabled. If you disable yourself you will get the same result.

If the END time of an event is passed the event goes into disabled state. Then on completion is checked. If you stop your server and then you start it and the END time is passed the event should be dropped, as it will be disabled. If you disable yourself you will get the same result.

Hi!

What's terrible is that events are deleted on the slaves when events are SLAVESIDE_DISABLED.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/111783

3096 Luis Soares	2010-06-22
      BUG#37666: Event set to DISABLED and ON COMPLETION NOT PRESERVE is 
      deleted at server start
      
      Some test cases showing how replication and server restarts behaves
      for SLAVESIDE_DISABLED events:
      
        1. There is no implicit drop event when master restarts.
        2. Slave drops SLAVESIDE_DISABLED events if these are set with
           ON_COMPLETION=DROP
        3. Slave preserves SLAVESIDE_DISABLED events if these are set 
           with ON_COMPLETION=PRESERVE

Noted in 5.6.5 changelog.

Setting an event to DISABLED status and with the ON COMPLETION NOT
PRESERVE attribute caused it to be dropped at the next server
restart.