Bug #91647 wrong result while alter an event
Submitted: 16 Jul 5:09 Modified: 10 Aug 4:56
Reporter: yghmgl yang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7, 5.7.22, 8.0.11 OS:Any
Assigned to: CPU Architecture:Any

[16 Jul 5:09] yghmgl yang
Description:
while alter an event, old event is disappear´╝îbut no new event is created,there is no any warning message.

How to repeat:

create database DATABASE_TEST_01;
show databases like 'DATABASE_TEST_01';
use DATABASE_TEST_01;
create table TABLE_TEST_01(time TIMESTAMP);
show tables like 'TABLE_TEST_01';
create event EVENT_TEST_01 on schedule every 5 second do insert into TABLE_TEST_01 values(now());
select EVENT_SCHEMA,EVENT_NAME,DEFINER,EVENT_DEFINITION,INTERVAL_VALUE,INTERVAL_FIELD,STATUS,ON_COMPLETION,EVENT_COMMENT from information_schema.events where EVENT_NAME='EVENT_TEST_01';
set @@global.event_scheduler=ON;
show variables like 'event_scheduler';

Alter definer='root'@'localhost' event EVENT_TEST_01 on schedule every 3 week ends current_timestamp + interval 1 day on completion not preserve rename to mysql.EVENT_TEST_02 comment 'add a comment' do insert into TABLE_TEST_01 values(utc_date());

select EVENT_SCHEMA,EVENT_NAME,DEFINER,EVENT_DEFINITION,INTERVAL_VALUE,INTERVAL_FIELD,STATUS,ON_COMPLETION,EVENT_COMMENT from information_schema.events where EVENT_NAME='EVENT_TEST_01';

select EVENT_SCHEMA,EVENT_NAME,DEFINER,EVENT_DEFINITION,INTERVAL_VALUE,INTERVAL_FIELD,STATUS,ON_COMPLETION,EVENT_COMMENT from information_schema.events where EVENT_NAME='EVENT_TEST_02';
[18 Jul 6:44] Umesh Shastry
Hello yghmgl yang,

Thank you for the report.

Thanks,
Umesh
[10 Aug 4:56] Nisha Gopalakrishnan
I looked into the example provided in the bug report i.e

Alter event EVENT_TEST_01 on schedule every 3 week
ends current_timestamp + interval 1 day on completion not preserve rename to
mysql.EVENT_TEST_02 do insert into TABLE_TEST_01
values(utc_date());

The schedule of 'every 3 weeks' and ends 'current_timestamp + interval 1 day'
causes the event never to be executed. Since the 'on completion' value is
'not preserve', the event is dropped when the scheduler is enabled. This is
the
case with 'create event' as well:

mysql> create event EVENT_TEST_01 on schedule every 1 week ends
current_timestamp + interval 1 day on completion not preserve  do insert into
TABLE_TEST_01 values(utc_date());
Query OK, 0 rows affected (0.01 sec)

mysql> select
EVENT_SCHEMA,EVENT_NAME,DEFINER,EVENT_DEFINITION,INTERVAL_VALUE,INTERVAL_FIELD
 ,STATUS,ON_COMPLETION,EVENT_COMMENT from information_schema.events where
EVENT_NAME='EVENT_TEST_01';

Empty set (0.00 sec)

mysql>

This is an expected behavior and we have historically not been throwing any
warnings
for events getting dropped after execution(or not as in this case). The user
has to set the
on completion to 'preserve' if he wants to retain the event.