1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
create database if not exists events_test;
use events_test;
drop event if exists event1;
Warnings:
Note 1305 Event event1 does not exist
create event event1 on schedule every 15 minute starts now() ends date_add(now(), interval 5 hour) DO begin end;
alter event event1 rename to event2 enable;
alter event event2 disable;
alter event event2 enable;
alter event event2 on completion not preserve;
alter event event2 on schedule every 1 year on completion preserve rename to event3 comment "new comment" do begin select 1; end__
alter event event3 rename to event2;
drop event event2;
create event event2 on schedule every 2 second starts now() ends date_add(now(), interval 5 hour) comment "some" DO begin end;
drop event event2;
create event e_43 on schedule every 1 second do set @a = 5;
set global event_scheduler = 1;
select sleep(2);
sleep(2)
0
alter event e_43 do alter event e_43 do set @a = 4;
select sleep(3);
sleep(3)
0
select db, name, body, status, interval_field, interval_value from mysql.event;
db name body status interval_field interval_value
events_test e_43 set @a = 4 ENABLED SECOND 1
drop event e_43;
select sleep(1);
sleep(1)
0
set global event_scheduler = 0;
create table t_event3 (a int, b float);
drop event if exists event3;
Warnings:
Note 1305 Event event3 does not exist
create event event3 on schedule every 50 + 10 minute starts date_add("20100101", interval 5 minute) ends date_add("20151010", interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand());
set max_allowed_packet=128000000;
select count(*) from t_event3;
count(*)
0
drop event event3;
drop table t_event3;
create event one_event on schedule every 10 second do select 123;
SHOW EVENTS;
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
events_test one_event root@localhost RECURRING NULL 10 INTERVAL_SECOND # # ENABLED
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events;
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
NULL events_test one_event root@localhost select 123 RECURRING NULL 10 INTERVAL_SECOND ENABLED NOT PRESERVE
CREATE DATABASE events_test2;
CREATE USER ev_test@localhost;
GRANT ALL ON events_test.* to ev_test@localhost;
GRANT ALL on events_test2.* to ev_test@localhost;
REVOKE EVENT ON events_test2.* FROM ev_test@localhost;
REVOKE PROCESS on *.* from ev_test@localhost;
select "NEW CONNECTION";
NEW CONNECTION
NEW CONNECTION
SELECT USER(), DATABASE();
USER() DATABASE()
ev_test@localhost events_test2
SHOW GRANTS;
Grants for ev_test@localhost
GRANT USAGE ON *.* TO 'ev_test'@'localhost'
GRANT ALL PRIVILEGES ON `events_test`.* TO 'ev_test'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `events_test2`.* TO 'ev_test'@'localhost'
"Here comes an error:";
SHOW EVENTS;
ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_test2'
USE events_test;
"Now the list should be empty:";
SHOW EVENTS;
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
select concat("Let's create some new events from the name of ",user());
concat("Let's create some new events from the name of ",user())
Let's create some new events from the name of ev_test@localhost
create event one_event on schedule every 20 second do select 123;
create event two_event on schedule every 20 second on completion not preserve comment "two event" do select 123;
create event three_event on schedule every 20 second on completion preserve comment "three event" do select 123;
"Now we should see 3 events:";
SHOW EVENTS;
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
events_test one_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
events_test three_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
events_test two_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
"This should show us only 3 events:";
SHOW FULL EVENTS;
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
events_test one_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
events_test three_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
events_test two_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
"This should show us only 2 events:";
SHOW FULL EVENTS LIKE 't%event';
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
events_test three_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
events_test two_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
"This should show us no events:";
SHOW FULL EVENTS FROM test LIKE '%';
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
DROP DATABASE events_test2;
"should see 1 event:";
SHOW EVENTS;
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
events_test one_event root@localhost RECURRING NULL 10 INTERVAL_SECOND # # ENABLED
"we should see 4 events now:";
SHOW FULL EVENTS;
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
events_test one_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
events_test three_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
events_test two_event ev_test@localhost RECURRING NULL 20 INTERVAL_SECOND # # ENABLED
events_test one_event root@localhost RECURRING NULL 10 INTERVAL_SECOND # # ENABLED
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events;
EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT
NULL events_test one_event ev_test@localhost select 123 RECURRING NULL 20 INTERVAL_SECOND ENABLED NOT PRESERVE
NULL events_test three_event ev_test@localhost select 123 RECURRING NULL 20 INTERVAL_SECOND ENABLED PRESERVE three event
NULL events_test two_event ev_test@localhost select 123 RECURRING NULL 20 INTERVAL_SECOND ENABLED NOT PRESERVE two event
NULL events_test one_event root@localhost select 123 RECURRING NULL 10 INTERVAL_SECOND ENABLED NOT PRESERVE
drop event one_event;
drop event two_event;
drop event three_event;
drop user ev_test@localhost;
drop event one_event;
create event e_26 on schedule at '2017-01-01 00:00:00' disable do set @a = 5;
select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event;
db name body definer convert_tz(execute_at, 'UTC', 'SYSTEM') on_completion
events_test e_26 set @a = 5 root@localhost 2017-01-01 00:00:00 DROP
drop event e_26;
create event e_26 on schedule at NULL disabled do set @a = 5;
ERROR HY000: Incorrect AT value: 'NULL'
create event e_26 on schedule at 'definitely not a datetime' disabled do set @a = 5;
ERROR HY000: Incorrect AT value: 'definitely not a datetime'
set names utf8;
create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1;
drop event задачка;
set event_scheduler=0;
ERROR HY000: Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL
set global event_scheduler=2;
ERROR 42000: Variable 'event_scheduler' can't be set to the value of '2'
drop database events_test;
|
