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;
6
set @concurrent_insert= @@global.concurrent_insert;
7
set @@global.concurrent_insert = 0;
8
select * from information_schema.global_variables where variable_name like 'event_scheduler';
9
VARIABLE_NAME VARIABLE_VALUE
11
SET GLOBAL event_scheduler = 'OFF';
12
CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1;
13
CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2;
14
ERROR HY000: Event 'Lower_case' already exists
15
DROP EVENT Lower_case;
17
CREATE EVENT �����_��������_1251 ON SCHEDULE EVERY 1 YEAR DO SELECT 100;
18
CREATE EVENT �����_��������_1251 ON SCHEDULE EVERY 2 YEAR DO SELECT 200;
19
ERROR HY000: Event '�����_��������_1251' already exists
20
DROP EVENT �����_��������_1251;
22
CREATE EVENT долен_регистър_утф8 ON SCHEDULE EVERY 3 YEAR DO SELECT 300;
23
CREATE EVENT ДОЛЕН_регистър_утф8 ON SCHEDULE EVERY 4 YEAR DO SELECT 400;
24
ERROR HY000: Event 'ДОЛЕН_регистър_утф8' already exists
25
DROP EVENT ДОЛЕН_регистър_утф8;
28
CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5;
29
ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present
30
create event e_55 on schedule at 99990101000000 do drop table t;
31
ERROR HY000: Incorrect AT value: '99990101000000'
32
create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t;
33
ERROR HY000: Incorrect STARTS value: '99990101000000'
34
create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t;
35
ERROR HY000: ENDS is either invalid or before STARTS
36
create event e_55 on schedule at 10000101000000 do drop table t;
37
ERROR HY000: Incorrect AT value: '10000101000000'
38
create event e_55 on schedule at 20000101000000 do drop table t;
40
Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
42
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
43
create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t;
44
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'starts 10000101000000 do drop table t' at line 1
45
create event e_55 on schedule at 20200101000000 ends 10000101000000 do drop table t;
46
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ends 10000101000000 do drop table t' at line 1
47
create event e_55 on schedule at 20200101000000 starts 10000101000000 ends 10000101000000 do drop table t;
48
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'starts 10000101000000 ends 10000101000000 do drop table t' at line 1
49
create event e_55 on schedule every 10 hour starts 10000101000000 do drop table t;
50
ERROR HY000: Incorrect STARTS value: '10000101000000'
51
set global event_scheduler=off;
52
delete from mysql.event;
53
set global event_scheduler= on;
54
set @old_sql_mode:=@@sql_mode;
56
select get_lock('test_bug16407', 60);
57
get_lock('test_bug16407', 60)
59
create event e_16407 on schedule every 60 second do
61
select get_lock('test_bug16407', 60);
63
"Now if everything is fine the event has compiled and is locked"
64
select /*1*/ user, host, db, info from information_schema.processlist
65
where state = 'User lock' and info = 'select get_lock(\'test_bug16407\', 60)';
67
root localhost events_test select get_lock('test_bug16407', 60)
68
select release_lock('test_bug16407');
69
release_lock('test_bug16407')
71
set global event_scheduler= off;
72
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
73
event_schema event_name sql_mode
74
events_test e_16407 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
75
"Let's check whether we change the sql_mode on ALTER EVENT"
76
set sql_mode='traditional';
77
alter event e_16407 do select 1;
78
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
79
event_schema event_name sql_mode
80
events_test e_16407 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
83
select get_lock('ee_16407_2', 60);
84
get_lock('ee_16407_2', 60)
86
set global event_scheduler= 1;
87
"Another sql_mode test"
88
set sql_mode="traditional";
89
create table events_smode_test(ev_name char(10), a date);
90
"This should never insert something"
91
create event ee_16407_2 on schedule every 60 second do
93
select get_lock('ee_16407_2', 60) /*ee_16407_2*/;
94
select release_lock('ee_16407_2');
95
insert into events_test.events_smode_test values('ee_16407_2','1980-19-02');
97
insert into events_test.events_smode_test values ('test','1980-19-02')|
98
ERROR 22007: Incorrect date value: '1980-19-02' for column 'a' at row 1
100
create event ee_16407_3 on schedule every 60 second do
102
select get_lock('ee_16407_2', 60) /*ee_16407_3*/;
103
select release_lock('ee_16407_2');
104
insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19');
105
insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29');
108
"This will insert rows but they will be truncated"
109
create event ee_16407_4 on schedule every 60 second do
111
select get_lock('ee_16407_2', 60) /*ee_16407_4*/;
112
select release_lock('ee_16407_2');
113
insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956');
115
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
116
event_schema event_name sql_mode
117
events_test ee_16407_2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
118
events_test ee_16407_3 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
119
events_test ee_16407_4
120
select /*2*/ user, host, db, info from information_schema.processlist
121
where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)';
123
root localhost events_test select get_lock('ee_16407_2', 60)
124
root localhost events_test select get_lock('ee_16407_2', 60)
125
root localhost events_test select get_lock('ee_16407_2', 60)
126
select release_lock('ee_16407_2');
127
release_lock('ee_16407_2')
129
select /*3*/ user, host, db, info from information_schema.processlist
130
where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)';
132
set global event_scheduler= off;
133
select * from events_test.events_smode_test order by ev_name, a;
135
ee_16407_3 1980-02-19
136
ee_16407_3 1980-02-29
137
ee_16407_4 0000-00-00
138
"OK, last check before we drop them"
139
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
140
event_schema event_name sql_mode
141
events_test ee_16407_2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
142
events_test ee_16407_3 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
143
events_test ee_16407_4
144
drop event ee_16407_2;
145
drop event ee_16407_3;
146
drop event ee_16407_4;
147
"And now one last test regarding sql_mode and call of SP from an event"
148
delete from events_test.events_smode_test;
150
select get_lock('ee_16407_5', 60);
151
get_lock('ee_16407_5', 60)
153
set global event_scheduler= on;
154
set sql_mode='traditional';
155
create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end|
156
create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end|
157
create event ee_16407_5 on schedule every 60 second do
159
select get_lock('ee_16407_5', 60) /*ee_16407_5*/;
160
select release_lock('ee_16407_5');
161
call events_test.ee_16407_5_pendant();
163
create event ee_16407_6 on schedule every 60 second do
165
select get_lock('ee_16407_5', 60) /*ee_16407_6*/;
166
select release_lock('ee_16407_5');
167
call events_test.ee_16407_6_pendant();
169
"Should have 2 locked processes"
170
select /*4*/ user, host, db, info from information_schema.processlist
171
where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)';
173
root localhost events_test select get_lock('ee_16407_5', 60)
174
root localhost events_test select get_lock('ee_16407_5', 60)
175
select release_lock('ee_16407_5');
176
release_lock('ee_16407_5')
178
"Should have 0 processes locked"
179
select /*5*/ user, host, db, info from information_schema.processlist
180
where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)';
182
select * from events_test.events_smode_test order by ev_name, a;
184
ee_16407_6 2004-02-29
185
"And here we check one more time before we drop the events"
186
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
187
event_schema event_name sql_mode
188
events_test ee_16407_5 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
189
events_test ee_16407_6 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
190
drop event ee_16407_5;
191
drop event ee_16407_6;
192
drop procedure ee_16407_5_pendant;
193
drop procedure ee_16407_6_pendant;
194
set global event_scheduler= off;
195
drop table events_smode_test;
196
set sql_mode=@old_sql_mode;
197
set global event_scheduler=off;
198
delete from mysql.user where User like 'mysqltest_%';
199
delete from mysql.db where User like 'mysqltest_%';
201
drop database if exists mysqltest_db1;
202
create user mysqltest_user1@localhost;
203
create database mysqltest_db1;
204
grant event on events_test.* to mysqltest_user1@localhost;
205
create event mysqltest_user1 on schedule every 10 second do select 42;
206
alter event mysqltest_user1 rename to mysqltest_db1.mysqltest_user1;
207
ERROR 42000: Access denied for user 'mysqltest_user1'@'localhost' to database 'mysqltest_db1'
208
"Let's test now rename when there is no select DB"
212
alter event events_test.mysqltest_user1 rename to mysqltest_user1;
213
ERROR 3D000: No database selected
214
select event_schema, event_name, definer, event_type, status from information_schema.events;
215
event_schema event_name definer event_type status
216
events_test mysqltest_user1 mysqltest_user1@localhost RECURRING ENABLED
217
drop event events_test.mysqltest_user1;
218
drop user mysqltest_user1@localhost;
219
drop database mysqltest_db1;
220
create event e_53 on schedule at (select s1 from ttx) do drop table t;
221
ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
222
create event e_53 on schedule every (select s1 from ttx) second do drop table t;
223
ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
224
create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t;
225
ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
226
create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t;
227
ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
228
drop event if exists e_16;
229
drop procedure if exists p_16;
230
create event e_16 on schedule every 1 second do set @a=5;
231
create procedure p_16 () alter event e_16 on schedule every @a second;
234
ERROR HY000: Incorrect INTERVAL value: 'NULL'
236
ERROR HY000: Incorrect INTERVAL value: 'NULL'
241
drop function if exists f22830;
242
drop event if exists e22830;
243
drop event if exists e22830_1;
244
drop event if exists e22830_2;
245
drop event if exists e22830_3;
246
drop event if exists e22830_4;
247
drop table if exists t1;
248
drop table if exists t2;
249
create table t1 (a int);
250
insert into t1 values (2);
251
create table t2 (a char(20));
252
insert into t2 values ("e22830_1");
253
create function f22830 () returns int return 5;
254
select get_lock('ee_22830', 60);
255
get_lock('ee_22830', 60)
257
set global event_scheduler=on;
258
create procedure p22830_wait()
260
select get_lock('ee_22830', 60);
261
select release_lock('ee_22830');
263
create event e22830 on schedule every f22830() second do
268
ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
269
create event e22830_1 on schedule every 1 hour do
272
alter event e22830_1 on schedule every (select 8 from dual) hour;
274
create event e22830_2 on schedule every 1 hour do
277
alter event e22830_2 on schedule every (select 8 from t1) hour;
279
create event e22830_3 on schedule every 1 hour do
282
alter event e22830_3 on schedule every f22830() hour;
284
create event e22830_4 on schedule every 1 hour do
287
alter event e22830_4 on schedule every (select f22830() from dual) hour;
289
"All events should be blocked in get_lock()"
290
select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name;
291
event_name event_definition interval_value interval_field
294
alter event e22830_1 on schedule every (select 8 from dual) hour;
298
alter event e22830_2 on schedule every (select 8 from t1) hour;
302
alter event e22830_3 on schedule every f22830() hour;
306
alter event e22830_4 on schedule every (select f22830() from dual) hour;
308
select release_lock('ee_22830');
309
release_lock('ee_22830')
311
set global event_scheduler=off;
312
select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name;
313
event_name event_definition interval_value interval_field
316
alter event e22830_1 on schedule every (select 8 from dual) hour;
320
alter event e22830_2 on schedule every (select 8 from t1) hour;
324
alter event e22830_3 on schedule every f22830() hour;
328
alter event e22830_4 on schedule every (select f22830() from dual) hour;
330
drop procedure p22830_wait;
331
drop function f22830;
332
drop event (select a from t2);
333
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select a from t2)' at line 1
340
DROP USER mysqltest_u1@localhost;
341
CREATE USER mysqltest_u1@localhost;
342
GRANT EVENT ON events_test.* TO mysqltest_u1@localhost;
343
CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
344
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
348
CREATE DEFINER=CURRENT_USER EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
349
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
352
ALTER DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR;
353
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
355
e1 mysqltest_u1@localhost
357
CREATE DEFINER=CURRENT_USER() EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
358
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
362
CREATE DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO
364
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
366
e1 mysqltest_u1@localhost
368
CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
369
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
371
e1 mysqltest_u1@localhost
373
CREATE DEFINER=CURRENT_USER EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
374
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
376
e1 mysqltest_u1@localhost
377
ALTER DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR;
378
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
379
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
381
e1 mysqltest_u1@localhost
383
CREATE DEFINER=CURRENT_USER() EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
384
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
386
e1 mysqltest_u1@localhost
388
CREATE DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
389
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
391
ERROR HY000: Unknown event 'e1'
392
DROP USER mysqltest_u1@localhost;
393
SET GLOBAL EVENT_SCHEDULER= OFF;
394
SET @save_time_zone= @@TIME_ZONE;
395
SET TIME_ZONE= '+00:00';
396
SET TIMESTAMP= UNIX_TIMESTAMP('2005-12-31 23:58:59');
397
CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
399
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
400
events_test e1 root@localhost +00:00 RECURRING NULL 1 DAY 2005-12-31 23:58:59 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
401
SET TIME_ZONE= '-01:00';
402
ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 00:00:00';
404
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
405
events_test e1 root@localhost -01:00 RECURRING NULL 1 DAY 2000-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
406
SET TIME_ZONE= '+02:00';
407
ALTER EVENT e1 ON SCHEDULE AT '2000-01-02 00:00:00'
408
ON COMPLETION PRESERVE DISABLE;
410
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
411
events_test e1 root@localhost +02:00 ONE TIME 2000-01-02 00:00:00 NULL NULL NULL NULL DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
412
SET TIME_ZONE= '-03:00';
413
ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY ENDS '2030-01-03 00:00:00'
414
ON COMPLETION PRESERVE DISABLE;
416
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
417
events_test e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2030-01-03 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
418
SET TIME_ZONE= '+04:00';
419
ALTER EVENT e1 DO SELECT 2;
421
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
422
events_test e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2030-01-03 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
424
SET TIME_ZONE='+05:00';
425
CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO
427
SET TIMESTAMP= @@TIMESTAMP + 1;
428
SET TIME_ZONE='-05:00';
429
CREATE EVENT e2 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO
431
SET TIMESTAMP= @@TIMESTAMP + 1;
432
SET TIME_ZONE='+00:00';
433
CREATE EVENT e3 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO
435
SELECT * FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_name;
436
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 ORIGINATOR CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
437
def 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 1 latin1 latin1_swedish_ci latin1_swedish_ci
438
def 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 1 latin1 latin1_swedish_ci latin1_swedish_ci
439
def 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 1 latin1 latin1_swedish_ci latin1_swedish_ci
441
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
442
events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
443
events_test e2 root@localhost -05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
444
events_test e3 root@localhost +00:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
445
SHOW CREATE EVENT e1;
446
Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
447
e1 +05:00 CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci
448
SHOW CREATE EVENT e2;
449
Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
450
e2 -05:00 CREATE DEFINER=`root`@`localhost` EVENT `e2` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci
451
SHOW CREATE EVENT e3;
452
Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
453
e3 +00:00 CREATE DEFINER=`root`@`localhost` EVENT `e3` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci
454
The following should fail, and nothing should be altered.
455
ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
456
ENDS '1999-01-02 00:00:00';
457
ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
458
ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
459
ENDS '1999-01-02 00:00:00' DISABLE;
460
ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
461
The following should give warnings, and nothing should be 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'
467
Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
468
CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
469
ENDS '1999-01-02 00:00:00' DISABLE
473
Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
474
CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO
477
Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
478
CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE
482
Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
484
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
485
events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
486
events_test e2 root@localhost -05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
487
events_test e3 root@localhost +00:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
488
The following should succeed giving a warning.
489
ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
490
ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE;
492
Note 1544 Event execution time is in the past. Event has been disabled
493
CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
494
ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE
498
Note 1544 Event execution time is in the past. Event has been disabled
499
CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00'
500
ON COMPLETION PRESERVE
504
Note 1544 Event execution time is in the past. Event has been disabled
505
The following should succeed without warnings.
506
ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00';
507
ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
508
ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE;
509
CREATE EVENT e6 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO
511
CREATE EVENT e7 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
512
ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE
515
CREATE EVENT e8 ON SCHEDULE AT '1999-01-01 00:00:00'
516
ON COMPLETION PRESERVE DISABLE
520
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
521
events_test e1 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
522
events_test e2 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
523
events_test e3 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
524
events_test e4 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
525
events_test e5 root@localhost +00:00 ONE TIME 1999-01-01 00:00:00 NULL NULL NULL NULL DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
526
events_test e6 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
527
events_test e7 root@localhost +00:00 RECURRING NULL 1 HOUR 1999-01-01 00:00:00 1999-01-02 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
528
events_test e8 root@localhost +00:00 ONE TIME 1999-01-01 00:00:00 NULL NULL NULL NULL DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
537
SET TIME_ZONE=@save_time_zone;
538
SET TIMESTAMP=DEFAULT;
539
drop event if exists new_event;
540
CREATE EVENT new_event ON SCHEDULE EVERY 0 SECOND DO SELECT 1;
541
ERROR HY000: INTERVAL is either not positive or too big
542
CREATE EVENT new_event ON SCHEDULE EVERY (SELECT 0) SECOND DO SELECT 1;
543
ERROR HY000: INTERVAL is either not positive or too big
544
CREATE EVENT new_event ON SCHEDULE EVERY "abcdef" SECOND DO SELECT 1;
545
ERROR HY000: INTERVAL is either not positive or too big
546
CREATE EVENT new_event ON SCHEDULE EVERY "0abcdef" SECOND DO SELECT 1;
547
ERROR HY000: INTERVAL is either not positive or too big
548
CREATE EVENT new_event ON SCHEDULE EVERY "a1bcdef" SECOND DO SELECT 1;
549
ERROR HY000: INTERVAL is either not positive or too big
550
CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "abcdef" UNION SELECT "abcdef") SECOND DO SELECT 1;
551
ERROR HY000: INTERVAL is either not positive or too big
552
CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "0abcdef") SECOND DO SELECT 1;
553
ERROR HY000: INTERVAL is either not positive or too big
554
CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "a1bcdef") SECOND DO SELECT 1;
555
ERROR HY000: INTERVAL is either not positive or too big
556
CREATE EVENT new_event ON SCHEDULE AT "every day" DO SELECT 1;
557
ERROR HY000: Incorrect AT value: 'every day'
558
CREATE EVENT new_event ON SCHEDULE AT "0every day" DO SELECT 1;
559
ERROR HY000: Incorrect AT value: '0every day'
560
CREATE EVENT new_event ON SCHEDULE AT (SELECT "every day") DO SELECT 1;
561
ERROR HY000: Incorrect AT value: 'every day'
562
CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() DO SELECT 1;
563
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'STARTS NOW() DO SELECT 1' at line 1
564
CREATE EVENT new_event ON SCHEDULE AT NOW() ENDS NOW() DO SELECT 1;
565
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ENDS NOW() DO SELECT 1' at line 1
566
CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() ENDS NOW() DO SELECT 1;
567
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'STARTS NOW() ENDS NOW() DO SELECT 1' at line 1
569
SHOW GRANTS FOR CURRENT_USER;
570
Grants for root@localhost
571
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
572
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
573
SET GLOBAL event_scheduler = ON;
574
CREATE TABLE events_test.event_log
575
(id int KEY AUTO_INCREMENT, ev_nm char(40), ev_cnt int, ev_tm timestamp)
578
CREATE USER evtest1@localhost;
579
SET PASSWORD FOR evtest1@localhost = password('ev1');
580
REVOKE ALL PRIVILEGES, GRANT OPTION FROM evtest1@localhost;
581
GRANT create, insert, select, event ON events_test.* TO evtest1@localhost;
582
GRANT select,insert ON test.* TO evtest1@localhost;
583
SHOW GRANTS FOR evtest1@localhost;
584
Grants for evtest1@localhost
585
GRANT USAGE ON *.* TO 'evtest1'@'localhost' IDENTIFIED BY PASSWORD '*3170F3644E31580C25DE4A08F4C07CC9A2D40C32'
586
GRANT SELECT, INSERT ON `test`.* TO 'evtest1'@'localhost'
587
GRANT SELECT, INSERT, CREATE, EVENT ON `events_test`.* TO 'evtest1'@'localhost'
589
CREATE EVENT ev_sched_1823 ON SCHEDULE EVERY 2 SECOND
592
SET @evname = 'ev_sched_1823';
594
SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname;
596
INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp());
599
SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname;
601
INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp());
605
Sleep till the first INSERT into events_test.event_log occured
606
SELECT COUNT(*) > 0 AS "Expect 1" FROM events_test.event_log;
610
DROP USER evtest1@localhost;
612
SELECT COUNT(*) INTO @row_cnt FROM events_test.event_log;
614
SELECT COUNT(*) > @row_cnt AS "Expect 0" FROM events_test.event_log;
617
DROP EVENT events_test.ev_sched_1823;
618
DROP TABLE events_test.event_log;
619
SET GLOBAL event_scheduler = OFF;
620
SET GLOBAL event_scheduler= ON;
621
CREATE EVENT bug28641 ON SCHEDULE AT '2038.01.18 03:00:00'
625
SET GLOBAL event_scheduler= OFF;
628
#####################################################################
630
# BUG#31111: --read-only crashes MySQL (events fail to load).
632
#####################################################################
634
DROP USER mysqltest_u1@localhost;
635
DROP EVENT IF EXISTS e1;
636
DROP EVENT IF EXISTS e2;
638
GRANT EVENT ON *.* TO mysqltest_u1@localhost;
640
SET GLOBAL READ_ONLY = 1;
643
# Connection: u1_con (mysqltest_u1@localhost/events_test).
646
CREATE EVENT e1 ON SCHEDULE AT '2020-01-01 00:00:00' DO SET @a = 1;
647
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
649
ALTER EVENT e1 COMMENT 'comment';
650
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
653
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
656
# Connection: root_con (root@localhost/events_test).
659
CREATE EVENT e1 ON SCHEDULE AT '2020-01-01 00:00:00' DO SET @a = 1;
661
ALTER EVENT e1 COMMENT 'comment';
665
SET GLOBAL READ_ONLY = 0;
668
# Connection: u1_con (mysqltest_u1@localhost/test).
671
CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND DO SET @a = 1;
672
CREATE EVENT e2 ON SCHEDULE EVERY 1 SECOND DO SET @a = 1;
676
last_executed IS NULL,
678
FROM INFORMATION_SCHEMA.EVENTS
679
WHERE event_schema = 'events_test';
680
event_name last_executed IS NULL definer
681
e1 1 mysqltest_u1@localhost
682
e2 1 mysqltest_u1@localhost
685
# Connection: root_con (root@localhost/events_test).
688
SET GLOBAL READ_ONLY = 1;
690
SET GLOBAL EVENT_SCHEDULER = ON;
692
# Waiting for the event scheduler to execute and drop event e1...
694
# Waiting for the event scheduler to execute and update event e2...
696
SET GLOBAL EVENT_SCHEDULER = OFF;
700
last_executed IS NULL,
702
FROM INFORMATION_SCHEMA.EVENTS
703
WHERE event_schema = 'events_test';
704
event_name last_executed IS NULL definer
705
e2 0 mysqltest_u1@localhost
708
ERROR HY000: Unknown event 'e1'
714
SET GLOBAL READ_ONLY = 0;
717
# Connection: default
720
DROP USER mysqltest_u1@localhost;
722
#####################################################################
726
#####################################################################
728
drop procedure if exists p;
729
set @old_mode= @@sql_mode;
730
set @@sql_mode= cast(pow(2,32)-1 as unsigned integer);
731
create event e1 on schedule every 1 day do select 1;
732
select @@sql_mode into @full_mode;
733
set @@sql_mode= @old_mode;
734
select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode;
735
select name from mysql.event where name = 'e1' and sql_mode = @full_mode;
739
SET @old_server_id = @@GLOBAL.server_id;
740
SET GLOBAL server_id = (1 << 32) - 1;
741
SELECT @@GLOBAL.server_id;
744
CREATE EVENT ev1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
745
SELECT event_name, originator FROM INFORMATION_SCHEMA.EVENTS;
746
event_name originator
749
SET GLOBAL server_id = @old_server_id;
750
CREATE DATABASE event_test12;
752
CREATE EVENT ev1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
753
CREATE DATABASE event_test1;
756
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
757
DROP DATABASE event_test1;
758
DROP DATABASE event_test12;
760
# Bug#12546938 (formerly known as bug#61005):
761
# CREATE IF NOT EXIST EVENT WILL CREATE MULTIPLE RUNNING EVENTS
764
SET GLOBAL event_scheduler = ON;
765
DROP TABLE IF EXISTS table_bug12546938;
766
DROP EVENT IF EXISTS event_Bug12546938;
767
CREATE TABLE table_bug12546938 (i INT);
768
# Create an event which will be executed with a small delay
769
# and won't be automatically dropped after that.
770
CREATE EVENT event_Bug12546938
771
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE
774
INSERT INTO table_bug12546938 VALUES(1);
777
# Now try to create the same event using CREATE EVENT IF NOT EXISTS.
778
# A warning should be emitted. A new event should not be created nor
779
# the old event should be re-executed.
780
CREATE EVENT IF NOT EXISTS event_bug12546938
781
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE
784
INSERT INTO table_bug12546938 VALUES (1);
788
Note 1537 Event 'event_bug12546938' already exists
789
# Wait until at least one instance of event is executed.
790
# Check that only one instance of our event was executed.
791
SELECT COUNT(*) FROM table_bug12546938;
795
DROP EVENT IF EXISTS event_Bug12546938;
796
DROP TABLE table_bug12546938;
797
SET GLOBAL EVENT_SCHEDULER = OFF;
798
DROP DATABASE IF EXISTS event_test11764334;
799
CREATE DATABASE event_test11764334;
800
USE event_test11764334;
801
CREATE EVENT ev1 ON SCHEDULE EVERY 3 SECOND DISABLE DO SELECT 1;
802
SHOW EVENTS IN event_test11764334 WHERE NAME='ev1';
803
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
804
event_test11764334 ev1 root@localhost SYSTEM RECURRING NULL 3 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
805
ALTER EVENT ev1 ON SCHEDULE EVERY 4 SECOND;
806
SHOW EVENTS IN event_test11764334 WHERE NAME='ev1';
807
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
808
event_test11764334 ev1 root@localhost SYSTEM RECURRING NULL 4 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
810
DROP DATABASE event_test11764334;
812
DROP DATABASE events_test;
813
SET GLOBAL event_scheduler= 'ON';
814
SET @@global.concurrent_insert= @concurrent_insert;
added
removed
mysql-test/r/events_bugs.result
