Skip to content

Commit 98db230

Browse files
author
kostja@vajra.(none)
committed
A set of changes aiming to make the Event Scheduler more user-friendly
when there are no up-to-date system tables to support it: - initialize the scheduler before reporting "Ready for connections". This ensures that warnings, if any, are printed before "Ready for connections", and this message is not mangled. - do not abort the scheduler if there are no system tables - check the tables once at start up, remember the status and disable the scheduler if the tables are not up to date. If one attempts to use the scheduler with bad tables, issue an error message. - clean up the behaviour of the module under LOCK TABLES and pre-locking mode - make sure implicit commit of Events DDL works as expected. - add more tests Collateral clean ups in the events code. This patch fixes Bug#23631 Events: SHOW VARIABLES doesn't work when mysql.event is damaged
1 parent c392623 commit 98db230

37 files changed

+2794
-1721
lines changed

‎mysql-test/r/events.result‎

Lines changed: 354 additions & 50 deletions
Large diffs are not rendered by default.

‎mysql-test/r/events_bugs.result‎

Lines changed: 149 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,7 @@
1-
create database if not exists events_test;
1+
drop database if exists events_test;
2+
drop database if exists mysqltest_db1;
3+
drop database if exists mysqltest_db2;
4+
create database events_test;
25
use events_test;
36
CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1;
47
CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2;
@@ -381,4 +384,149 @@ ERROR 42000: Access denied; you need the SUPER privilege for this operation
381384
DROP EVENT e1;
382385
ERROR HY000: Unknown event 'e1'
383386
DROP USER mysqltest_u1@localhost;
387+
SET GLOBAL EVENT_SCHEDULER= OFF;
388+
SET @save_time_zone= @@TIME_ZONE;
389+
SET TIME_ZONE= '+00:00';
390+
SET TIMESTAMP= UNIX_TIMESTAMP('2005-12-31 23:58:59');
391+
CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
392+
SHOW EVENTS;
393+
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status
394+
events_test e1 root@localhost +00:00 RECURRING NULL 1 DAY 2005-12-31 23:58:59 NULL ENABLED
395+
SET TIME_ZONE= '-01:00';
396+
ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 00:00:00';
397+
SHOW EVENTS;
398+
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status
399+
events_test e1 root@localhost -01:00 RECURRING NULL 1 DAY 2000-01-01 00:00:00 NULL ENABLED
400+
SET TIME_ZONE= '+02:00';
401+
ALTER EVENT e1 ON SCHEDULE AT '2000-01-02 00:00:00'
402+
ON COMPLETION PRESERVE DISABLE;
403+
SHOW EVENTS;
404+
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status
405+
events_test e1 root@localhost +02:00 ONE TIME 2000-01-02 00:00:00 NULL NULL NULL NULL DISABLED
406+
SET TIME_ZONE= '-03:00';
407+
ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY ENDS '2030-01-03 00:00:00'
408+
ON COMPLETION PRESERVE DISABLE;
409+
SHOW EVENTS;
410+
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status
411+
events_test e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2030-01-03 00:00:00 DISABLED
412+
SET TIME_ZONE= '+04:00';
413+
ALTER EVENT e1 DO SELECT 2;
414+
SHOW EVENTS;
415+
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status
416+
events_test e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2030-01-03 00:00:00 ENABLED
417+
DROP EVENT e1;
418+
SET TIME_ZONE='+05:00';
419+
CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO
420+
SELECT 1;
421+
SET TIMESTAMP= @@TIMESTAMP + 1;
422+
SET TIME_ZONE='-05:00';
423+
CREATE EVENT e2 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO
424+
SELECT 1;
425+
SET TIMESTAMP= @@TIMESTAMP + 1;
426+
SET TIME_ZONE='+00:00';
427+
CREATE EVENT e3 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO
428+
SELECT 1;
429+
SELECT * FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_name;
430+
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER TIME_ZONE EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD SQL_MODE STARTS ENDS STATUS ON_COMPLETION CREATED LAST_ALTERED LAST_EXECUTED EVENT_COMMENT
431+
NULL events_test e1 root@localhost +05:00 SQL SELECT 1 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED NOT PRESERVE 2005-12-31 23:58:59 2005-12-31 23:58:59 NULL
432+
NULL events_test e2 root@localhost -05:00 SQL SELECT 1 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED NOT PRESERVE 2005-12-31 23:59:00 2005-12-31 23:59:00 NULL
433+
NULL events_test e3 root@localhost +00:00 SQL SELECT 1 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED NOT PRESERVE 2005-12-31 23:59:01 2005-12-31 23:59:01 NULL
434+
SHOW EVENTS;
435+
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status
436+
events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED
437+
events_test e2 root@localhost -05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED
438+
events_test e3 root@localhost +00:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED
439+
SHOW CREATE EVENT e1;
440+
Event sql_mode time_zone Create Event
441+
e1 +05:00 CREATE EVENT `e1` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1
442+
SHOW CREATE EVENT e2;
443+
Event sql_mode time_zone Create Event
444+
e2 -05:00 CREATE EVENT `e2` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1
445+
SHOW CREATE EVENT e3;
446+
Event sql_mode time_zone Create Event
447+
e3 +00:00 CREATE EVENT `e3` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1
448+
The following should fail, and nothing should be altered.
449+
ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
450+
ENDS '1999-01-02 00:00:00';
451+
ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been altered
452+
ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
453+
ENDS '1999-01-02 00:00:00' DISABLE;
454+
ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been altered
455+
The following should give warnings, and nothing should be created.
456+
CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
457+
ENDS '1999-01-02 00:00:00'
458+
DO
459+
SELECT 1;
460+
Warnings:
461+
Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created
462+
CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
463+
ENDS '1999-01-02 00:00:00' DISABLE
464+
DO
465+
SELECT 1;
466+
Warnings:
467+
Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created
468+
CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO
469+
SELECT 1;
470+
Warnings:
471+
Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created
472+
CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE
473+
DO
474+
SELECT 1;
475+
Warnings:
476+
Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created
477+
SHOW EVENTS;
478+
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status
479+
events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED
480+
events_test e2 root@localhost -05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED
481+
events_test e3 root@localhost +00:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED
482+
The following should succeed giving a warning.
483+
ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
484+
ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE;
485+
Warnings:
486+
Note 1533 Event execution time is in the past. Event has been disabled
487+
CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
488+
ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE
489+
DO
490+
SELECT 1;
491+
Warnings:
492+
Note 1533 Event execution time is in the past. Event has been disabled
493+
CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00'
494+
ON COMPLETION PRESERVE
495+
DO
496+
SELECT 1;
497+
Warnings:
498+
Note 1533 Event execution time is in the past. Event has been disabled
499+
The following should succeed without warnings.
500+
ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00';
501+
ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
502+
ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE;
503+
CREATE EVENT e6 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO
504+
SELECT 1;
505+
CREATE EVENT e7 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
506+
ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE
507+
DO
508+
SELECT 1;
509+
CREATE EVENT e8 ON SCHEDULE AT '1999-01-01 00:00:00'
510+
ON COMPLETION PRESERVE DISABLE
511+
DO
512+
SELECT 1;
513+
SHOW EVENTS;
514+
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status
515+
events_test e1 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED
516+
events_test e2 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 NULL ENABLED
517+
events_test e3 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED
518+
events_test e4 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED
519+
events_test e5 root@localhost +00:00 ONE TIME 1999-01-01 00:00:00 NULL NULL NULL NULL DISABLED
520+
events_test e6 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 NULL ENABLED
521+
events_test e7 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED
522+
events_test e8 root@localhost +00:00 ONE TIME 1999-01-01 00:00:00 NULL NULL NULL NULL DISABLED
523+
DROP EVENT e8;
524+
DROP EVENT e7;
525+
DROP EVENT e6;
526+
DROP EVENT e5;
527+
DROP EVENT e4;
528+
DROP EVENT e3;
529+
DROP EVENT e2;
530+
DROP EVENT e1;
531+
SET TIME_ZONE=@save_time_zone;
384532
drop database events_test;

‎mysql-test/r/events_restart_phase1.result‎

Lines changed: 13 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,16 @@
1-
create database if not exists mysqltest_events_test;
2-
use mysqltest_events_test;
31
set global event_scheduler=off;
2+
drop database if exists events_test;
3+
create database events_test;
4+
use events_test;
45
create table execution_log(name char(10));
5-
create event abc1 on schedule every 1 second do insert into execution_log value('abc1');
6-
create event abc2 on schedule every 1 second do insert into execution_log value('abc2');
7-
create event abc3 on schedule every 1 second do insert into execution_log value('abc3');
8-
select name from execution_log;
9-
name
10-
insert into mysql.event values ('db1','bad','select 42','root@localhost',NULL,1000,'MICROSECOND','2006-05-05 17:39:11','2006-05-05 17:39:20','2016-05-05 15:39:24','2016-05-05 15:39:11',NULL,'ENABLED','DROP','','comment1','SYSTEM');
11-
insert into mysql.event values ('db1','bad2','sect','root@localhost',NULL,1000,'SECOND','2006-05-05 17:39:11','2006-05-05 17:39:20','2016-05-05 15:39:24','2016-05-05 15:39:11',NULL,'ENABLED','DROP','','comment2','SYSTEM');
6+
create event abc1 on schedule every 1 second do
7+
insert into execution_log value('abc1');
8+
create event abc2 on schedule every 1 second do
9+
insert into execution_log value('abc2');
10+
create event abc3 on schedule every 1 second do
11+
insert into execution_log value('abc3');
12+
create table event_like like mysql.event;
13+
insert into event_like select * from mysql.event;
14+
alter table mysql.event
15+
change column body body longtext character set utf8 collate utf8_bin;
1216
"Now we restart the server"

‎mysql-test/r/events_restart_phase2.result‎

Lines changed: 42 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,42 @@
1-
use mysqltest_events_test;
2-
"Should get 0 rows because the queue aborted run
3-
select distinct name from execution_log order by name;
4-
name
5-
delete from mysql.event where name like 'bad%';
6-
"Now restart the server again"
1+
use events_test;
2+
select @@event_scheduler;
3+
@@event_scheduler
4+
DISABLED
5+
show events;
6+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
7+
select event_name from information_schema.events;
8+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
9+
show create event intact_check;
10+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
11+
drop event no_such_event;
12+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
13+
create event intact_check_1 on schedule every 5 hour do select 5;
14+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
15+
alter event intact_check_1 on schedule every 8 hour do select 8;
16+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
17+
alter event intact_check_1 rename to intact_check_2;
18+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
19+
drop event intact_check_1;
20+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
21+
drop event intact_check_2;
22+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
23+
drop event intact_check;
24+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
25+
set global event_scheduler=on;
26+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
27+
set global event_scheduler=off;
28+
ERROR HY000: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
29+
show variables like 'event_scheduler';
30+
Variable_name Value
31+
event_scheduler DISABLED
32+
Make sure that we still can create and drop databases,
33+
and no warnings are produced.
34+
drop database if exists mysqltest_database_not_exists;
35+
Warnings:
36+
Note 1008 Can't drop database 'mysqltest_database_not_exists'; database doesn't exist
37+
create database mysqltest_db1;
38+
drop database mysqltest_db1;
39+
Restore the original mysql.event table
40+
drop table mysql.event;
41+
rename table event_like to mysql.event;
42+
Now let's restart the server again

‎mysql-test/r/events_restart_phase3.result‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,12 @@
1-
use mysqltest_events_test;
1+
use events_test;
2+
select @@event_scheduler;
3+
@@event_scheduler
4+
ON
25
"Should get 3 rows : abc1, abc2, abc3
36
select distinct name from execution_log order by name;
47
name
58
abc1
69
abc2
710
abc3
8-
drop event abc1;
9-
drop event abc2;
10-
drop event abc3;
1111
drop table execution_log;
12-
drop database mysqltest_events_test;
12+
drop database events_test;

‎mysql-test/r/events_scheduling.result‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,8 @@
11
CREATE DATABASE IF NOT EXISTS events_test;
22
USE events_test;
33
SET GLOBAL event_scheduler=OFF;
4+
Try agian to make sure it's allowed
5+
SET GLOBAL event_scheduler=OFF;
46
SHOW VARIABLES LIKE 'event_scheduler';
57
Variable_name Value
68
event_scheduler OFF
@@ -13,6 +15,8 @@ SHOW VARIABLES LIKE 'event_scheduler';
1315
Variable_name Value
1416
event_scheduler OFF
1517
SET GLOBAL event_scheduler=ON;
18+
Try again to make sure it's allowed
19+
SET GLOBAL event_scheduler=ON;
1620
SHOW VARIABLES LIKE 'event_scheduler';
1721
Variable_name Value
1822
event_scheduler ON

0 commit comments

Comments
 (0)