1drop database if exists events_test; 2drop database if exists mysqltest_db1; 3drop database if exists mysqltest_db2; 4create database events_test; 5use events_test; 6set @concurrent_insert= @@global.concurrent_insert; 7set @@global.concurrent_insert = 0; 8select * from information_schema.global_variables where variable_name like 'event_scheduler'; 9VARIABLE_NAME VARIABLE_VALUE 10EVENT_SCHEDULER ON 11SET GLOBAL event_scheduler = 'OFF'; 12CREATE EVENT lower_case ON SCHEDULE EVERY 1 MINUTE DO SELECT 1; 13CREATE EVENT Lower_case ON SCHEDULE EVERY 2 MINUTE DO SELECT 2; 14ERROR HY000: Event 'Lower_case' already exists 15DROP EVENT Lower_case; 16SET NAMES cp1251; 17CREATE EVENT �����_��������_1251 ON SCHEDULE EVERY 1 YEAR DO SELECT 100; 18CREATE EVENT �����_��������_1251 ON SCHEDULE EVERY 2 YEAR DO SELECT 200; 19ERROR HY000: Event '�����_��������_1251' already exists 20DROP EVENT �����_��������_1251; 21SET NAMES utf8; 22CREATE EVENT долен_регистър_утф8 ON SCHEDULE EVERY 3 YEAR DO SELECT 300; 23CREATE EVENT ДОЛЕН_регистър_утф8 ON SCHEDULE EVERY 4 YEAR DO SELECT 400; 24ERROR HY000: Event 'ДОЛЕН_регистър_утф8' already exists 25DROP EVENT ДОЛЕН_регистър_утф8; 26SET NAMES latin1; 27set @a=3; 28CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5; 29ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present 30create event e_55 on schedule at 99990101000000 do drop table t; 31ERROR HY000: Incorrect AT value: '99990101000000' 32create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t; 33ERROR HY000: Incorrect STARTS value: '99990101000000' 34create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t; 35ERROR HY000: ENDS is either invalid or before STARTS 36create event e_55 on schedule at 10000101000000 do drop table t; 37ERROR HY000: Incorrect AT value: '10000101000000' 38create event e_55 on schedule at 20000101000000 do drop table t; 39Warnings: 40Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. 41show events; 42Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 43create event e_55 on schedule at 20380101000000 starts 10000101000000 do drop table t; 44ERROR 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 45create event e_55 on schedule at 20380101000000 ends 10000101000000 do drop table t; 46ERROR 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 47create event e_55 on schedule at 20380101000000 starts 10000101000000 ends 10000101000000 do drop table t; 48ERROR 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 49create event e_55 on schedule every 10 hour starts 10000101000000 do drop table t; 50ERROR HY000: Incorrect STARTS value: '10000101000000' 51set global event_scheduler=off; 52delete from mysql.event; 53set global event_scheduler= on; 54set @old_sql_mode:=@@sql_mode; 55set sql_mode=ansi; 56select get_lock('test_bug16407', 60); 57get_lock('test_bug16407', 60) 581 59create event e_16407 on schedule every 60 second do 60begin 61select get_lock('test_bug16407', 60); 62end| 63"Now if everything is fine the event has compiled and is locked" 64select /*1*/ user, host, db, info from information_schema.processlist 65where state = 'User lock' and info = 'select get_lock(\'test_bug16407\', 60)'; 66user host db info 67root localhost events_test select get_lock('test_bug16407', 60) 68select release_lock('test_bug16407'); 69release_lock('test_bug16407') 701 71set global event_scheduler= off; 72select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; 73event_schema event_name sql_mode 74events_test e_16407 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI 75"Let's check whether we change the sql_mode on ALTER EVENT" 76set sql_mode='traditional'; 77alter event e_16407 do select 1; 78select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; 79event_schema event_name sql_mode 80events_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,NO_ENGINE_SUBSTITUTION 81drop event e_16407; 82set sql_mode="ansi"; 83select get_lock('ee_16407_2', 60); 84get_lock('ee_16407_2', 60) 851 86set global event_scheduler= 1; 87"Another sql_mode test" 88set sql_mode="traditional"; 89create table events_smode_test(ev_name char(10), a date); 90"This should never insert something" 91create event ee_16407_2 on schedule every 60 second do 92begin 93select get_lock('ee_16407_2', 60) /*ee_16407_2*/; 94select release_lock('ee_16407_2'); 95insert into events_test.events_smode_test values('ee_16407_2','1980-19-02'); 96end| 97insert into events_test.events_smode_test values ('test','1980-19-02')| 98ERROR 22007: Incorrect date value: '1980-19-02' for column 'a' at row 1 99"This is ok" 100create event ee_16407_3 on schedule every 60 second do 101begin 102select get_lock('ee_16407_2', 60) /*ee_16407_3*/; 103select release_lock('ee_16407_2'); 104insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19'); 105insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29'); 106end| 107set sql_mode=""| 108"This will insert rows but they will be truncated" 109create event ee_16407_4 on schedule every 60 second do 110begin 111select get_lock('ee_16407_2', 60) /*ee_16407_4*/; 112select release_lock('ee_16407_2'); 113insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956'); 114end| 115select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; 116event_schema event_name sql_mode 117events_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,NO_ENGINE_SUBSTITUTION 118events_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,NO_ENGINE_SUBSTITUTION 119events_test ee_16407_4 120select /*2*/ user, host, db, info from information_schema.processlist 121where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; 122user host db info 123root localhost events_test select get_lock('ee_16407_2', 60) 124root localhost events_test select get_lock('ee_16407_2', 60) 125root localhost events_test select get_lock('ee_16407_2', 60) 126select release_lock('ee_16407_2'); 127release_lock('ee_16407_2') 1281 129select /*3*/ user, host, db, info from information_schema.processlist 130where state = 'User lock' and info = 'select get_lock(\'ee_16407_2\', 60)'; 131user host db info 132set global event_scheduler= off; 133select * from events_test.events_smode_test order by ev_name, a; 134ev_name a 135ee_16407_3 1980-02-19 136ee_16407_3 1980-02-29 137ee_16407_4 0000-00-00 138"OK, last check before we drop them" 139select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; 140event_schema event_name sql_mode 141events_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,NO_ENGINE_SUBSTITUTION 142events_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,NO_ENGINE_SUBSTITUTION 143events_test ee_16407_4 144drop event ee_16407_2; 145drop event ee_16407_3; 146drop event ee_16407_4; 147"And now one last test regarding sql_mode and call of SP from an event" 148delete from events_test.events_smode_test; 149set sql_mode='ansi'; 150select get_lock('ee_16407_5', 60); 151get_lock('ee_16407_5', 60) 1521 153set global event_scheduler= on; 154set sql_mode='traditional'; 155create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end| 156create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end| 157create event ee_16407_5 on schedule every 60 second do 158begin 159select get_lock('ee_16407_5', 60) /*ee_16407_5*/; 160select release_lock('ee_16407_5'); 161call events_test.ee_16407_5_pendant(); 162end| 163create event ee_16407_6 on schedule every 60 second do 164begin 165select get_lock('ee_16407_5', 60) /*ee_16407_6*/; 166select release_lock('ee_16407_5'); 167call events_test.ee_16407_6_pendant(); 168end| 169"Should have 2 locked processes" 170select /*4*/ user, host, db, info from information_schema.processlist 171where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; 172user host db info 173root localhost events_test select get_lock('ee_16407_5', 60) 174root localhost events_test select get_lock('ee_16407_5', 60) 175select release_lock('ee_16407_5'); 176release_lock('ee_16407_5') 1771 178"Should have 0 processes locked" 179select /*5*/ user, host, db, info from information_schema.processlist 180where state = 'User lock' and info = 'select get_lock(\'ee_16407_5\', 60)'; 181user host db info 182select * from events_test.events_smode_test order by ev_name, a; 183ev_name a 184ee_16407_6 2004-02-29 185"And here we check one more time before we drop the events" 186select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; 187event_schema event_name sql_mode 188events_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,NO_ENGINE_SUBSTITUTION 189events_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,NO_ENGINE_SUBSTITUTION 190drop event ee_16407_5; 191drop event ee_16407_6; 192drop procedure ee_16407_5_pendant; 193drop procedure ee_16407_6_pendant; 194set global event_scheduler= off; 195drop table events_smode_test; 196set sql_mode=@old_sql_mode; 197set global event_scheduler=off; 198delete from mysql.user where User like 'mysqltest_%'; 199delete from mysql.db where User like 'mysqltest_%'; 200flush privileges; 201drop database if exists mysqltest_db1; 202create user mysqltest_user1@localhost; 203create database mysqltest_db1; 204grant event on events_test.* to mysqltest_user1@localhost; 205create event mysqltest_user1 on schedule every 10 second do select 42; 206alter event mysqltest_user1 rename to mysqltest_db1.mysqltest_user1; 207ERROR 42000: Access denied for user 'mysqltest_user1'@'localhost' to database 'mysqltest_db1' 208"Let's test now rename when there is no select DB" 209select database(); 210database() 211NULL 212alter event events_test.mysqltest_user1 rename to mysqltest_user1; 213ERROR 3D000: No database selected 214select event_schema, event_name, definer, event_type, status from information_schema.events; 215event_schema event_name definer event_type status 216events_test mysqltest_user1 mysqltest_user1@localhost RECURRING ENABLED 217drop event events_test.mysqltest_user1; 218drop user mysqltest_user1@localhost; 219drop database mysqltest_db1; 220create event e_53 on schedule at (select s1 from ttx) do drop table t; 221ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' 222create event e_53 on schedule every (select s1 from ttx) second do drop table t; 223ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' 224create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t; 225ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' 226create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t; 227ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' 228drop event if exists e_16; 229drop procedure if exists p_16; 230create event e_16 on schedule every 1 second do set @a=5; 231create procedure p_16 () alter event e_16 on schedule every @a second; 232set @a = null; 233call p_16(); 234ERROR HY000: Incorrect INTERVAL value: 'NULL' 235call p_16(); 236ERROR HY000: Incorrect INTERVAL value: 'NULL' 237set @a= 6; 238call p_16(); 239drop procedure p_16; 240drop event e_16; 241drop function if exists f22830; 242drop event if exists e22830; 243drop event if exists e22830_1; 244drop event if exists e22830_2; 245drop event if exists e22830_3; 246drop event if exists e22830_4; 247drop table if exists t1; 248drop table if exists t2; 249create table t1 (a int); 250insert into t1 values (2); 251create table t2 (a char(20)); 252insert into t2 values ("e22830_1"); 253create function f22830 () returns int return 5; 254select get_lock('ee_22830', 60); 255get_lock('ee_22830', 60) 2561 257set global event_scheduler=on; 258create procedure p22830_wait() 259begin 260select get_lock('ee_22830', 60); 261select release_lock('ee_22830'); 262end| 263create event e22830 on schedule every f22830() second do 264begin 265call p22830_wait(); 266select 123; 267end| 268ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' 269create event e22830_1 on schedule every 1 hour do 270begin 271call p22830_wait(); 272alter event e22830_1 on schedule every (select 8 from dual) hour; 273end| 274create event e22830_2 on schedule every 1 hour do 275begin 276call p22830_wait(); 277alter event e22830_2 on schedule every (select 8 from t1) hour; 278end| 279create event e22830_3 on schedule every 1 hour do 280begin 281call p22830_wait(); 282alter event e22830_3 on schedule every f22830() hour; 283end| 284create event e22830_4 on schedule every 1 hour do 285begin 286call p22830_wait(); 287alter event e22830_4 on schedule every (select f22830() from dual) hour; 288end| 289"All events should be blocked in get_lock()" 290select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; 291event_name event_definition interval_value interval_field 292e22830_1 begin 293call p22830_wait(); 294alter event e22830_1 on schedule every (select 8 from dual) hour; 295end 1 HOUR 296e22830_2 begin 297call p22830_wait(); 298alter event e22830_2 on schedule every (select 8 from t1) hour; 299end 1 HOUR 300e22830_3 begin 301call p22830_wait(); 302alter event e22830_3 on schedule every f22830() hour; 303end 1 HOUR 304e22830_4 begin 305call p22830_wait(); 306alter event e22830_4 on schedule every (select f22830() from dual) hour; 307end 1 HOUR 308select release_lock('ee_22830'); 309release_lock('ee_22830') 3101 311set global event_scheduler=off; 312select event_name, event_definition, interval_value, interval_field from information_schema.events order by event_name; 313event_name event_definition interval_value interval_field 314e22830_1 begin 315call p22830_wait(); 316alter event e22830_1 on schedule every (select 8 from dual) hour; 317end 8 HOUR 318e22830_2 begin 319call p22830_wait(); 320alter event e22830_2 on schedule every (select 8 from t1) hour; 321end 1 HOUR 322e22830_3 begin 323call p22830_wait(); 324alter event e22830_3 on schedule every f22830() hour; 325end 1 HOUR 326e22830_4 begin 327call p22830_wait(); 328alter event e22830_4 on schedule every (select f22830() from dual) hour; 329end 1 HOUR 330drop procedure p22830_wait; 331drop function f22830; 332drop event (select a from t2); 333ERROR 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 334drop event e22830_1; 335drop event e22830_2; 336drop event e22830_3; 337drop event e22830_4; 338drop table t1; 339drop table t2; 340DROP USER mysqltest_u1@localhost; 341CREATE USER mysqltest_u1@localhost; 342GRANT EVENT ON events_test.* TO mysqltest_u1@localhost; 343CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; 344SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; 345event_name definer 346e1 root@localhost 347DROP EVENT e1; 348CREATE DEFINER=CURRENT_USER EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; 349SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; 350event_name definer 351e1 root@localhost 352ALTER DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR; 353SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; 354event_name definer 355e1 mysqltest_u1@localhost 356DROP EVENT e1; 357CREATE DEFINER=CURRENT_USER() EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; 358SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; 359event_name definer 360e1 root@localhost 361DROP EVENT e1; 362CREATE DEFINER=mysqltest_u1@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO 363SELECT 1; 364SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; 365event_name definer 366e1 mysqltest_u1@localhost 367DROP EVENT e1; 368CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; 369SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; 370event_name definer 371e1 mysqltest_u1@localhost 372DROP EVENT e1; 373CREATE DEFINER=CURRENT_USER EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; 374SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; 375event_name definer 376e1 mysqltest_u1@localhost 377ALTER DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 HOUR; 378ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 379SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; 380event_name definer 381e1 mysqltest_u1@localhost 382DROP EVENT e1; 383CREATE DEFINER=CURRENT_USER() EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; 384SELECT event_name, definer FROM INFORMATION_SCHEMA.EVENTS; 385event_name definer 386e1 mysqltest_u1@localhost 387DROP EVENT e1; 388CREATE DEFINER=root@localhost EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; 389ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 390DROP EVENT e1; 391ERROR HY000: Unknown event 'e1' 392DROP USER mysqltest_u1@localhost; 393SET GLOBAL EVENT_SCHEDULER= OFF; 394SET @save_time_zone= @@TIME_ZONE; 395SET TIME_ZONE= '+00:00'; 396SET TIMESTAMP= UNIX_TIMESTAMP('2005-12-31 23:58:59'); 397CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; 398SHOW EVENTS; 399Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 400events_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 401SET TIME_ZONE= '-01:00'; 402ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 00:00:00'; 403SHOW EVENTS; 404Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 405events_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 406SET TIME_ZONE= '+02:00'; 407ALTER EVENT e1 ON SCHEDULE AT '2000-01-02 00:00:00' 408 ON COMPLETION PRESERVE DISABLE; 409SHOW EVENTS; 410Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 411events_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 412SET TIME_ZONE= '-03:00'; 413ALTER EVENT e1 ON SCHEDULE EVERY 1 DAY ENDS '2038-01-03 00:00:00' 414 ON COMPLETION PRESERVE DISABLE; 415SHOW EVENTS; 416Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 417events_test e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2038-01-03 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 418SET TIME_ZONE= '+04:00'; 419ALTER EVENT e1 DO SELECT 2; 420SHOW EVENTS; 421Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 422events_test e1 root@localhost -03:00 RECURRING NULL 1 DAY 2005-12-31 20:58:59 2038-01-03 00:00:00 DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 423DROP EVENT e1; 424SET TIME_ZONE='+05:00'; 425CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO 426SELECT 1; 427SET TIMESTAMP= @@TIMESTAMP + 1; 428SET TIME_ZONE='-05:00'; 429CREATE EVENT e2 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO 430SELECT 1; 431SET TIMESTAMP= @@TIMESTAMP + 1; 432SET TIME_ZONE='+00:00'; 433CREATE EVENT e3 ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00' DO 434SELECT 1; 435SELECT * FROM INFORMATION_SCHEMA.EVENTS ORDER BY event_name; 436EVENT_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 437def events_test e1 root@localhost +05:00 SQL SELECT 1 RECURRING NULL 1 DAY NO_ENGINE_SUBSTITUTION 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 438def events_test e2 root@localhost -05:00 SQL SELECT 1 RECURRING NULL 1 DAY NO_ENGINE_SUBSTITUTION 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 439def events_test e3 root@localhost +00:00 SQL SELECT 1 RECURRING NULL 1 DAY NO_ENGINE_SUBSTITUTION 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 440SHOW EVENTS; 441Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 442events_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 443events_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 444events_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 445SHOW CREATE EVENT e1; 446Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation 447e1 NO_ENGINE_SUBSTITUTION +05:00 CREATE DEFINER=`root`@`localhost` 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 448SHOW CREATE EVENT e2; 449Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation 450e2 NO_ENGINE_SUBSTITUTION -05:00 CREATE DEFINER=`root`@`localhost` 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 451SHOW CREATE EVENT e3; 452Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation 453e3 NO_ENGINE_SUBSTITUTION +00:00 CREATE DEFINER=`root`@`localhost` 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 454The following should fail, and nothing should be altered. 455ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' 456 ENDS '1999-01-02 00:00:00'; 457ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future. 458ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' 459 ENDS '1999-01-02 00:00:00' DISABLE; 460ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future. 461The following should give warnings, and nothing should be created. 462CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' 463 ENDS '1999-01-02 00:00:00' 464DO 465SELECT 1; 466Warnings: 467Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. 468CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' 469 ENDS '1999-01-02 00:00:00' DISABLE 470DO 471SELECT 1; 472Warnings: 473Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. 474CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO 475SELECT 1; 476Warnings: 477Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. 478CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE 479DO 480SELECT 1; 481Warnings: 482Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. 483SHOW EVENTS; 484Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 485events_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 486events_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 487events_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 488The following should succeed giving a warning. 489ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' 490 ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE; 491Warnings: 492Note 1544 Event execution time is in the past. Event has been disabled 493CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' 494 ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE 495DO 496SELECT 1; 497Warnings: 498Note 1544 Event execution time is in the past. Event has been disabled 499CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00' 500 ON COMPLETION PRESERVE 501DO 502SELECT 1; 503Warnings: 504Note 1544 Event execution time is in the past. Event has been disabled 505The following should succeed without warnings. 506ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'; 507ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' 508 ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE; 509CREATE EVENT e6 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO 510SELECT 1; 511CREATE EVENT e7 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' 512 ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DISABLE 513DO 514SELECT 1; 515CREATE EVENT e8 ON SCHEDULE AT '1999-01-01 00:00:00' 516 ON COMPLETION PRESERVE DISABLE 517DO 518SELECT 1; 519SHOW EVENTS; 520Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 521events_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 522events_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 523events_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 524events_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 525events_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 526events_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 527events_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 528events_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 529DROP EVENT e8; 530DROP EVENT e7; 531DROP EVENT e6; 532DROP EVENT e5; 533DROP EVENT e4; 534DROP EVENT e3; 535DROP EVENT e2; 536DROP EVENT e1; 537SET TIME_ZONE=@save_time_zone; 538SET TIMESTAMP=DEFAULT; 539drop event if exists new_event; 540CREATE EVENT new_event ON SCHEDULE EVERY 0 SECOND DO SELECT 1; 541ERROR HY000: INTERVAL is either not positive or too big 542CREATE EVENT new_event ON SCHEDULE EVERY (SELECT 0) SECOND DO SELECT 1; 543ERROR HY000: INTERVAL is either not positive or too big 544CREATE EVENT new_event ON SCHEDULE EVERY "abcdef" SECOND DO SELECT 1; 545ERROR HY000: INTERVAL is either not positive or too big 546CREATE EVENT new_event ON SCHEDULE EVERY "0abcdef" SECOND DO SELECT 1; 547ERROR HY000: INTERVAL is either not positive or too big 548CREATE EVENT new_event ON SCHEDULE EVERY "a1bcdef" SECOND DO SELECT 1; 549ERROR HY000: INTERVAL is either not positive or too big 550CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "abcdef" UNION SELECT "abcdef") SECOND DO SELECT 1; 551ERROR HY000: INTERVAL is either not positive or too big 552CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "0abcdef") SECOND DO SELECT 1; 553ERROR HY000: INTERVAL is either not positive or too big 554CREATE EVENT new_event ON SCHEDULE EVERY (SELECT "a1bcdef") SECOND DO SELECT 1; 555ERROR HY000: INTERVAL is either not positive or too big 556CREATE EVENT new_event ON SCHEDULE AT "every day" DO SELECT 1; 557ERROR HY000: Incorrect AT value: 'every day' 558CREATE EVENT new_event ON SCHEDULE AT "0every day" DO SELECT 1; 559ERROR HY000: Incorrect AT value: '0every day' 560CREATE EVENT new_event ON SCHEDULE AT (SELECT "every day") DO SELECT 1; 561ERROR HY000: Incorrect AT value: 'every day' 562CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() DO SELECT 1; 563ERROR 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 564CREATE EVENT new_event ON SCHEDULE AT NOW() ENDS NOW() DO SELECT 1; 565ERROR 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 566CREATE EVENT new_event ON SCHEDULE AT NOW() STARTS NOW() ENDS NOW() DO SELECT 1; 567ERROR 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 568USE test; 569SHOW GRANTS FOR CURRENT_USER; 570Grants for root@localhost 571GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION 572GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION 573SET GLOBAL event_scheduler = ON; 574CREATE TABLE events_test.event_log 575(id int KEY AUTO_INCREMENT, ev_nm char(40), ev_cnt int, ev_tm timestamp) 576ENGINE=MyISAM; 577SET autocommit=0; 578CREATE USER evtest1@localhost; 579SET PASSWORD FOR evtest1@localhost = password('ev1'); 580REVOKE ALL PRIVILEGES, GRANT OPTION FROM evtest1@localhost; 581GRANT create, insert, select, event ON events_test.* TO evtest1@localhost; 582GRANT select,insert ON test.* TO evtest1@localhost; 583SHOW GRANTS FOR evtest1@localhost; 584Grants for evtest1@localhost 585GRANT USAGE ON *.* TO 'evtest1'@'localhost' IDENTIFIED BY PASSWORD '*3170F3644E31580C25DE4A08F4C07CC9A2D40C32' 586GRANT SELECT, INSERT ON `test`.* TO 'evtest1'@'localhost' 587GRANT SELECT, INSERT, CREATE, EVENT ON `events_test`.* TO 'evtest1'@'localhost' 588connection e1; 589CREATE EVENT ev_sched_1823 ON SCHEDULE EVERY 2 SECOND 590DO BEGIN 591SET AUTOCOMMIT = 0; 592SET @evname = 'ev_sched_1823'; 593SET @cnt = 0; 594SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname; 595IF @cnt < 6 THEN 596INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); 597COMMIT; 598END IF; 599SELECT COUNT(*) INTO @cnt FROM events_test.event_log WHERE ev_nm = @evname; 600IF @cnt < 6 THEN 601INSERT INTO events_test.event_log VALUES (NULL,@evname,@cnt+1,current_timestamp()); 602ROLLBACK; 603END IF; 604END;| 605Sleep till the first INSERT into events_test.event_log occured 606SELECT COUNT(*) > 0 AS "Expect 1" FROM events_test.event_log; 607Expect 1 6081 609connection default; 610DROP USER evtest1@localhost; 611Sleep 4 seconds 612SELECT COUNT(*) INTO @row_cnt FROM events_test.event_log; 613Sleep 4 seconds 614SELECT COUNT(*) > @row_cnt AS "Expect 0" FROM events_test.event_log; 615Expect 0 6160 617DROP EVENT events_test.ev_sched_1823; 618DROP TABLE events_test.event_log; 619SET GLOBAL event_scheduler = OFF; 620SET GLOBAL event_scheduler= ON; 621CREATE EVENT bug28641 ON SCHEDULE AT '2038.01.18 03:00:00' 622DO BEGIN 623SELECT 1; 624END;| 625SET GLOBAL event_scheduler= OFF; 626DROP EVENT bug28641; 627 628##################################################################### 629# 630# BUG#31111: --read-only crashes MySQL (events fail to load). 631# 632##################################################################### 633 634DROP USER mysqltest_u1@localhost; 635DROP EVENT IF EXISTS e1; 636DROP EVENT IF EXISTS e2; 637 638GRANT EVENT ON *.* TO mysqltest_u1@localhost; 639 640SET GLOBAL READ_ONLY = 1; 641 642# 643# Connection: u1_con (mysqltest_u1@localhost/events_test). 644# 645 646CREATE EVENT e1 ON SCHEDULE AT '2038-01-01 00:00:00' DO SET @a = 1; 647ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement 648 649ALTER EVENT e1 COMMENT 'comment'; 650ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement 651 652DROP EVENT e1; 653ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement 654 655# 656# Connection: root_con (root@localhost/events_test). 657# 658 659CREATE EVENT e1 ON SCHEDULE AT '2038-01-01 00:00:00' DO SET @a = 1; 660 661ALTER EVENT e1 COMMENT 'comment'; 662 663DROP EVENT e1; 664 665SET GLOBAL READ_ONLY = 0; 666 667# 668# Connection: u1_con (mysqltest_u1@localhost/test). 669# 670 671CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND DO SET @a = 1; 672CREATE EVENT e2 ON SCHEDULE EVERY 1 SECOND DO SET @a = 1; 673 674SELECT 675event_name, 676last_executed IS NULL, 677definer 678FROM INFORMATION_SCHEMA.EVENTS 679WHERE event_schema = 'events_test'; 680event_name last_executed IS NULL definer 681e1 1 mysqltest_u1@localhost 682e2 1 mysqltest_u1@localhost 683 684# 685# Connection: root_con (root@localhost/events_test). 686# 687 688SET GLOBAL READ_ONLY = 1; 689 690SET GLOBAL EVENT_SCHEDULER = ON; 691 692# Waiting for the event scheduler to execute and drop event e1... 693 694# Waiting for the event scheduler to execute and update event e2... 695 696SET GLOBAL EVENT_SCHEDULER = OFF; 697 698SELECT 699event_name, 700last_executed IS NULL, 701definer 702FROM INFORMATION_SCHEMA.EVENTS 703WHERE event_schema = 'events_test'; 704event_name last_executed IS NULL definer 705e2 0 mysqltest_u1@localhost 706 707DROP EVENT e1; 708ERROR HY000: Unknown event 'e1' 709 710# Cleanup. 711 712DROP EVENT e2; 713 714SET GLOBAL READ_ONLY = 0; 715 716# 717# Connection: default 718# 719 720DROP USER mysqltest_u1@localhost; 721 722##################################################################### 723# 724# End of BUG#31111. 725# 726##################################################################### 727 728drop procedure if exists p; 729set @old_mode= @@sql_mode; 730set @@sql_mode= cast(pow(2,32)-1 as unsigned integer); 731Warnings: 732Warning 1681 'ERROR_FOR_DIVISION_BY_ZERO' is deprecated and will be removed in a future release. 733Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release. 734Warning 1681 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release. 735create event e1 on schedule every 1 day do select 1; 736select @@sql_mode into @full_mode; 737set @@sql_mode= @old_mode; 738select replace(@full_mode, ',,,', ',NOT_USED,') into @full_mode; 739select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode; 740select name from mysql.event where name = 'e1' and sql_mode = @full_mode; 741name 742e1 743drop event e1; 744SET @old_server_id = @@GLOBAL.server_id; 745SET GLOBAL server_id = (1 << 32) - 1; 746SELECT @@GLOBAL.server_id; 747@@GLOBAL.server_id 7484294967295 749CREATE EVENT ev1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; 750SELECT event_name, originator FROM INFORMATION_SCHEMA.EVENTS; 751event_name originator 752ev1 4294967295 753DROP EVENT ev1; 754SET GLOBAL server_id = @old_server_id; 755CREATE DATABASE event_test12; 756USE event_test12; 757CREATE EVENT ev1 ON SCHEDULE EVERY 1 DAY DO SELECT 1; 758CREATE DATABASE event_test1; 759USE event_test1; 760SHOW EVENTS; 761Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 762DROP DATABASE event_test1; 763DROP DATABASE event_test12; 764# 765# Bug#12546938 (formerly known as bug#61005): 766# CREATE IF NOT EXIST EVENT WILL CREATE MULTIPLE RUNNING EVENTS 767# 768USE events_test; 769SET GLOBAL event_scheduler = ON; 770DROP TABLE IF EXISTS table_bug12546938; 771DROP EVENT IF EXISTS event_Bug12546938; 772CREATE TABLE table_bug12546938 (i INT); 773# Create an event which will be executed with a small delay 774# and won't be automatically dropped after that. 775CREATE EVENT event_Bug12546938 776ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE 777ENABLE DO 778BEGIN 779INSERT INTO table_bug12546938 VALUES(1); 780END 781| 782# Now try to create the same event using CREATE EVENT IF NOT EXISTS. 783# A warning should be emitted. A new event should not be created nor 784# the old event should be re-executed. 785CREATE EVENT IF NOT EXISTS event_bug12546938 786ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE 787ENABLE DO 788BEGIN 789INSERT INTO table_bug12546938 VALUES (1); 790END 791| 792Warnings: 793Note 1537 Event 'event_bug12546938' already exists 794# Wait until at least one instance of event is executed. 795# Check that only one instance of our event was executed. 796SELECT COUNT(*) FROM table_bug12546938; 797COUNT(*) 7981 799# Clean-up. 800DROP EVENT IF EXISTS event_Bug12546938; 801DROP TABLE table_bug12546938; 802SET GLOBAL EVENT_SCHEDULER = OFF; 803DROP DATABASE IF EXISTS event_test11764334; 804CREATE DATABASE event_test11764334; 805USE event_test11764334; 806CREATE EVENT ev1 ON SCHEDULE EVERY 3 SECOND DISABLE DO SELECT 1; 807SHOW EVENTS IN event_test11764334 WHERE NAME='ev1'; 808Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 809event_test11764334 ev1 root@localhost SYSTEM RECURRING NULL 3 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 810ALTER EVENT ev1 ON SCHEDULE EVERY 4 SECOND; 811SHOW EVENTS IN event_test11764334 WHERE NAME='ev1'; 812Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation 813event_test11764334 ev1 root@localhost SYSTEM RECURRING NULL 4 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci 814DROP EVENT ev1; 815DROP DATABASE event_test11764334; 816USE test; 817DROP DATABASE events_test; 818SET GLOBAL event_scheduler= 'ON'; 819SET @@global.concurrent_insert= @concurrent_insert; 820