1
create database if not exists events_test;
3
CREATE USER pauline@localhost;
5
GRANT EVENT ON db_x.* TO pauline@localhost;
7
CREATE TABLE x_table(a int);
8
CREATE EVENT e_x1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE db_x;
9
CREATE EVENT e_x2 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE x_table;
10
SHOW DATABASES LIKE 'db_x';
13
SET GLOBAL event_scheduler=1;
14
SHOW DATABASES LIKE 'db_x';
17
SHOW TABLES FROM db_x;
20
SET GLOBAL event_scheduler=2;
24
DROP USER pauline@localhost;
26
SET GLOBAL event_scheduler=2;
27
drop event if exists event1;
29
Note 1305 Event event1 does not exist
30
create event event1 on schedule every 15 minute starts now() ends date_add(now(), interval 5 hour) DO begin end;
31
alter event event1 rename to event2 enable;
32
alter event event2 disable;
33
alter event event2 enable;
34
alter event event2 on completion not preserve;
35
alter event event2 on schedule every 1 year on completion preserve rename to event3 comment "new comment" do begin select 1; end__
36
alter event event3 rename to event2;
38
create event event2 on schedule every 2 second starts now() ends date_add(now(), interval 5 hour) comment "some" DO begin end;
40
CREATE EVENT event_starts_test ON SCHEDULE EVERY 10 SECOND COMMENT "" DO SELECT 1;
42
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
43
events_test event_starts_test root@localhost RECURRING NULL 10 SECOND # # ENABLED
44
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
45
starts IS NULL ends IS NULL comment
47
ALTER EVENT event_starts_test ON SCHEDULE AT '2020-02-02 20:00:02';
49
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
50
events_test event_starts_test root@localhost ONE TIME 2020-02-02 17:00:02 NULL NULL # # ENABLED
51
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
52
starts IS NULL ends IS NULL comment
54
ALTER EVENT event_starts_test COMMENT "non-empty comment";
56
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
57
events_test event_starts_test root@localhost ONE TIME 2020-02-02 17:00:02 NULL NULL # # ENABLED
58
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
59
starts IS NULL ends IS NULL comment
61
ALTER EVENT event_starts_test COMMENT "";
63
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
64
events_test event_starts_test root@localhost ONE TIME 2020-02-02 17:00:02 NULL NULL # # ENABLED
65
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
66
starts IS NULL ends IS NULL comment
68
DROP EVENT event_starts_test;
69
CREATE EVENT event_starts_test ON SCHEDULE EVERY 20 SECOND STARTS '2020-02-02 20:00:02' ENDS '2022-02-02 20:00:02' DO SELECT 2;
71
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
72
events_test event_starts_test root@localhost RECURRING NULL 20 SECOND # # ENABLED
73
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
74
starts IS NULL ends IS NULL comment
76
ALTER EVENT event_starts_test COMMENT "non-empty comment";
78
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
79
events_test event_starts_test root@localhost RECURRING NULL 20 SECOND # # ENABLED
80
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
81
starts IS NULL ends IS NULL comment
83
ALTER EVENT event_starts_test COMMENT "";
85
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
86
events_test event_starts_test root@localhost RECURRING NULL 20 SECOND # # ENABLED
87
DROP EVENT event_starts_test;
88
create event e_43 on schedule every 1 second do set @a = 5;
89
set global event_scheduler = 1;
90
alter event e_43 do alter event e_43 do set @a = 4;
91
select db, name, body, status, interval_field, interval_value from mysql.event;
92
db name body status interval_field interval_value
93
events_test e_43 set @a = 4 ENABLED SECOND 1
95
"Let's check whether we can use non-qualified names"
96
create table non_qualif(a int);
97
create event non_qualif_ev on schedule every 10 minute do insert into non_qualif values (800219);
98
select * from non_qualif;
101
drop event non_qualif_ev;
102
drop table non_qualif;
103
set global event_scheduler = 2;
104
create table t_event3 (a int, b float);
105
drop event if exists event3;
107
Note 1305 Event event3 does not exist
108
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());
109
select count(*) from t_event3;
115
CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1;
116
SHOW CREATE EVENT root6;
117
Event sql_mode Create Event
118
root6 CREATE EVENT `root6` ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1
119
create event root7 on schedule every 2 year do select 1;
120
SHOW CREATE EVENT root7;
121
Event sql_mode Create Event
122
root7 CREATE EVENT `root7` ON SCHEDULE EVERY 2 YEAR ON COMPLETION NOT PRESERVE ENABLE DO select 1
123
create event root8 on schedule every '2:5' year_month do select 1;
124
SHOW CREATE EVENT root8;
125
Event sql_mode Create Event
126
root8 CREATE EVENT `root8` ON SCHEDULE EVERY '2-5' YEAR_MONTH ON COMPLETION NOT PRESERVE ENABLE DO select 1
127
create event root8_1 on schedule every '2:15' year_month do select 1;
128
SHOW CREATE EVENT root8_1;
129
Event sql_mode Create Event
130
root8_1 CREATE EVENT `root8_1` ON SCHEDULE EVERY '3-3' YEAR_MONTH ON COMPLETION NOT PRESERVE ENABLE DO select 1
131
create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1;
132
SHOW CREATE EVENT root9;
133
Event sql_mode Create Event
134
root9 CREATE EVENT `root9` ON SCHEDULE EVERY 2 WEEK ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' DO select 1
135
create event root10 on schedule every '20:5' day_hour do select 1;
136
SHOW CREATE EVENT root10;
137
Event sql_mode Create Event
138
root10 CREATE EVENT `root10` ON SCHEDULE EVERY '20 5' DAY_HOUR ON COMPLETION NOT PRESERVE ENABLE DO select 1
139
create event root11 on schedule every '20:25' day_hour do select 1;
140
SHOW CREATE EVENT root11;
141
Event sql_mode Create Event
142
root11 CREATE EVENT `root11` ON SCHEDULE EVERY '21 1' DAY_HOUR ON COMPLETION NOT PRESERVE ENABLE DO select 1
143
create event root12 on schedule every '20:25' hour_minute do select 1;
144
SHOW CREATE EVENT root12;
145
Event sql_mode Create Event
146
root12 CREATE EVENT `root12` ON SCHEDULE EVERY '20:25' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
147
create event root13 on schedule every '25:25' hour_minute do select 1;
148
SHOW CREATE EVENT root13;
149
Event sql_mode Create Event
150
root13 CREATE EVENT `root13` ON SCHEDULE EVERY '25:25' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
151
create event root13_1 on schedule every '11:65' hour_minute do select 1;
152
SHOW CREATE EVENT root13_1;
153
Event sql_mode Create Event
154
root13_1 CREATE EVENT `root13_1` ON SCHEDULE EVERY '12:5' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
155
create event root14 on schedule every '35:35' minute_second do select 1;
156
SHOW CREATE EVENT root14;
157
Event sql_mode Create Event
158
root14 CREATE EVENT `root14` ON SCHEDULE EVERY '35:35' MINUTE_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
159
create event root15 on schedule every '35:66' minute_second do select 1;
160
SHOW CREATE EVENT root15;
161
Event sql_mode Create Event
162
root15 CREATE EVENT `root15` ON SCHEDULE EVERY '36:6' MINUTE_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
163
create event root16 on schedule every '35:56' day_minute do select 1;
164
SHOW CREATE EVENT root16;
165
Event sql_mode Create Event
166
root16 CREATE EVENT `root16` ON SCHEDULE EVERY '1 11:56' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
167
create event root17 on schedule every '35:12:45' day_minute do select 1;
168
SHOW CREATE EVENT root17;
169
Event sql_mode Create Event
170
root17 CREATE EVENT `root17` ON SCHEDULE EVERY '35 12:45' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
171
create event root17_1 on schedule every '35:25:65' day_minute do select 1;
172
SHOW CREATE EVENT root17_1;
173
Event sql_mode Create Event
174
root17_1 CREATE EVENT `root17_1` ON SCHEDULE EVERY '36 2:5' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
175
create event root18 on schedule every '35:12:45' hour_second do select 1;
176
SHOW CREATE EVENT root18;
177
Event sql_mode Create Event
178
root18 CREATE EVENT `root18` ON SCHEDULE EVERY '35:12:45' HOUR_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
179
create event root19 on schedule every '15:59:85' hour_second do select 1;
180
SHOW CREATE EVENT root19;
181
Event sql_mode Create Event
182
root19 CREATE EVENT `root19` ON SCHEDULE EVERY '16:0:25' HOUR_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
183
create event root20 on schedule every '50:20:12:45' day_second do select 1;
184
SHOW CREATE EVENT root20;
185
Event sql_mode Create Event
186
root20 CREATE EVENT `root20` ON SCHEDULE EVERY '50 20:12:45' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
188
create event ����21 on schedule every '50:23:59:95' day_second COMMENT '���� � 1251 ��������' do select 1;
189
SHOW CREATE EVENT ����21;
190
Event sql_mode Create Event
191
����21 CREATE EVENT `����21` ON SCHEDULE EVERY '51 0:0:35' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE COMMENT '���� � 1251 ��������' DO select 1
192
insert into mysql.event (db, name, body, definer, interval_value, interval_field) values (database(), "root22", "select 1", user(), 100, "SECOND_MICROSECOND");
193
show create event root22;
194
ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND'
196
ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND'
218
CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing";
220
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
221
events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED
222
ALTER TABLE mysql.event ADD dummy INT FIRST;
224
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 17. Table probably corrupted
225
ALTER TABLE mysql.event DROP dummy, ADD dummy2 VARCHAR(64) FIRST;
227
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 17. Table probably corrupted
228
ALTER TABLE mysql.event DROP dummy2;
230
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
231
events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED
232
CREATE TABLE event_like LIKE mysql.event;
233
INSERT INTO event_like SELECT * FROM mysql.event;
234
ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default '';
235
SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;
236
ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log.
237
ALTER TABLE mysql.event MODIFY db char(20) character set utf8 collate utf8_bin default '';
238
SHOW CREATE TABLE mysql.event;
240
event CREATE TABLE `event` (
241
`db` char(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
242
`name` char(64) NOT NULL DEFAULT '',
243
`body` longblob NOT NULL,
244
`definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
245
`execute_at` datetime DEFAULT NULL,
246
`interval_value` int(11) DEFAULT NULL,
247
`interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
248
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
249
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
250
`last_executed` datetime DEFAULT NULL,
251
`starts` datetime DEFAULT NULL,
252
`ends` datetime DEFAULT NULL,
253
`status` enum('ENABLED','DISABLED') NOT NULL DEFAULT 'ENABLED',
254
`on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
255
`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE') NOT NULL DEFAULT '',
256
`comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
257
PRIMARY KEY (`db`,`name`)
258
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events'
259
SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;
260
ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log.
261
ALTER TABLE mysql.event MODIFY db char(64) character set utf8 collate utf8_bin default '';
264
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
265
events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED
266
ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default '';
267
SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;
268
ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log.
269
ALTER TABLE mysql.event MODIFY db varchar(64) character set utf8 collate utf8_bin default '';
270
SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;
271
ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log.
272
ALTER TABLE mysql.event DROP comment, DROP starts;
273
SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;
274
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 14. Table probably corrupted
275
DROP TABLE mysql.event;
276
CREATE TABLE mysql.event like event_like;
277
INSERT INTO mysql.event SELECT * FROM event_like;
278
DROP TABLE event_like;
280
Db Name Definer Type Execute at Interval value Interval field Starts Ends Status
281
events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED
282
DROP EVENT intact_check;
283
create event e_26 on schedule at '2017-01-01 00:00:00' disable do set @a = 5;
284
select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event;
285
db name body definer convert_tz(execute_at, 'UTC', 'SYSTEM') on_completion
286
events_test e_26 set @a = 5 root@localhost 2017-01-01 00:00:00 DROP
288
create event e_26 on schedule at NULL disabled do set @a = 5;
289
ERROR HY000: Incorrect AT value: 'NULL'
290
create event e_26 on schedule at 'definitely not a datetime' disabled do set @a = 5;
291
ERROR HY000: Incorrect AT value: 'definitely not a datetime'
293
create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1;
295
set event_scheduler=2;
296
ERROR HY000: Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL
297
set global event_scheduler=3;
298
ERROR 42000: Variable 'event_scheduler' can't be set to the value of '3'
299
"DISABLE the scheduler. Testing that it does not work when the variable is 0"
300
set global event_scheduler=2;
301
select definer, name, db from mysql.event;
303
select get_lock("test_lock1", 20);
304
get_lock("test_lock1", 20)
306
create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20);
307
"Should return 1 row"
308
select definer, name, db from mysql.event;
310
root@localhost закачка events_test
311
"Should be only 1 process"
312
select /*1*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info;
313
user host db command state info
314
event_scheduler localhost NULL Connect Suspended NULL
315
select release_lock("test_lock1");
316
release_lock("test_lock1")
319
"Should have 0 events"
320
select count(*) from mysql.event;
323
"ENABLE the scheduler and get a lock"
324
set global event_scheduler=1;
325
select get_lock("test_lock2", 20);
326
get_lock("test_lock2", 20)
328
"Create an event which tries to acquire a mutex. The event locks on the mutex"
329
create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20);
330
"Let some time pass to the event starts"
331
"Should have only 2 processes: the scheduler and the locked event"
332
select /*2*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info;
333
user host db command state info
334
event_scheduler localhost NULL Connect Sleeping NULL
335
root localhost events_test Connect User lock select get_lock("test_lock2", 20)
336
"Release the mutex, the event worker should finish."
337
"Release the mutex, the event worker should finish."
338
select release_lock("test_lock2");
339
release_lock("test_lock2")
342
set global event_scheduler=1;
343
select get_lock("test_lock2_1", 20);
344
get_lock("test_lock2_1", 20)
346
create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20);
347
"Should have only 3 processes: the scheduler, our conn and the locked event"
348
select /*3*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info;
349
user host db command state info
350
event_scheduler localhost NULL Connect Sleeping NULL
351
root localhost events_test Connect User lock select get_lock("test_lock2_1", 20)
352
set global event_scheduler=2;
353
"Should have only our process now:"
354
select /*4*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info;
355
user host db command state info
356
event_scheduler localhost NULL Connect Suspended NULL
357
root localhost events_test Connect User lock select get_lock("test_lock2_1", 20)
358
drop event закачка21;
359
create table t_16 (s1 int);
360
create trigger t_16_bi before insert on t_16 for each row create event e_16 on schedule every 1 second do set @a=5;
361
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
363
create event white_space
364
on schedule every 10 hour
368
select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
369
event_schema event_name definer event_definition
370
events_test white_space root@localhost select 1
371
drop event white_space;
372
create event white_space on schedule every 10 hour disable do
374
select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
375
event_schema event_name definer event_definition
376
events_test white_space root@localhost select 2
377
drop event white_space;
378
create event white_space on schedule every 10 hour disable do select 3;
379
select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
380
event_schema event_name definer event_definition
381
events_test white_space root@localhost select 3
382
drop event white_space;
383
create event e1 on schedule every 1 year do set @a = 5;
384
create table t1 (s1 int);
385
create trigger t1_ai after insert on t1 for each row show create event e1;
386
ERROR 0A000: Not allowed to return a result set from a trigger
389
drop database events_test;
added
removed
pbxt/mysql-test-update/mysql-test/r/events.result
