#### t/events_bugs.test # # Last change: # 2009-01-08 mleich # - Fix for # Bug#41111 events_bugs fails sporadically on pushbuild # - Avoid effects of # Bug#41925 Warning 1366 Incorrect string value: ... for column processlist.info # # Please set $ SET SQL_MODE=""; let $fixed_bug41925= 0; # # Dear maintainer of this test. Please do NOT remove the next big comment. # The tests for events were quite unstable over a significant time because the # effects of events in general and their representation within the processlist # were partially not known. Therefore I had to make excessive experiments. # The comment with the outcome of these experiments will be moved into a README # file as soon as the tests for events get their own testsuite. # # Outcome of some experiments (mleich, mysql-5.1 2008-12): # -------------------------------------------------------- # 0. Most time used setup: # High parallel I/O load # set global event_scheduler= off; # sleep 3; # use events_test; # create event e_16407 on schedule every 2 second do # begin # select 'FIRST COMMAND', sleep(0.5); # select 'SECOND COMMAND'; # end| # set global event_scheduler= on; # Start observation of the processlist # # # 1. SET GLOBAL event_scheduler = 'ON' and immediate observation of the processlist. # 1.1 Effects around event scheduler: # 1.1.1 First phase (very short) # No user 'event_scheduler' within information_schema.processlist. # 1.1.2 Second phase observed (independend of probably existing events and very short) was # USER HOST DB COMMAND TIME STATE INFO # event_scheduler localhost NULL Daemon 0 Initialized NULL # 1.1.3 Third phase observed: # 1.1.3.1 Case we do not have existing events (rather long) # USER HOST DB COMMAND TIME STATE INFO # event_scheduler localhost NULL Daemon 0 Waiting on empty queue NULL # 1.1.3.2 Case there exists already an event # 1.1.3.2.1 Event executor is not visible in processlist but comes up soon # USER HOST DB COMMAND TIME STATE INFO # event_scheduler localhost NULL Daemon 0 NULL # or # event_scheduler localhost NULL Daemon 0 NULL NULL # 1.1.3.2.2 A bit later, at least one event executor is or was visible in processlist # The states mentioned in 3.2.1 or a bit later # USER HOST DB COMMAND TIME STATE INFO # event_scheduler localhost NULL Daemon 0 Waiting for next activation NULL # 1.2 Effects around event executor: # Typical processlist content: # USER evtest1 -- Definer of event # DB events_test -- DB during time of event creation (use DB is not allowed in events) # COMMAND Connect # STATE NULL # INFO SET @evname = 'ev_sched_1823' -- Part of the event code # # State before "User sleep select 'FIRST COMMAND', sleep(0.5);" # ID USER HOST DB COMMAND TIME STATE INFO # 7 event_scheduler localhost NULL Connect 0 NULL NULL # !! The user is not the event creator and the DB is different. !! # !! This means that we must get later a change of the identity. !! # or # USER HOST DB COMMAND TIME STATE INFO # root localhost events_test Connect 0 checking permissions # or # USER HOST DB COMMAND TIME STATE INFO # root localhost events_test Connect 0 checking permissions CREATE PROCEDURE .... # or # USER HOST DB COMMAND TIME STATE INFO # root localhost events_test Connect 0 NULL select 'FIRST COMMAND', sleep(0.5) # or # USER HOST DB COMMAND TIME STATE INFO # root localhost events_test Connect 0 checking permissions select 'FIRST COMMAND'... # or # USER HOST DB COMMAND TIME STATE INFO # root localhost events_test Connect 0 Opening table select 'FIRST COMMAND', sleep(0.5) # or # USER HOST DB COMMAND TIME STATE INFO # root localhost events_test Connect 0 Locked select 'FIRST COMMAND', sleep(0.5) # or # USER HOST DB COMMAND TIME STATE INFO # root localhost events_test Connect 0 executing select 'FIRST COMMAND', sleep(0.5) # # State "User sleep select 'FIRST COMMAND', sleep(0.5);" reached # USER HOST DB COMMAND TIME STATE INFO # root localhost events_test Connect 0 User sleep select 'FIRST COMMAND', sleep(0.5) # # State at end (! It looks like a slow CREATE PROC !) of event code execution was sometimes # USER HOST DB COMMAND TIME STATE INFO # root localhost events_test Connect 0 logging slow query CREATE PROCEDURE `e_16407`... # # State after running some event code was sometimes # USER HOST DB COMMAND TIME STATE INFO # root localhost events_test Connect 0 logging slow query select 'SECOND COMMAND' # # State somewhere (I guess just before the event executor disappears) # USER HOST DB COMMAND TIME STATE INFO # Event thread fin events_test Connect 0 Clearing NULL # # # 2. SET GLOBAL event_scheduler = 'OFF'; # Immediate observation of the processlist. # Effects: # 1. I never found the user 'event_scheduler' within the processlist. # 2. Events just during execution could be found within the processlist # = It does not look like "SET GLOBAL event_scheduler = 'OFF'" stops them. # ==> Everything mentioned in 1.2 above could be observed. # # Several subtests were weak because they showed random result set differences after issuing # "SET GLOBAL EVENT_SCHEDULER= off;". # 1. Reason one: There were already event executors # Fix: Wait till there is no event executor active ==> no session WHERE # - command IN ('Connect') # There must be no parallel session being just in "Connect" phase! # or # - user = # There must be no parallel session of this person. # or user = 'event_scheduler' with command = 'Connect' # The session which will soon change its identity to event creator. # # 2. Reason two: If an event modifies a MyISAM table than a delayed visibilty of changes # might occur (concurrent_inserts=on or delayed inserts ...). # # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc --disable_warnings drop database if exists events_test; drop database if exists mysqltest_db1; drop database if exists mysqltest_db2; --enable_warnings set collation_server=latin1_swedish_ci; create database events_test; use events_test; # We use MyISAM tables and must avoid effects (visibility of changes might be # delayed) caused by concurrent_insert on (default). set @concurrent_insert= @@global.concurrent_insert; set @@global.concurrent_insert = 0; # # START: Bug #31332 --event-scheduler option misbehaving # # NOTE!! this test must come first! It's testing that the --event-scheduler # option with no argument in events_bugs-master.opt turns the scheduler on. select * from information_schema.global_variables where variable_name like 'event_scheduler'; # Check that the event_scheduler is really running --source include/running_event_scheduler.inc SET GLOBAL event_scheduler = 'OFF'; --source include/check_events_off.inc # # END: Bug #31332 # # # START - 16415: Events: event names are case sensitive # CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1; --error ER_EVENT_ALREADY_EXISTS CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2; DROP EVENT Lower_case; SET NAMES cp1251; CREATE EVENT __1251 ON SCHEDULE EVERY 1 YEAR DO SELECT 100; --error ER_EVENT_ALREADY_EXISTS CREATE EVENT __1251 ON SCHEDULE EVERY 2 YEAR DO SELECT 200; DROP EVENT __1251; SET NAMES utf8; CREATE EVENT долен_регистър_утф8 ON SCHEDULE EVERY 3 YEAR DO SELECT 300; --error ER_EVENT_ALREADY_EXISTS CREATE EVENT ДОЛЕН_регистър_утф8 ON SCHEDULE EVERY 4 YEAR DO SELECT 400; DROP EVENT ДОЛЕН_регистър_утф8; SET NAMES latin1; # # END - 16415: Events: event names are case sensitive # # # START - BUG#16408: Events: crash for an event in a procedure # set @a=3; --error ER_EVENT_RECURSION_FORBIDDEN CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5; # # END - BUG#16408: Events: crash for an event in a procedure # # # Start - 16396: Events: Distant-future dates become past dates # --error ER_WRONG_VALUE create event e_55 on schedule at 99990101000000 do drop table t; --error ER_WRONG_VALUE create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t; --error ER_EVENT_ENDS_BEFORE_STARTS create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t; --error ER_WRONG_VALUE create event e_55 on schedule at 10000101000000 do drop table t; # For the purpose of backup we allow times in the past. Here, no # error will be given, but the event won't be created. One may think # of that as if the event was created, then it turned out it's in the # past, so it was dropped because of implicit ON COMPLETION NOT # PRESERVE. create event e_55 on schedule at 20000101000000 do drop table t; show events; --error ER_PARSE_ERROR create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; --error ER_PARSE_ERROR create event e_55 on schedule at 20200101000000 ends 10000101000000 do drop table t; --error ER_PARSE_ERROR create event e_55 on schedule at 20200101000000 starts 10000101000000 ends 10000101000000 do drop table t; --error ER_WRONG_VALUE create event e_55 on schedule every 10 hour starts 10000101000000 do drop table t; # # End - 16396: Events: Distant-future dates become past dates # # # Start - 16407: Events: Changes in sql_mode won't be taken into account # set global event_scheduler=off; --source include/check_events_off.inc delete from mysql.event; set global event_scheduler= on; --source include/running_event_scheduler.inc set @old_sql_mode:=@@sql_mode; set sql_mode=ansi; select get_lock('test_bug16407', 60); delimiter |; create event e_16407 on schedule every 60 second do begin select get_lock('test_bug16407', 60); end| delimiter ;| # The default session has the user lock. # We wait till one event runs and hangs when trying to get the user lock. let $wait_condition= select count(*) > 0 from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'test_bug16407\', 60)'; --source include/wait_condition.inc --echo "Now if everything is fine the event has compiled and is locked" # Expect to see one session in events_test waiting for the user lock. select /*1*/ user, host, db, info from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'test_bug16407\', 60)'; select release_lock('test_bug16407'); # Try to avoid # Bug#39863 events_bugs fails sporadically on pushbuild (extra processes in I_S.PROCESSLIST) # which is most probably caused by # Bug#32782 User lock hash fails to find lock # "various issues related to missing or incorrect return results # from release_lock()." # Therefore we check here if the event executor is no more locked or # we waited >= 5 seconds for this to happen. let $wait_timeout= 5; let $wait_condition= select count(*) = 0 from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'test_bug16407\', 60)'; --source include/wait_condition.inc if (!`$wait_condition`) { --echo ERROR: There must be no session with --echo state = 'User lock' and info = 'select get_lock('test_bug16407', 60) --echo within the processlist. --echo We probably hit Bug#32782 User lock hash fails to find lock SELECT * FROM information_schema.processlist; --echo Abort exit; } set global event_scheduler= off; # Wait till the event scheduler and all event executors have finished their work. --source include/check_events_off.inc select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; --echo "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; drop event e_16407; set sql_mode="ansi"; select get_lock('ee_16407_2', 60); set global event_scheduler= 1; --source include/running_event_scheduler.inc --echo "Another sql_mode test" set sql_mode="traditional"; create table events_smode_test(ev_name char(10), a date); --echo "This should never insert something" delimiter |; 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| --error ER_TRUNCATED_WRONG_VALUE insert into events_test.events_smode_test values ('test','1980-19-02')| --echo "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=""| --echo "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| delimiter ;| select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; # We wait till we have three event executors waiting for the removal of the lock. let $wait_condition= select count(*) = 3 from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; --source include/wait_condition.inc # There is an extreme low risk that an additional event execution is just coming # up because # - the events have to be started every 60 seconds. # - we are just after event creation + waiting for seeing 3 locked # We expect to see three event executors in state 'User lock'. select /*2*/ user, host, db, info from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; select release_lock('ee_16407_2'); # Try to avoid # Bug#39863 events_bugs fails sporadically on pushbuild (extra processes in I_S.PROCESSLIST) # which is most probably caused by # Bug#32782 User lock hash fails to find lock # "various issues related to missing or incorrect return results # from release_lock()." # Therefore we check here if the event executing sessions disappeared or # we waited >= 5 seconds for this to happen. let $wait_timeout= 5; let $wait_condition= select count(*) = 0 from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; --source include/wait_condition.inc if (!`$wait_condition`) { --echo ERROR: There must be no session with --echo state = 'User lock' and info = 'select get_lock('test_bug16407_2', 60) --echo within the processlist. --echo We probably hit Bug#32782 User lock hash fails to find lock SELECT * FROM information_schema.processlist; --echo Abort exit; } # We expect to see no event executors in state 'User lock'. if(!$fixed_bug41925) { --disable_warnings } select /*3*/ user, host, db, info from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; if(!$fixed_bug41925) { --enable_warnings } set global event_scheduler= off; # Wait till the event scheduler and all event executors have finished their work. # Without this the next queries will give unstable results. --source include/check_events_off.inc select * from events_test.events_smode_test order by ev_name, a; --echo "OK, last check before we drop them" select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; drop event ee_16407_2; drop event ee_16407_3; drop event ee_16407_4; --echo "And now one last test regarding sql_mode and call of SP from an event" delete from events_test.events_smode_test; set sql_mode='ansi'; select get_lock('ee_16407_5', 60); set global event_scheduler= on; --source include/running_event_scheduler.inc set sql_mode='traditional'; delimiter |; # ee_16407_5_pendant() should not insert anything because of invalid date. 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| delimiter ;| let $wait_condition= select count(*) = 2 from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; --source include/wait_condition.inc --echo "Should have 2 locked processes" select /*4*/ user, host, db, info from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; select release_lock('ee_16407_5'); let $wait_condition= select count(*) = 0 from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; --source include/wait_condition.inc --echo "Should have 0 processes locked" if(!$fixed_bug41925) { --disable_warnings } select /*5*/ user, host, db, info from information_schema.processlist where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; if(!$fixed_bug41925) { --enable_warnings } # Wait till all event executors have finished their work, so that we can be sure # that their changes to events_smode_test are done. --source include/no_running_events.inc select * from events_test.events_smode_test order by ev_name, a; --echo "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; 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; --source include/check_events_off.inc drop table events_smode_test; set sql_mode=@old_sql_mode; # # End - 16407: Events: Changes in sql_mode won't be taken into account # # # START - 18897: Events: unauthorized action possible with alter event rename # set global event_scheduler=off; --source include/check_events_off.inc --disable_warnings delete from mysql.user where User like 'mysqltest_%'; delete from mysql.db where User like 'mysqltest_%'; flush privileges; drop database if exists mysqltest_db1; --enable_warnings create user mysqltest_user1@localhost; create database mysqltest_db1; grant event on events_test.* to mysqltest_user1@localhost; connect (conn2,localhost,mysqltest_user1,,events_test); create event mysqltest_user1 on schedule every 10 second do select 42; --error ER_DBACCESS_DENIED_ERROR alter event mysqltest_user1 rename to mysqltest_db1.mysqltest_user1; --echo "Let's test now rename when there is no select DB" disconnect conn2; connect (conn2,localhost,mysqltest_user1,,*NO-ONE*); select database(); --error ER_NO_DB_ERROR alter event events_test.mysqltest_user1 rename to mysqltest_user1; select event_schema, event_name, definer, event_type, status from information_schema.events; drop event events_test.mysqltest_user1; disconnect conn2; connection default; drop user mysqltest_user1@localhost; drop database mysqltest_db1; # # END - 18897: Events: unauthorized action possible with alter event rename # # # START - BUG#16394: Events: Crash if schedule contains SELECT # --error ER_SUBQUERIES_NOT_SUPPORTED create event e_53 on schedule at (select s1 from ttx) do drop table t; --error ER_SUBQUERIES_NOT_SUPPORTED create event e_53 on schedule every (select s1 from ttx) second do drop table t; --error ER_SUBQUERIES_NOT_SUPPORTED create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t; --error ER_SUBQUERIES_NOT_SUPPORTED create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t; # # END - BUG#16394: Events: Crash if schedule contains SELECT # # # START - BUG#22397: Events: crash with procedure which alters events # --disable_warnings drop event if exists e_16; drop procedure if exists p_16; --enable_warnings 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; --error ER_WRONG_VALUE call p_16(); --error ER_WRONG_VALUE call p_16(); set @a= 6; call p_16(); drop procedure p_16; drop event e_16; # # START - BUG#22830 Events: crash with procedure which alters events with function # --disable_warnings 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; --enable_warnings 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; select get_lock('ee_22830', 60); set global event_scheduler=on; --source include/running_event_scheduler.inc delimiter |; create procedure p22830_wait() begin select get_lock('ee_22830', 60); select release_lock('ee_22830'); end| --error ER_SUBQUERIES_NOT_SUPPORTED create event e22830 on schedule every f22830() second do begin call p22830_wait(); select 123; end| create event e22830_1 on schedule every 1 hour do begin call p22830_wait(); alter event e22830_1 on schedule every (select 8 from dual) hour; end| create event e22830_2 on schedule every 1 hour do begin call p22830_wait(); alter event e22830_2 on schedule every (select 8 from t1) hour; end| create event e22830_3 on schedule every 1 hour do begin call p22830_wait(); alter event e22830_3 on schedule every f22830() hour; end| create event e22830_4 on schedule every 1 hour do begin call p22830_wait(); alter event e22830_4 on schedule every (select f22830() from dual) hour; end| delimiter ;| --echo "All events should be blocked in get_lock()" select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; select release_lock('ee_22830'); let $wait_condition= select group_concat(interval_value order by interval_value) = '1,1,1,8' from information_schema.events; --source include/wait_condition.inc set global event_scheduler=off; --source include/check_events_off.inc select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; drop procedure p22830_wait; drop function f22830; --error ER_PARSE_ERROR drop event (select a from t2); drop event e22830_1; drop event e22830_2; drop event e22830_3; drop event e22830_4; drop table t1; drop table t2; # # BUG#16425: Events: no DEFINER clause # --error 0,ER_CANNOT_USER DROP USER mysqltest_u1@localhost; CREATE USER mysqltest_u1@localhost; GRANT EVENT ON events_test.* TO mysqltest_u1@localhost; CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; DROP EVENT e1; CREATE DEFINER=CURRENT_USER EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; ALTER DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR; SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; DROP EVENT e1; CREATE DEFINER=CURRENT_USER() EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; DROP EVENT e1; CREATE DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; DROP EVENT e1; connect (conn1, localhost, mysqltest_u1, , events_test); CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; DROP EVENT e1; CREATE DEFINER=CURRENT_USER EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; --error ER_SPECIFIC_ACCESS_DENIED_ERROR ALTER DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR; SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; DROP EVENT e1; CREATE DEFINER=CURRENT_USER() EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; DROP EVENT e1; --error ER_SPECIFIC_ACCESS_DENIED_ERROR CREATE DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; --error ER_EVENT_DOES_NOT_EXIST DROP EVENT e1; disconnect conn1; connection default; DROP USER mysqltest_u1@localhost; # # BUG#16420: Events: timestamps become UTC # BUG#26429: SHOW CREATE EVENT is incorrect for an event that # STARTS NOW() # BUG#26431: Impossible to re-create an event from backup if its # STARTS clause is in the past # WL#3698: Events: execution in local time zone # # Here we only check non-concurrent aspects of the patch. # For the actual tests of time zones please see events_time_zone.test # SET GLOBAL EVENT_SCHEDULER= OFF; --source include/check_events_off.inc SET @save_time_zone= @@TIME_ZONE; #---------------------------------------------------------------------- # We will use a separate connection because SET TIMESTAMP will stop # the clock in that connection. SET TIME_ZONE= '+00:00'; SET TIMESTAMP= UNIX_TIMESTAMP('2005-12-31 23:58:59'); # Test when event time zone is updated on ALTER EVENT. # CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; SHOW EVENTS; # Test storing and updating of the event time zone. # SET TIME_ZONE= '-01:00'; ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 00:00:00'; SHOW EVENTS; # This will update event time zone. SET TIME_ZONE= '+02:00'; ALTER EVENT e1 ON SCHEDULE AT '2000-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE; SHOW EVENTS; # This will update event time zone. SET TIME_ZONE= '-03:00'; ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY ENDS '2030-01-03 00:00:00' ON COMPLETION PRESERVE DISABLE; SHOW EVENTS; # This will not update event time zone, as no time is being adjusted. SET TIME_ZONE= '+04:00'; ALTER EVENT e1 DO SELECT 2; SHOW EVENTS; DROP EVENT e1; #---------------------------------------------------------------------- # Create some events. SET TIME_ZONE='+05:00'; CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO SELECT 1; SET TIMESTAMP= @@TIMESTAMP + 1; SET TIME_ZONE='-05:00'; CREATE EVENT e2 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO SELECT 1; SET TIMESTAMP= @@TIMESTAMP + 1; SET TIME_ZONE='+00:00'; CREATE EVENT e3 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO SELECT 1; # Test INFORMATION_SCHEMA.EVENTS. # SELECT * FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_name; # Test SHOW EVENTS. # SHOW EVENTS; # Test SHOW CREATE EVENT. # SHOW CREATE EVENT e1; SHOW CREATE EVENT e2; SHOW CREATE EVENT e3; #---------------------------------------------------------------------- # Test times in the past. # --echo The following should fail, and nothing should be altered. --error ER_EVENT_CANNOT_ALTER_IN_THE_PAST ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00'; --error ER_EVENT_CANNOT_ALTER_IN_THE_PAST ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DISABLE; --echo The following should give warnings, and nothing should be created. CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DO SELECT 1; CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DISABLE DO SELECT 1; CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO SELECT 1; CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE DO SELECT 1; SHOW EVENTS; --echo The following should succeed giving a warning. ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE; CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DO SELECT 1; CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00' ON COMPLETION PRESERVE DO SELECT 1; --echo The following should succeed without warnings. ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'; ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE; CREATE EVENT e6 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO SELECT 1; CREATE EVENT e7 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE DO SELECT 1; CREATE EVENT e8 ON SCHEDULE AT '1999-01-01 00:00:00' ON COMPLETION PRESERVE DISABLE DO SELECT 1; SHOW EVENTS; DROP EVENT e8; DROP EVENT e7; DROP EVENT e6; DROP EVENT e5; DROP EVENT e4; DROP EVENT e3; DROP EVENT e2; DROP EVENT e1; SET TIME_ZONE=@save_time_zone; SET TIMESTAMP=DEFAULT; # # START - BUG#28666 CREATE EVENT ... EVERY 0 SECOND let server crash # --disable_warnings drop event if exists new_event; --enable_warnings --error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG CREATE EVENT new_event ON SCHEDULE EVERY 0 SECOND DO SELECT 1; --error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG CREATE EVENT new_event ON SCHEDULE EVERY (SELECT 0) SECOND DO SELECT 1; --error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG CREATE EVENT new_event ON SCHEDULE EVERY "abcdef" SECOND DO SELECT 1; --error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG CREATE EVENT new_event ON SCHEDULE EVERY "0abcdef" SECOND DO SELECT 1; --error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG CREATE EVENT new_event ON SCHEDULE EVERY "a1bcdef" SECOND DO SELECT 1; --error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "abcdef" UNION SELECT "abcdef") SECOND DO SELECT 1; --error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "0abcdef") SECOND DO SELECT 1; --error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "a1bcdef") SECOND DO SELECT 1; --error ER_WRONG_VALUE CREATE EVENT new_event ON SCHEDULE AT "every day" DO SELECT 1; --error ER_WRONG_VALUE CREATE EVENT new_event ON SCHEDULE AT "0every day" DO SELECT 1; --error ER_WRONG_VALUE CREATE EVENT new_event ON SCHEDULE AT (SELECT "every day") DO SELECT 1; --error ER_PARSE_ERROR CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() DO SELECT 1; --error ER_PARSE_ERROR CREATE EVENT new_event ON SCHEDULE AT NOW() ENDS NOW() DO SELECT 1; --error ER_PARSE_ERROR CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() ENDS NOW() DO SELECT 1; # # START - BUG#28924 If I drop the user who is the definer of an active event # then server cores # let $engine=MyISAM; USE test; SHOW GRANTS FOR CURRENT_USER; SET GLOBAL event_scheduler = ON; --source include/running_event_scheduler.inc eval CREATE TABLE events_test.event_log (id int KEY AUTO_INCREMENT, ev_nm char(40), ev_cnt int, ev_tm timestamp) ENGINE=$engine; SET autocommit=0; CREATE USER evtest1@localhost; SET PASSWORD FOR evtest1@localhost = password('ev1'); REVOKE ALL PRIVILEGES, GRANT OPTION FROM evtest1@localhost; GRANT create, insert, select, event ON events_test.* TO evtest1@localhost; GRANT select,insert ON test.* TO evtest1@localhost; SHOW GRANTS FOR evtest1@localhost; --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK connect (e1,localhost,evtest1,ev1,events_test,$MASTER_MYPORT,$MASTER_MYSOCK); DELIMITER |; CREATE EVENT ev_sched_1823 ON SCHEDULE EVERY 2 SECOND DO BEGIN SET AUTOCOMMIT = 0; SET @evname = 'ev_sched_1823'; SET @cnt = 0; SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname; IF @cnt < 6 THEN INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); COMMIT; END IF; SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname; IF @cnt < 6 THEN INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); ROLLBACK; END IF; END;| DELIMITER ;| # 1. Show that the new created event is runnable and gets executed within a # reasonable time like 4 seconds. Till ~ 2 seconds could pass on a heavy # loaded testing box before something gets executed). # Detection of execution is via the records inserted by the event. --echo Sleep till the first INSERT into events_test.event_log occurred let $wait_timeout= 4; let $wait_condition= SELECT COUNT(*) > 0 FROM events_test.event_log; --source include/wait_condition.inc SELECT COUNT(*) > 0 AS "Expect 1" FROM events_test.event_log; # connection default; DROP USER evtest1@localhost; # 2. If we meet BUG#28924 again than a server crash happens within the next # few seconds # 3. Check that the event is never executed again # It could be that an event execution was running before the DROP USER # and all implicite actions belonging to this are completed. # Lets assume that ~ 4 seconds waiting are enough for the event # scheduler to detect that --echo Sleep 4 seconds sleep 4; SELECT COUNT(*) INTO @row_cnt FROM events_test.event_log; # Give the event mechanism ~ 4 seconds to do something wrong # (execute the event of the dropped user -> inser rows). --echo Sleep 4 seconds sleep 4; SELECT COUNT(*) > @row_cnt AS "Expect 0" FROM events_test.event_log; # # Cleanup disconnect e1; DROP EVENT events_test.ev_sched_1823; DROP TABLE events_test.event_log; SET GLOBAL event_scheduler = OFF; --source include/check_events_off.inc # # Bug#28641 CREATE EVENT with '2038.01.18 03:00:00' let server crash. # SET GLOBAL event_scheduler= ON; --source include/running_event_scheduler.inc DELIMITER |; CREATE EVENT bug28641 ON SCHEDULE AT '2038.01.18 03:00:00' DO BEGIN SELECT 1; END;| DELIMITER ;| SET GLOBAL event_scheduler= OFF; --source include/check_events_off.inc DROP EVENT bug28641; ########################################################################### --echo --echo ##################################################################### --echo # --echo # BUG#31111: --read-only crashes MySQL (events fail to load). --echo # --echo ##################################################################### --echo --error 0,ER_CANNOT_USER DROP USER mysqltest_u1@localhost; --disable_warnings DROP EVENT IF EXISTS e1; DROP EVENT IF EXISTS e2; --enable_warnings --echo # Check that an ordinary user can not create/update/drop events in the # read-only mode. GRANT EVENT ON *.* TO mysqltest_u1@localhost; --echo SET GLOBAL READ_ONLY = 1; --echo --connect(u1_con,localhost,mysqltest_u1,,events_test) --echo --error ER_OPTION_PREVENTS_STATEMENT CREATE EVENT e1 ON SCHEDULE AT '2038-01-01 00:00:00' DO SET @a = 1; --echo --error ER_OPTION_PREVENTS_STATEMENT ALTER EVENT e1 COMMENT 'comment'; --echo --error ER_OPTION_PREVENTS_STATEMENT DROP EVENT e1; --echo # Check that the super user still can create/update/drop events. --connect(root_con,localhost,root,,events_test) --echo CREATE EVENT e1 ON SCHEDULE AT '2038-01-01 00:00:00' DO SET @a = 1; --echo ALTER EVENT e1 COMMENT 'comment'; --echo DROP EVENT e1; --echo # # Switch to read-write mode; create test events under the user mysqltest_u1; # switch back to read-only mode. # SET GLOBAL READ_ONLY = 0; --echo --connection u1_con --echo CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND DO SET @a = 1; CREATE EVENT e2 ON SCHEDULE EVERY 1 SECOND DO SET @a = 1; --echo SELECT event_name, last_executed IS NULL, definer FROM INFORMATION_SCHEMA.EVENTS WHERE event_schema = 'events_test'; --echo --connection root_con --echo SET GLOBAL READ_ONLY = 1; # Check that the event scheduler is able to update event. --echo SET GLOBAL EVENT_SCHEDULER = ON; --source include/running_event_scheduler.inc --echo --echo # Waiting for the event scheduler to execute and drop event e1... let $wait_timeout = 4; let $wait_condition = SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.EVENTS WHERE event_schema = 'events_test' AND event_name = 'e1'; --source include/wait_condition.inc --echo --echo # Waiting for the event scheduler to execute and update event e2... let $wait_condition = SELECT last_executed IS NOT NULL FROM INFORMATION_SCHEMA.EVENTS WHERE event_schema = 'events_test' AND event_name = 'e2'; --source include/wait_condition.inc --echo SET GLOBAL EVENT_SCHEDULER = OFF; --source include/check_events_off.inc --echo SELECT event_name, last_executed IS NULL, definer FROM INFORMATION_SCHEMA.EVENTS WHERE event_schema = 'events_test'; --echo --error ER_EVENT_DOES_NOT_EXIST DROP EVENT e1; --echo --echo # Cleanup. --echo DROP EVENT e2; --echo SET GLOBAL READ_ONLY = 0; --echo --disconnect u1_con --disconnect root_con --connection default --echo DROP USER mysqltest_u1@localhost; --echo --echo ##################################################################### --echo # --echo # End of BUG#31111. --echo # --echo ##################################################################### --echo # # Bug#32633 Can not create any routine if SQL_MODE=no_engine_substitution # # Ensure that when new SQL modes are introduced, they are also added to # the mysql.event table. # --disable_warnings drop procedure if exists p; --enable_warnings set @old_mode= @@sql_mode; set @@sql_mode= cast(pow(2,32)-1 as unsigned integer); create event e1 on schedule every 1 day do select 1; select @@sql_mode into @full_mode; set @@sql_mode= @old_mode; select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode; select name from mysql.event where name = 'e1' and sql_mode = @full_mode; drop event e1; # # Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id # SET @old_server_id = @@GLOBAL.server_id; SET GLOBAL server_id = (1 << 32) - 1; SELECT @@GLOBAL.server_id; CREATE EVENT ev1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; SELECT event_name, originator FROM INFORMATION_SCHEMA.EVENTS; DROP EVENT ev1; SET GLOBAL server_id = @old_server_id; # # Bug#11751148: show events shows events in other schema # CREATE DATABASE event_test12; USE event_test12; CREATE EVENT ev1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; CREATE DATABASE event_test1; USE event_test1; # Following show events should not show ev1 SHOW EVENTS; DROP DATABASE event_test1; DROP DATABASE event_test12; --echo # --echo # Bug#12546938 (formerly known as bug#61005): --echo # CREATE IF NOT EXIST EVENT WILL CREATE MULTIPLE RUNNING EVENTS --echo # USE events_test; SET GLOBAL event_scheduler = ON; --disable_warnings DROP TABLE IF EXISTS table_bug12546938; DROP EVENT IF EXISTS event_Bug12546938; --enable_warnings CREATE TABLE table_bug12546938 (i INT); delimiter |; --echo # Create an event which will be executed with a small delay --echo # and won't be automatically dropped after that. CREATE EVENT event_Bug12546938 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE ENABLE DO BEGIN INSERT INTO table_bug12546938 VALUES(1); END | --echo # Now try to create the same event using CREATE EVENT IF NOT EXISTS. --echo # A warning should be emitted. A new event should not be created nor --echo # the old event should be re-executed. CREATE EVENT IF NOT EXISTS event_bug12546938 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE ENABLE DO BEGIN INSERT INTO table_bug12546938 VALUES (1); END | delimiter ;| --echo # Wait until at least one instance of event is executed. let $wait_condition= SELECT COUNT(*) FROM table_bug12546938; --source include/wait_condition.inc --echo # Check that only one instance of our event was executed. SELECT COUNT(*) FROM table_bug12546938; --echo # Clean-up. DROP EVENT IF EXISTS event_Bug12546938; DROP TABLE table_bug12546938; SET GLOBAL EVENT_SCHEDULER = OFF; # # Bug#11764334 - 57156: ALTER EVENT CHANGES THE EVENT STATUS # --disable_warnings DROP DATABASE IF EXISTS event_test11764334; --enable_warnings CREATE DATABASE event_test11764334; USE event_test11764334; CREATE EVENT ev1 ON SCHEDULE EVERY 3 SECOND DISABLE DO SELECT 1; --replace_column 9 # 10 # SHOW EVENTS IN event_test11764334 WHERE NAME='ev1'; ALTER EVENT ev1 ON SCHEDULE EVERY 4 SECOND; --replace_column 9 # 10 # SHOW EVENTS IN event_test11764334 WHERE NAME='ev1'; DROP EVENT ev1; DROP DATABASE event_test11764334; USE test; ########################################################################### # # End of tests # # !!! KEEP this section AT THE END of this file !!! # ########################################################################### # Ensure that all event executors have finished their work and cannot harm # the next test. --source include/no_running_events.inc DROP DATABASE events_test; # Restore the original state i.e to how it was when test started # (option with no argument in events_bugs-master.opt turns the scheduler on). SET GLOBAL event_scheduler= 'ON'; --source include/running_event_scheduler.inc SET @@global.concurrent_insert= @concurrent_insert; # THIS MUST BE THE LAST LINE in this file.