MySQL Bugs: #46226: CREATE EVENT IF NOT EXISTS does not work properly creating multiple instances

Bug #46226	CREATE EVENT IF NOT EXISTS does not work properly creating multiple instances
Submitted:	16 Jul 2009 14:33	Modified:	10 Sep 2018 11:16
Reporter:	Willy Rendering	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.31, 5.1, azalea bzr	OS:	Any (MS Windows, Linux)
Assigned to:		CPU Architecture:	Any
Tags:	CREATE EVENT, DROP EVENT, IF EXISTS, if not exists

Description:
Creating an already existing event with the 'IF NOT EXISTS' clause in the sql-command will generate a warning, but the event is still created.

Show event won't show more than one instance of the event, but at the specified interval the event will be executed multiple times.

Dropping the event with a 'IF EXISTS' clause will remove only one instance of the event, the rest will remain in memory? Because creating a new instance will repeat in multiple instances being fired on each interval again.

In other words:

Creating the recurring event 'BLAH' five times will result in a situation in which 'BLAH' is executed five times on its interval.

Deleting the event 'BLAH' will stop the event. Creating a new instance of this event will create a situation in which 'BLAH' is executed five times again on its interval. Making it necessary to execute the delete command four additional times to remove the event completely.

How to repeat:
- Create a test table with a datetime column without a unique property.
- Create an event with a 'IF NOT EXISTS' clause in it, that fires every 10 seconds inserting now() into the test table
- Verify that the event is being fired every 10 seconds

- Create the event two more times
- Verify that the event is fired every 10 seconds three times

- Drop the event twice with a 'IF EXISTS' clause in the query
- Verify that the event has stopped

- Create the event one more time
- Verify that the event is fired every 10 seconds two times again

Suggested fix:
Fix the 'IF NOT EXISTS' clause and make it impossible to create mutliple instances of one event

Thank you for the report.

Verified as described.

Testcase for the test suite:

SET GLOBAL event_scheduler=off;
drop event if exists event1;
drop event if exists event2;
create table t1(f1 int);
delimiter |;
create event if not exists event1 on schedule every 1 second starts now() ends date_add(now(), interval 5 hour) DO begin insert into t1 values(1); end |
alter event event1 enable|
create event if not exists event2 on schedule every 1 second starts now() ends date_add(now(), interval 5 hour) DO begin insert into t1 values(2); end |
alter event event2 enable|
delimiter ;|
SET GLOBAL event_scheduler=on;

sleep 10;

select * from t1;

I am sorry: wrong test used.

With correct test problem is not repeatable. Additionally version 5.1.31 is old. Please try with current version 5.1.36 and inform us if problem still exists.

Correct test case I used:

SET GLOBAL event_scheduler=off;
drop event if exists event1;
drop event if exists event2;
create table t1(f1 int);
delimiter |;
create event if not exists event1 on schedule every 1 second starts now() ends date_add(now(), interval 5 hour) DO begin insert into t1 values(1); end |
alter event event1 enable|
create event if not exists event1 on schedule every 1 second starts now() ends date_add(now(), interval 5 hour) DO begin insert into t1 values(2); end |
alter event event1 enable|
delimiter ;|
SET GLOBAL event_scheduler=on;

sleep 10;

select * from t1;

Repeated in 5.1.36 with the same results.

Only difference is 5.1.31 gives a warning 'Event already exists' whereas 5.1.36 does not.

Thank you for the feedback.

Please provide repeatable test case as I did.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Posted by developer:
 
In no feedback state for 9 years. Closing.