BUG#25211: events_bugs.test fails on sapsrv1

The problem was that the events_bugs test could randomly fail due to
races in the test case.

The solution is to replace fixed sleeps with reliable polling of a
certain state to settle.  For that, a new auxiliary script
include/wait_condition.inc is used, that allows waiting for a given
query to return true.


mysql-test/include/wait_until_rows_count.inc:
  Script is rewritten using new include/wait_condition.inc.
mysql-test/r/events_bugs.result:
  Update result: add missing quotation mark.
mysql-test/t/events_bugs.test:
  Replace --sleep with the reliable waiting for a certain state.
mysql-test/include/wait_condition.inc:
  New BitKeeper file ``mysql-test/include/wait_condition.inc''

4 files changed, 204 insertions, 69 deletions

diff --git a/mysql-test/include/wait_condition.inc b/mysql-test/include/wait_condition.inc
new file mode 100644
index 00000000000..cd80b58d44e
--- /dev/null
+++ b/mysql-test/include/wait_condition.inc
@@ -0,0 +1,39 @@
+# include/wait_condition.inc
+#
+# SUMMARY
+#
+#    Waits until the passed statement returns true, or the operation
+#    times out.
+#
+# USAGE
+#
+#    let $wait_condition=
+#      SELECT c = 3 FROM t;
+#    --source include/wait_condition.inc
+#
+# EXAMPLE
+#    events_bugs.test
+#
+
+--disable_query_log
+
+let $wait_counter= 300;
+while ($wait_counter)
+{
+    let $success= `$wait_condition`;
+    if ($success)
+    {
+        let $wait_counter= 0;
+    }
+    if (!$success)
+    {
+        real_sleep 0.1;
+        dec $wait_counter;
+    }
+}
+if (!$success)
+{
+  echo Timeout in wait_condition.inc for $wait_condition;
+}
+
+--enable_query_log
diff --git a/mysql-test/include/wait_until_rows_count.inc b/mysql-test/include/wait_until_rows_count.inc
index cf2a21d335a..f1b79c54424 100644
--- a/mysql-test/include/wait_until_rows_count.inc
+++ b/mysql-test/include/wait_until_rows_count.inc
@@ -1,52 +1,20 @@
 # include/wait_until_rows_count.inc
-# inspired by wait_for_slave_status by Matthias Leich
 #
 # SUMMARY
 #
-#    Waits until SELECT count(*)-$count from $table returns zero
+#    Waits until SELECT count(*) = $count from $table returns true, or
+#    the operation times out.
 #
 # USAGE
 #
-#    Set vars like
-#      let $count=11;
-#      let $table=t1;
-#      # invoke the macro
-#      --include wait_until_rows_count.inc
+#    let $count= 5;
+#    let $table= t1;
+#    --source include/wait_until_rows_count.inc
 #
 # EXAMPLE
-# extra/binlog/binlog_insert_delayed.test
+#    extra/binlog/binlog_insert_delayed.test
 #
-#
-# TODO: generalize up to wait_[until|while] with arbitrary select or even query and
-#       a condition to wait or get awakened
-#       It's impossible to implement such a "most" general macro without
-#       extending mysqltest. Just no way to pass a query as an argument and
-#       evaluate it here, like eval "$quuery". One is bound
-#       to specify it inside of the macro
-
---disable_query_log
-
-let $wait_counter= 300;     # max wait in 0.1 seconds
-while ($wait_counter)
-{
-    eval select count(*)-$count from $table into @rez;
-    let $rez=`select @rez`;
-    let $success=`SELECT @rez = 0`;
-    let $no_success=1;
-    if ($success)
-    {
-        let $wait_counter= 1; # droppping counter to leave loop
-        let $no_success=0;
-    }
-    if ($no_success)
-    {
-       --sleep 0.1
-    }
-    dec $wait_counter;
-}
 
---enable_query_log
-if ($no_success)
-{
-	--die Timeout in wait_until_rows_count.inc, required table never had a prescribed number of rows.
-}
+let $wait_condition=
+  select count(*) = $count from $table;
+--source include/wait_condition.inc
diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result
index 363033e487a..44b930e0705 100644
--- a/mysql-test/r/events_bugs.result
+++ b/mysql-test/r/events_bugs.result
@@ -49,7 +49,7 @@ begin
 select get_lock('test_bug16407', 60);
 drop table "hashed_num";
 end|
-"Now if everything is fine the event has compiled and is locked
+"Now if everything is fine the event has compiled and is locked"
 select /*1*/ user, host, db, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info;
 user	host	db	info
 root	localhost	events_test	select get_lock('test_bug16407', 60)
@@ -239,26 +239,83 @@ insert into t1 values (2);
 create table t2 (a char(20));
 insert into t2 values ("e22830_1");
 create function f22830 () returns int return 5;
-create event e22830 on schedule every f22830() second do select 123;
+select get_lock('ee_22830', 60);
+get_lock('ee_22830', 60)
+1
+set global event_scheduler=on;
+create procedure p22830_wait()
+begin
+select get_lock('ee_22830', 60);
+select release_lock('ee_22830');
+end|
+create event e22830 on schedule every f22830() second do
+begin
+call p22830_wait();
+select 123;
+end|
 ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
-create event e22830_1 on schedule every 1 hour do alter event e22830_1 on schedule every (select 8 from dual) hour;
-create event e22830_2 on schedule every 1 hour do alter event e22830_2 on schedule every (select 8 from t1) hour;
-create event e22830_3 on schedule every 1 hour do alter event e22830_3 on schedule every f22830() hour;
-create event e22830_4 on schedule every 1 hour do alter event e22830_4 on schedule every (select f22830() from dual) hour;
+create event e22830_1 on schedule every 1 hour do
+begin
+call p22830_wait();
+alter event e22830_1 on schedule every (select 8 from dual) hour;
+end|
+create event e22830_2 on schedule every 1 hour do
+begin
+call p22830_wait();
+alter event e22830_2 on schedule every (select 8 from t1) hour;
+end|
+create event e22830_3 on schedule every 1 hour do
+begin
+call p22830_wait();
+alter event e22830_3 on schedule every f22830() hour;
+end|
+create event e22830_4 on schedule every 1 hour do
+begin
+call p22830_wait();
+alter event e22830_4 on schedule every (select f22830() from dual) hour;
+end|
+"All events should be blocked in get_lock()"
 select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name;
 event_name	event_definition	interval_value	interval_field
-e22830_1	alter event e22830_1 on schedule every (select 8 from dual) hour	1	HOUR
-e22830_2	alter event e22830_2 on schedule every (select 8 from t1) hour	1	HOUR
-e22830_3	alter event e22830_3 on schedule every f22830() hour	1	HOUR
-e22830_4	alter event e22830_4 on schedule every (select f22830() from dual) hour	1	HOUR
-set global event_scheduler=on;
+e22830_1	begin
+call p22830_wait();
+alter event e22830_1 on schedule every (select 8 from dual) hour;
+end	1	HOUR
+e22830_2	begin
+call p22830_wait();
+alter event e22830_2 on schedule every (select 8 from t1) hour;
+end	1	HOUR
+e22830_3	begin
+call p22830_wait();
+alter event e22830_3 on schedule every f22830() hour;
+end	1	HOUR
+e22830_4	begin
+call p22830_wait();
+alter event e22830_4 on schedule every (select f22830() from dual) hour;
+end	1	HOUR
+select release_lock('ee_22830');
+release_lock('ee_22830')
+1
 set global event_scheduler=off;
 select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name;
 event_name	event_definition	interval_value	interval_field
-e22830_1	alter event e22830_1 on schedule every (select 8 from dual) hour	8	HOUR
-e22830_2	alter event e22830_2 on schedule every (select 8 from t1) hour	1	HOUR
-e22830_3	alter event e22830_3 on schedule every f22830() hour	1	HOUR
-e22830_4	alter event e22830_4 on schedule every (select f22830() from dual) hour	1	HOUR
+e22830_1	begin
+call p22830_wait();
+alter event e22830_1 on schedule every (select 8 from dual) hour;
+end	8	HOUR
+e22830_2	begin
+call p22830_wait();
+alter event e22830_2 on schedule every (select 8 from t1) hour;
+end	1	HOUR
+e22830_3	begin
+call p22830_wait();
+alter event e22830_3 on schedule every f22830() hour;
+end	1	HOUR
+e22830_4	begin
+call p22830_wait();
+alter event e22830_4 on schedule every (select f22830() from dual) hour;
+end	1	HOUR
+drop procedure p22830_wait;
 drop function f22830;
 drop event (select a from t2);
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select a from t2)' at line 1
diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test
index e9751608a46..26abf663ce1 100644
--- a/mysql-test/t/events_bugs.test
+++ b/mysql-test/t/events_bugs.test
@@ -78,8 +78,13 @@ begin
   drop table "hashed_num";
 end|
 delimiter ;|
---sleep 0.8
---echo "Now if everything is fine the event has compiled and is locked
+
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where info = 'select get_lock(\'test_bug16407\', 60)';
+--source include/wait_condition.inc
+
+--echo "Now if everything is fine the event has compiled and is locked"
 select /*1*/ user, host, db, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info;
 select release_lock('test_bug16407');
 
@@ -127,10 +132,22 @@ begin
 end|
 delimiter ;|
 select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
---sleep 1
+
+let $wait_condition=
+  select count(*) = 3 from information_schema.processlist
+  where info = 'select get_lock(\'ee_16407_2\', 60)';
+--source include/wait_condition.inc
+
 select /*2*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info;
 select release_lock('ee_16407_2');
---sleep 1.2
+
+let $wait_condition=
+  select count(*) = 1 and user = 'event_scheduler' and info is null
+  from information_schema.processlist
+  where (command!='Daemon' || user='event_scheduler')
+  and (info is null or info not like '%processlist%');
+--source include/wait_condition.inc
+
 select /*3*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info;
 set global event_scheduler= off;
 select * from events_smode_test order by ev_name, a;
@@ -150,6 +167,7 @@ set global event_scheduler= on;
 
 set sql_mode='traditional';
 delimiter |;
+# ee_16407_5_pendant() should not insert anything because of invalid date.
 create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end|
 create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end|
 create event ee_16407_5 on schedule every 60 second do
@@ -165,11 +183,23 @@ begin
   call events_test.ee_16407_6_pendant();
 end|
 delimiter ;|
---sleep 1
+
+let $wait_condition=
+  select count(*) = 2 from information_schema.processlist
+  where info = 'select get_lock(\'ee_16407_5\', 60)';
+--source include/wait_condition.inc
+
 --echo "Should have 2 locked processes"
 select /*4*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info;
 select release_lock('ee_16407_5');
---sleep 1.3
+
+let $wait_condition=
+  select count(*) = 1 and user = 'event_scheduler' and info is null
+  from information_schema.processlist
+  where (command!='Daemon' || user='event_scheduler')
+  and (info is null or info not like '%processlist%');
+--source include/wait_condition.inc
+
 --echo "Should have 0 processes locked"
 select /*5*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info;
 select * from events_smode_test order by ev_name, a;
@@ -272,17 +302,58 @@ insert into t1 values (2);
 create table t2 (a char(20));
 insert into t2 values ("e22830_1");
 create function f22830 () returns int return 5;
+
+select get_lock('ee_22830', 60);
+set global event_scheduler=on;
+
+delimiter |;
+create procedure p22830_wait()
+begin
+  select get_lock('ee_22830', 60);
+  select release_lock('ee_22830');
+end|
+
 --error ER_NOT_SUPPORTED_YET
-create event e22830 on schedule every f22830() second do select 123;
-create event e22830_1 on schedule every 1 hour do alter event e22830_1 on schedule every (select 8 from dual) hour;
-create event e22830_2 on schedule every 1 hour do alter event e22830_2 on schedule every (select 8 from t1) hour;
-create event e22830_3 on schedule every 1 hour do alter event e22830_3 on schedule every f22830() hour;
-create event e22830_4 on schedule every 1 hour do alter event e22830_4 on schedule every (select f22830() from dual) hour;
+create event e22830 on schedule every f22830() second do
+begin
+  call p22830_wait();
+  select 123;
+end|
+create event e22830_1 on schedule every 1 hour do
+begin
+  call p22830_wait();
+  alter event e22830_1 on schedule every (select 8 from dual) hour;
+end|
+create event e22830_2 on schedule every 1 hour do
+begin
+  call p22830_wait();
+  alter event e22830_2 on schedule every (select 8 from t1) hour;
+end|
+create event e22830_3 on schedule every 1 hour do
+begin
+  call p22830_wait();
+  alter event e22830_3 on schedule every f22830() hour;
+end|
+create event e22830_4 on schedule every 1 hour do
+begin
+  call p22830_wait();
+  alter event e22830_4 on schedule every (select f22830() from dual) hour;
+end|
+delimiter ;|
+
+--echo "All events should be blocked in get_lock()"
 select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name;
-set global event_scheduler=on;
---sleep 4
+
+select release_lock('ee_22830');
+
+let $wait_condition=
+  select group_concat(interval_value order by interval_value) = '1,1,1,8'
+  from information_schema.events;
+--source include/wait_condition.inc
+
 set global event_scheduler=off;
 select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name;
+drop procedure p22830_wait;
 drop function f22830;
 --error ER_PARSE_ERROR
 drop event (select a from t2);