diff options
author | unknown <andrey@lmy004.> | 2006-05-22 20:46:13 +0200 |
---|---|---|
committer | unknown <andrey@lmy004.> | 2006-05-22 20:46:13 +0200 |
commit | f4781a7e4cf2825d42e9df481a42d47a48b1dfb3 (patch) | |
tree | 3d492384ce369edfba63a049dc688265d9d27a21 /mysql-test/r | |
parent | 61bd3fa055e6d3e39ac20ed3c7568cccf955cf6e (diff) | |
download | mariadb-git-f4781a7e4cf2825d42e9df481a42d47a48b1dfb3.tar.gz |
fix for bug #17619 Scheduler race conditions
- Scheduler is either initialized at server start or never.
Starting & stopping is now suspending & resuming.
- The scheduler has clear OO interface
- Now all calls to the scheduler are synchronous
- GLOBAL event_scheduler uses thd::sys_var_tmp (see set_var.cc)
- External API is encapsulated into class Events
- Includes fixes for all comments of Kostja's review of 19.05.2005
Starting to merge into 5.1-release (5.1.10) and push
BitKeeper/etc/ignore:
Added libmysqld/event_scheduler.cc to the ignore list
libmysqld/Makefile.am:
executor -> scheduler
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_microsec.result:
update result
mysql-test/r/events_scheduling.result:
update result
mysql-test/r/events_stress.result:
update result
mysql-test/t/disabled.def:
enable these tests
mysql-test/t/events.test:
optimize the test a bit for speed, save some seconds runtime
remove FULL from SHOW EVENTS
mostly use I_S.EVENTS
mysql-test/t/events_bugs.test:
Skip irrelevant for the current design tests - all events are loaded
on server startup. Change in mysql.event will be visible on next server start.
Don't use numeric error codes.
mysql-test/t/events_logs_tests.test:
optimize the test a bit for speed
mysql-test/t/events_microsec.test:
Skip irrelevant for the current design tests - all events are loaded
on server startup. Change in mysql.event will be visible on next server start.
Don't use numeric error codes.
mysql-test/t/events_scheduling.test:
broader test
mysql-test/t/events_stress.test:
Rework the test to the new architecture of suspending/resuming.
Use less events, no need for thousands, hundreds is still ok.
sql/Makefile.am:
executor -> scheduler
sql/cmakelists.txt:
executor -> scheduler
sql/event.cc:
- remove todo comments
- remove unneded evex_queue abstraction functions
- move events_init() and events_shutdown() from event_executor.cc to here
- export db_create_event
- remove evex_load_and_compile_event, part of class Event_scheduler
- integrate the public interface found in event.h and used by sql_parse.cc
to use the new class Event_scheduler.
sql/event.h:
- add COND_finished so if one thread kills a running event it waits on this
- export callback event_timed_definer_equal, event_timed_identifier_equal(),
event_timed_name_equal and event_timed_db_equal()
to be used by Event_scheduler::drop_matching_events()
- cleanup event.h
- encapsulated all external interface into class Events
sql/event_executor.cc:
make it empty, will delete after that
sql/event_priv.h:
- more things in the private header
- remove event queue abstraction functions. tightly bind to QUEUE
- export privately db_drop_event, db_find_event, db_create_event()
- made change_security_context() and restore_security_context() free functions
sql/event_timed.cc:
- fix calculation of time when ENDS is set (STARTS is always set)
- during Event_timed::compile() set the right Security_ctx. Prevents a crash
during Event_scheduler::load_events_from_db()
- add Event_timed::kill_thread()
- implement event_timed_*_equal()
- made change_security_context() and restore_security_context() free functions.
- Comments cleanups
sql/lex.h:
new word scheduler for SHOW SCHEDULER STATUS (available only debug builds)
sql/log.cc:
move these from event_scheduler.cc
sql/mysql_priv.h:
refactor kill_one_thread
export sql_print_message_func and sql_print_message_handlers
sql/mysqld.cc:
In close_connections, called by kill_server() skip the main scheduler
thread and use events_shutdown() for shutting down the scheduler, in the same
manner it's done for RPL.
Add a new value to --event-scheduler :
0 <- No scheduler available
1 <- Start with scheduler enabled
2 <- Start with scheduler suspended
sql/repl_failsafe.cc:
refactor thd::system_thread to be an enum
sql/set_var.cc:
move sys_var_event_executor::update() to set_var.cc
executor -> scheduler
use thd::sys_var_tmp
sql/set_var.h:
executor -> scheduler
sql/share/errmsg.txt:
3 new error messages
sql/sql_class.cc:
refactor thd::system_thread to be an enum . more type-safety
sql/sql_class.h:
refactor thd::system_thread to be an enum . more type-safety
sql/sql_db.cc:
get the error from evex_drop_schema_events
sql/sql_error.h:
export warning_level_names
sql/sql_lex.h:
new command SHOW SCHEDULER STATUS, available only in debug build and
for debug purposes.
sql/sql_parse.cc:
refactor kill_one_thread() -> does the *dirty* work, and sql_kill
just the reporting.
add handler for SQLCOM_SHOW_SCHEDULER_STATUS
sql/sql_show.cc:
fix verbosity handling (this will be obsoleted anyway by the fix for 17394).
sql/sql_yacc.yy:
remove FULL from SHOW EVENTS
add SHOW SCHEDULER STATUS in debug builds
sql/table.cc:
Fix valgrind warning.
Diffstat (limited to 'mysql-test/r')
-rw-r--r-- | mysql-test/r/events.result | 52 | ||||
-rw-r--r-- | mysql-test/r/events_bugs.result | 8 | ||||
-rw-r--r-- | mysql-test/r/events_logs_tests.result | 23 | ||||
-rw-r--r-- | mysql-test/r/events_microsec.result | 46 | ||||
-rw-r--r-- | mysql-test/r/events_scheduling.result | 9 | ||||
-rw-r--r-- | mysql-test/r/events_stress.result | 69 |
6 files changed, 89 insertions, 118 deletions
diff --git a/mysql-test/r/events.result b/mysql-test/r/events.result index 01d206be7cb..d02a2af3c9f 100644 --- a/mysql-test/r/events.result +++ b/mysql-test/r/events.result @@ -17,13 +17,13 @@ db_x SHOW TABLES FROM db_x; Tables_in_db_x x_table -SET GLOBAL event_scheduler=0; +SET GLOBAL event_scheduler=2; DROP EVENT e_x1; DROP EVENT e_x2; DROP DATABASE db_x; DROP USER pauline@localhost; USE events_test; -SET GLOBAL event_scheduler=0; +SET GLOBAL event_scheduler=2; drop event if exists event1; Warnings: Note 1305 Event event1 does not exist @@ -100,7 +100,7 @@ a 800219 drop event non_qualif_ev; drop table non_qualif; -set global event_scheduler = 0; +set global event_scheduler = 2; create table t_event3 (a int, b float); drop event if exists event3; Warnings: @@ -324,18 +324,18 @@ events_test one_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED "This should show us only 3 events:"; -SHOW FULL EVENTS; +SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test one_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED "This should show us only 2 events:"; -SHOW FULL EVENTS LIKE 't%event'; +SHOW EVENTS LIKE 't%event'; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED "This should show us no events:"; -SHOW FULL EVENTS FROM test LIKE '%'; +SHOW EVENTS FROM test LIKE '%'; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status DROP DATABASE events_test2; "should see 1 event:"; @@ -343,11 +343,8 @@ SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test one_event root@localhost RECURRING NULL 10 SECOND # # ENABLED "we should see 4 events now:"; -SHOW FULL EVENTS; +SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -events_test one_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED -events_test three_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED -events_test two_event ev_test@localhost RECURRING NULL 20 SECOND # # ENABLED events_test one_event root@localhost RECURRING NULL 10 SECOND # # ENABLED SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT from information_schema.events; EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT @@ -373,12 +370,12 @@ ERROR HY000: Incorrect AT value: 'definitely not a datetime' set names utf8; create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1; drop event задачка; -set event_scheduler=0; +set event_scheduler=2; ERROR HY000: Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL -set global event_scheduler=2; -ERROR 42000: Variable 'event_scheduler' can't be set to the value of '2' +set global event_scheduler=3; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of '3' "DISABLE the scheduler. Testing that it does not work when the variable is 0" -set global event_scheduler=0; +set global event_scheduler=2; select definer, name, db from mysql.event; definer name db select get_lock("test_lock1", 20); @@ -389,9 +386,10 @@ create event закачка on schedule every 10 hour do select get_lock("test_l select definer, name, db from mysql.event; definer name db root@localhost закачка events_test -"Should be 0 processes" +"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 +event_scheduler localhost NULL Connect Suspended NULL select release_lock("test_lock1"); release_lock("test_lock1") 1 @@ -409,11 +407,12 @@ get_lock("test_lock2", 20) 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 /*1*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info; +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 Connect Sleeping 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 @@ -423,21 +422,17 @@ select get_lock("test_lock2_1", 20); get_lock("test_lock2_1", 20) 1 create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20); -"Should see 1 process, locked on get_lock(" -"Shutting down the scheduler, it should wait for the running event" -set global event_scheduler=0; -"Should have only 2 processes: the scheduler and the locked event" -select /*4*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info; +"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 Connect Sleeping NULL root localhost events_test Connect User lock select get_lock("test_lock2_1", 20) -"Release the lock so the child process should finish. Hence the scheduler also" -select release_lock("test_lock2_1"); -release_lock("test_lock2_1") -1 -"Should see 0 processes now:" -select /*5*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info; +set global event_scheduler=2; +"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 +event_scheduler localhost NULL Connect Suspended NULL +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; @@ -457,6 +452,9 @@ select 2; select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; event_schema event_name definer event_body events_test white_space root@localhost select 2 +select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; +event_schema event_name definer event_body +events_test white_space root@localhost select 2 drop event white_space; create event white_space on schedule every 10 hour disable do select 3; select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index ef1ccfadecb..bc89c692f9a 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -35,7 +35,7 @@ create event e_55 on schedule every 10 hour starts 99990101000000 do drop table ERROR HY000: Incorrect STARTS value: '99990101000000' create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t; ERROR HY000: ENDS is either invalid or before STARTS -set global event_scheduler=0; +set global event_scheduler=2; "Wait a bit to settle down" delete from mysql.event; set global event_scheduler= 1; @@ -57,7 +57,7 @@ root localhost events_test Connect User lock select get_lock('test_bug16407', 60 select release_lock('test_bug16407'); release_lock('test_bug16407') 1 -set global event_scheduler= 0; +set global event_scheduler= 2; select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; event_schema event_name sql_mode events_test e_16407 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI @@ -115,7 +115,7 @@ release_lock('ee_16407_2') 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 Connect Sleeping NULL -set global event_scheduler= 0; +set global event_scheduler= 2; select * from events_smode_test order by ev_name, a; ev_name a ee_16407_3 1980-02-19 @@ -175,7 +175,7 @@ drop event ee_16407_5; drop event ee_16407_6; drop procedure ee_16407_5_pendant; drop procedure ee_16407_6_pendant; -set global event_scheduler= 0; +set global event_scheduler= 2; drop table events_smode_test; set sql_mode=@old_sql_mode; drop database events_test; diff --git a/mysql-test/r/events_logs_tests.result b/mysql-test/r/events_logs_tests.result index ab1666fefb9..911bc8b2d60 100644 --- a/mysql-test/r/events_logs_tests.result +++ b/mysql-test/r/events_logs_tests.result @@ -8,7 +8,7 @@ BEGIN SELECT user_host, argument FROM mysql.general_log WHERE argument LIKE '%alabala%'; END| "Check General Query Log" -SET GLOBAL event_scheduler=0; +SET GLOBAL event_scheduler=2; create event log_general on schedule every 1 minute do SELect 'alabala', sleep(3) from dual; TRUNCATE mysql.general_log; "1 row, the current statement!" @@ -22,7 +22,7 @@ user_host argument root[root] @ localhost [localhost] SELect 'alabala', sleep(3) from dual DROP PROCEDURE select_general_log; DROP EVENT log_general; -SET GLOBAL event_scheduler=0; +SET GLOBAL event_scheduler=2; "Check slow query log" "Save the values" SET @old_global_long_query_time:=(select get_value()); @@ -36,14 +36,14 @@ SELECT user_host, query_time, db, sql_text FROM mysql.slow_log; user_host query_time db sql_text "Set new values" SET GLOBAL long_query_time=4; -SET SESSION long_query_time=2; +SET SESSION long_query_time=1; "Check that logging is working" -SELECT SLEEP(3); -SLEEP(3) +SELECT SLEEP(2); +SLEEP(2) 0 SELECT user_host, query_time, db, sql_text FROM mysql.slow_log; user_host query_time db sql_text -root[root] @ localhost [] SLEEPVAL events_test SELECT SLEEP(3) +root[root] @ localhost [] SLEEPVAL events_test SELECT SLEEP(2) TRUNCATE mysql.slow_log; CREATE TABLE slow_event_test (slo_val tinyint, val tinyint); "This won't go to the slow log" @@ -54,7 +54,7 @@ SET GLOBAL event_scheduler=1; "Sleep some more time than the actual event run will take" SHOW VARIABLES LIKE 'event_scheduler'; Variable_name Value -event_scheduler ON +event_scheduler 1 "Check our table. Should see 1 row" SELECT * FROM slow_event_test; slo_val val @@ -64,18 +64,19 @@ SELECT user_host, query_time, db, sql_text FROM mysql.slow_log; user_host query_time db sql_text "This should go to the slow log" SET SESSION long_query_time=10; +SET GLOBAL long_query_time=1; DROP EVENT long_event; -CREATE EVENT long_event2 ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(5); +CREATE EVENT long_event2 ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(2); "Sleep some more time than the actual event run will take" "Check our table. Should see 2 rows" SELECT * FROM slow_event_test; slo_val val 4 0 -4 0 -"Check slow log. Should see 1 row because 5 is over the threshold of 4 for GLOBAL, though under SESSION which is 10" +1 0 +"Check slow log. Should see 1 row because 4 is over the threshold of 3 for GLOBAL, though under SESSION which is 10" SELECT user_host, query_time, db, sql_text FROM mysql.slow_log; user_host query_time db sql_text -root[root] @ localhost [localhost] SLEEPVAL events_test INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(5) +root[root] @ localhost [localhost] SLEEPVAL events_test INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(2) DROP EVENT long_event2; SET GLOBAL long_query_time =@old_global_long_query_time; SET SESSION long_query_time =@old_session_long_query_time; diff --git a/mysql-test/r/events_microsec.result b/mysql-test/r/events_microsec.result index ed15b066b93..b96bd551511 100644 --- a/mysql-test/r/events_microsec.result +++ b/mysql-test/r/events_microsec.result @@ -10,50 +10,4 @@ CREATE EVENT micro_test ON SCHEDULE EVERY 100 MINUTE_MICROSECOND DO SELECT 1; ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' CREATE EVENT micro_test ON SCHEDULE EVERY 100 SECOND_MICROSECOND DO SELECT 1; ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' -"Now create normal event and change it on SQL level" -CREATE EVENT micro_test2 ON SCHEDULE EVERY 1 MONTH DO SELECT 1; -UPDATE mysql.event SET interval_field='MICROSECOND' WHERE db=database() AND definer=user() AND name='micro_test2'; -SHOW CREATE EVENT micro_test2; -ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' -SET GLOBAL event_scheduler=0; -"Should not be running:" -SHOW VARIABLES like 'event_scheduler'; -Variable_name Value -event_scheduler OFF -UPDATE mysql.event SET interval_field='DAY_MICROSECOND' WHERE db=database() AND definer=user() AND name='micro_test2'; -SHOW CREATE EVENT micro_test2; -ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' -SET GLOBAL event_scheduler=0; -"Should not be running:" -SHOW VARIABLES like 'event_scheduler'; -Variable_name Value -event_scheduler OFF -UPDATE mysql.event SET interval_field='SECOND_MICROSECOND' WHERE db=database() AND definer=user() AND name='micro_test2'; -SHOW CREATE EVENT micro_test2; -ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' -SET GLOBAL event_scheduler=0; -"Should not be running:" -SHOW VARIABLES like 'event_scheduler'; -Variable_name Value -event_scheduler OFF -UPDATE mysql.event SET interval_field='HOUR_MICROSECOND' WHERE db=database() AND definer=user() AND name='micro_test2'; -SHOW CREATE EVENT micro_test2; -ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' -SET GLOBAL event_scheduler=0; -"Should not be running:" -SHOW VARIABLES like 'event_scheduler'; -Variable_name Value -event_scheduler OFF -UPDATE mysql.event SET interval_field='MINUTE_MICROSECOND' WHERE db=database() AND definer=user() AND name='micro_test2'; -SHOW CREATE EVENT micro_test2; -ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' -SET GLOBAL event_scheduler=0; -"Should not be running:" -SHOW VARIABLES like 'event_scheduler'; -Variable_name Value -event_scheduler OFF -SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER='event_scheduler'; -COUNT(*) -0 -DROP EVENT micro_test2; drop database events_test; diff --git a/mysql-test/r/events_scheduling.result b/mysql-test/r/events_scheduling.result index 8b1f29d320f..aec2053f0e7 100644 --- a/mysql-test/r/events_scheduling.result +++ b/mysql-test/r/events_scheduling.result @@ -14,7 +14,7 @@ ENDS NOW() + INTERVAL 6 SECOND ON COMPLETION PRESERVE DO INSERT INTO table_2 VALUES(1); CREATE EVENT only_one_time ON SCHEDULE EVERY 2 SECOND ENDS NOW() + INTERVAL 1 SECOND DO INSERT INTO table_3 VALUES(1); -CREATE EVENT two_time ON SCHEDULE EVERY 1 SECOND ENDS NOW() + INTERVAL 1 SECOND DO INSERT INTO table_4 VALUES(1); +CREATE EVENT two_time ON SCHEDULE EVERY 1 SECOND ENDS NOW() + INTERVAL 1 SECOND ON COMPLETION PRESERVE DO INSERT INTO table_4 VALUES(1); SELECT IF(SUM(a) >= 4, 'OK', 'ERROR') FROM table_1; IF(SUM(a) >= 4, 'OK', 'ERROR') OK @@ -38,9 +38,12 @@ DROP EVENT start_n_end; "Already dropped because ended. Therefore an error." DROP EVENT only_one_time; ERROR HY000: Unknown event 'only_one_time' -"Already dropped because ended. Therefore an error." +"Should be preserved" +SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS; +EVENT_NAME STATUS +E19170 ENABLED +two_time DISABLED DROP EVENT two_time; -ERROR HY000: Unknown event 'two_time' DROP TABLE table_1; DROP TABLE table_2; DROP TABLE table_3; diff --git a/mysql-test/r/events_stress.result b/mysql-test/r/events_stress.result index 9f95cfad75d..a903a4502bf 100644 --- a/mysql-test/r/events_stress.result +++ b/mysql-test/r/events_stress.result @@ -1,46 +1,61 @@ CREATE DATABASE IF NOT EXISTS events_test; -CREATE DATABASE events_test2; -USE events_test2; +CREATE DATABASE events_conn1_test2; +CREATE TABLE events_test.fill_it(test_name varchar(20), occur datetime); +CREATE USER event_user2@localhost; +CREATE DATABASE events_conn2_db; +GRANT ALL ON *.* TO event_user2@localhost; +CREATE USER event_user3@localhost; +CREATE DATABASE events_conn3_db; +GRANT ALL ON *.* TO event_user3@localhost; +"In the second connection we create some events which won't be dropped till the end" +"In the second connection we create some events which won't be dropped till the end" +USE events_conn1_test2; CREATE EVENT ev_drop1 ON SCHEDULE EVERY 10 MINUTE DISABLE DO SELECT 1; CREATE EVENT ev_drop2 ON SCHEDULE EVERY 10 MINUTE DISABLE DO SELECT 1; CREATE EVENT ev_drop3 ON SCHEDULE EVERY 10 MINUTE DISABLE DO SELECT 1; USE events_test; -SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_test2'; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS; +COUNT(*) +203 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; COUNT(*) 3 -DROP DATABASE events_test2; -SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_test2'; +DROP DATABASE events_conn1_test2; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; COUNT(*) 0 "Now testing stability - dropping db -> events while they are running" -CREATE DATABASE events_test2; -USE events_test2; -SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_test2'; +CREATE DATABASE events_conn1_test2; +USE events_conn1_test2; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; COUNT(*) -1000 +100 SET GLOBAL event_scheduler=1; -DROP DATABASE events_test2; -SET GLOBAL event_scheduler=0; -SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_test2'; +DROP DATABASE events_conn1_test2; +SET GLOBAL event_scheduler=2; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; COUNT(*) 0 -CREATE DATABASE events_test3; -USE events_test3; -SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_test3'; +CREATE DATABASE events_conn1_test3; +USE events_conn1_test3; +SET GLOBAL event_scheduler=1; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test3'; COUNT(*) -950 -CREATE DATABASE events_test4; -USE events_test4; -CREATE DATABASE events_test2; -USE events_test2; -SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_test2'; +100 +CREATE DATABASE events_conn1_test4; +USE events_conn1_test4; +CREATE DATABASE events_conn1_test2; +USE events_conn1_test2; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; COUNT(*) -1050 -DROP DATABASE events_test2; -SET GLOBAL event_scheduler=0; -DROP DATABASE events_test3; -SET GLOBAL event_scheduler=1; -DROP DATABASE events_test4; +100 +DROP DATABASE events_conn2_db; +DROP DATABASE events_conn3_db; +DROP DATABASE events_conn1_test2; +DROP DATABASE events_conn1_test3; +SET GLOBAL event_scheduler=2; +DROP DATABASE events_conn1_test4; SET GLOBAL event_scheduler=1; USE events_test; +DROP TABLE fill_it; DROP DATABASE events_test; |