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