Debian Codegraph @

mysql-test/suite/events/events_2.result

raw
1 set sql_mode="";
2 drop database if exists events_test;
3 create database events_test;
4 use events_test;
5 create event e_26 on schedule at '2037-01-01 00:00:00' disable do set @a = 5;
6 Warnings:
7 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
8 select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event;
9 db name body definer convert_tz(execute_at, 'UTC', 'SYSTEM') on_completion
10 events_test e_26 set @a = 5 root@localhost 2037-01-01 00:00:00 DROP
11 drop event e_26;
12 create event e_26 on schedule at NULL disable do set @a = 5;
13 ERROR HY000: Incorrect AT value: 'NULL'
14 create event e_26 on schedule at 'definitely not a datetime' disable do set @a = 5;
15 ERROR HY000: Incorrect AT value: 'definitely not a datetime'
16 set names utf8;
17 create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1;
18 Warnings:
19 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
20 drop event задачка;
21 "DISABLE the scheduler. Testing that it does not work when the variable is 0"
22 set global event_scheduler=off;
23 select definer, name, db from mysql.event;
24 definer name db
25 select get_lock("test_lock1", 20);
26 get_lock("test_lock1", 20)
27 1
28 create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20);
29 Warnings:
30 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
31 "Should return 1 row"
32 select definer, name, db from mysql.event;
33 definer name db
34 root@localhost закачка events_test
35 "Should be only 0 process"
36 select /*1*/ user, host, db, command, state, info
37 from information_schema.processlist
38 where (user='event_scheduler')
39 order by info;
40 user host db command state info
41 select release_lock("test_lock1");
42 release_lock("test_lock1")
43 1
44 drop event закачка;
45 "Should have 0 events"
46 select count(*) from mysql.event;
47 count(*)
48 0
49 "ENABLE the scheduler and get a lock"
50 set global event_scheduler=on;
51 select get_lock("test_lock2", 20);
52 get_lock("test_lock2", 20)
53 1
54 "Create an event which tries to acquire a mutex. The event locks on the mutex"
55 create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20);
56 "Should have only 2 processes: the scheduler and the locked event"
57 select /*2*/ user, host, db, command, state, info
58 from information_schema.processlist
59 where (info like "select get_lock%" OR user='event_scheduler')
60 order by info;
61 user host db command state info
62 event_scheduler localhost NULL Daemon Waiting for next activation NULL
63 root localhost events_test Connect User lock select get_lock("test_lock2", 20)
64 "Release the mutex, the event worker should finish."
65 select release_lock("test_lock2");
66 release_lock("test_lock2")
67 1
68 drop event закачка;
69 set global event_scheduler=1;
70 select get_lock("test_lock2_1", 20);
71 get_lock("test_lock2_1", 20)
72 1
73 create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20);
74 "Should have only 2 processes: the scheduler and the locked event"
75 select /*3*/ user, host, db, command, state, info
76 from information_schema.processlist
77 where (info like "select get_lock%" OR user='event_scheduler')
78 order by info;
79 user host db command state info
80 event_scheduler localhost NULL Daemon Waiting for next activation NULL
81 root localhost events_test Connect User lock select get_lock("test_lock2_1", 20)
82 set global event_scheduler=off;
83 "Should have only our process now:"
84 select /*4*/ user, host, db, command, state, info
85 from information_schema.processlist
86 where (info like "select get_lock%" OR user='event_scheduler')
87 order by info;
88 user host db command state info
89 root localhost events_test Connect User lock select get_lock("test_lock2_1", 20)
90 select release_lock("test_lock2_1");
91 release_lock("test_lock2_1")
92 1
93 drop event закачка21;
94 create table t_16 (s1 int);
95 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;
96 ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present
97 drop table t_16;
98 create event white_space
99 on schedule every 10 hour
100 disable
101 do
102 select 1;
103 Warnings:
104 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
105 select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
106 event_schema event_name definer event_definition
107 events_test white_space root@localhost select 1
108 drop event white_space;
109 create event white_space on schedule every 10 hour disable do
110 select 2;
111 Warnings:
112 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
113 select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
114 event_schema event_name definer event_definition
115 events_test white_space root@localhost select 2
116 drop event white_space;
117 create event white_space on schedule every 10 hour disable do select 3;
118 Warnings:
119 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
120 select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
121 event_schema event_name definer event_definition
122 events_test white_space root@localhost select 3
123 drop event white_space;
124 create event e1 on schedule every 1 year do set @a = 5;
125 Warnings:
126 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
127 create table t1 (s1 int);
128 create trigger t1_ai after insert on t1 for each row show create event e1;
129 ERROR 0A000: Not allowed to return a result set from a trigger
130 drop table t1;
131 drop event e1;
132 SHOW EVENTS FROM aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
133 ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
134 SHOW EVENTS FROM ``;
135 ERROR 42000: Incorrect database name ''
136 SHOW EVENTS FROM `events\\test`;
137 Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
138
139 LOCK TABLES mode.
140
141 create table t1 (a int);
142 create event e1 on schedule every 10 hour do select 1;
143 Warnings:
144 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
145 lock table t1 read;
146 show create event e1;
147 Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
148 e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_uca1400_ai_ci utf8mb4_uca1400_ai_ci
149 select event_name from information_schema.events;
150 event_name
151 e1
152 create event e2 on schedule every 10 hour do select 1;
153 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
154 alter event e2 disable;
155 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
156 alter event e2 rename to e3;
157 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
158 drop event e2;
159 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
160 drop event e1;
161 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
162 unlock tables;
163 lock table t1 write;
164 show create event e1;
165 Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
166 e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_uca1400_ai_ci utf8mb4_uca1400_ai_ci
167 select event_name from information_schema.events;
168 event_name
169 e1
170 create event e2 on schedule every 10 hour do select 1;
171 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
172 alter event e2 disable;
173 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
174 alter event e2 rename to e3;
175 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
176 drop event e2;
177 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
178 drop event e1;
179 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
180 unlock tables;
181 lock table t1 read, mysql.event read;
182 show create event e1;
183 Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
184 e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_uca1400_ai_ci utf8mb4_uca1400_ai_ci
185 select event_name from information_schema.events;
186 event_name
187 e1
188 create event e2 on schedule every 10 hour do select 1;
189 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
190 alter event e2 disable;
191 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
192 alter event e2 rename to e3;
193 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
194 drop event e2;
195 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
196 drop event e1;
197 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
198 unlock tables;
199 lock table t1 write, mysql.event read;
200 show create event e1;
201 Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
202 e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_uca1400_ai_ci utf8mb4_uca1400_ai_ci
203 select event_name from information_schema.events;
204 event_name
205 e1
206 create event e2 on schedule every 10 hour do select 1;
207 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
208 alter event e2 disable;
209 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
210 alter event e2 rename to e3;
211 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
212 drop event e2;
213 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
214 drop event e1;
215 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
216 unlock tables;
217 lock table t1 read, mysql.event write;
218 ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
219 lock table t1 write, mysql.event write;
220 ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
221 lock table mysql.event write;
222 show create event e1;
223 Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
224 e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8mb3 utf8mb3_uca1400_ai_ci utf8mb4_uca1400_ai_ci
225 select event_name from information_schema.events;
226 event_name
227 e1
228 create event e2 on schedule every 10 hour do select 1;
229 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
230 alter event e2 disable;
231 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
232 alter event e2 rename to e3;
233 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
234 drop event e3;
235 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
236 drop event e1;
237 ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
238 unlock tables;
239 drop event e1;
240 Make sure we have left no events
241 select event_name from information_schema.events;
242 event_name
243
244 Events in sub-statements, events and prelocking
245
246
247 create event e1 on schedule every 10 hour do select 1;
248 Warnings:
249 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
250 create function f1() returns int
251 begin
252 show create event e1;
253 return 1;
254 end|
255 ERROR 0A000: Not allowed to return a result set from a function
256 create trigger trg before insert on t1 for each row
257 begin
258 show create event e1;
259 end|
260 ERROR 0A000: Not allowed to return a result set from a trigger
261 create function f1() returns int
262 begin
263 select event_name from information_schema.events;
264 return 1;
265 end|
266 ERROR 0A000: Not allowed to return a result set from a function
267 create trigger trg before insert on t1 for each row
268 begin
269 select event_name from information_schema.events;
270 end|
271 ERROR 0A000: Not allowed to return a result set from a trigger
272 create function f1() returns int
273 begin
274 create event e2 on schedule every 10 hour do select 1;
275 return 1;
276 end|
277 ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present
278 create function f1() returns int
279 begin
280 alter event e1 rename to e2;
281 return 1;
282 end|
283 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
284 create function f1() returns int
285 begin
286 drop event e2;
287 return 1;
288 end|
289 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
290 ----------------------------------------------------------------------
291 create trigger trg before insert on t1 for each row
292 begin
293 set new.a= f1();
294 end|
295 create function f1() returns int
296 begin
297 call p1();
298 return 0;
299 end|
300 create procedure p1()
301 begin
302 select event_name from information_schema.events;
303 end|
304 insert into t1 (a) values (1)|
305 ERROR 0A000: Not allowed to return a result set from a trigger
306 drop procedure p1|
307 create procedure p1()
308 begin
309 show create event e1;
310 end|
311 insert into t1 (a) values (1)|
312 ERROR 0A000: Not allowed to return a result set from a trigger
313 drop procedure p1|
314 create procedure p1()
315 begin
316 create temporary table tmp select event_name from information_schema.events;
317 end|
318 expected to work, since we redirect the output into a tmp table
319 insert into t1 (a) values (1)|
320 select * from tmp|
321 event_name
322 e1
323 drop temporary table tmp|
324 drop procedure p1|
325 create procedure p1()
326 begin
327 alter event e1 rename to e2;
328 end|
329 insert into t1 (a) values (1)|
330 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
331 drop procedure p1|
332 create procedure p1()
333 begin
334 drop event e1;
335 end|
336 insert into t1 (a) values (1)|
337 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
338 drop table t1|
339 drop event e1|
340 set names utf8;
341 create event имя_события_в_кодировке_утф8_длиной_больше_чем_48 on schedule every 2 year do select 1;
342 Warnings:
343 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
344 select EVENT_NAME from information_schema.events
345 where event_schema='test';
346 EVENT_NAME
347 drop event имя_события_в_кодировке_утф8_длиной_больше_чем_48;
348 create event
349 очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66
350 on schedule every 2 year do select 1;
351 ERROR 42000: Identifier name 'очень_очень_очень_очень_очень_очень_очень_очень_длин...' is too long
352 create event event_35981 on schedule every 6 month on completion preserve
353 disable
354 do
355 select 1;
356 Warnings:
357 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
358 The following SELECTs should all give 1
359 select count(*) from information_schema.events
360 where event_schema = database() and event_name = 'event_35981' and
361 on_completion = 'PRESERVE';
362 count(*)
363 1
364 alter event event_35981 enable;
365 select count(*) from information_schema.events
366 where event_schema = database() and event_name = 'event_35981' and
367 on_completion = 'PRESERVE';
368 count(*)
369 1
370 alter event event_35981 on completion not preserve;
371 select count(*) from information_schema.events
372 where event_schema = database() and event_name = 'event_35981' and
373 on_completion = 'NOT PRESERVE';
374 count(*)
375 1
376 alter event event_35981 disable;
377 select count(*) from information_schema.events
378 where event_schema = database() and event_name = 'event_35981' and
379 on_completion = 'NOT PRESERVE';
380 count(*)
381 1
382 alter event event_35981 on completion preserve;
383 select count(*) from information_schema.events
384 where event_schema = database() and event_name = 'event_35981' and
385 on_completion = 'PRESERVE';
386 count(*)
387 1
388 drop event event_35981;
389 create event event_35981 on schedule every 6 month disable
390 do
391 select 1;
392 Warnings:
393 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
394 select count(*) from information_schema.events
395 where event_schema = database() and event_name = 'event_35981' and
396 on_completion = 'NOT PRESERVE';
397 count(*)
398 1
399 drop event event_35981;
400 create event event_35981 on schedule every 1 hour starts current_timestamp
401 on completion not preserve
402 do
403 select 1;
404 Warnings:
405 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
406 alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
407 ends '1999-01-02 00:00:00';
408 ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future
409 drop event event_35981;
410 create event event_35981 on schedule every 1 hour starts current_timestamp
411 on completion not preserve
412 do
413 select 1;
414 Warnings:
415 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
416 alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
417 ends '1999-01-02 00:00:00' on completion preserve;
418 Warnings:
419 Note 1544 Event execution time is in the past. Event has been disabled
420 drop event event_35981;
421 create event event_35981 on schedule every 1 hour starts current_timestamp
422 on completion preserve
423 do
424 select 1;
425 Warnings:
426 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
427 alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
428 ends '1999-01-02 00:00:00';
429 Warnings:
430 Note 1544 Event execution time is in the past. Event has been disabled
431 alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
432 ends '1999-01-02 00:00:00' on completion not preserve;
433 ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future
434 alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00'
435 ends '1999-01-02 00:00:00' on completion preserve;
436 Warnings:
437 Note 1544 Event execution time is in the past. Event has been disabled
438 drop event event_35981;
439 drop database events_test;
440 #
441 # MDEV-37744 Table Charset Mismatch (Primary/Replica) via Event
442 #
443 set global event_scheduler=1;
444 create schema andre default charset utf8mb4 collate utf8mb4_general_ci;
445 use andre;
446 create event daily_table_creation
447 on schedule every 1 day starts now() + interval 5 second do
448 create table andre_table (
449 id int(11) not null auto_increment primary key,
450 col_1 varchar(50) not null
451 );
452 show create table andre_table;
453 Table Create Table
454 andre_table CREATE TABLE `andre_table` (
455 `id` int(11) NOT NULL AUTO_INCREMENT,
456 `col_1` varchar(50) NOT NULL,
457 PRIMARY KEY (`id`)
458 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
459 set global event_scheduler=0;
460 drop event daily_table_creation;
461 drop schema andre;
462 use test;
463 # End of 10.11 tests