diff options
| -rw-r--r-- | mysql-test/include/system_db_struct.inc | 1 | ||||
| -rw-r--r-- | mysql-test/r/events.result | 105 | ||||
| -rw-r--r-- | mysql-test/r/system_mysql_db.result | 20 | ||||
| -rw-r--r-- | mysql-test/t/events.test | 79 | ||||
| -rw-r--r-- | mysql-test/t/system_mysql_db_fix.test | 2 | ||||
| -rw-r--r-- | scripts/mysql_create_system_tables.sh | 31 | ||||
| -rw-r--r-- | scripts/mysql_fix_privilege_tables.sql | 4 | ||||
| -rw-r--r-- | sql/event_executor.cc | 28 |
8 files changed, 257 insertions, 13 deletions
diff --git a/mysql-test/include/system_db_struct.inc b/mysql-test/include/system_db_struct.inc index c66590b2fd8..9e8886377fc 100644 --- a/mysql-test/include/system_db_struct.inc +++ b/mysql-test/include/system_db_struct.inc @@ -12,3 +12,4 @@ show create table tables_priv; show create table columns_priv; show create table procs_priv; show create table proc; +show create table event; diff --git a/mysql-test/r/events.result b/mysql-test/r/events.result index d8aee068c5e..dcfd80dbcd1 100644 --- a/mysql-test/r/events.result +++ b/mysql-test/r/events.result @@ -1,4 +1,5 @@ -use test; +create database if not exists events_test; +use events_test; drop event if exists event1; Warnings: Note 1305 Event event1 does not exist @@ -19,3 +20,105 @@ count(*) 0 drop event event3; drop table t_event3; +set names utf8; +create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1; +drop event задачка; +set event_scheduler=0; +ERROR HY000: Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL +set global event_scheduler=2; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of '2' +set global event_scheduler=0; +select count(*) from mysql.event; +count(*) +0 +select get_lock("test_lock1", 20); +get_lock("test_lock1", 20) +1 +create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20); +select count(*) from mysql.event; +count(*) +1 +show processlist; +Id User Host db Command Time State Info +1 root localhost events_test Query 0 NULL show processlist +select release_lock("test_lock1"); +release_lock("test_lock1") +1 +drop event закачка; +select count(*) from mysql.event; +count(*) +0 +set global event_scheduler=1; +select get_lock("test_lock2", 20); +get_lock("test_lock2", 20) +1 +create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20); +select sleep(2); +sleep(2) +0 +show processlist; +Id User Host db Command Time State Info +1 root localhost events_test Query 0 NULL show processlist +2 event_scheduler NULL Connect 1 Sleeping NULL +3 root events_test Connect 1 User lock select get_lock("test_lock2", 20) +select release_lock("test_lock2"); +release_lock("test_lock2") +1 +drop event закачка; +set global event_scheduler=1; +select get_lock("test_lock2_1", 20); +get_lock("test_lock2_1", 20) +1 +create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20); +select sleep(2); +sleep(2) +0 +show processlist; +Id User Host db Command Time State Info +1 root localhost events_test Query 0 NULL show processlist +2 event_scheduler NULL Connect 1 Sleeping NULL +4 root events_test Connect 1 User lock select get_lock("test_lock2_1", 20) +set global event_scheduler=0; +select sleep(2); +sleep(2) +0 +show processlist; +Id User Host db Command Time State Info +1 root localhost events_test Query 0 NULL show processlist +2 event_scheduler NULL Connect 3 Sleeping NULL +4 root events_test Connect 3 User lock select get_lock("test_lock2_1", 20) +select release_lock("test_lock2_1"); +release_lock("test_lock2_1") +1 +select sleep(2); +sleep(2) +0 +show processlist; +Id User Host db Command Time State Info +1 root localhost events_test Query 0 NULL show processlist +drop event закачка21; +set global event_scheduler=1; +select get_lock("test_lock3", 20); +get_lock("test_lock3", 20) +1 +create event закачка on schedule every 10 hour do select get_lock("test_lock3", 20); +select sleep(2); +sleep(2) +0 +show processlist; +Id User Host db Command Time State Info +1 root localhost events_test Query 0 NULL show processlist +5 event_scheduler NULL Connect 2 Sleeping NULL +6 root events_test Connect 2 User lock select get_lock("test_lock3", 20) +drop event закачка; +select release_lock("test_lock3"); +release_lock("test_lock3") +1 +set global event_scheduler=0; +select sleep(2); +sleep(2) +0 +show processlist; +Id User Host db Command Time State Info +1 root localhost events_test Query 0 NULL show processlist +drop database events_test; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 32f0dc8b68c..2e4e2dc10ea 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -184,5 +184,25 @@ proc CREATE TABLE `proc` ( `comment` char(64) character set utf8 collate utf8_bin NOT NULL default '', PRIMARY KEY (`db`,`name`,`type`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures' +show create table event; +Table Create Table +event CREATE TABLE `event` ( + `db` char(64) character set utf8 collate utf8_bin NOT NULL default '', + `name` char(64) character set utf8 collate utf8_bin NOT NULL default '', + `body` longblob NOT NULL, + `definer` char(77) character set utf8 collate utf8_bin NOT NULL default '', + `execute_at` datetime default NULL, + `interval_value` int(11) default NULL, + `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, + `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `modified` timestamp NOT NULL default '0000-00-00 00:00:00', + `last_executed` datetime default NULL, + `starts` datetime default NULL, + `ends` datetime default NULL, + `status` enum('ENABLED','DISABLED') NOT NULL default 'ENABLED', + `on_completion` enum('DROP','PRESERVE') NOT NULL default 'DROP', + `comment` varchar(64) character set utf8 collate utf8_bin NOT NULL default '', + PRIMARY KEY (`db`,`name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events' show tables; Tables_in_test diff --git a/mysql-test/t/events.test b/mysql-test/t/events.test index 44b115a1572..aa8e4ede06e 100644 --- a/mysql-test/t/events.test +++ b/mysql-test/t/events.test @@ -1,4 +1,5 @@ -use test; +create database if not exists events_test; +use events_test; drop event if exists event1; create event event1 on schedule every 15 minute starts now() ends date_add(now(), interval 5 hour) DO begin end; alter event event1 rename to event2; @@ -15,3 +16,79 @@ set max_allowed_packet=128000000; select count(*) from t_event3; drop event event3; drop table t_event3; + +set names utf8; +create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1; +drop event задачка; + +# event_scheduler is a global var +--error 1229 +set event_scheduler=0; +# event_scheduler could be only either 0 or 1 +--error 1231 +set global event_scheduler=2; + +set global event_scheduler=0; +select count(*) from mysql.event; +select get_lock("test_lock1", 20); +create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20); +select count(*) from mysql.event; +show processlist; +select release_lock("test_lock1"); +drop event закачка; +select count(*) from mysql.event; + +set global event_scheduler=1; +select get_lock("test_lock2", 20); +create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20); +select sleep(2); +show processlist; +select release_lock("test_lock2"); +drop event закачка; + +# +# 1. get a lock +# 2. create an event +# 3. sleep so it has time to start +# 4. should appear in processlist +# 5. kill the scheduler, it will wait for the child to stop +# 6. both processes should be there on show processlist +# 7. release the lock and sleep, both scheduler and child should end +set global event_scheduler=1; +select get_lock("test_lock2_1", 20); +create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20); +select sleep(2); +show processlist; +set global event_scheduler=0; +select sleep(2); +show processlist; +select release_lock("test_lock2_1"); +select sleep(2); +show processlist; +drop event закачка21; + +set global event_scheduler=1; +select get_lock("test_lock3", 20); +create event закачка on schedule every 10 hour do select get_lock("test_lock3", 20); +select sleep(2); +show processlist; +drop event закачка; +select release_lock("test_lock3"); + +# +# test with very often occuring event +# (disabled for now, locks) +#select get_lock("test_lock4", 20); +#create event закачка4 on schedule every 1 second do select get_lock("test_lock4", 20); +#select sleep(3); +#show processlist; +#drop event закачка4; +#select release_lock("test_lock4"); + +set global event_scheduler=0; +select sleep(2); +show processlist; +#the following locks for some reason and is a bug, commented for now +#select count(*) from mysql.event; + +drop database events_test; diff --git a/mysql-test/t/system_mysql_db_fix.test b/mysql-test/t/system_mysql_db_fix.test index 9b475a6f494..7974b2fd62d 100644 --- a/mysql-test/t/system_mysql_db_fix.test +++ b/mysql-test/t/system_mysql_db_fix.test @@ -85,7 +85,7 @@ INSERT INTO user VALUES ('localhost','', '','N','N','N','N','N','N','N','N',' -- disable_query_log -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, event; -- enable_query_log diff --git a/scripts/mysql_create_system_tables.sh b/scripts/mysql_create_system_tables.sh index 2529eaf1937..6e0ff3b6389 100644 --- a/scripts/mysql_create_system_tables.sh +++ b/scripts/mysql_create_system_tables.sh @@ -741,6 +741,35 @@ then c_p="$c_p comment='Stored Procedures';" fi + +if test ! -f $mdata/event.frm +then + c_ev="$c_ev CREATE TABLE event (" + c_ev="$c_ev db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default ''," + c_ev="$c_ev name char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default ''," + c_ev="$c_ev body longblob NOT NULL," + c_ev="$c_ev definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default ''," + c_ev="$c_ev execute_at DATETIME default NULL," + c_ev="$c_ev interval_value int(11) default NULL," + c_ev="$c_ev interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK'," + c_ev="$c_ev 'SECOND','MICROSECOND', 'YEAR_MONTH','DAY_HOUR'," + c_ev="$c_ev 'DAY_MINUTE','DAY_SECOND'," + c_ev="$c_ev 'HOUR_MINUTE','HOUR_SECOND'," + c_ev="$c_ev 'MINUTE_SECOND','DAY_MICROSECOND'," + c_ev="$c_ev 'HOUR_MICROSECOND','MINUTE_MICROSECOND'," + c_ev="$c_ev 'SECOND_MICROSECOND') default NULL," + c_ev="$c_ev created TIMESTAMP NOT NULL," + c_ev="$c_ev modified TIMESTAMP NOT NULL," + c_ev="$c_ev last_executed DATETIME default NULL," + c_ev="$c_ev starts DATETIME default NULL," + c_ev="$c_ev ends DATETIME default NULL," + c_ev="$c_ev status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED'," + c_ev="$c_ev on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP'," + c_ev="$c_ev comment varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default ''," + c_ev="$c_ev PRIMARY KEY (db,name)" + c_ev="$c_ev ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';" +fi + cat << END_OF_DATA use mysql; set storage_engine=myisam; @@ -781,5 +810,7 @@ $i_tzls $c_p $c_pp +$c_ev + END_OF_DATA diff --git a/scripts/mysql_fix_privilege_tables.sql b/scripts/mysql_fix_privilege_tables.sql index 58be425ebf2..af995836c0c 100644 --- a/scripts/mysql_fix_privilege_tables.sql +++ b/scripts/mysql_fix_privilege_tables.sql @@ -562,6 +562,6 @@ CREATE TABLE event ( # EVENT privilege # -ALTER TABLE mysql.user add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv; -ALTER TABLE mysql.db add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL; +ALTER TABLE user add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv; +ALTER TABLE db add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL; diff --git a/sql/event_executor.cc b/sql/event_executor.cc index 07e53d62a5e..ab42734455c 100644 --- a/sql/event_executor.cc +++ b/sql/event_executor.cc @@ -234,11 +234,16 @@ event_executor_main(void *arg) event_timed *et; cnt++; - DBUG_PRINT("info", ("EVEX External Loop %d", cnt)); + DBUG_PRINT("info", ("EVEX External Loop %d thd->k", cnt)); thd->proc_info = "Sleeping"; - if (!evex_queue_num_elements(EVEX_EQ_NAME) || - !event_executor_running_global_var) + if (!event_executor_running_global_var) + { + sql_print_information("Scheduler asked to stop."); + break; + } + + if (!evex_queue_num_elements(EVEX_EQ_NAME)) { my_sleep(1000000);// sleep 1s continue; @@ -279,13 +284,16 @@ event_executor_main(void *arg) We sleep t2sleep seconds but we check every second whether this thread has been killed, or there is a new candidate */ - while (t2sleep-- && !thd->killed && + while (t2sleep-- && !thd->killed && event_executor_running_global_var && evex_queue_num_elements(EVEX_EQ_NAME) && (evex_queue_first_element(&EVEX_EQ_NAME, event_timed*) == et)) my_sleep(1000000); } if (!event_executor_running_global_var) - continue; + { + sql_print_information("Scheduler asked to stop."); + break; + } } @@ -345,7 +353,7 @@ err: evex_main_thread_id= 0; VOID(pthread_mutex_unlock(&LOCK_evex_running)); - sql_print_information("Event scheduler stopping"); + sql_print_information("Event scheduler stopping. Waiting for worker threads to finish."); /* TODO: A better will be with a conditional variable @@ -392,7 +400,7 @@ err_no_thd: VOID(pthread_mutex_unlock(&LOCK_evex_running)); free_root(&evex_mem_root, MYF(0)); - sql_print_information("Event scheduler stopped"); + sql_print_information("Event scheduler stopped."); #ifndef DBUG_FAULTY_THR my_thread_end(); @@ -605,14 +613,18 @@ end: bool sys_var_event_executor::update(THD *thd, set_var *var) { // here start the thread if not running. + DBUG_ENTER("sys_var_event_executor::update"); VOID(pthread_mutex_lock(&LOCK_evex_running)); *value= var->save_result.ulong_value; + + DBUG_PRINT("new_value", ("%d", *value)); if ((my_bool) *value && !evex_is_running) { VOID(pthread_mutex_unlock(&LOCK_evex_running)); init_events(); } else VOID(pthread_mutex_unlock(&LOCK_evex_running)); - return 0; + + DBUG_RETURN(0); } |