fix for bug #17494 (The algorithm for calculating execution times is not fully correct)

This also should fix 17493 and 17346, and probably 16397 (not tested).
WL#1034 (Internal CRON)
(post-review commit)


mysql-test/r/events.result:
  update result
mysql-test/t/events.test:
  update test
sql/event.cc:
  seems we get a crash if we compile it this way. better let the worker thread do it,
  this increases the stability.
sql/event_executor.cc:
  kick in more often
sql/event_timed.cc:
  - don't NULLify last_executed
  - implement (fix) better get_next_time() which does not use last_executed as basis
    but STARTS. STARTS is used as basis and the next point of time which is before ENDS,
    if set, is being found. The point > now and (point-starts) % interval_expression == 0.
sql/item_timefunc.cc:
  move calc_time_diff to time.cc as it can be reused in other parts of the 
  server code (see event_timed.cc)
sql/mysql_priv.h:
  export calc_time_diff() moved to time.cc from item_timefunc.cc
sql/sql_show.cc:
  - fix presenting of LAST_EXECUTED
  - cleanup a bit
sql/time.cc:
  - move calc_time_diff() from item_timefunc.cc to here

1 files changed, 36 insertions, 0 deletions

diff --git a/mysql-test/t/events_scheduling.test b/mysql-test/t/events_scheduling.test
new file mode 100644
index 00000000000..41bbb1da3c0
--- /dev/null
+++ b/mysql-test/t/events_scheduling.test
@@ -0,0 +1,36 @@
+CREATE DATABASE IF NOT EXISTS events_test;
+USE events_test;
+CREATE TABLE table_1(a int);
+CREATE TABLE table_2(a int);
+CREATE TABLE table_3(a int);
+CREATE TABLE table_4(a int);
+SET GLOBAL event_scheduler=1;
+CREATE EVENT two_sec ON SCHEDULE EVERY 2 SECOND DO INSERT INTO table_1 VALUES(1);
+CREATE EVENT start_n_end
+  ON SCHEDULE EVERY 1 SECOND
+  ENDS NOW() + INTERVAL 6 SECOND
+  ON COMPLETION PRESERVE
+  DO INSERT INTO table_2 VALUES(1);
+--sleep 5
+CREATE EVENT only_one_time ON SCHEDULE EVERY 2 SECOND ENDS NOW() + INTERVAL 1 SECOND DO INSERT INTO table_3 VALUES(1);
+CREATE EVENT two_time ON SCHEDULE EVERY 1 SECOND ENDS NOW() + INTERVAL 1 SECOND DO INSERT INTO table_4 VALUES(1);
+--sleep 5
+SELECT IF(SUM(a) >= 4, 'OK', 'ERROR') FROM table_1;
+SELECT IF(SUM(a) >= 5, 'OK', 'ERROR') FROM table_2;
+SELECT IF(SUM(a) >  0, 'OK', 'ERROR') FROM table_3;
+SELECT IF(SUM(a) >  0, 'OK', 'ERROR') FROM table_4;
+DROP EVENT two_sec;
+SELECT IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR') FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='start_n_end' AND ENDS IS NOT NULL;
+SELECT IF(LAST_EXECUTED-ENDS < 2, 'OK', 'ERROR') FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='start_n_end' AND ENDS IS NOT NULL;
+DROP EVENT start_n_end;
+--echo "Already dropped because ended. Therefore an error."
+--error 1517
+DROP EVENT only_one_time;
+--echo "Already dropped because ended. Therefore an error."
+--error 1517
+DROP EVENT two_time;
+DROP TABLE table_1;
+DROP TABLE table_2;
+DROP TABLE table_3;
+DROP TABLE table_4;
+DROP DATABASE events_test;