create database if not exists events_test; use events_test; CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1; CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2; ERROR HY000: Event 'Lower_case' already exists DROP EVENT Lower_case; SET NAMES cp1251; CREATE EVENT �����_��������_1251 ON SCHEDULE EVERY 1 YEAR DO SELECT 100; CREATE EVENT �����_��������_1251 ON SCHEDULE EVERY 2 YEAR DO SELECT 200; ERROR HY000: Event 'ДоЛеН_регистър_1251' already exists DROP EVENT �����_��������_1251; SET NAMES utf8; CREATE EVENT долен_регистър_утф8 ON SCHEDULE EVERY 3 YEAR DO SELECT 300; CREATE EVENT ДОЛЕН_регистър_утф8 ON SCHEDULE EVERY 4 YEAR DO SELECT 400; ERROR HY000: Event 'ДОЛЕН_регистър_утф8' already exists DROP EVENT ДОЛЕН_регистър_утф8; SET NAMES latin1; set @a=3; CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5; ERROR HY000: Recursivity of EVENT DDL statements is forbidden when body is present create event e_55 on schedule at 99990101000000 do drop table t; ERROR HY000: Incorrect AT value: '99990101000000' create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t; ERROR HY000: Incorrect STARTS value: '99990101000000' create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t; ERROR HY000: ENDS is either invalid or before STARTS create event e_55 on schedule at 10000101000000 do drop table t; ERROR HY000: Activation (AT) time is in the past create event e_55 on schedule at 20000101000000 do drop table t; ERROR HY000: Activation (AT) time is in the past create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'starts 10000101000000 do drop table t' at line 1 create event e_55 on schedule at 20200101000000 ends 10000101000000 do drop table t; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ends 10000101000000 do drop table t' at line 1 create event e_55 on schedule at 20200101000000 starts 10000101000000 ends 10000101000000 do drop table t; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'starts 10000101000000 ends 10000101000000 do drop table t' at line 1 create event e_55 on schedule every 10 hour starts 10000101000000 do drop table t; ERROR HY000: Incorrect STARTS value: '10000101000000' set global event_scheduler=off; delete from mysql.event; set global event_scheduler= on; set @old_sql_mode:=@@sql_mode; set sql_mode=ansi; select get_lock('test_bug16407', 60); get_lock('test_bug16407', 60) 1 create event e_16407 on schedule every 60 second do begin select get_lock('test_bug16407', 60); drop table "hashed_num"; end| "Now if everything is fine the event has compiled and is locked select /*1*/ user, host, db, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; user host db info root localhost events_test select get_lock('test_bug16407', 60) select release_lock('test_bug16407'); release_lock('test_bug16407') 1 set global event_scheduler= off; select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; event_schema event_name sql_mode events_test e_16407 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI "Let's check whether we change the sql_mode on ALTER EVENT" set sql_mode='traditional'; alter event e_16407 do select 1; select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; event_schema event_name sql_mode 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 drop event e_16407; set sql_mode="ansi"; select get_lock('ee_16407_2', 60); get_lock('ee_16407_2', 60) 1 set global event_scheduler= 1; "Another sql_mode test" set sql_mode="traditional"; create table events_smode_test(ev_name char(10), a date) engine=myisam; "This should never insert something" create event ee_16407_2 on schedule every 60 second do begin select get_lock('ee_16407_2', 60) /*ee_16407_2*/; select release_lock('ee_16407_2'); insert into events_test.events_smode_test values('ee_16407_2','1980-19-02'); end| insert into events_smode_test values ('test','1980-19-02')| ERROR 22007: Incorrect date value: '1980-19-02' for column 'a' at row 1 "This is ok" create event ee_16407_3 on schedule every 60 second do begin select get_lock('ee_16407_2', 60) /*ee_16407_3*/; select release_lock('ee_16407_2'); insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19'); insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29'); end| set sql_mode=""| "This will insert rows but they will be truncated" create event ee_16407_4 on schedule every 60 second do begin select get_lock('ee_16407_2', 60) /*ee_16407_4*/; select release_lock('ee_16407_2'); insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956'); end| select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; event_schema event_name sql_mode 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 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 events_test ee_16407_4 select /*2*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; user host db info event_scheduler localhost NULL NULL root localhost events_test select get_lock('ee_16407_2', 60) root localhost events_test select get_lock('ee_16407_2', 60) root localhost events_test select get_lock('ee_16407_2', 60) select release_lock('ee_16407_2'); release_lock('ee_16407_2') 1 select /*3*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; user host db info event_scheduler localhost NULL NULL set global event_scheduler= off; select * from events_smode_test order by ev_name, a; ev_name a ee_16407_3 1980-02-19 ee_16407_3 1980-02-29 ee_16407_4 0000-00-00 "OK, last check before we drop them" select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; event_schema event_name sql_mode 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 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 events_test ee_16407_4 drop event ee_16407_2; drop event ee_16407_3; drop event ee_16407_4; "And now one last test regarding sql_mode and call of SP from an event" delete from events_smode_test; set sql_mode='ansi'; select get_lock('ee_16407_5', 60); get_lock('ee_16407_5', 60) 1 set global event_scheduler= on; set sql_mode='traditional'; create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end| create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end| create event ee_16407_5 on schedule every 60 second do begin select get_lock('ee_16407_5', 60) /*ee_16407_5*/; select release_lock('ee_16407_5'); call events_test.ee_16407_5_pendant(); end| create event ee_16407_6 on schedule every 60 second do begin select get_lock('ee_16407_5', 60) /*ee_16407_6*/; select release_lock('ee_16407_5'); call events_test.ee_16407_6_pendant(); end| "Should have 2 locked processes" select /*4*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; user host db info event_scheduler localhost NULL NULL root localhost events_test select get_lock('ee_16407_5', 60) root localhost events_test select get_lock('ee_16407_5', 60) select release_lock('ee_16407_5'); release_lock('ee_16407_5') 1 "Should have 0 processes locked" select /*5*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; user host db info event_scheduler localhost NULL NULL select * from events_smode_test order by ev_name, a; ev_name a ee_16407_6 2004-02-29 "And here we check one more time before we drop the events" select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; event_schema event_name sql_mode 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 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 drop event ee_16407_5; drop event ee_16407_6; drop procedure ee_16407_5_pendant; drop procedure ee_16407_6_pendant; set global event_scheduler= off; drop table events_smode_test; set sql_mode=@old_sql_mode; set global event_scheduler=off; delete from mysql.user where User like 'mysqltest_%'; delete from mysql.db where User like 'mysqltest_%'; flush privileges; drop database if exists mysqltest_db1; create user mysqltest_user1@localhost; create database mysqltest_db1; grant event on events_test.* to mysqltest_user1@localhost; create event mysqltest_user1 on schedule every 10 second do select 42; alter event mysqltest_user1 rename to mysqltest_db1.mysqltest_user1; ERROR 42000: Access denied for user 'mysqltest_user1'@'localhost' to database 'mysqltest_db1' "Let's test now rename when there is no select DB" select database(); database() NULL alter event events_test.mysqltest_user1 rename to mysqltest_user1; ERROR 3D000: No database selected select event_schema, event_name, definer, event_type, status from information_schema.events; event_schema event_name definer event_type status events_test mysqltest_user1 mysqltest_user1@localhost RECURRING ENABLED drop event events_test.mysqltest_user1; drop user mysqltest_user1@localhost; drop database mysqltest_db1; create event e_53 on schedule at (select s1 from ttx) do drop table t; ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' create event e_53 on schedule every (select s1 from ttx) second do drop table t; ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t; ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t; ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' drop event if exists e_16; drop procedure if exists p_16; create event e_16 on schedule every 1 second do set @a=5; create procedure p_16 () alter event e_16 on schedule every @a second; set @a = null; call p_16(); ERROR HY000: Incorrect INTERVAL value: 'NULL' call p_16(); ERROR HY000: Incorrect INTERVAL value: 'NULL' set @a= 6; call p_16(); drop procedure p_16; drop event e_16; drop function if exists f22830; drop event if exists e22830; drop event if exists e22830_1; drop event if exists e22830_2; drop event if exists e22830_3; drop event if exists e22830_4; drop table if exists t1; drop table if exists t2; create table t1 (a int); insert into t1 values (2); create table t2 (a char(20)); insert into t2 values ("e22830_1"); create function f22830 () returns int return 5; create event e22830 on schedule every f22830() second do select 123; ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' create event e22830_1 on schedule every 1 hour do alter event e22830_1 on schedule every (select 8 from dual) hour; create event e22830_2 on schedule every 1 hour do alter event e22830_2 on schedule every (select 8 from t1) hour; create event e22830_3 on schedule every 1 hour do alter event e22830_3 on schedule every f22830() hour; create event e22830_4 on schedule every 1 hour do alter event e22830_4 on schedule every (select f22830() from dual) hour; select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; event_name event_definition interval_value interval_field e22830_1 alter event e22830_1 on schedule every (select 8 from dual) hour 1 HOUR e22830_2 alter event e22830_2 on schedule every (select 8 from t1) hour 1 HOUR e22830_3 alter event e22830_3 on schedule every f22830() hour 1 HOUR e22830_4 alter event e22830_4 on schedule every (select f22830() from dual) hour 1 HOUR set global event_scheduler=on; set global event_scheduler=off; select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; event_name event_definition interval_value interval_field e22830_1 alter event e22830_1 on schedule every (select 8 from dual) hour 8 HOUR e22830_2 alter event e22830_2 on schedule every (select 8 from t1) hour 1 HOUR e22830_3 alter event e22830_3 on schedule every f22830() hour 1 HOUR e22830_4 alter event e22830_4 on schedule every (select f22830() from dual) hour 1 HOUR drop function f22830; drop event (select a from t2); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select a from t2)' at line 1 drop event e22830_1; drop event e22830_2; drop event e22830_3; drop event e22830_4; drop table t1; drop table t2; drop database events_test;