• unknown's avatar
    WL#3337 (Event scheduler new architecture) · db57e46c
    unknown authored
    This is a post-review patch.
    
    Fixes the typelib implementation, available only in 5.1.11.
    
    --event-scheduler cmdline : DISABLED | ON | OFF | 0 | 1
    DISABLED - makes the scheduler unavailable during the server run
    (ON|1)-  When the server is started the scheduler will be started. It can
             be stopped and restarted by setting appropriate values to
             GLOBAL event_scheduler
    (OFF|0)- When the server is started, the scheduler won't be started. It
             can be started and again stopped by setting appropriate values to
             GLOBAL event_scheduler. _DEFAULT_ value
    
    The GLOBAL variable event_scheduler can have the following values:
    OFF | ON | 0 | 1
    DISABLED is not possible and every attempt will end with an error that
    it's not a valid value for the variable.
    OFF | 0 - This is the pre-5.1.11 behavior - The scheduler stops, if not
              already stopped, and can be started again  by setting
              the value of the variable to ON|1.
    ON | 1  - This is the pre-5.1.11 behavior - The scheduler starts, if not
              already started, and can be stopped again by setting the value
              of the variable to OFF|0.
    
    
    mysql-test/r/events.result:
      update result
    mysql-test/r/events_bugs.result:
      update result
    mysql-test/r/events_logs_tests.result:
      update result
    mysql-test/r/events_restart_phase1.result:
      update result
    mysql-test/r/events_restart_phase3.result:
      update result
    mysql-test/r/events_scheduling.result:
      update result
    mysql-test/r/events_stress.result:
      update result
    mysql-test/t/events.test:
      update test:
      2 -> off
      1 -> on
    mysql-test/t/events_bugs.test:
      update test:
      2 -> off
      1 -> on
    mysql-test/t/events_logs_tests.test:
      update test:
      2 -> off
      1 -> on
    mysql-test/t/events_restart_phase1.test:
      update test:
      2 -> off
      1 -> on
    mysql-test/t/events_restart_phase2-master.opt:
      update master file : 1 => on
    mysql-test/t/events_scheduling.test:
      update test:
      2 -> off
      1 -> on
      
      add tests for event_scheduler global variable representation from
      SHOW VARIABLES.
    mysql-test/t/events_stress.test:
      update test:
      2 -> off
      1 -> on
    sql/events.cc:
      Implement two different TYPELIBs for --event-scheduler cmd line
      option and for GLOBAL variable event_scheduler
      
      --event-scheduler cmdline : DISABLED | ON | OFF | 0 | 1
      DISABLED - makes the scheduler unavailable during the server run
      (ON|1)-  When the server is started the scheduler will be started. It can
               be stopped and restarted by setting appropriate values to
               GLOBAL event_scheduler
      (OFF|0)- When the server is started, the scheduler won't be started. It
               can be started and again stopped by setting appropriate values to
               GLOBAL event_scheduler. _DEFAULT_ value
      
      The GLOBAL variable event_scheduler can have the following values:
      OFF | ON | 0 | 1
      DISABLED is not possible and every attempt will end with an error that
      it's not a valid value for the variable.
      OFF | 0 - This is the pre-5.1.11 behavior - The scheduler stops, if not
                already stopped, and can be started again  by setting
                the value of the variable to ON|1.
      ON | 1  - This is the pre-5.1.11 behavior - The scheduler starts, if not
                already started, and can be stopped again by setting the value
                of the variable to OFF|0.
    sql/events.h:
      additional TYPELIB for GLOBAL event_scheduler
    sql/mysqld.cc:
      --event-scheduler should be checked against a TYPELIB and
      therefore should be GET_STR, as well as we make the parameter optional.
      When not provided OFF|0 is used.
    sql/set_var.cc:
      Implement typelib for event_scheduler variable.
      If allows both INT_RESULT -> 0 | 1 
      and STRING_RESULT -> OFF | ON
      The variable is shown as DISABLED | ON | OFF
    sql/set_var.h:
      Implement typelib, which expects both STRING and INT,
      for event_scheduler.
    db57e46c
events.result 20.8 KB
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 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 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 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 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 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 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 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 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 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412
create database if not exists events_test;
use events_test;
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=off;
DROP EVENT e_x1;
DROP EVENT e_x2;
DROP DATABASE db_x;
DROP USER pauline@localhost;
USE events_test;
SET GLOBAL event_scheduler=off;
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;
alter event event1 rename to event2 enable;
alter event event2 disable;
alter event event2 enable;
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;
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;
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
events_test	event_starts_test	root@localhost	RECURRING	NULL	10	SECOND	#	#	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
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
events_test	event_starts_test	root@localhost	RECURRING	NULL	20	SECOND	#	#	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
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
events_test	event_starts_test	root@localhost	RECURRING	NULL	20	SECOND	#	#	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
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
events_test	event_starts_test	root@localhost	RECURRING	NULL	20	SECOND	#	#	ENABLED
DROP EVENT event_starts_test;
create table test_nested(a int);
create event e_43 on schedule every 1 second do set @a = 5;
alter event e_43 do alter event e_43 do set @a = 4;
ERROR HY000: Recursivity of EVENT DDL statements is forbidden when body is present
alter event e_43 do
begin
alter event e_43 on schedule every 5 minute;
insert into test_nested values(1);
end|
set global event_scheduler = on;
select db, name, body, status, interval_field, interval_value from mysql.event;
db	name	body	status	interval_field	interval_value
events_test	e_43	begin
alter event e_43 on schedule every 5 minute;
insert into test_nested values(1);
end	ENABLED	MINUTE	5
drop event e_43;
drop table test_nested;
"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;
alter event non_existant rename to non_existant_too;
ERROR HY000: Unknown event 'non_existant'
set global event_scheduler = off;
create event existant on schedule at now() + interval 1 year do select 12;
alter event non_existant rename to existant;
ERROR HY000: Event 'existant' already exists
alter event existant rename to events_test.existant;
ERROR HY000: Same old and new event name
drop event existant;
create table t_event3 (a int, b float);
drop event if exists event3;
Warnings:
Note	1305	Event event3 does not exist
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());
select count(*) from t_event3;
count(*)
0
drop event event3;
drop table t_event3;
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 `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 `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 `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 `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 `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 `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 `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 `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 `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 `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 `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 `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 `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 `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 `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 `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 `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 `root20` ON SCHEDULE EVERY '50 20:12:45' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE DO select 1
set names cp1251;
create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåíòàð' do select 1;
SHOW CREATE EVENT ðóóò21;
Event	sql_mode	Create Event
ðóóò21		CREATE EVENT `ðóóò21` ON SCHEDULE EVERY '51 0:0:35' DAY_SECOND ON COMPLETION NOT PRESERVE ENABLE COMMENT 'òîâà å 1251 êîìåíòàð' DO select 1
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;
ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND'
SHOW EVENTS;
ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND'
drop event root22;
create event root23 on schedule every -100 year do select 1;
ERROR HY000: INTERVAL is either not positive or too big
create event root23 on schedule every 222222222222222222222 year do select 1;
ERROR HY000: INTERVAL is either not positive or too big
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;
drop event ðóóò21;
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	HOUR	#	#	ENABLED
ALTER TABLE mysql.event ADD dummy INT FIRST;
SHOW EVENTS;
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 17. Table probably corrupted
ALTER TABLE mysql.event DROP dummy, ADD dummy2 VARCHAR(64) FIRST;
SHOW EVENTS;
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 17. Table probably corrupted
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	HOUR	#	#	ENABLED
CREATE TABLE event_like LIKE mysql.event;
INSERT INTO event_like SELECT * FROM mysql.event;
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 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) 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',
  `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 '',
  PRIMARY KEY (`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	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;
ERROR HY000: Column count of mysql.event is wrong. Expected 16, found 14. Table probably corrupted
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	HOUR	#	#	ENABLED
DROP EVENT intact_check;
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
events_test	e_26	set @a = 5	root@localhost	2017-01-01 00:00:00	DROP
drop event e_26;
create event e_26 on schedule at NULL disable do set @a = 5;
ERROR HY000: Incorrect AT value: 'NULL'
create event e_26 on schedule at 'definitely not a datetime' disable 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=off;
ERROR HY000: Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL
set global event_scheduler=3;
ERROR 42000: Variable 'event_scheduler' can't be set to the value of '3'
set global event_scheduler=disabled;
ERROR 42000: Variable 'event_scheduler' can't be set to the value of 'disabled'
"DISABLE the scheduler. Testing that it does not work when the variable is 0"
set global event_scheduler=off;
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"
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
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=on;
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 2 processes: the scheduler and the locked event"
select /*2*/ 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	Daemon	Waiting for next activation	NULL
root	localhost	events_test	Connect	User lock	select get_lock("test_lock2", 20)
"Release the mutex, the event worker should finish."
"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 have only 3 processes: the scheduler, our conn and the locked event"
select /*3*/ 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	Daemon	Waiting for next activation	NULL
root	localhost	events_test	Connect	User lock	select get_lock("test_lock2_1", 20)
set global event_scheduler=off;
"Should have only our process now:"
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
root	localhost	events_test	Connect	User lock	select get_lock("test_lock2_1", 20)
drop event закачка21;
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: Recursivity of EVENT DDL statements is forbidden when body is present
drop table t_16;
create event white_space
on schedule every 10 hour
disable
do
select 1;
select event_schema, event_name, definer, event_definition from information_schema.events where event_name='white_space';
event_schema	event_name	definer	event_definition
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_definition from information_schema.events where event_name='white_space';
event_schema	event_name	definer	event_definition
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_definition from information_schema.events where event_name='white_space';
event_schema	event_name	definer	event_definition
events_test	white_space	root@localhost	select 3
drop event white_space;
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;
drop database events_test;