events_bugs.test 11.1 KB
Newer Older
1 2 3
# Can't test with embedded server that doesn't support grants
-- source include/not_embedded.inc

4 5
create database if not exists events_test;
use events_test;
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

#
# 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
#

29 30 31 32
#
# START - BUG#16408: Events: crash for an event in a procedure
#
set @a=3;
33
--error ER_EVENT_RECURSIVITY_FORBIDDEN
34 35 36 37 38
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
#

39 40 41
#
# Start - 16396: Events: Distant-future dates become past dates
#
42
--error ER_WRONG_VALUE
43
create event e_55 on schedule at 99990101000000 do drop table t;
44
--error ER_WRONG_VALUE
45
create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t;
46
--error ER_EVENT_ENDS_BEFORE_STARTS
47
create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t;
48 49 50 51 52 53 54 55 56 57 58 59 60
--error ER_EVENT_EXEC_TIME_IN_THE_PAST
create event e_55 on schedule at 10000101000000 do drop table t;
--error ER_EVENT_EXEC_TIME_IN_THE_PAST
create event e_55 on schedule at 20000101000000 do drop table t;
--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;

61 62 63 64
#
# End  -  16396: Events: Distant-future dates become past dates
#

65 66 67
#
# Start - 16407: Events: Changes in sql_mode won't be taken into account
#
68
set global event_scheduler=off;
69
delete from mysql.event;
70
set global event_scheduler= on;
71 72 73 74 75 76 77 78 79 80
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);
  drop table "hashed_num";
end|
delimiter ;|
81
--sleep 0.8
82
--echo "Now if everything is fine the event has compiled and is locked
andrey@example.com's avatar
andrey@example.com committed
83
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;
84
select release_lock('test_bug16407');
85

86
set global event_scheduler= off;
87

88 89 90 91 92 93 94
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;

95 96 97 98
set sql_mode="ansi";
select get_lock('ee_16407_2', 60);

set global event_scheduler= 1;
99 100 101 102 103 104 105
--echo "Another sql_mode test"
set sql_mode="traditional";
create table events_smode_test(ev_name char(10), a date) engine=myisam;
--echo "This should never insert something"
delimiter |;
create event ee_16407_2 on schedule every 60 second do
begin
106
  select get_lock('ee_16407_2', 60) /*ee_16407_2*/;
107
  select release_lock('ee_16407_2');
108 109
  insert into events_test.events_smode_test values('ee_16407_2','1980-19-02');
end|
110
--error ER_TRUNCATED_WRONG_VALUE
111 112 113 114
insert into events_smode_test values ('test','1980-19-02')|
--echo "This is ok"
create event ee_16407_3 on schedule every 60 second do
begin
115
  select get_lock('ee_16407_2', 60) /*ee_16407_3*/;
116
  select release_lock('ee_16407_2');
117 118 119 120 121 122 123
  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
124
  select get_lock('ee_16407_2', 60) /*ee_16407_4*/;
125
  select release_lock('ee_16407_2');
126 127 128 129
  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;
130
--sleep 1
andrey@example.com's avatar
andrey@example.com committed
131
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;
132
select release_lock('ee_16407_2');
133
--sleep 1.2
andrey@example.com's avatar
andrey@example.com committed
134
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;
135
set global event_scheduler= off;
136 137 138 139 140 141 142 143 144 145
select * from 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_smode_test;
146 147 148
set sql_mode='ansi';
select get_lock('ee_16407_5', 60);

149
set global event_scheduler= on;
150

151 152 153 154 155 156
set sql_mode='traditional';
delimiter |;
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
157
  select get_lock('ee_16407_5', 60) /*ee_16407_5*/;
158
  select release_lock('ee_16407_5');
159 160 161 162
  call events_test.ee_16407_5_pendant();
end|
create event ee_16407_6 on schedule every 60 second do
begin
163
  select get_lock('ee_16407_5', 60) /*ee_16407_6*/;
164
  select release_lock('ee_16407_5');
165 166 167
  call events_test.ee_16407_6_pendant();
end|
delimiter ;|
168
--sleep 1
169
--echo "Should have 2 locked processes"
andrey@example.com's avatar
andrey@example.com committed
170
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;
171
select release_lock('ee_16407_5');
172
--sleep 1.3
173
--echo "Should have 0 processes locked"
andrey@example.com's avatar
andrey@example.com committed
174
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;
175 176 177 178 179 180 181
select * from 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;
182
set global event_scheduler= off;
183 184 185 186 187
drop table events_smode_test;
set sql_mode=@old_sql_mode;
#
# End  - 16407: Events: Changes in sql_mode won't be taken into account  
#
188 189 190 191

#
# START - 18897: Events: unauthorized action possible with alter event rename
#
192
set global event_scheduler=off;
193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221
--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
#

222 223 224
#
# START - BUG#16394: Events: Crash if schedule contains SELECT
#
225
--error ER_NOT_SUPPORTED_YET
226
create event e_53 on schedule at (select s1 from ttx) do drop table t;
227
--error ER_NOT_SUPPORTED_YET
228
create event e_53 on schedule every (select s1 from ttx) second do drop table t;
229
--error ER_NOT_SUPPORTED_YET
230
create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t;
231
--error ER_NOT_SUPPORTED_YET
232 233 234 235 236
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
#

237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255
#
# 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;
256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282

#
# 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;
--error ER_NOT_SUPPORTED_YET
create event e22830 on schedule every f22830() second do select 123;
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;
set global event_scheduler=on;
andrey@example.com's avatar
andrey@example.com committed
283
--sleep 2.4
284 285 286 287 288 289 290 291 292 293 294 295 296 297 298
set global event_scheduler=off;
select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name;
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;

#
# End of tests
#
299
drop database events_test;