events.result 24.6 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
CREATE EVENT event_starts_test ON SCHEDULE EVERY 10 SECOND COMMENT "" DO SELECT 1;
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
unknown's avatar
unknown committed
43
events_test	event_starts_test	root@localhost	RECURRING	NULL	10	SECOND	#	#	ENABLED
44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
starts IS NULL	ends IS NULL	comment
0	1	
ALTER EVENT event_starts_test ON SCHEDULE AT '2020-02-02 20:00:02';
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
events_test	event_starts_test	root@localhost	ONE TIME	2020-02-02 17:00:02	NULL	NULL	#	#	ENABLED
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
starts IS NULL	ends IS NULL	comment
1	1	
ALTER EVENT event_starts_test COMMENT "non-empty comment";
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
events_test	event_starts_test	root@localhost	ONE TIME	2020-02-02 17:00:02	NULL	NULL	#	#	ENABLED
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
starts IS NULL	ends IS NULL	comment
1	1	non-empty comment
ALTER EVENT event_starts_test COMMENT "";
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
events_test	event_starts_test	root@localhost	ONE TIME	2020-02-02 17:00:02	NULL	NULL	#	#	ENABLED
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
starts IS NULL	ends IS NULL	comment
1	1	
DROP EVENT event_starts_test;
CREATE EVENT event_starts_test ON SCHEDULE EVERY 20 SECOND STARTS '2020-02-02 20:00:02' ENDS '2022-02-02 20:00:02' DO SELECT 2;
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
unknown's avatar
unknown committed
72
events_test	event_starts_test	root@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
73 74 75 76 77 78
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
starts IS NULL	ends IS NULL	comment
0	0	
ALTER EVENT event_starts_test COMMENT "non-empty comment";
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
unknown's avatar
unknown committed
79
events_test	event_starts_test	root@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
80 81 82 83 84 85
SELECT starts IS NULL, ends IS NULL, comment FROM mysql.event WHERE db='events_test' AND name='event_starts_test';
starts IS NULL	ends IS NULL	comment
0	0	non-empty comment
ALTER EVENT event_starts_test COMMENT "";
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
unknown's avatar
unknown committed
86
events_test	event_starts_test	root@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
87
DROP EVENT event_starts_test;
88 89 90 91 92
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
93
events_test	e_43	set @a = 4	ENABLED	SECOND	1
94
drop event e_43;
95 96 97 98 99 100 101 102
"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;
103
set global event_scheduler = 0;
unknown's avatar
unknown committed
104 105 106 107
create table t_event3 (a int, b float);
drop event if exists event3;
Warnings:
Note	1305	Event event3 does not exist
108
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
109 110 111 112 113
select count(*) from t_event3;
count(*)
0
drop event event3;
drop table t_event3;
114 115 116 117
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
118
root6		CREATE EVENT `root6` ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1
119 120 121
create event root7 on schedule every 2 year do select 1;
SHOW CREATE EVENT root7;
Event	sql_mode	Create Event
122
root7		CREATE EVENT `root7` ON SCHEDULE EVERY 2 YEAR ON COMPLETION NOT PRESERVE ENABLE DO select 1
123 124 125
create event root8 on schedule every '2:5' year_month do select 1;
SHOW CREATE EVENT root8;
Event	sql_mode	Create Event
126
root8		CREATE EVENT `root8` ON SCHEDULE EVERY '2-5' YEAR_MONTH ON COMPLETION NOT PRESERVE ENABLE DO select 1
127 128 129
create event root8_1 on schedule every '2:15' year_month do select 1;
SHOW CREATE EVENT root8_1;
Event	sql_mode	Create Event
130
root8_1		CREATE EVENT `root8_1` ON SCHEDULE EVERY '3-3' YEAR_MONTH ON COMPLETION NOT PRESERVE ENABLE DO select 1
131 132 133
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
134
root9		CREATE EVENT `root9` ON SCHEDULE EVERY 2 WEEK ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' DO select 1
135 136 137
create event root10 on schedule every '20:5' day_hour do select 1;
SHOW CREATE EVENT root10;
Event	sql_mode	Create Event
138
root10		CREATE EVENT `root10` ON SCHEDULE EVERY '20 5' DAY_HOUR ON COMPLETION NOT PRESERVE ENABLE DO select 1
139 140 141
create event root11 on schedule every '20:25' day_hour do select 1;
SHOW CREATE EVENT root11;
Event	sql_mode	Create Event
142
root11		CREATE EVENT `root11` ON SCHEDULE EVERY '21 1' DAY_HOUR ON COMPLETION NOT PRESERVE ENABLE DO select 1
143 144 145
create event root12 on schedule every '20:25' hour_minute do select 1;
SHOW CREATE EVENT root12;
Event	sql_mode	Create Event
146
root12		CREATE EVENT `root12` ON SCHEDULE EVERY '20:25' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
147 148 149
create event root13 on schedule every '25:25' hour_minute do select 1;
SHOW CREATE EVENT root13;
Event	sql_mode	Create Event
150
root13		CREATE EVENT `root13` ON SCHEDULE EVERY '25:25' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
151 152 153
create event root13_1 on schedule every '11:65' hour_minute do select 1;
SHOW CREATE EVENT root13_1;
Event	sql_mode	Create Event
154
root13_1		CREATE EVENT `root13_1` ON SCHEDULE EVERY '12:5' HOUR_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
155 156 157
create event root14 on schedule every '35:35' minute_second do select 1;
SHOW CREATE EVENT root14;
Event	sql_mode	Create Event
158
root14		CREATE EVENT `root14` ON SCHEDULE EVERY '35:35' MINUTE_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
159 160 161
create event root15 on schedule every '35:66' minute_second do select 1;
SHOW CREATE EVENT root15;
Event	sql_mode	Create Event
162
root15		CREATE EVENT `root15` ON SCHEDULE EVERY '36:6' MINUTE_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
163 164 165
create event root16 on schedule every '35:56' day_minute do select 1;
SHOW CREATE EVENT root16;
Event	sql_mode	Create Event
166
root16		CREATE EVENT `root16` ON SCHEDULE EVERY '1 11:56' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
167 168 169
create event root17 on schedule every '35:12:45' day_minute do select 1;
SHOW CREATE EVENT root17;
Event	sql_mode	Create Event
170
root17		CREATE EVENT `root17` ON SCHEDULE EVERY '35 12:45' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
171 172 173
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
174
root17_1		CREATE EVENT `root17_1` ON SCHEDULE EVERY '36 2:5' DAY_MINUTE ON COMPLETION NOT PRESERVE ENABLE DO select 1
175 176 177
create event root18 on schedule every '35:12:45' hour_second do select 1;
SHOW CREATE EVENT root18;
Event	sql_mode	Create Event
178
root18		CREATE EVENT `root18` ON SCHEDULE EVERY '35:12:45' HOUR_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
179 180 181
create event root19 on schedule every '15:59:85' hour_second do select 1;
SHOW CREATE EVENT root19;
Event	sql_mode	Create Event
182
root19		CREATE EVENT `root19` ON SCHEDULE EVERY '16:0:25' HOUR_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
183 184 185
create event root20 on schedule every '50:20:12:45' day_second do select 1;
SHOW CREATE EVENT root20;
Event	sql_mode	Create Event
186
root20		CREATE EVENT `root20` ON SCHEDULE EVERY '50 20:12:45' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
187
set names cp1251;
unknown's avatar
unknown committed
188 189
create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåíòàð' do select 1;
SHOW CREATE EVENT ðóóò21;
190
Event	sql_mode	Create Event
191
ðóóò21		CREATE EVENT `ðóóò21` ON SCHEDULE EVERY '51 0:0:35' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE COMMENT 'òîâà å 1251 êîìåíòàð' DO select 1
192 193
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
194
ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND'
195
SHOW EVENTS;
unknown's avatar
unknown committed
196
ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND'
197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215
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
216
drop event ðóóò21;
217 218 219 220
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
unknown's avatar
unknown committed
221
events_test	intact_check	root@localhost	RECURRING	NULL	10	HOUR	#	#	ENABLED
222 223
ALTER TABLE mysql.event ADD dummy INT FIRST;
SHOW EVENTS;
unknown's avatar
unknown committed
224
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 17. Table probably corrupted
225 226
ALTER TABLE mysql.event DROP dummy, ADD dummy2 VARCHAR(64) FIRST;
SHOW EVENTS;
unknown's avatar
unknown committed
227
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 17. Table probably corrupted
228 229 230
ALTER TABLE mysql.event DROP dummy2;
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
unknown's avatar
unknown committed
231
events_test	intact_check	root@localhost	RECURRING	NULL	10	HOUR	#	#	ENABLED
232 233
CREATE TABLE event_like LIKE mysql.event;
INSERT INTO event_like SELECT * FROM mysql.event;
234 235 236
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.
237 238 239 240
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` (
241
  `db` char(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
242
  `name` char(64) NOT NULL DEFAULT '',
243
  `body` longblob NOT NULL,
244 245 246 247 248 249 250 251 252 253 254 255 256
  `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',
  `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 '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
257
  PRIMARY KEY (`definer`,`db`,`name`)
258 259 260 261 262 263 264
) 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
unknown's avatar
unknown committed
265 266 267 268
events_test	intact_check	root@localhost	RECURRING	NULL	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.
269 270 271 272 273
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
274
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 14. Table probably corrupted
275 276 277 278 279 280
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
unknown's avatar
unknown committed
281
events_test	intact_check	root@localhost	RECURRING	NULL	10	HOUR	#	#	ENABLED
282
DROP EVENT intact_check;
283 284 285
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
unknown's avatar
unknown committed
286
events_test	one_event	root@localhost	RECURRING	NULL	10	SECOND	#	#	ENABLED
287 288
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
unknown's avatar
unknown committed
289
NULL	events_test	one_event	root@localhost	select 123	RECURRING	NULL	10	SECOND	ENABLED	NOT PRESERVE	
290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305
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'
306
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'
307
"Here comes an error:";
308 309 310
SHOW EVENTS;
ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_test2'
USE events_test;
311
"Now the list should be empty:";
312 313 314 315 316 317 318 319
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;
320
"Now we should see 3 events:";
321 322
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
unknown's avatar
unknown committed
323 324 325
events_test	one_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
events_test	three_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
events_test	two_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
326
"This should show us only 3 events:";
327 328
SHOW FULL EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
unknown's avatar
unknown committed
329 330 331
events_test	one_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
events_test	three_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
events_test	two_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
332
"This should show us only 2 events:";
333 334
SHOW FULL EVENTS LIKE 't%event';
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
unknown's avatar
unknown committed
335 336
events_test	three_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
events_test	two_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
337
"This should show us no events:";
338 339 340
SHOW FULL EVENTS FROM test LIKE '%';
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
DROP DATABASE events_test2;
341
"should see 1 event:";
342 343
SHOW EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
unknown's avatar
unknown committed
344
events_test	one_event	root@localhost	RECURRING	NULL	10	SECOND	#	#	ENABLED
345
"we should see 4 events now:";
346 347
SHOW FULL EVENTS;
Db	Name	Definer	Type	Execute at	Interval value	Interval field	Starts	Ends	Status
unknown's avatar
unknown committed
348 349 350 351
events_test	one_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
events_test	three_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
events_test	two_event	ev_test@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
events_test	one_event	root@localhost	RECURRING	NULL	10	SECOND	#	#	ENABLED
352 353
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
unknown's avatar
unknown committed
354 355 356 357
NULL	events_test	one_event	ev_test@localhost	select 123	RECURRING	NULL	20	SECOND	ENABLED	NOT PRESERVE	
NULL	events_test	three_event	ev_test@localhost	select 123	RECURRING	NULL	20	SECOND	ENABLED	PRESERVE	three event
NULL	events_test	two_event	ev_test@localhost	select 123	RECURRING	NULL	20	SECOND	ENABLED	NOT PRESERVE	two event
NULL	events_test	one_event	root@localhost	select 123	RECURRING	NULL	10	SECOND	ENABLED	NOT PRESERVE	
358 359 360 361 362
drop event one_event;
drop event two_event;
drop event three_event;
drop user ev_test@localhost;
drop event one_event;
363
"Sleep a bit so the server closes the second connection"
364 365 366
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
367
events_test	e_26	set @a = 5	root@localhost	2017-01-01 00:00:00	DROP
368 369 370 371 372 373 374 375 376 377 378 379
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'
380 381 382 383 384 385 386 387 388 389 390 391
"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
392 393 394
"Should be 0 processes"
select /*1*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
user	host	db	command	state	info
395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410
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"
411 412 413 414 415
"Should have only 2 processes: the scheduler and the locked event"
select /*1*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
user	host	db	command	state	info
event_scheduler	localhost	NULL	Connect	Sleeping	NULL
root	localhost	events_test	Connect	User lock	select get_lock("test_lock2", 20)
416 417 418 419 420 421 422 423 424 425
"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);
426
"Should see 1 process, locked on get_lock("
427 428
"Shutting down the scheduler, it should wait for the running event"
set global event_scheduler=0;
429 430 431 432 433
"Should have only 2 processes: the scheduler and the locked event"
select /*4*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
user	host	db	command	state	info
event_scheduler	localhost	NULL	Connect	Sleeping	NULL
root	localhost	events_test	Connect	User lock	select get_lock("test_lock2_1", 20)
434 435 436 437
"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
438 439 440
"Should see 0 processes now:"
select /*5*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info;
user	host	db	command	state	info
441
drop event закачка21;
442 443 444 445
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;
446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465
create event white_space
on schedule every 10 hour
disable
do
select 1;
select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space';
event_schema	event_name	definer	event_body
events_test	white_space	root@localhost	select 1
drop event white_space;
create event white_space on schedule every 10 hour disable do
select 2;
select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space';
event_schema	event_name	definer	event_body
events_test	white_space	root@localhost	select 2
drop event white_space;
create event white_space on schedule every 10 hour disable do	select 3;
select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space';
event_schema	event_name	definer	event_body
events_test	white_space	root@localhost	select 3
drop event white_space;
466 467 468 469 470 471
create event e1 on schedule every 1 year do set @a = 5;
create table t1 (s1 int);
create trigger t1_ai after insert on t1 for each row show create event e1;
ERROR 0A000: Not allowed to return a result set from a trigger
drop table t1;
drop event e1;
472
drop database events_test;