events_bugs.result 30.6 KB
Newer Older
1 2 3 4
drop database if exists events_test;
drop database if exists mysqltest_db1;
drop database if exists mysqltest_db2;
create database events_test;
5
use events_test;
6 7 8 9 10
CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1;
CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2;
ERROR HY000: Event 'Lower_case' already exists
DROP EVENT Lower_case;
SET NAMES cp1251;
unknown's avatar
unknown committed
11 12
CREATE EVENT __1251 ON SCHEDULE EVERY 1 YEAR DO SELECT 100;
CREATE EVENT __1251 ON SCHEDULE EVERY 2 YEAR DO SELECT 200;
13
ERROR HY000: Event 'ДоЛеН_регистър_1251' already exists
unknown's avatar
unknown committed
14
DROP EVENT __1251;
15 16 17 18 19 20
SET NAMES utf8;
CREATE EVENT долен_регистър_утф8 ON SCHEDULE EVERY 3 YEAR DO SELECT 300;
CREATE EVENT ДОЛЕН_регистър_утф8 ON SCHEDULE EVERY 4 YEAR DO SELECT 400;
ERROR HY000: Event 'ДОЛЕН_регистър_утф8' already exists
DROP EVENT ДОЛЕН_регистър_утф8;
SET NAMES latin1;
21 22
set @a=3;
CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5;
23
ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present
24 25 26 27 28 29
create event e_55 on schedule at 99990101000000 do drop table t;
ERROR HY000: Incorrect AT value: '99990101000000'
create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t;
ERROR HY000: Incorrect STARTS value: '99990101000000'
create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t;
ERROR HY000: ENDS is either invalid or before STARTS
30
create event e_55 on schedule at 10000101000000 do drop table t;
31
ERROR HY000: Incorrect AT value: '10000101000000'
32
create event e_55 on schedule at 20000101000000 do drop table t;
33 34 35
Warnings:
Note	1584	Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created
show events;
unknown's avatar
unknown committed
36
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
37 38 39 40 41 42 43 44
create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'starts 10000101000000 do drop table t' at line 1
create event e_55 on schedule at 20200101000000 ends 10000101000000 do drop table t;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ends 10000101000000 do drop table t' at line 1
create event e_55 on schedule at 20200101000000 starts 10000101000000 ends 10000101000000 do drop table t;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'starts 10000101000000 ends 10000101000000 do drop table t' at line 1
create event e_55 on schedule every 10 hour starts 10000101000000 do drop table t;
ERROR HY000: Incorrect STARTS value: '10000101000000'
45
set global event_scheduler=off;
46
delete from mysql.event;
47
set global event_scheduler= on;
48 49 50 51 52 53 54 55 56
set @old_sql_mode:=@@sql_mode;
set sql_mode=ansi;
select get_lock('test_bug16407', 60);
get_lock('test_bug16407', 60)
1
create event e_16407 on schedule every 60 second do
begin
select get_lock('test_bug16407', 60);
end|
57
"Now if everything is fine the event has compiled and is locked"
unknown's avatar
unknown committed
58
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;
unknown's avatar
unknown committed
59 60
user	host	db	info
root	localhost	events_test	select get_lock('test_bug16407', 60)
61 62 63
select release_lock('test_bug16407');
release_lock('test_bug16407')
1
64
set global event_scheduler= off;
65 66 67 68 69 70 71 72 73 74
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
event_schema	event_name	sql_mode
events_test	e_16407	REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
"Let's check whether we change the sql_mode on ALTER EVENT"
set sql_mode='traditional';
alter event e_16407 do select 1;
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
event_schema	event_name	sql_mode
events_test	e_16407	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
drop event e_16407;
75 76 77 78 79
set sql_mode="ansi";
select get_lock('ee_16407_2', 60);
get_lock('ee_16407_2', 60)
1
set global event_scheduler= 1;
80 81 82 83 84 85
"Another sql_mode test"
set sql_mode="traditional";
create table events_smode_test(ev_name char(10), a date) engine=myisam;
"This should never insert something"
create event ee_16407_2 on schedule every 60 second do
begin
86
select get_lock('ee_16407_2', 60) /*ee_16407_2*/;
87
select release_lock('ee_16407_2');
88 89 90 91 92 93 94
insert into events_test.events_smode_test values('ee_16407_2','1980-19-02');
end|
insert into events_smode_test values ('test','1980-19-02')|
ERROR 22007: Incorrect date value: '1980-19-02' for column 'a' at row 1
"This is ok"
create event ee_16407_3 on schedule every 60 second do
begin
95
select get_lock('ee_16407_2', 60) /*ee_16407_3*/;
96
select release_lock('ee_16407_2');
97 98 99 100 101 102 103
insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19');
insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29');
end|
set sql_mode=""|
"This will insert rows but they will be truncated"
create event ee_16407_4 on schedule every 60 second do
begin
104
select get_lock('ee_16407_2', 60) /*ee_16407_4*/;
105
select release_lock('ee_16407_2');
106 107 108 109 110 111 112
insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956');
end|
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
event_schema	event_name	sql_mode
events_test	ee_16407_2	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
events_test	ee_16407_3	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
events_test	ee_16407_4	
unknown's avatar
unknown committed
113
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;
unknown's avatar
unknown committed
114 115
user	host	db	info
event_scheduler	localhost	NULL	NULL
unknown's avatar
unknown committed
116 117 118
root	localhost	events_test	select get_lock('ee_16407_2', 60)
root	localhost	events_test	select get_lock('ee_16407_2', 60)
root	localhost	events_test	select get_lock('ee_16407_2', 60)
119 120 121
select release_lock('ee_16407_2');
release_lock('ee_16407_2')
1
unknown's avatar
unknown committed
122
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;
unknown's avatar
unknown committed
123 124
user	host	db	info
event_scheduler	localhost	NULL	NULL
125
set global event_scheduler= off;
126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141
select * from events_smode_test order by ev_name, a;
ev_name	a
ee_16407_3	1980-02-19
ee_16407_3	1980-02-29
ee_16407_4	0000-00-00
"OK, last check before we drop them"
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
event_schema	event_name	sql_mode
events_test	ee_16407_2	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
events_test	ee_16407_3	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
events_test	ee_16407_4	
drop event ee_16407_2;
drop event ee_16407_3;
drop event ee_16407_4;
"And now one last test regarding sql_mode and call of SP from an event"
delete from events_smode_test;
142 143 144 145
set sql_mode='ansi';
select get_lock('ee_16407_5', 60);
get_lock('ee_16407_5', 60)
1
146
set global event_scheduler= on;
147 148 149 150 151
set sql_mode='traditional';
create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end|
create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end|
create event ee_16407_5 on schedule every 60 second do
begin
152
select get_lock('ee_16407_5', 60) /*ee_16407_5*/;
153
select release_lock('ee_16407_5');
154 155 156 157
call events_test.ee_16407_5_pendant();
end|
create event ee_16407_6 on schedule every 60 second do
begin
158
select get_lock('ee_16407_5', 60) /*ee_16407_6*/;
159
select release_lock('ee_16407_5');
160 161
call events_test.ee_16407_6_pendant();
end|
162
"Should have 2 locked processes"
unknown's avatar
unknown committed
163
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;
unknown's avatar
unknown committed
164 165
user	host	db	info
event_scheduler	localhost	NULL	NULL
unknown's avatar
unknown committed
166 167
root	localhost	events_test	select get_lock('ee_16407_5', 60)
root	localhost	events_test	select get_lock('ee_16407_5', 60)
168 169 170
select release_lock('ee_16407_5');
release_lock('ee_16407_5')
1
171
"Should have 0 processes locked"
unknown's avatar
unknown committed
172
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;
unknown's avatar
unknown committed
173 174
user	host	db	info
event_scheduler	localhost	NULL	NULL
175 176 177 178 179 180 181 182 183 184 185 186
select * from events_smode_test order by ev_name, a;
ev_name	a
ee_16407_6	2004-02-29
"And here we check one more time before we drop the events"
select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name;
event_schema	event_name	sql_mode
events_test	ee_16407_5	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
events_test	ee_16407_6	STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
drop event ee_16407_5;
drop event ee_16407_6;
drop procedure ee_16407_5_pendant;
drop procedure ee_16407_6_pendant;
187
set global event_scheduler= off;
188 189
drop table events_smode_test;
set sql_mode=@old_sql_mode;
190
set global event_scheduler=off;
191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212
delete from mysql.user where User like 'mysqltest_%';
delete from mysql.db where User like 'mysqltest_%';
flush privileges;
drop database if exists mysqltest_db1;
create user mysqltest_user1@localhost;
create database mysqltest_db1;
grant event on events_test.* to mysqltest_user1@localhost;
create event mysqltest_user1 on schedule every 10 second do select 42;
alter event mysqltest_user1 rename to mysqltest_db1.mysqltest_user1;
ERROR 42000: Access denied for user 'mysqltest_user1'@'localhost' to database 'mysqltest_db1'
"Let's test now rename when there is no select DB"
select database();
database()
NULL
alter event events_test.mysqltest_user1 rename to mysqltest_user1;
ERROR 3D000: No database selected
select event_schema, event_name, definer, event_type, status from information_schema.events;
event_schema	event_name	definer	event_type	status
events_test	mysqltest_user1	mysqltest_user1@localhost	RECURRING	ENABLED
drop event events_test.mysqltest_user1;
drop user mysqltest_user1@localhost;
drop database mysqltest_db1;
213
create event e_53 on schedule at (select s1 from ttx) do drop table t;
unknown's avatar
unknown committed
214
ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
215
create event e_53 on schedule every (select s1 from ttx) second do drop table t;
unknown's avatar
unknown committed
216
ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
217
create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t;
unknown's avatar
unknown committed
218
ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
219
create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t;
unknown's avatar
unknown committed
220
ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
221 222 223 224 225 226 227 228 229 230 231 232 233
drop event if exists e_16;
drop procedure if exists p_16;
create event e_16 on schedule every 1 second do set @a=5;
create procedure p_16 () alter event e_16 on schedule every @a second;
set @a = null;
call p_16();
ERROR HY000: Incorrect INTERVAL value: 'NULL'
call p_16();
ERROR HY000: Incorrect INTERVAL value: 'NULL'
set @a= 6;
call p_16();
drop procedure p_16;
drop event e_16;
unknown's avatar
unknown committed
234 235 236 237 238 239 240 241 242 243 244 245 246
drop function if exists f22830;
drop event if exists e22830;
drop event if exists e22830_1;
drop event if exists e22830_2;
drop event if exists e22830_3;
drop event if exists e22830_4;
drop table if exists t1;
drop table if exists t2;
create table t1 (a int);
insert into t1 values (2);
create table t2 (a char(20));
insert into t2 values ("e22830_1");
create function f22830 () returns int return 5;
247 248 249 250 251 252 253 254 255 256 257 258 259 260
select get_lock('ee_22830', 60);
get_lock('ee_22830', 60)
1
set global event_scheduler=on;
create procedure p22830_wait()
begin
select get_lock('ee_22830', 60);
select release_lock('ee_22830');
end|
create event e22830 on schedule every f22830() second do
begin
call p22830_wait();
select 123;
end|
unknown's avatar
unknown committed
261
ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282
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|
"All events should be blocked in get_lock()"
unknown's avatar
unknown committed
283 284
select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name;
event_name	event_definition	interval_value	interval_field
285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303
e22830_1	begin
call p22830_wait();
alter event e22830_1 on schedule every (select 8 from dual) hour;
end	1	HOUR
e22830_2	begin
call p22830_wait();
alter event e22830_2 on schedule every (select 8 from t1) hour;
end	1	HOUR
e22830_3	begin
call p22830_wait();
alter event e22830_3 on schedule every f22830() hour;
end	1	HOUR
e22830_4	begin
call p22830_wait();
alter event e22830_4 on schedule every (select f22830() from dual) hour;
end	1	HOUR
select release_lock('ee_22830');
release_lock('ee_22830')
1
unknown's avatar
unknown committed
304 305 306
set global event_scheduler=off;
select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name;
event_name	event_definition	interval_value	interval_field
307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323
e22830_1	begin
call p22830_wait();
alter event e22830_1 on schedule every (select 8 from dual) hour;
end	8	HOUR
e22830_2	begin
call p22830_wait();
alter event e22830_2 on schedule every (select 8 from t1) hour;
end	1	HOUR
e22830_3	begin
call p22830_wait();
alter event e22830_3 on schedule every f22830() hour;
end	1	HOUR
e22830_4	begin
call p22830_wait();
alter event e22830_4 on schedule every (select f22830() from dual) hour;
end	1	HOUR
drop procedure p22830_wait;
unknown's avatar
unknown committed
324 325 326 327 328 329 330 331 332
drop function f22830;
drop event (select a from t2);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select a from t2)' at line 1
drop event e22830_1;
drop event e22830_2;
drop event e22830_3;
drop event e22830_4;
drop table t1;
drop table t2;
333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385
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;
event_name	definer
e1	root@localhost
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;
event_name	definer
e1	root@localhost
ALTER DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR;
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
event_name	definer
e1	mysqltest_u1@localhost
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;
event_name	definer
e1	root@localhost
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;
event_name	definer
e1	mysqltest_u1@localhost
DROP EVENT e1;
CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
event_name	definer
e1	mysqltest_u1@localhost
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;
event_name	definer
e1	mysqltest_u1@localhost
ALTER DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS;
event_name	definer
e1	mysqltest_u1@localhost
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;
event_name	definer
e1	mysqltest_u1@localhost
DROP EVENT e1;
CREATE DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
DROP EVENT e1;
ERROR HY000: Unknown event 'e1'
DROP USER mysqltest_u1@localhost;
386 387 388 389 390 391
SET GLOBAL EVENT_SCHEDULER= OFF;
SET @save_time_zone= @@TIME_ZONE;
SET TIME_ZONE= '+00:00';
SET TIMESTAMP= UNIX_TIMESTAMP('2005-12-31 23:58:59');
CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1;
SHOW EVENTS;
unknown's avatar
unknown committed
392 393
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
events_test	e1	root@localhost	+00:00	RECURRING	NULL	1	DAY	2005-12-31 23:58:59	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
394 395 396
SET TIME_ZONE= '-01:00';
ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 00:00:00';
SHOW EVENTS;
unknown's avatar
unknown committed
397 398
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
events_test	e1	root@localhost	-01:00	RECURRING	NULL	1	DAY	2000-01-01 00:00:00	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
399 400 401 402
SET TIME_ZONE= '+02:00';
ALTER EVENT e1 ON SCHEDULE AT '2000-01-02 00:00:00'
  ON COMPLETION PRESERVE DISABLE;
SHOW EVENTS;
unknown's avatar
unknown committed
403 404
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
events_test	e1	root@localhost	+02:00	ONE TIME	2000-01-02 00:00:00	NULL	NULL	NULL	NULL	DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
405 406 407 408
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;
unknown's avatar
unknown committed
409 410
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
events_test	e1	root@localhost	-03:00	RECURRING	NULL	1	DAY	2005-12-31 20:58:59	2030-01-03 00:00:00	DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
411 412 413
SET TIME_ZONE= '+04:00';
ALTER EVENT e1 DO SELECT 2;
SHOW EVENTS;
unknown's avatar
unknown committed
414 415
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
events_test	e1	root@localhost	-03:00	RECURRING	NULL	1	DAY	2005-12-31 20:58:59	2030-01-03 00:00:00	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
416 417 418 419 420 421 422 423 424 425 426 427 428
DROP EVENT e1;
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;
SELECT * FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_name;
unknown's avatar
unknown committed
429 430 431 432
EVENT_CATALOG	EVENT_SCHEMA	EVENT_NAME	DEFINER	TIME_ZONE	EVENT_BODY	EVENT_DEFINITION	EVENT_TYPE	EXECUTE_AT	INTERVAL_VALUE	INTERVAL_FIELD	SQL_MODE	STARTS	ENDS	STATUS	ON_COMPLETION	CREATED	LAST_ALTERED	LAST_EXECUTED	EVENT_COMMENT	ORIGINATOR	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
NULL	events_test	e1	root@localhost	+05:00	SQL	SELECT 1	RECURRING	NULL	1	DAY		2006-01-01 00:00:00	NULL	ENABLED	NOT PRESERVE	2005-12-31 23:58:59	2005-12-31 23:58:59	NULL		1	latin1	latin1_swedish_ci	latin1_swedish_ci
NULL	events_test	e2	root@localhost	-05:00	SQL	SELECT 1	RECURRING	NULL	1	DAY		2006-01-01 00:00:00	NULL	ENABLED	NOT PRESERVE	2005-12-31 23:59:00	2005-12-31 23:59:00	NULL		1	latin1	latin1_swedish_ci	latin1_swedish_ci
NULL	events_test	e3	root@localhost	+00:00	SQL	SELECT 1	RECURRING	NULL	1	DAY		2006-01-01 00:00:00	NULL	ENABLED	NOT PRESERVE	2005-12-31 23:59:01	2005-12-31 23:59:01	NULL		1	latin1	latin1_swedish_ci	latin1_swedish_ci
433
SHOW EVENTS;
unknown's avatar
unknown committed
434 435 436 437
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
events_test	e1	root@localhost	+05:00	RECURRING	NULL	1	DAY	2006-01-01 00:00:00	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e2	root@localhost	-05:00	RECURRING	NULL	1	DAY	2006-01-01 00:00:00	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e3	root@localhost	+00:00	RECURRING	NULL	1	DAY	2006-01-01 00:00:00	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
438
SHOW CREATE EVENT e1;
unknown's avatar
unknown committed
439 440
Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database Collation
e1		+05:00	CREATE EVENT `e1` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1	latin1	latin1_swedish_ci	latin1_swedish_ci
441
SHOW CREATE EVENT e2;
unknown's avatar
unknown committed
442 443
Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database Collation
e2		-05:00	CREATE EVENT `e2` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1	latin1	latin1_swedish_ci	latin1_swedish_ci
444
SHOW CREATE EVENT e3;
unknown's avatar
unknown committed
445 446
Event	sql_mode	time_zone	Create Event	character_set_client	collation_connection	Database Collation
e3		+00:00	CREATE EVENT `e3` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 1	latin1	latin1_swedish_ci	latin1_swedish_ci
447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476
The following should fail, and nothing should be altered.
ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
  ENDS '1999-01-02 00:00:00';
ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been altered
ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'
  ENDS '1999-01-02 00:00:00' DISABLE;
ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been altered
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;
Warnings:
Note	1584	Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created
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;
Warnings:
Note	1584	Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created
CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO
SELECT 1;
Warnings:
Note	1584	Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created
CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE
DO
SELECT 1;
Warnings:
Note	1584	Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created
SHOW EVENTS;
unknown's avatar
unknown committed
477 478 479 480
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
events_test	e1	root@localhost	+05:00	RECURRING	NULL	1	DAY	2006-01-01 00:00:00	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e2	root@localhost	-05:00	RECURRING	NULL	1	DAY	2006-01-01 00:00:00	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e3	root@localhost	+00:00	RECURRING	NULL	1	DAY	2006-01-01 00:00:00	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512
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;
Warnings:
Note	1533	Event execution time is in the past. Event has been disabled
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;
Warnings:
Note	1533	Event execution time is in the past. Event has been disabled
CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00'
  ON COMPLETION PRESERVE
DO
SELECT 1;
Warnings:
Note	1533	Event execution time is in the past. Event has been disabled
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;
unknown's avatar
unknown committed
513 514 515 516 517 518 519 520 521
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
events_test	e1	root@localhost	+00:00	RECURRING	NULL	1	HOUR	1999-01-01 00:00:00	1999-01-02 00:00:00	DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e2	root@localhost	+00:00	RECURRING	NULL	1	HOUR	1999-01-01 00:00:00	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e3	root@localhost	+00:00	RECURRING	NULL	1	HOUR	1999-01-01 00:00:00	1999-01-02 00:00:00	DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e4	root@localhost	+00:00	RECURRING	NULL	1	HOUR	1999-01-01 00:00:00	1999-01-02 00:00:00	DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e5	root@localhost	+00:00	ONE TIME	1999-01-01 00:00:00	NULL	NULL	NULL	NULL	DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e6	root@localhost	+00:00	RECURRING	NULL	1	HOUR	1999-01-01 00:00:00	NULL	ENABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e7	root@localhost	+00:00	RECURRING	NULL	1	HOUR	1999-01-01 00:00:00	1999-01-02 00:00:00	DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
events_test	e8	root@localhost	+00:00	ONE TIME	1999-01-01 00:00:00	NULL	NULL	NULL	NULL	DISABLED	1	latin1	latin1_swedish_ci	latin1_swedish_ci
522 523 524 525 526 527 528 529 530
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;
531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559
drop event if exists new_event;
CREATE EVENT new_event ON SCHEDULE EVERY 0 SECOND DO SELECT 1;
ERROR HY000: INTERVAL is either not positive or too big
CREATE EVENT new_event ON SCHEDULE EVERY (SELECT 0) SECOND DO SELECT 1;
ERROR HY000: INTERVAL is either not positive or too big
CREATE EVENT new_event ON SCHEDULE EVERY "abcdef" SECOND DO SELECT 1;
ERROR HY000: INTERVAL is either not positive or too big
CREATE EVENT new_event ON SCHEDULE EVERY "0abcdef" SECOND DO SELECT 1;
ERROR HY000: INTERVAL is either not positive or too big
CREATE EVENT new_event ON SCHEDULE EVERY "a1bcdef" SECOND DO SELECT 1;
ERROR HY000: INTERVAL is either not positive or too big
CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "abcdef" UNION SELECT "abcdef") SECOND DO SELECT 1;
ERROR HY000: INTERVAL is either not positive or too big
CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "0abcdef") SECOND DO SELECT 1;
ERROR HY000: INTERVAL is either not positive or too big
CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "a1bcdef") SECOND DO SELECT 1;
ERROR HY000: INTERVAL is either not positive or too big
CREATE EVENT new_event ON SCHEDULE AT "every day" DO SELECT 1;
ERROR HY000: Incorrect AT value: 'every day'
CREATE EVENT new_event ON SCHEDULE AT "0every day" DO SELECT 1;
ERROR HY000: Incorrect AT value: '0every day'
CREATE EVENT new_event ON SCHEDULE AT (SELECT "every day") DO SELECT 1;
ERROR HY000: Incorrect AT value: 'every day'
CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() DO SELECT 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STARTS NOW() DO SELECT 1' at line 1
CREATE EVENT new_event ON SCHEDULE AT NOW() ENDS NOW() DO SELECT 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENDS NOW() DO SELECT 1' at line 1
CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() ENDS NOW() DO SELECT 1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STARTS NOW() ENDS NOW() DO SELECT 1' at line 1
560 561 562 563 564 565 566 567 568 569 570 571 572 573
USE test;
SHOW GRANTS FOR CURRENT_USER;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
SET GLOBAL event_scheduler = ON;
CREATE TABLE event_log (id int KEY AUTO_INCREMENT,
ev_nm char(40), ev_cnt int,
ev_tm timestamp) ENGINE=MyISAM;
SET @ev_base_date = 20281224180000;
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;
unknown's avatar
unknown committed
574
GRANT select,insert ON test.* TO evtest1@localhost;
575 576 577
SHOW GRANTS FOR evtest1@localhost;
Grants for evtest1@localhost
GRANT USAGE ON *.* TO 'evtest1'@'localhost' IDENTIFIED BY PASSWORD '*3170F3644E31580C25DE4A08F4C07CC9A2D40C32'
unknown's avatar
unknown committed
578
GRANT SELECT, INSERT ON `test`.* TO 'evtest1'@'localhost'
579 580 581 582 583 584 585 586 587
GRANT SELECT, INSERT, CREATE, EVENT ON `events_test`.* TO 'evtest1'@'localhost'
connection e1;
USE events_test;
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 test.event_log WHERE ev_nm = @evname;
unknown's avatar
unknown committed
588
IF @cnt < 6 THEN
589 590
INSERT INTO test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp());
COMMIT;
591
END IF;
592
SELECT COUNT(*) INTO @cnt FROM test.event_log WHERE ev_nm = @evname;
unknown's avatar
unknown committed
593
IF @cnt < 6 THEN
594 595
INSERT INTO test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp());
ROLLBACK;
unknown's avatar
unknown committed
596
END IF;
597 598
END;|
connection default;
599
DROP EVENT ev_sched_1823;
600 601 602 603 604 605 606 607 608 609 610 611 612
DROP USER evtest1@localhost;
USE test;
=====================================================================================
select id,ev_nm,ev_cnt from event_log order by id;
id	ev_nm	ev_cnt
1	ev_sched_1823	1
2	ev_sched_1823	2
3	ev_sched_1823	3
4	ev_sched_1823	4
5	ev_sched_1823	5
6	ev_sched_1823	6
DROP TABLE event_log;
SET GLOBAL event_scheduler = OFF;
unknown's avatar
unknown committed
613
DROP DATABASE events_test;