Debian Codegraph @

mysql-test/suite/events/events_2.test

raw
1 # changes 2008-02-20 hhunger splitted events.test into events_1 and events_2
2 #
3 # Can't test with embedded server that doesn't support grants
4 -- source include/not_embedded.inc
5
6 --disable_service_connection
7
8 set sql_mode="";
9 --source include/default_charset.inc
10
11 --disable_warnings
12 drop database if exists events_test;
13 --enable_warnings
14 create database events_test;
15 use events_test;
16
17 #
18 # mysql.event intact checking end
19 #
20
21 create event e_26 on schedule at '2037-01-01 00:00:00' disable do set @a = 5;
22 select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event;
23 drop event e_26;
24 --error ER_WRONG_VALUE
25 create event e_26 on schedule at NULL disable do set @a = 5;
26 --error ER_WRONG_VALUE
27 create event e_26 on schedule at 'definitely not a datetime' disable do set @a = 5;
28
29 set names utf8;
30 create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1;
31 drop event задачка;
32
33 --echo "DISABLE the scheduler. Testing that it does not work when the variable is 0"
34 set global event_scheduler=off;
35 select definer, name, db from mysql.event;
36 select get_lock("test_lock1", 20);
37 create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20);
38 --echo "Should return 1 row"
39 select definer, name, db from mysql.event;
40
41 --echo "Should be only 0 process"
42 select /*1*/ user, host, db, command, state, info
43 from information_schema.processlist
44 where (user='event_scheduler')
45 order by info;
46 select release_lock("test_lock1");
47 drop event закачка;
48 --echo "Should have 0 events"
49 select count(*) from mysql.event;
50
51 #
52 #
53 #
54 --echo "ENABLE the scheduler and get a lock"
55 set global event_scheduler=on;
56 select get_lock("test_lock2", 20);
57 --echo "Create an event which tries to acquire a mutex. The event locks on the mutex"
58 create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20);
59
60 --echo "Should have only 2 processes: the scheduler and the locked event"
61 let $wait_condition= select count(*) = 2 from information_schema.processlist
62 where ( (state like 'User lock%' AND info like 'select get_lock%')
63 OR (command='Daemon' AND user='event_scheduler' AND
64 state = 'Waiting for next activation'));
65 --source include/wait_condition.inc
66
67 select /*2*/ user, host, db, command, state, info
68 from information_schema.processlist
69 where (info like "select get_lock%" OR user='event_scheduler')
70 order by info;
71 --echo "Release the mutex, the event worker should finish."
72 select release_lock("test_lock2");
73 drop event закачка;
74
75 # Wait for get_lock("test_lock2") to complete,
76 # to avoid polluting the next test information_schema.processlist
77 let $wait_condition= select count(*) = 0 from information_schema.processlist
78 where info='select get_lock("test_lock2", 20)';
79 --source include/wait_condition.inc
80
81
82 ##
83 ## 1. get a lock
84 ## 2. create an event
85 ## 3. sleep so it has time to start
86 ## 4. should appear in processlist
87 ## 5. kill the scheduler, it will wait for the child to stop
88 ## 6. both processes should be there on show processlist
89 ## 7. release the lock and sleep, both scheduler and child should end
90 set global event_scheduler=1;
91 select get_lock("test_lock2_1", 20);
92 create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20);
93
94 --echo "Should have only 2 processes: the scheduler and the locked event"
95 let $wait_condition= select count(*) = 2 from information_schema.processlist
96 where ( (state like 'User lock%' AND info like 'select get_lock%')
97 OR (command='Daemon' AND user='event_scheduler' AND
98 state = 'Waiting for next activation'));
99 --source include/wait_condition.inc
100
101 select /*3*/ user, host, db, command, state, info
102 from information_schema.processlist
103 where (info like "select get_lock%" OR user='event_scheduler')
104 order by info;
105
106 set global event_scheduler=off;
107
108 let $wait_condition= select count(*) =1 from information_schema.processlist
109 where (info like "select get_lock%" OR user='event_scheduler');
110 --source include/wait_condition.inc
111
112 --echo "Should have only our process now:"
113 select /*4*/ user, host, db, command, state, info
114 from information_schema.processlist
115 where (info like "select get_lock%" OR user='event_scheduler')
116 order by info;
117 select release_lock("test_lock2_1");
118 drop event закачка21;
119 let $wait_condition=
120 select count(*) = 0 from information_schema.processlist
121 where db='events_test' and command = 'Connect' and user=current_user();
122 --source include/wait_condition.inc
123
124 ####
125 # Bug #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement
126 #
127 create table t_16 (s1 int);
128 --error ER_EVENT_RECURSION_FORBIDDEN
129 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;
130 drop table t_16;
131 #
132 # end of test case
133 ####
134
135 #
136 # START: BUG #17453: Creating Event crash the server
137 #
138 create event white_space
139 on schedule every 10 hour
140 disable
141 do
142 select 1;
143 select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
144 drop event white_space;
145 create event white_space on schedule every 10 hour disable do
146
147 select 2;
148 select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
149 drop event white_space;
150 create event white_space on schedule every 10 hour disable do select 3;
151 select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
152 drop event white_space;
153 #
154 # END: BUG #17453: Creating Event crash the server
155 #
156
157 #
158 # Bug#17403 "Events: packets out of order with show create event"
159 #
160 create event e1 on schedule every 1 year do set @a = 5;
161 create table t1 (s1 int);
162 --error ER_SP_NO_RETSET
163 create trigger t1_ai after insert on t1 for each row show create event e1;
164 drop table t1;
165 drop event e1;
166
167 ##set global event_scheduler=1;
168 ##select get_lock("test_lock3", 20);
169 ##create event закачка on schedule every 10 hour do select get_lock("test_lock3", 20);
170 ##select sleep(2);
171 ##select /*5*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
172 ##drop event закачка;
173 ##select release_lock("test_lock3");
174
175 #
176 # test with very often occuring event
177 # (disabled for now, locks)
178 ##select get_lock("test_lock4", 20);
179 ##create event закачка4 on schedule every 1 second do select get_lock("test_lock4", 20);
180 ##select sleep(3);
181 ##select /*6*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
182 ##drop event закачка4;
183 ##select release_lock("test_lock4");
184
185 ##set global event_scheduler=off;
186 ##select sleep(2);
187 ##--replace_column 1 # 6 #
188 ##show processlist;
189 ##select count(*) from mysql.event;
190
191 #
192 # Test wrong syntax
193 #
194
195 --error ER_WRONG_DB_NAME
196 SHOW EVENTS FROM aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
197 --error ER_WRONG_DB_NAME
198 SHOW EVENTS FROM ``;
199
200 SHOW EVENTS FROM `events\\test`;
201 #
202 # A check for events SQL under LOCK TABLES and in pre-locked mode.
203 #
204 --echo
205 --echo LOCK TABLES mode.
206 --echo
207 #
208 # SHOW CREATE EVENT and INFORMATION_SCHEMA.events are available and
209 # cause an implicit lock/unlock of mysql.event table, regardless of the
210 # currently locked tables.
211 #
212 create table t1 (a int);
213 create event e1 on schedule every 10 hour do select 1;
214 #
215 lock table t1 read;
216 #
217 --replace_regex /STARTS '[^']+'/STARTS '#'/
218 show create event e1;
219 select event_name from information_schema.events;
220 --error ER_LOCK_OR_ACTIVE_TRANSACTION
221 create event e2 on schedule every 10 hour do select 1;
222 --error ER_LOCK_OR_ACTIVE_TRANSACTION
223 alter event e2 disable;
224 --error ER_LOCK_OR_ACTIVE_TRANSACTION
225 alter event e2 rename to e3;
226 --error ER_LOCK_OR_ACTIVE_TRANSACTION
227 drop event e2;
228 --error ER_LOCK_OR_ACTIVE_TRANSACTION
229 drop event e1;
230 unlock tables;
231 #
232 lock table t1 write;
233 #
234 --replace_regex /STARTS '[^']+'/STARTS '#'/
235 show create event e1;
236 select event_name from information_schema.events;
237 --error ER_LOCK_OR_ACTIVE_TRANSACTION
238 create event e2 on schedule every 10 hour do select 1;
239 --error ER_LOCK_OR_ACTIVE_TRANSACTION
240 alter event e2 disable;
241 --error ER_LOCK_OR_ACTIVE_TRANSACTION
242 alter event e2 rename to e3;
243 --error ER_LOCK_OR_ACTIVE_TRANSACTION
244 drop event e2;
245 --error ER_LOCK_OR_ACTIVE_TRANSACTION
246 drop event e1;
247 unlock tables;
248 #
249 lock table t1 read, mysql.event read;
250 #
251 --replace_regex /STARTS '[^']+'/STARTS '#'/
252 show create event e1;
253 select event_name from information_schema.events;
254 --error ER_LOCK_OR_ACTIVE_TRANSACTION
255 create event e2 on schedule every 10 hour do select 1;
256 --error ER_LOCK_OR_ACTIVE_TRANSACTION
257 alter event e2 disable;
258 --error ER_LOCK_OR_ACTIVE_TRANSACTION
259 alter event e2 rename to e3;
260 --error ER_LOCK_OR_ACTIVE_TRANSACTION
261 drop event e2;
262 --error ER_LOCK_OR_ACTIVE_TRANSACTION
263 drop event e1;
264 unlock tables;
265 #
266 lock table t1 write, mysql.event read;
267 #
268 --replace_regex /STARTS '[^']+'/STARTS '#'/
269 show create event e1;
270 select event_name from information_schema.events;
271 --error ER_LOCK_OR_ACTIVE_TRANSACTION
272 create event e2 on schedule every 10 hour do select 1;
273 --error ER_LOCK_OR_ACTIVE_TRANSACTION
274 alter event e2 disable;
275 --error ER_LOCK_OR_ACTIVE_TRANSACTION
276 alter event e2 rename to e3;
277 --error ER_LOCK_OR_ACTIVE_TRANSACTION
278 drop event e2;
279 --error ER_LOCK_OR_ACTIVE_TRANSACTION
280 drop event e1;
281 unlock tables;
282 #
283 --error ER_WRONG_LOCK_OF_SYSTEM_TABLE
284 lock table t1 read, mysql.event write;
285 #
286 --error ER_WRONG_LOCK_OF_SYSTEM_TABLE
287 lock table t1 write, mysql.event write;
288 #
289 lock table mysql.event write;
290 --replace_regex /STARTS '[^']+'/STARTS '#'/
291 show create event e1;
292 select event_name from information_schema.events;
293 --error ER_LOCK_OR_ACTIVE_TRANSACTION
294 create event e2 on schedule every 10 hour do select 1;
295 --error ER_LOCK_OR_ACTIVE_TRANSACTION
296 alter event e2 disable;
297 --error ER_LOCK_OR_ACTIVE_TRANSACTION
298 alter event e2 rename to e3;
299 --error ER_LOCK_OR_ACTIVE_TRANSACTION
300 drop event e3;
301 --error ER_LOCK_OR_ACTIVE_TRANSACTION
302 drop event e1;
303 unlock tables;
304 drop event e1;
305 --echo Make sure we have left no events
306 select event_name from information_schema.events;
307 --echo
308 --echo Events in sub-statements, events and prelocking
309 --echo
310 --echo
311 create event e1 on schedule every 10 hour do select 1;
312 delimiter |;
313 --error ER_SP_NO_RETSET
314 create function f1() returns int
315 begin
316 show create event e1;
317 return 1;
318 end|
319 --error ER_SP_NO_RETSET
320 create trigger trg before insert on t1 for each row
321 begin
322 show create event e1;
323 end|
324 --error ER_SP_NO_RETSET
325 create function f1() returns int
326 begin
327 select event_name from information_schema.events;
328 return 1;
329 end|
330 --error ER_SP_NO_RETSET
331 create trigger trg before insert on t1 for each row
332 begin
333 select event_name from information_schema.events;
334 end|
335 --error ER_EVENT_RECURSION_FORBIDDEN
336 create function f1() returns int
337 begin
338 create event e2 on schedule every 10 hour do select 1;
339 return 1;
340 end|
341 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
342 create function f1() returns int
343 begin
344 alter event e1 rename to e2;
345 return 1;
346 end|
347 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
348 create function f1() returns int
349 begin
350 drop event e2;
351 return 1;
352 end|
353 --echo ----------------------------------------------------------------------
354 create trigger trg before insert on t1 for each row
355 begin
356 set new.a= f1();
357 end|
358 create function f1() returns int
359 begin
360 call p1();
361 return 0;
362 end|
363 create procedure p1()
364 begin
365 select event_name from information_schema.events;
366 end|
367 --error ER_SP_NO_RETSET
368 insert into t1 (a) values (1)|
369 drop procedure p1|
370 create procedure p1()
371 begin
372 show create event e1;
373 end|
374 --error ER_SP_NO_RETSET
375 insert into t1 (a) values (1)|
376 drop procedure p1|
377 create procedure p1()
378 begin
379 create temporary table tmp select event_name from information_schema.events;
380 end|
381 --echo expected to work, since we redirect the output into a tmp table
382 insert into t1 (a) values (1)|
383 select * from tmp|
384 drop temporary table tmp|
385 drop procedure p1|
386 create procedure p1()
387 begin
388 alter event e1 rename to e2;
389 end|
390 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
391 insert into t1 (a) values (1)|
392 drop procedure p1|
393 create procedure p1()
394 begin
395 drop event e1;
396 end|
397 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
398 insert into t1 (a) values (1)|
399 drop table t1|
400 drop event e1|
401 delimiter ;|
402
403 #
404 # Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte
405 #
406 set names utf8;
407 create event имя_события_в_кодировке_утф8_длиной_больше_чем_48 on schedule every 2 year do select 1;
408 select EVENT_NAME from information_schema.events
409 where event_schema='test';
410 drop event имя_события_в_кодировке_утф8_длиной_больше_чем_48;
411 --error 1059
412 create event
413 очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66
414 on schedule every 2 year do select 1;
415
416 #
417 # Bug#35981: ALTER EVENT causes the server to change the PRESERVE option.
418 #
419
420 create event event_35981 on schedule every 6 month on completion preserve
421 disable
422 do
423 select 1;
424
425 echo The following SELECTs should all give 1;
426
427 # show current ON_COMPLETION
428 select count(*) from information_schema.events
429 where event_schema = database() and event_name = 'event_35981' and
430 on_completion = 'PRESERVE';
431
432 # show ON_COMPLETION remains "PRESERVE" when not given in ALTER EVENT
433 alter event event_35981 enable;
434 select count(*) from information_schema.events
435 where event_schema = database() and event_name = 'event_35981' and
436 on_completion = 'PRESERVE';
437
438 # show we can change ON_COMPLETION
439 alter event event_35981 on completion not preserve;
440 select count(*) from information_schema.events
441 where event_schema = database() and event_name = 'event_35981' and
442 on_completion = 'NOT PRESERVE';
443
444 # show ON_COMPLETION remains "NOT PRESERVE" when not given in ALTER EVENT
445 alter event event_35981 disable;
446 select count(*) from information_schema.events
447 where event_schema = database() and event_name = 'event_35981' and
448 on_completion = 'NOT PRESERVE';
449
450 # show we can change ON_COMPLETION
451 alter event event_35981 on completion preserve;
452 select count(*) from information_schema.events
453 where event_schema = database() and event_name = 'event_35981' and
454 on_completion = 'PRESERVE';
455
456
457 drop event event_35981;
458
459 create event event_35981 on schedule every 6 month disable
460 do
461 select 1;
462
463 # show that the defaults for CREATE EVENT are still correct (NOT PRESERVE)
464 select count(*) from information_schema.events
465 where event_schema = database() and event_name = 'event_35981' and
466 on_completion = 'NOT PRESERVE';
467
468 drop event event_35981;
469
470
471 # show that backdating doesn't break
472
473 create event event_35981 on schedule every 1 hour starts current_timestamp
474 on completion not preserve
475 do
476 select 1;
477
478 # should fail thanks to above's NOT PRESERVE
479 --error ER_EVENT_CANNOT_ALTER_IN_THE_PAST
480 alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
481 ends '1999-01-02 00:00:00';
482
483 drop event event_35981;
484
485 create event event_35981 on schedule every 1 hour starts current_timestamp
486 on completion not preserve
487 do
488 select 1;
489
490 # succeed with warning
491 alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
492 ends '1999-01-02 00:00:00' on completion preserve;
493
494 drop event event_35981;
495
496
497
498 create event event_35981 on schedule every 1 hour starts current_timestamp
499 on completion preserve
500 do
501 select 1;
502
503 # this should succeed thanks to above PRESERVE! give a warning though.
504 alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
505 ends '1999-01-02 00:00:00';
506
507 # this should fail, as the event would have passed already
508 --error ER_EVENT_CANNOT_ALTER_IN_THE_PAST
509 alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
510 ends '1999-01-02 00:00:00' on completion not preserve;
511
512 # should succeed giving a warning
513 alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
514 ends '1999-01-02 00:00:00' on completion preserve;
515
516 drop event event_35981;
517
518 #
519 # End of tests
520 #
521
522 let $wait_condition=
523 select count(*) = 0 from information_schema.processlist
524 where db='events_test' and command = 'Connect' and user=current_user();
525 --source include/wait_condition.inc
526
527 drop database events_test;
528 --enable_service_connection
529
530 --echo #
531 --echo # MDEV-37744 Table Charset Mismatch (Primary/Replica) via Event
532 --echo #
533 set global event_scheduler=1;
534 create schema andre default charset utf8mb4 collate utf8mb4_general_ci;
535 use andre;
536 create event daily_table_creation
537 on schedule every 1 day starts now() + interval 5 second do
538 create table andre_table (
539 id int(11) not null auto_increment primary key,
540 col_1 varchar(50) not null
541 );
542
543 let $wait_condition= select count(*)= 1 from information_schema.tables where table_name = 'andre_table';
544 source include/wait_condition.inc;
545 show create table andre_table;
546 set global event_scheduler=0;
547 drop event daily_table_creation;
548 drop schema andre;
549 use test;
550
551 --echo # End of 10.11 tests