summaryrefslogtreecommitdiff
path: root/mysql-test/main/events_2.test
blob: 2012f94e9f2c7fd2c971daa94169cbb179c8003a (plain)
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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
# changes 2008-02-20 hhunger splitted events.test into events_1 and events_2
#
# Can't test with embedded server that doesn't support grants
-- source include/not_embedded.inc

set sql_mode="";
--source include/default_charset.inc

--disable_warnings
drop database if exists events_test;
--enable_warnings
create database events_test;
use events_test;

#
# mysql.event intact checking end
#

create event e_26 on schedule at '2037-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;
drop event e_26;
--error ER_WRONG_VALUE
create event e_26 on schedule at NULL disable do set @a = 5;
--error ER_WRONG_VALUE
create event e_26 on schedule at 'definitely not a datetime' disable do set @a = 5;

set names utf8;
create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1;
drop event задачка;

--echo "DISABLE the scheduler. Testing that it does not work when the variable is 0"
set global event_scheduler=off;
select definer, name, db from mysql.event;
select get_lock("test_lock1", 20);
create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20);
--echo "Should return 1 row"
select definer, name, db from mysql.event;

--echo "Should be only 0 process"
select /*1*/ user, host, db, command, state, info
  from information_schema.processlist
  where (user='event_scheduler')
  order by info;
select release_lock("test_lock1");
drop event закачка;
--echo "Should have 0 events"
select count(*) from mysql.event;

#
#
#
--echo "ENABLE the scheduler and get a lock"
set global event_scheduler=on;
select get_lock("test_lock2", 20);
--echo "Create an event which tries to acquire a mutex. The event locks on the mutex"
create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20);

--echo "Should have only 2 processes: the scheduler and the locked event"
let $wait_condition= select count(*) = 2 from information_schema.processlist
  where ( (state like 'User lock%' AND info like 'select get_lock%')
       OR (command='Daemon' AND user='event_scheduler' AND
           state = 'Waiting for next activation'));
--source include/wait_condition.inc

select /*2*/ user, host, db, command, state, info
  from information_schema.processlist
  where (info like "select get_lock%" OR user='event_scheduler')
  order by info;
--echo "Release the mutex, the event worker should finish."
select release_lock("test_lock2");
drop event закачка;

# Wait for get_lock("test_lock2") to complete,
# to avoid polluting the next test information_schema.processlist
let $wait_condition= select count(*) = 0 from information_schema.processlist
  where info='select get_lock("test_lock2", 20)';
--source include/wait_condition.inc


##
## 1. get a lock
## 2. create an event
## 3. sleep so it has time to start
## 4. should appear in processlist
## 5. kill the scheduler, it will wait for the child to stop
## 6. both processes should be there on show processlist
## 7. release the lock and sleep, both scheduler and child should end
set global event_scheduler=1;
select get_lock("test_lock2_1", 20);
create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20);

--echo "Should have only 2 processes: the scheduler and the locked event"
let $wait_condition= select count(*) = 2 from information_schema.processlist
  where ( (state like 'User lock%' AND info like 'select get_lock%')
          OR (command='Daemon' AND user='event_scheduler' AND
              state = 'Waiting for next activation'));
--source include/wait_condition.inc

select /*3*/ user, host, db, command, state, info
  from information_schema.processlist
  where (info like "select get_lock%" OR user='event_scheduler')
  order by info;

set global event_scheduler=off;

let $wait_condition= select count(*) =1 from information_schema.processlist
  where (info like "select get_lock%" OR user='event_scheduler');
--source include/wait_condition.inc

--echo "Should have only our process now:"
select /*4*/ user, host, db, command, state, info
  from information_schema.processlist
  where (info like "select get_lock%" OR user='event_scheduler')
  order by info;
select release_lock("test_lock2_1");
drop event закачка21;
let $wait_condition=
  select count(*) = 0 from information_schema.processlist
  where db='events_test' and command = 'Connect' and user=current_user();
--source include/wait_condition.inc

####
# Bug #16410  Events: CREATE EVENT is legal in a CREATE TRIGGER statement
#
create table t_16 (s1 int);
--error ER_EVENT_RECURSION_FORBIDDEN
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;
drop table t_16;
#
# end of test case
####

#
# START: BUG #17453: Creating Event crash the server
#
create event white_space
on schedule every 10 hour
disable
do
select 1;
select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
drop event white_space;
create event white_space on schedule every 10 hour disable do

select 2;
select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
drop event white_space;
create event white_space on schedule every 10 hour disable do	select 3;
select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
drop event white_space;
#
# END:  BUG #17453: Creating Event crash the server
#

#
# Bug#17403 "Events: packets out of order with show create event"
#
create event e1 on schedule every 1 year do set @a = 5;
create table t1 (s1 int);
--error ER_SP_NO_RETSET
create trigger t1_ai after insert on t1 for each row show create event e1;
drop table t1;
drop event e1;

##set global event_scheduler=1;
##select get_lock("test_lock3", 20);
##create event закачка on schedule every 10 hour do select get_lock("test_lock3", 20);
##select sleep(2);
##select /*5*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
##drop event закачка;
##select release_lock("test_lock3");

#
# test with very often occuring event
# (disabled for now, locks)
##select get_lock("test_lock4", 20);
##create event закачка4 on schedule every 1 second do select get_lock("test_lock4", 20);
##select sleep(3);
##select /*6*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
##drop event закачка4;
##select release_lock("test_lock4");

##set global event_scheduler=off;
##select sleep(2);
##--replace_column 1 # 6 #
##show processlist;
##select count(*) from mysql.event;

#
# Test wrong syntax
#

--error ER_WRONG_DB_NAME 
SHOW EVENTS FROM aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
--error ER_WRONG_DB_NAME
SHOW EVENTS FROM ``;

SHOW EVENTS FROM `events\\test`;
#
# A check for events SQL under LOCK TABLES and in pre-locked mode.
#
--echo
--echo LOCK TABLES mode.
--echo 
#
# SHOW CREATE EVENT and INFORMATION_SCHEMA.events are available and 
# cause an implicit lock/unlock of mysql.event table, regardless of the
# currently locked tables.
#
create table t1 (a int);
create event e1 on schedule every 10 hour do select 1;
#
lock table t1 read;
#
--replace_regex /STARTS '[^']+'/STARTS '#'/
show create event e1;
select event_name from information_schema.events;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
create event e2 on schedule every 10 hour do select 1;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter event e2 disable;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter event e2 rename to e3;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
drop event e2;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
drop event e1;
unlock tables;
#
lock table t1 write;
#
--replace_regex /STARTS '[^']+'/STARTS '#'/
show create event e1;
select event_name from information_schema.events;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
create event e2 on schedule every 10 hour do select 1;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter event e2 disable;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter event e2 rename to e3;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
drop event e2;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
drop event e1;
unlock tables;
#
lock table t1 read, mysql.event read;
#
--replace_regex /STARTS '[^']+'/STARTS '#'/
show create event e1;
select event_name from information_schema.events;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
create event e2 on schedule every 10 hour do select 1;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter event e2 disable;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter event e2 rename to e3;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
drop event e2;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
drop event e1;
unlock tables;
#
lock table t1 write, mysql.event read;
#
--replace_regex /STARTS '[^']+'/STARTS '#'/
show create event e1;
select event_name from information_schema.events;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
create event e2 on schedule every 10 hour do select 1;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter event e2 disable;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter event e2 rename to e3;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
drop event e2;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
drop event e1;
unlock tables;
#
--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
lock table t1 read, mysql.event write;
#
--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
lock table t1 write, mysql.event write;
#
lock table mysql.event write;
--replace_regex /STARTS '[^']+'/STARTS '#'/
show create event e1;
select event_name from information_schema.events;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
create event e2 on schedule every 10 hour do select 1;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter event e2 disable;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter event e2 rename to e3;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
drop event e3;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
drop event e1;
unlock tables;
drop event e1;
--echo Make sure we have left no events 
select event_name from information_schema.events;
--echo
--echo Events in sub-statements, events and prelocking
--echo 
--echo 
create event e1 on schedule every 10 hour do select 1;
delimiter |;
--error ER_SP_NO_RETSET
create function f1() returns int
begin
  show create event e1;
  return 1;
end|
--error ER_SP_NO_RETSET
create trigger trg before insert on t1 for each row
begin
  show create event e1;
end|
--error ER_SP_NO_RETSET
create function f1() returns int
begin
  select event_name from information_schema.events;
  return 1;
end|
--error ER_SP_NO_RETSET
create trigger trg before insert on t1 for each row
begin
  select event_name from information_schema.events;
end|
--error ER_EVENT_RECURSION_FORBIDDEN
create function f1() returns int
begin
  create event e2 on schedule every 10 hour do select 1;
  return 1;
end|
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
create function f1() returns int
begin
  alter event e1 rename to e2;
  return 1;
end|
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
create function f1() returns int
begin
  drop event e2;
  return 1;
end|
--echo ----------------------------------------------------------------------
create trigger trg before insert on t1 for each row
begin
  set new.a= f1();
end|
create function f1() returns int
begin
  call p1();
  return 0;
end|
create procedure p1()
begin
  select event_name from information_schema.events;
end|
--error ER_SP_NO_RETSET
insert into t1 (a) values (1)|
drop procedure p1|
create procedure p1()
begin
  show create event e1; 
end|
--error ER_SP_NO_RETSET
insert into t1 (a) values (1)|
drop procedure p1|
create procedure p1()
begin
  create temporary table tmp select event_name from information_schema.events;
end|
--echo expected to work, since we redirect the output into a tmp table
insert into t1 (a) values (1)|
select * from tmp|
drop temporary table tmp|
drop procedure p1|
create procedure p1()
begin
  alter event e1 rename to e2;
end|
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 (a) values (1)|
drop procedure p1|
create procedure p1()
begin
  drop event e1;
end|
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
insert into t1 (a) values (1)|
drop table t1|
drop event e1|
delimiter ;|

#
# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte
#
set names utf8;
create event имя_события_в_кодировке_утф8_длиной_больше_чем_48 on schedule every 2 year do select 1;
select EVENT_NAME from information_schema.events
where event_schema='test';
drop event имя_события_в_кодировке_утф8_длиной_больше_чем_48;
--error 1059
create event
очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66
on schedule every 2 year do select 1;

#
# Bug#35981: ALTER EVENT causes the server to change the PRESERVE option.
#

create event event_35981 on schedule every 6 month on completion preserve
disable
do
  select 1;

echo The following SELECTs should all give 1;

# show current ON_COMPLETION
select  count(*) from information_schema.events
where   event_schema = database() and event_name = 'event_35981' and
        on_completion = 'PRESERVE';

# show ON_COMPLETION remains "PRESERVE" when not given in ALTER EVENT
alter   event event_35981 enable;
select  count(*) from information_schema.events
where   event_schema = database() and event_name = 'event_35981' and
        on_completion = 'PRESERVE';

# show we can change ON_COMPLETION
alter   event event_35981 on completion not preserve;
select  count(*) from information_schema.events
where   event_schema = database() and event_name = 'event_35981' and
        on_completion = 'NOT PRESERVE';

# show ON_COMPLETION remains "NOT PRESERVE" when not given in ALTER EVENT
alter   event event_35981 disable;
select  count(*) from information_schema.events
where   event_schema = database() and event_name = 'event_35981' and
        on_completion = 'NOT PRESERVE';

# show we can change ON_COMPLETION
alter   event event_35981 on completion preserve;
select  count(*) from information_schema.events
where   event_schema = database() and event_name = 'event_35981' and
        on_completion = 'PRESERVE';


drop event event_35981;

create event event_35981 on schedule every 6 month disable
do
  select 1;

# show that the defaults for CREATE EVENT are still correct (NOT PRESERVE)
select  count(*) from information_schema.events
where   event_schema = database() and event_name = 'event_35981' and
        on_completion = 'NOT PRESERVE';

drop event event_35981;


# show that backdating doesn't break

create event event_35981 on schedule every 1 hour starts current_timestamp
  on completion not preserve
do
  select 1;

# should fail thanks to above's NOT PRESERVE
--error ER_EVENT_CANNOT_ALTER_IN_THE_PAST
alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
  ends '1999-01-02 00:00:00';

drop event event_35981;

create event event_35981 on schedule every 1 hour starts current_timestamp
  on completion not preserve
do
  select 1;

# succeed with warning
alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
  ends '1999-01-02 00:00:00' on completion preserve;

drop event event_35981;



create event event_35981 on schedule every 1 hour starts current_timestamp
  on completion preserve
do
  select 1;

# this should succeed thanks to above PRESERVE! give a warning though.
alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
  ends '1999-01-02 00:00:00';

# this should fail, as the event would have passed already
--error ER_EVENT_CANNOT_ALTER_IN_THE_PAST
alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
  ends '1999-01-02 00:00:00' on completion not preserve;

# should succeed giving a warning
alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
  ends '1999-01-02 00:00:00' on completion preserve;

drop event event_35981;

# 
# End of tests
#

let $wait_condition=
  select count(*) = 0 from information_schema.processlist
  where db='events_test' and command = 'Connect' and user=current_user();
--source include/wait_condition.inc

drop database events_test;