Bug #91647 wrong result while alter an event

• Submitted: 16 Jul 2018 5:09
• Modified: 10 Aug 2018 4:56
• Reporter: yghmgl yang
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.7, 5.7.22, 8.0.11
• OS: Any
• CPU Architecture: Any

[16 Jul 2018 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 2018 6:44] MySQL Verification Team

Hello yghmgl yang,

Thank you for the report.

Thanks,
Umesh

[10 Aug 2018 4:56] Nisha Padmini 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.