MySQL Bugs: #44533: No way to use debug_sync code to wait for event to fire.

Bug #44533	No way to use debug_sync code to wait for event to fire.
Submitted:	29 Apr 2009 2:50	Modified:	17 Aug 2009 20:06
Reporter:	Hema Sridharan	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.1, mysql-6.0	OS:	Any
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
At present, there is no way to give deterministic results when events
are fired in mysql test framework. An alternative way for firing events
to produce deterministic results are

1. Use appropriate sleeps
2. Use wait_condition include

Both the above ways give inconsistent results in pushbuild, as they can
delay the firing of events. There should be a deterministic wait to know
when an event will fire without using sleep, wait loops or wait
conditions. The debug_sync code has to be implemented to wait for an
event to fire.

How to repeat:
Create tests with events in mysql test framework. Please take a look at
the attached test of events that produces inconsistent results.

Test file(events.test) will produce inconsistent results when executed in mtr

Attachment: events.test (application/test, text), 671 bytes.

I recommend closing this as "Won't fix". Using a proper "wait until" loop will solve the problem, no need to implement something special for that as long as each functionality in the server supports asking about it's state. 

Why isn't itpossible to write a proper "wait until" loop in this case?

Of course the "sleep 2" should not be there.

The problem with using a loop is the scheduler (and event) execute in a separate thread. Thus, the best we can do is poll some table to know if the event has fired. This is often done using the insert of the event itself, but that has proven unreliable in pushbuild.

The perfect solution would be is the debug sync utility worked with the event code.

I think using debug insertion will work. Checking feasibility.

There is no easy way to get the debug synch points working for events. It would require asking the runtime team to make changes to how events are handled, specifically, how event firing and reporting and completion.

There is, however, the technique suggested using a wait_condition. It is possible to use the wait_condition reliably given:

- the timeout is set very high for slower machines
- the $wait_condition is carefully constructed to test the result of the
  event and *not* the status of the event (it is possible for the status to be
  set to 'disabled' before the body of the event has completed)
- the event is a one-time only event
- if the event is recurring then one will have to use a different wait 
  condition for each time it fires and the content of the result (e.g. the
  INSERT) would have to be different

For example, the following test uses a wait condition based on the result of the body of the event. It has been executed over 1,000 times without failure.

--disable_warnings
DROP DATABASE IF EXISTS bug_44533;
--enable_warnings

--echo #
--echo # Create a database to be used throughout the test case
--echo #
CREATE DATABASE bug_44533;
CREATE TABLE bug_44533.t1(a INT, b char(20));
INSERT INTO bug_44533.t1 VALUES (1, 'one'), (2,'two'), (3,'three');

CREATE EVENT bug_44533.e1 ON SCHEDULE AT CURRENT_TIMESTAMP
 ON COMPLETION PRESERVE ENABLE
 DO INSERT INTO bug_44533.t1 VALUES(100, 'event e1 fired');

SET GLOBAL event_scheduler=on;

LET $wait_condition = SELECT count(*) = 1 FROM bug_44533.t1 WHERE b = 'event e1 fired';
--source include/wait_condition.inc

SELECT * FROM bug_44533.t1;
SELECT name, body, status FROM mysql.event;

SET GLOBAL event_scheduler=off;

DROP DATABASE bug_44533;

Note that the wait condition is based on the INSERT of the event e1 and not the status of the event or scheduler. This is the only reliable way to use a wait_condition include file.

Note also the sequence of the statements concerning turning on the scheduler. It must be off before the event is created and the event must be enabled on creation. Then and only then can one turn on the scheduler and wait for the event to fire. Any other sequence or creating the event as disabled has failed during testing. There is something odd about how the scheduler handles a disabled event -- it seems it takes longer for it to load and execute which could explain random failures in previous attempts to use the wait loop.