events.result 20.7 KB
Newer Older
1 2
create database if not exists events_test;
use events_test;
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
CREATE USER pauline@localhost;
CREATE DATABASE db_x;
GRANT EVENT ON db_x.* TO pauline@localhost;
USE db_x;
CREATE TABLE x_table(a int);
CREATE EVENT e_x1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE db_x;
CREATE EVENT e_x2 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE x_table;
SHOW DATABASES LIKE 'db_x';
Database (db_x)
db_x
SET GLOBAL event_scheduler=1;
SHOW DATABASES LIKE 'db_x';
Database (db_x)
db_x
SHOW TABLES FROM db_x;
Tables_in_db_x
x_table
SET GLOBAL event_scheduler=0;
DROP EVENT e_x1;
DROP EVENT e_x2;
DROP DATABASE db_x;
DROP USER pauline@localhost;
USE events_test;
26
SET GLOBAL event_scheduler=0;
unknown's avatar
unknown committed
27 28 29 30
drop event if exists event1;
Warnings:
Note	1305	Event event1 does not exist
create event event1 on schedule every 15 minute starts now() ends date_add(now(), interval 5 hour) DO begin end;
31 32 33
alter event event1 rename to event2 enable;
alter event event2 disable;
alter event event2 enable;
34 35 36
alter event event2 on completion not preserve;
alter event event2 on schedule every 1 year on completion preserve rename to event3 comment "new comment" do begin select 1; end__
alter event event3 rename to event2;
unknown's avatar
unknown committed
37 38 39
drop event event2;
create event event2 on schedule every 2 second starts now() ends date_add(now(), interval 5 hour) comment "some" DO begin end;
drop event event2;
40 41 42 43 44
create event e_43 on schedule every 1 second do set @a = 5;
set global event_scheduler = 1;
alter event e_43 do alter event e_43 do set @a = 4;
select db, name, body, status, interval_field, interval_value from mysql.event;
db	name	body	status	interval_field	interval_value
45
events_test	e_43	set @a = 4	ENABLED	SECOND	1
46
drop event e_43;
47 48 49 50 51 52 53 54
"Let's check whether we can use non-qualified names"
create table non_qualif(a int);
create event non_qualif_ev on schedule every 10 minute do insert into non_qualif values (800219);
select * from non_qualif;
a
800219
drop event non_qualif_ev;
drop table non_qualif;
55
set global event_scheduler = 0;
unknown's avatar
unknown committed
56 57 58 59
create table t_event3 (a int, b float);
drop event if exists event3;
Warnings:
Note	1305	Event event3 does not exist
60
create event event3 on schedule every 50 + 10 minute starts date_add("20100101", interval 5 minute) ends date_add("20151010", interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand());
unknown's avatar
unknown committed
61 62 63 64 65 66
set max_allowed_packet=128000000;
select count(*) from t_event3;
count(*)
0
drop event event3;
drop table t_event3;
67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140
set names utf8;
CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1;
SHOW CREATE EVENT root6;
Event	sql_mode	Create Event
root6		CREATE EVENT `events_test`.`root6` ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1
create event root7 on schedule every 2 year do select 1;
SHOW CREATE EVENT root7;
Event	sql_mode	Create Event
root7		CREATE EVENT `events_test`.`root7` ON SCHEDULE EVERY 2 YEAR ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root8 on schedule every '2:5' year_month do select 1;
SHOW CREATE EVENT root8;
Event	sql_mode	Create Event
root8		CREATE EVENT `events_test`.`root8` ON SCHEDULE EVERY '2-5' YEAR_MONTH ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root8_1 on schedule every '2:15' year_month do select 1;
SHOW CREATE EVENT root8_1;
Event	sql_mode	Create Event
root8_1		CREATE EVENT `events_test`.`root8_1` ON SCHEDULE EVERY '3-3' YEAR_MONTH ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1;
SHOW CREATE EVENT root9;
Event	sql_mode	Create Event
root9		CREATE EVENT `events_test`.`root9` ON SCHEDULE EVERY 2 WEEK ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' DO select 1
create event root10 on schedule every '20:5' day_hour do select 1;
SHOW CREATE EVENT root10;
Event	sql_mode	Create Event
root10		CREATE EVENT `events_test`.`root10` ON SCHEDULE EVERY '20 5' DAY_HOUR ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root11 on schedule every '20:25' day_hour do select 1;
SHOW CREATE EVENT root11;
Event	sql_mode	Create Event
root11		CREATE EVENT `events_test`.`root11` ON SCHEDULE EVERY '21 1' DAY_HOUR ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root12 on schedule every '20:25' hour_minute do select 1;
SHOW CREATE EVENT root12;
Event	sql_mode	Create Event
root12		CREATE EVENT `events_test`.`root12` ON SCHEDULE EVERY '20:25' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root13 on schedule every '25:25' hour_minute do select 1;
SHOW CREATE EVENT root13;
Event	sql_mode	Create Event
root13		CREATE EVENT `events_test`.`root13` ON SCHEDULE EVERY '25:25' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root13_1 on schedule every '11:65' hour_minute do select 1;
SHOW CREATE EVENT root13_1;
Event	sql_mode	Create Event
root13_1		CREATE EVENT `events_test`.`root13_1` ON SCHEDULE EVERY '12:5' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root14 on schedule every '35:35' minute_second do select 1;
SHOW CREATE EVENT root14;
Event	sql_mode	Create Event
root14		CREATE EVENT `events_test`.`root14` ON SCHEDULE EVERY '35:35' MINUTE_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root15 on schedule every '35:66' minute_second do select 1;
SHOW CREATE EVENT root15;
Event	sql_mode	Create Event
root15		CREATE EVENT `events_test`.`root15` ON SCHEDULE EVERY '36:6' MINUTE_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root16 on schedule every '35:56' day_minute do select 1;
SHOW CREATE EVENT root16;
Event	sql_mode	Create Event
root16		CREATE EVENT `events_test`.`root16` ON SCHEDULE EVERY '1 11:56' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root17 on schedule every '35:12:45' day_minute do select 1;
SHOW CREATE EVENT root17;
Event	sql_mode	Create Event
root17		CREATE EVENT `events_test`.`root17` ON SCHEDULE EVERY '35 12:45' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root17_1 on schedule every '35:25:65' day_minute do select 1;
SHOW CREATE EVENT root17_1;
Event	sql_mode	Create Event
root17_1		CREATE EVENT `events_test`.`root17_1` ON SCHEDULE EVERY '36 2:5' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root18 on schedule every '35:12:45' hour_second do select 1;
SHOW CREATE EVENT root18;
Event	sql_mode	Create Event
root18		CREATE EVENT `events_test`.`root18` ON SCHEDULE EVERY '35:12:45' HOUR_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root19 on schedule every '15:59:85' hour_second do select 1;
SHOW CREATE EVENT root19;
Event	sql_mode	Create Event
root19		CREATE EVENT `events_test`.`root19` ON SCHEDULE EVERY '16:0:25' HOUR_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
create event root20 on schedule every '50:20:12:45' day_second do select 1;
SHOW CREATE EVENT root20;
Event	sql_mode	Create Event
root20		CREATE EVENT `events_test`.`root20` ON SCHEDULE EVERY '50 20:12:45' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
set names cp1251;
unknown's avatar
unknown committed
141 142
create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåíòàð' do select 1;
SHOW CREATE EVENT ðóóò21;
143
Event	sql_mode	Create Event
unknown's avatar
unknown committed
144
ðóóò21		CREATE EVENT `events_test`.`ðóóò21` ON SCHEDULE EVERY '51 0:0:35' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE COMMENT 'òîâà å 1251 êîìåíòàð' DO select 1
145 146
insert into mysql.event (db, name, body, definer, interval_value, interval_field) values (database(), "root22", "select 1", user(), 100, "SECOND_MICROSECOND");
show create event root22;
unknown's avatar
unknown committed
147
ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND'
148
SHOW EVENTS;
unknown's avatar
unknown committed
149
ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND'
150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168
drop event root22;
drop event root6;
drop event root7;
drop event root8;
drop event root8_1;
drop event root9;
drop event root10;
drop event root11;
drop event root12;
drop event root13;
drop event root13_1;
drop event root14;
drop event root15;
drop event root16;
drop event root17;
drop event root17_1;
drop event root18;
drop event root19;
drop event root20;
unknown's avatar
unknown committed
169
drop event ðóóò21;
170 171 172 173 174 175 176
set names latin1;
CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing";
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
events_test	intact_check	root@localhost	RECURRING	NULL	10	10 HOUR	#	#	ENABLED
ALTER TABLE mysql.event ADD dummy INT FIRST;
SHOW EVENTS;
unknown's avatar
unknown committed
177
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 17. Table probably corrupted
178 179
ALTER TABLE mysql.event DROP dummy, ADD dummy2 VARCHAR(64) FIRST;
SHOW EVENTS;
unknown's avatar
unknown committed
180
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 17. Table probably corrupted
181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204
ALTER TABLE mysql.event DROP dummy2;
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
events_test	intact_check	root@localhost	RECURRING	NULL	10	10 HOUR	#	#	ENABLED
CREATE TABLE event_like LIKE mysql.event;
INSERT INTO event_like SELECT * FROM mysql.event;
ALTER TABLE mysql.event MODIFY db char(20) character set utf8 collate utf8_bin default '';
SHOW CREATE TABLE mysql.event;
Table	Create Table
event	CREATE TABLE `event` (
  `db` char(20) character set utf8 collate utf8_bin NOT NULL default '',
  `name` char(64) character set utf8 collate utf8_bin NOT NULL default '',
  `body` longblob NOT NULL,
  `definer` char(77) character set utf8 collate utf8_bin NOT NULL default '',
  `execute_at` datetime default NULL,
  `interval_value` int(11) default NULL,
  `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL,
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL default '0000-00-00 00:00:00',
  `last_executed` datetime default NULL,
  `starts` datetime default NULL,
  `ends` datetime default NULL,
  `status` enum('ENABLED','DISABLED') NOT NULL default 'ENABLED',
  `on_completion` enum('DROP','PRESERVE') NOT NULL default 'DROP',
unknown's avatar
unknown committed
205
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE') NOT NULL default '',
206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223
  `comment` char(64) character set utf8 collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`definer`,`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events'
SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;
ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log.
ALTER TABLE mysql.event MODIFY db char(64) character set utf8 collate utf8_bin default '';
"This should work"
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
events_test	intact_check	root@localhost	RECURRING	NULL	10	10 HOUR	#	#	ENABLED
ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default '';
SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;
ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log.
ALTER TABLE mysql.event MODIFY db varchar(64) character set utf8 collate utf8_bin default '';
SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;
ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log.
ALTER TABLE mysql.event DROP comment, DROP starts;
SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;
unknown's avatar
unknown committed
224
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 14. Table probably corrupted
225 226 227 228 229 230 231 232
DROP TABLE mysql.event;
CREATE TABLE mysql.event like event_like;
INSERT INTO  mysql.event SELECT * FROM event_like;
DROP TABLE event_like;
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
events_test	intact_check	root@localhost	RECURRING	NULL	10	10 HOUR	#	#	ENABLED
DROP EVENT intact_check;
233 234 235
create event one_event on schedule every 10 second do select 123;
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
236
events_test	one_event	root@localhost	RECURRING	NULL	10	10 SECOND	#	#	ENABLED
237 238
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events;
EVENT_CATALOG	EVENT_SCHEMA	EVENT_NAME	DEFINER	EVENT_BODY	EVENT_TYPE	EXECUTE_AT	INTERVAL_VALUE	INTERVAL_FIELD	STATUS	ON_COMPLETION	EVENT_COMMENT
239
NULL	events_test	one_event	root@localhost	select 123	RECURRING	NULL	10	10 SECOND	ENABLED	NOT PRESERVE	
240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255
CREATE DATABASE events_test2;
CREATE USER ev_test@localhost;
GRANT ALL ON events_test.* to ev_test@localhost;
GRANT ALL on events_test2.* to ev_test@localhost;
REVOKE EVENT ON events_test2.* FROM ev_test@localhost;
REVOKE PROCESS on *.* from ev_test@localhost;
select "NEW CONNECTION";
NEW CONNECTION
NEW CONNECTION
SELECT USER(), DATABASE();
USER()	DATABASE()
ev_test@localhost	events_test2
SHOW GRANTS;
Grants for ev_test@localhost
GRANT USAGE ON *.* TO 'ev_test'@'localhost'
GRANT ALL PRIVILEGES ON `events_test`.* TO 'ev_test'@'localhost'
256
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `events_test2`.* TO 'ev_test'@'localhost'
257
"Here comes an error:";
258 259 260
SHOW EVENTS;
ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_test2'
USE events_test;
261
"Now the list should be empty:";
262 263 264 265 266 267 268 269
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
select concat("Let's create some new events from the name of ",user());
concat("Let's create some new events from the name of ",user())
Let's create some new events from the name of ev_test@localhost
create event one_event on schedule every 20 second do select 123;
create event two_event on schedule every 20 second on completion not preserve comment "two event" do select 123;
create event three_event on schedule every 20 second on completion preserve comment "three event" do select 123;
270
"Now we should see 3 events:";
271 272
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
273 274 275
events_test	one_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
events_test	three_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
events_test	two_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
276
"This should show us only 3 events:";
277 278
SHOW FULL EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
279 280 281
events_test	one_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
events_test	three_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
events_test	two_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
282
"This should show us only 2 events:";
283 284
SHOW FULL EVENTS LIKE 't%event';
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
285 286
events_test	three_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
events_test	two_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
287
"This should show us no events:";
288 289 290
SHOW FULL EVENTS FROM test LIKE '%';
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
DROP DATABASE events_test2;
291
"should see 1 event:";
292 293
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
294
events_test	one_event	root@localhost	RECURRING	NULL	10	10 SECOND	#	#	ENABLED
295
"we should see 4 events now:";
296 297
SHOW FULL EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
298 299 300 301
events_test	one_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
events_test	three_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
events_test	two_event	ev_test@localhost	RECURRING	NULL	20	20 SECOND	#	#	ENABLED
events_test	one_event	root@localhost	RECURRING	NULL	10	10 SECOND	#	#	ENABLED
302 303
SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events;
EVENT_CATALOG	EVENT_SCHEMA	EVENT_NAME	DEFINER	EVENT_BODY	EVENT_TYPE	EXECUTE_AT	INTERVAL_VALUE	INTERVAL_FIELD	STATUS	ON_COMPLETION	EVENT_COMMENT
304 305 306 307
NULL	events_test	one_event	ev_test@localhost	select 123	RECURRING	NULL	20	20 SECOND	ENABLED	NOT PRESERVE	
NULL	events_test	three_event	ev_test@localhost	select 123	RECURRING	NULL	20	20 SECOND	ENABLED	PRESERVE	three event
NULL	events_test	two_event	ev_test@localhost	select 123	RECURRING	NULL	20	20 SECOND	ENABLED	NOT PRESERVE	two event
NULL	events_test	one_event	root@localhost	select 123	RECURRING	NULL	10	10 SECOND	ENABLED	NOT PRESERVE	
308 309 310 311 312
drop event one_event;
drop event two_event;
drop event three_event;
drop user ev_test@localhost;
drop event one_event;
313
"Sleep a bit so the server closes the second connection"
314 315 316
create event e_26 on schedule at '2017-01-01 00:00:00' disable do set @a = 5;
select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event;
db	name	body	definer	convert_tz(execute_at, 'UTC', 'SYSTEM')	on_completion
317
events_test	e_26	set @a = 5	root@localhost	2017-01-01 00:00:00	DROP
318 319 320 321 322 323 324 325 326 327 328 329
drop event e_26;
create event e_26 on schedule at NULL disabled do set @a = 5;
ERROR HY000: Incorrect AT value: 'NULL'
create event e_26 on schedule at 'definitely not a datetime' disabled do set @a = 5;
ERROR HY000: Incorrect AT value: 'definitely not a datetime'
set names utf8;
create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1;
drop event задачка;
set event_scheduler=0;
ERROR HY000: Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL
set global event_scheduler=2;
ERROR 42000: Variable 'event_scheduler' can't be set to the value of '2'
330 331 332 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
"DISABLE the scheduler. Testing that it does not work when the variable is 0"
set global event_scheduler=0;
select definer, name, db from mysql.event;
definer	name	db
select get_lock("test_lock1", 20);
get_lock("test_lock1", 20)
1
create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20);
"Should return 1 row"
select definer, name, db from mysql.event;
definer	name	db
root@localhost	закачка	events_test
"Should be only 1 process"
show processlist;
Id	User	Host	db	Command	Time	State	Info
#	root	localhost	events_test	Query	#	NULL	show processlist
select release_lock("test_lock1");
release_lock("test_lock1")
1
drop event закачка;
"Should have 0 events"
select count(*) from mysql.event;
count(*)
0
"ENABLE the scheduler and get a lock"
set global event_scheduler=1;
select get_lock("test_lock2", 20);
get_lock("test_lock2", 20)
1
"Create an event which tries to acquire a mutex. The event locks on the mutex"
create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20);
"Let some time pass to the event starts"
"Should have only 3 processes: the scheduler, our conn and the locked event"
show processlist;
Id	User	Host	db	Command	Time	State	Info
#	root	localhost	events_test	Query	#	NULL	show processlist
unknown's avatar
unknown committed
366 367
#	event_scheduler	connecting host	NULL	Connect	#	Sleeping	NULL
#	root	localhost	events_test	Connect	#	User lock	select get_lock("test_lock2", 20)
368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384
"Release the mutex, the event worker should finish."
select release_lock("test_lock2");
release_lock("test_lock2")
1
drop event закачка;
set global event_scheduler=1;
select get_lock("test_lock2_1", 20);
get_lock("test_lock2_1", 20)
1
create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20);
"Should see 2 processes, one locked on get_lock("
"Shutting down the scheduler, it should wait for the running event"
set global event_scheduler=0;
"Should have only 3 processes: the scheduler, our conn and the locked event"
show processlist;
Id	User	Host	db	Command	Time	State	Info
#	root	localhost	events_test	Query	#	NULL	show processlist
unknown's avatar
unknown committed
385
#	event_scheduler	connecting host	NULL	Connect	#	Sleeping	NULL
386 387 388 389 390 391 392 393 394
"Release the lock so the child process should finish. Hence the scheduler also"
select release_lock("test_lock2_1");
release_lock("test_lock2_1")
1
"Should have only our process now:"
show processlist;
Id	User	Host	db	Command	Time	State	Info
#	root	localhost	events_test	Query	#	NULL	show processlist
drop event закачка21;
395 396 397 398
create table t_16 (s1 int);
create trigger t_16_bi before insert on t_16 for each row create event  e_16 on schedule every 1 second do set @a=5;
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
drop table t_16;
399
drop database events_test;