From f8ff8c01e4fcc440efa7c9b0f2c7bed1b07c6582 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 20 Feb 2008 14:40:46 +0100 Subject: Patch for bug#32664: event.test has timeout randomly. File is split into event_1.test and event_2.test. BitKeeper/deleted/.del-events.test: Delete: mysql-test/t/events.test BitKeeper/deleted/.del-events.result: Delete: mysql-test/r/events.result BitKeeper/etc/ignore: Added mysql-test/reg.log to the ignore list --- mysql-test/r/events_2.result | 321 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 321 insertions(+) create mode 100644 mysql-test/r/events_2.result (limited to 'mysql-test/r/events_2.result') diff --git a/mysql-test/r/events_2.result b/mysql-test/r/events_2.result new file mode 100644 index 00000000000..9da8c37edae --- /dev/null +++ b/mysql-test/r/events_2.result @@ -0,0 +1,321 @@ +drop database if exists events_test; +create database events_test; +use events_test; +create event e_26 on schedule at '2017-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; +db name body definer convert_tz(execute_at, 'UTC', 'SYSTEM') on_completion +events_test e_26 set @a = 5 root@localhost 2017-01-01 00:00:00 DROP +drop event e_26; +create event e_26 on schedule at NULL disable do set @a = 5; +ERROR HY000: Incorrect AT value: 'NULL' +create event e_26 on schedule at 'definitely not a datetime' disable do set @a = 5; +ERROR HY000: Incorrect AT value: 'definitely not a datetime' +set names utf8; +create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1; +drop event задачка; +set event_scheduler=off; +ERROR HY000: Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL +set global event_scheduler=3; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of '3' +set global event_scheduler=disabled; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of 'disabled' +"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; +definer name db +select get_lock("test_lock1", 20); +get_lock("test_lock1", 20) +1 +create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20); +"Should return 1 row" +select definer, name, db from mysql.event; +definer name db +root@localhost закачка events_test +"Should be only 0 process" +select /*1*/ user, host, db, command, state, info +from information_schema.processlist +where (user='event_scheduler') +order by info; +user host db command state info +select release_lock("test_lock1"); +release_lock("test_lock1") +1 +drop event закачка; +"Should have 0 events" +select count(*) from mysql.event; +count(*) +0 +"ENABLE the scheduler and get a lock" +set global event_scheduler=on; +select get_lock("test_lock2", 20); +get_lock("test_lock2", 20) +1 +"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); +"Should have only 2 processes: the scheduler and the locked event" +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; +user host db command state info +event_scheduler localhost NULL Daemon Waiting for next activation NULL +root localhost events_test Connect User lock select get_lock("test_lock2", 20) +"Release the mutex, the event worker should finish." +select release_lock("test_lock2"); +release_lock("test_lock2") +1 +drop event закачка; +set global event_scheduler=1; +select get_lock("test_lock2_1", 20); +get_lock("test_lock2_1", 20) +1 +create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20); +"Should have only 2 processes: the scheduler and the locked event" +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; +user host db command state info +event_scheduler localhost NULL Daemon Waiting for next activation NULL +root localhost events_test Connect User lock select get_lock("test_lock2_1", 20) +set global event_scheduler=off; +"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; +user host db command state info +root localhost events_test Connect User lock select get_lock("test_lock2_1", 20) +select release_lock("test_lock2_1"); +release_lock("test_lock2_1") +1 +drop event закачка21; +create table t_16 (s1 int); +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; +ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present +drop table t_16; +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'; +event_schema event_name definer event_definition +events_test white_space root@localhost select 1 +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'; +event_schema event_name definer event_definition +events_test white_space root@localhost select 2 +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'; +event_schema event_name definer event_definition +events_test white_space root@localhost select 3 +drop event white_space; +create event e1 on schedule every 1 year do set @a = 5; +create table t1 (s1 int); +create trigger t1_ai after insert on t1 for each row show create event e1; +ERROR 0A000: Not allowed to return a result set from a trigger +drop table t1; +drop event e1; +SHOW EVENTS FROM aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +ERROR 42000: Incorrect database name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' +SHOW EVENTS FROM ``; +ERROR 42000: Incorrect database name '' +SHOW EVENTS FROM `events\\test`; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation + +LOCK TABLES mode. + +create table t1 (a int); +create event e1 on schedule every 10 hour do select 1; +lock table t1 read; +show create event e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8 utf8_general_ci latin1_swedish_ci +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +alter event e2 disable; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +alter event e2 rename to e3; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +drop event e2; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +drop event e1; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +unlock tables; +lock table t1 write; +show create event e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8 utf8_general_ci latin1_swedish_ci +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +alter event e2 disable; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +alter event e2 rename to e3; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +drop event e2; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +drop event e1; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +unlock tables; +lock table t1 read, mysql.event read; +show create event e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8 utf8_general_ci latin1_swedish_ci +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +alter event e2 disable; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +alter event e2 rename to e3; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +drop event e2; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +drop event e1; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +unlock tables; +lock table t1 write, mysql.event read; +show create event e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8 utf8_general_ci latin1_swedish_ci +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +alter event e2 disable; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +alter event e2 rename to e3; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +drop event e2; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +drop event e1; +ERROR HY000: Table 'event' was locked with a READ lock and can't be updated +unlock tables; +lock table t1 read, mysql.event write; +ERROR HY000: You can't combine write-locking of system tables with other tables or lock types +lock table t1 write, mysql.event write; +ERROR HY000: You can't combine write-locking of system tables with other tables or lock types +lock table mysql.event write; +show create event e1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +e1 SYSTEM CREATE EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8 utf8_general_ci latin1_swedish_ci +select event_name from information_schema.events; +event_name +e1 +create event e2 on schedule every 10 hour do select 1; +alter event e2 disable; +alter event e2 rename to e3; +drop event e3; +drop event e1; +unlock tables; +Make sure we have left no events +select event_name from information_schema.events; +event_name + +Events in sub-statements, events and prelocking + + +create event e1 on schedule every 10 hour do select 1; +create function f1() returns int +begin +show create event e1; +return 1; +end| +ERROR 0A000: Not allowed to return a result set from a function +create trigger trg before insert on t1 for each row +begin +show create event e1; +end| +ERROR 0A000: Not allowed to return a result set from a trigger +create function f1() returns int +begin +select event_name from information_schema.events; +return 1; +end| +ERROR 0A000: Not allowed to return a result set from a function +create trigger trg before insert on t1 for each row +begin +select event_name from information_schema.events; +end| +ERROR 0A000: Not allowed to return a result set from a trigger +create function f1() returns int +begin +create event e2 on schedule every 10 hour do select 1; +return 1; +end| +ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present +create function f1() returns int +begin +alter event e1 rename to e2; +return 1; +end| +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +create function f1() returns int +begin +drop event e2; +return 1; +end| +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +---------------------------------------------------------------------- +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| +insert into t1 (a) values (1)| +ERROR 0A000: Not allowed to return a result set from a trigger +drop procedure p1| +create procedure p1() +begin +show create event e1; +end| +insert into t1 (a) values (1)| +ERROR 0A000: Not allowed to return a result set from a trigger +drop procedure p1| +create procedure p1() +begin +create temporary table tmp select event_name from information_schema.events; +end| +expected to work, since we redirect the output into a tmp table +insert into t1 (a) values (1)| +select * from tmp| +event_name +e1 +drop temporary table tmp| +drop procedure p1| +create procedure p1() +begin +alter event e1 rename to e2; +end| +insert into t1 (a) values (1)| +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +drop procedure p1| +create procedure p1() +begin +drop event e1; +end| +insert into t1 (a) values (1)| +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +drop table t1| +drop event e1| +drop database events_test; -- cgit v1.2.1