path: root/mysql-test/t/events_bugs.test

#### 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 $
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  <empty>  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 = <who created the maybe current running events>
#                 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
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_NOT_SUPPORTED_YET
create event e_53 on schedule at (select s1 from ttx) do drop table t;
--error ER_NOT_SUPPORTED_YET
create event e_53 on schedule every (select s1 from ttx) second do drop table t;
--error ER_NOT_SUPPORTED_YET
create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t;
--error ER_NOT_SUPPORTED_YET
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_NOT_SUPPORTED_YET
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;

--echo connection e1;
--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;
#
--echo connection default;
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

--echo #
--echo # Connection: u1_con (mysqltest_u1@localhost/events_test).
--echo #

--connect(u1_con,localhost,mysqltest_u1,,events_test)

--echo

--error ER_OPTION_PREVENTS_STATEMENT
CREATE EVENT e1 ON SCHEDULE AT '2020-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.

--echo #
--echo # Connection: root_con (root@localhost/events_test).
--echo #

--connect(root_con,localhost,root,,events_test)

--echo

CREATE EVENT e1 ON SCHEDULE AT '2020-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

--echo #
--echo # Connection: u1_con (mysqltest_u1@localhost/test).
--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

--echo #
--echo # Connection: root_con (root@localhost/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

--echo #
--echo # Connection: default
--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.