diff options
| -rw-r--r-- | mysql-test/r/events.result | 11 | ||||
| -rw-r--r-- | mysql-test/r/events_scheduling.result | 46 | ||||
| -rw-r--r-- | mysql-test/t/events.test | 23 | ||||
| -rw-r--r-- | mysql-test/t/events_scheduling.test | 36 | ||||
| -rw-r--r-- | sql/event.cc | 7 | ||||
| -rw-r--r-- | sql/event_executor.cc | 31 | ||||
| -rw-r--r-- | sql/event_timed.cc | 211 | ||||
| -rw-r--r-- | sql/item_timefunc.cc | 82 | ||||
| -rw-r--r-- | sql/mysql_priv.h | 2 | ||||
| -rw-r--r-- | sql/sql_show.cc | 14 | ||||
| -rw-r--r-- | sql/time.cc | 76 |
11 files changed, 367 insertions, 172 deletions
diff --git a/mysql-test/r/events.result b/mysql-test/r/events.result index 06c846da781..5ff26d7428d 100644 --- a/mysql-test/r/events.result +++ b/mysql-test/r/events.result @@ -106,7 +106,6 @@ drop event if exists event3; Warnings: Note 1305 Event event3 does not exist create event event3 on schedule every 50 + 10 minute starts date_add("20100101", interval 5 minute) ends date_add("20151010", interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand()); -set max_allowed_packet=128000000; select count(*) from t_event3; count(*) 0 @@ -232,6 +231,9 @@ Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED CREATE TABLE event_like LIKE mysql.event; INSERT INTO event_like SELECT * FROM mysql.event; +ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; +SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; +ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. ALTER TABLE mysql.event MODIFY db char(20) character set utf8 collate utf8_bin default ''; SHOW CREATE TABLE mysql.event; Table Create Table @@ -260,11 +262,10 @@ ALTER TABLE mysql.event MODIFY db char(64) character set utf8 collate utf8_bin d "This should work" SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status -events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED -ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; -SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; -ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. +events_test intact_check root@localhost RECURRING NULL 10 HOUR # # ENABLED ALTER TABLE mysql.event MODIFY db varchar(64) character set utf8 collate utf8_bin default ''; +Warnings: +Warning 1265 Data truncated for column 'db' at row 1 SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ERROR HY000: Cannot load from mysql.event. Table probably corrupted. See error log. ALTER TABLE mysql.event DROP comment, DROP starts; diff --git a/mysql-test/r/events_scheduling.result b/mysql-test/r/events_scheduling.result new file mode 100644 index 00000000000..67e8e6d5691 --- /dev/null +++ b/mysql-test/r/events_scheduling.result @@ -0,0 +1,46 @@ +CREATE DATABASE IF NOT EXISTS events_test; +USE events_test; +CREATE TABLE table_1(a int); +CREATE TABLE table_2(a int); +CREATE TABLE table_3(a int); +CREATE TABLE table_4(a int); +SET GLOBAL event_scheduler=1; +CREATE EVENT two_sec ON SCHEDULE EVERY 2 SECOND DO INSERT INTO table_1 VALUES(1); +CREATE EVENT start_n_end +ON SCHEDULE EVERY 1 SECOND +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); +SELECT IF(SUM(a) >= 4, 'OK', 'ERROR') FROM table_1; +IF(SUM(a) >= 4, 'OK', 'ERROR') +OK +SELECT IF(SUM(a) >= 5, 'OK', 'ERROR') FROM table_2; +IF(SUM(a) >= 5, 'OK', 'ERROR') +OK +SELECT IF(SUM(a) > 0, 'OK', 'ERROR') FROM table_3; +IF(SUM(a) > 0, 'OK', 'ERROR') +OK +SELECT IF(SUM(a) > 0, 'OK', 'ERROR') FROM table_4; +IF(SUM(a) > 0, 'OK', 'ERROR') +OK +DROP EVENT two_sec; +SELECT IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR') FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='start_n_end' AND ENDS IS NOT NULL; +IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR') +OK +SELECT IF(LAST_EXECUTED-ENDS < 2, 'OK', 'ERROR') FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='start_n_end' AND ENDS IS NOT NULL; +IF(LAST_EXECUTED-ENDS < 2, 'OK', 'ERROR') +OK +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." +DROP EVENT two_time; +ERROR HY000: Unknown event 'two_time' +DROP TABLE table_1; +DROP TABLE table_2; +DROP TABLE table_3; +DROP TABLE table_4; +DROP DATABASE events_test; diff --git a/mysql-test/t/events.test b/mysql-test/t/events.test index 755d4e7775b..73cadd406d8 100644 --- a/mysql-test/t/events.test +++ b/mysql-test/t/events.test @@ -101,7 +101,6 @@ set global event_scheduler = 0; create table t_event3 (a int, b float); drop event if exists event3; create event event3 on schedule every 50 + 10 minute starts date_add("20100101", interval 5 minute) ends date_add("20151010", interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand()); -set max_allowed_packet=128000000; select count(*) from t_event3; drop event event3; drop table t_event3; @@ -148,8 +147,8 @@ SHOW CREATE EVENT root19; create event root20 on schedule every '50:20:12:45' day_second do select 1; SHOW CREATE EVENT root20; set names cp1251; -create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåíòàð' do select 1; -SHOW CREATE EVENT ðóóò21; +create event ðóóò21 on schedule every '50:23:59:95' day_second COMMENT 'òîâà å 1251 êîìåÃòà ð' do select 1; +SHOW CREATE EVENT ðóóò21; insert into mysql.event (db, name, body, definer, interval_value, interval_field) values (database(), "root22", "select 1", user(), 100, "SECOND_MICROSECOND"); --error 1235 show create event root22; @@ -174,7 +173,7 @@ drop event root17_1; drop event root18; drop event root19; drop event root20; -drop event ðóóò21; +drop event ðóóò21; set names latin1; # @@ -202,6 +201,9 @@ CREATE TABLE event_like LIKE mysql.event; INSERT INTO event_like SELECT * FROM mysql.event; #sleep a bit or we won't catch the change of time --sleep 1 +ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; +--error 1526 +SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ALTER TABLE mysql.event MODIFY db char(20) character set utf8 collate utf8_bin default ''; #wait a bit or we won't see the difference because of seconds resolution --sleep 1 @@ -220,6 +222,7 @@ ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; --sleep 1 ALTER TABLE mysql.event MODIFY db varchar(64) character set utf8 collate utf8_bin default ''; +--sleep 1 --error ER_CANNOT_LOAD_FROM_TABLE SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; --sleep 1 @@ -412,7 +415,8 @@ select 1; select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; drop event white_space; create event white_space on schedule every 10 hour disable do -
select 2; + +select 2; select event_schema, event_name, definer, event_body from information_schema.events where event_name='white_space'; drop event white_space; create event white_space on schedule every 10 hour disable do select 3; @@ -422,7 +426,7 @@ drop event white_space; # END: BUG #17453: Creating Event crash the server # -# +##set global event_scheduler=1; # Bug#17403 "Events: packets out of order with show create event" # create event e1 on schedule every 1 year do set @a = 5; @@ -436,7 +440,7 @@ drop event e1; ##select get_lock("test_lock3", 20); ##create event закачка on schedule every 10 hour do select get_lock("test_lock3", 20); ##select sleep(2); -##select /*7*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info; +##show processlist; ##drop event закачка; ##select release_lock("test_lock3"); @@ -446,13 +450,14 @@ drop event e1; ##select get_lock("test_lock4", 20); ##create event закачка4 on schedule every 1 second do select get_lock("test_lock4", 20); ##select sleep(3); -##select /*8*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info; +##--replace_column 1 # 6 # ##drop event закачка4; ##select release_lock("test_lock4"); ##set global event_scheduler=0; ##select sleep(2); -##select /*9*/ user, host, db, command, state, info from information_schema.processlist where info is null or info not like '%processlist%' order by info; +##--replace_column 1 # 6 # ##select count(*) from mysql.event; drop database events_test; + diff --git a/mysql-test/t/events_scheduling.test b/mysql-test/t/events_scheduling.test new file mode 100644 index 00000000000..41bbb1da3c0 --- /dev/null +++ b/mysql-test/t/events_scheduling.test @@ -0,0 +1,36 @@ +CREATE DATABASE IF NOT EXISTS events_test; +USE events_test; +CREATE TABLE table_1(a int); +CREATE TABLE table_2(a int); +CREATE TABLE table_3(a int); +CREATE TABLE table_4(a int); +SET GLOBAL event_scheduler=1; +CREATE EVENT two_sec ON SCHEDULE EVERY 2 SECOND DO INSERT INTO table_1 VALUES(1); +CREATE EVENT start_n_end + ON SCHEDULE EVERY 1 SECOND + ENDS NOW() + INTERVAL 6 SECOND + ON COMPLETION PRESERVE + DO INSERT INTO table_2 VALUES(1); +--sleep 5 +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); +--sleep 5 +SELECT IF(SUM(a) >= 4, 'OK', 'ERROR') FROM table_1; +SELECT IF(SUM(a) >= 5, 'OK', 'ERROR') FROM table_2; +SELECT IF(SUM(a) > 0, 'OK', 'ERROR') FROM table_3; +SELECT IF(SUM(a) > 0, 'OK', 'ERROR') FROM table_4; +DROP EVENT two_sec; +SELECT IF(TIME_TO_SEC(TIMEDIFF(ENDS,STARTS))=6, 'OK', 'ERROR') FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='start_n_end' AND ENDS IS NOT NULL; +SELECT IF(LAST_EXECUTED-ENDS < 2, 'OK', 'ERROR') FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA=DATABASE() AND EVENT_NAME='start_n_end' AND ENDS IS NOT NULL; +DROP EVENT start_n_end; +--echo "Already dropped because ended. Therefore an error." +--error 1517 +DROP EVENT only_one_time; +--echo "Already dropped because ended. Therefore an error." +--error 1517 +DROP EVENT two_time; +DROP TABLE table_1; +DROP TABLE table_2; +DROP TABLE table_3; +DROP TABLE table_4; +DROP DATABASE events_test; diff --git a/sql/event.cc b/sql/event.cc index 3bfbfbec483..4a3c6aad30c 100644 --- a/sql/event.cc +++ b/sql/event.cc @@ -1051,13 +1051,6 @@ evex_load_and_compile_event(THD * thd, sp_name *spn, LEX_STRING definer, thd->restore_backup_open_tables_state(&backup); if (ret) goto done; - - /* - allocate on evex_mem_root. if you call without evex_mem_root - then sphead will not be cleared! - */ - if ((ret= ett->compile(thd, &evex_mem_root))) - goto done; ett->compute_next_execution_time(); if (use_lock) diff --git a/sql/event_executor.cc b/sql/event_executor.cc index 92acf154c75..498760bb6fd 100644 --- a/sql/event_executor.cc +++ b/sql/event_executor.cc @@ -42,6 +42,8 @@ pthread_mutex_t LOCK_event_arrays, // mutex for when working with t LOCK_workers_count, // mutex for when inc/dec uint workers_count LOCK_evex_running; // mutes for managing bool evex_is_running +static pthread_mutex_t LOCK_evex_main_thread; // mutex for when working with the queue +bool scheduler_main_thread_running= false; bool evex_is_running= false; @@ -111,6 +113,7 @@ evex_init_mutexes() pthread_mutex_init(&LOCK_event_arrays, MY_MUTEX_INIT_FAST); pthread_mutex_init(&LOCK_workers_count, MY_MUTEX_INIT_FAST); pthread_mutex_init(&LOCK_evex_running, MY_MUTEX_INIT_FAST); + pthread_mutex_init(&LOCK_evex_main_thread, MY_MUTEX_INIT_FAST); event_executor_running_global_var= opt_event_executor; } @@ -241,6 +244,7 @@ shutdown_events() pthread_mutex_destroy(&LOCK_event_arrays); pthread_mutex_destroy(&LOCK_workers_count); pthread_mutex_destroy(&LOCK_evex_running); + pthread_mutex_destroy(&LOCK_evex_main_thread); } DBUG_VOID_RETURN; } @@ -351,6 +355,7 @@ executor_wait_till_next_event_exec(THD *thd) t2sleep= evex_time_diff(&et->execute_at, &time_now); VOID(pthread_mutex_unlock(&LOCK_event_arrays)); + t2sleep*=20; DBUG_PRINT("evex main thread",("unlocked LOCK_event_arrays")); if (t2sleep > 0) { @@ -366,7 +371,7 @@ executor_wait_till_next_event_exec(THD *thd) modified)) { DBUG_PRINT("evex main thread",("will sleep a bit more.")); - my_sleep(1000000); + my_sleep(50000); } DBUG_PRINT("info",("saved_modified=%llu current=%llu", modified, evex_queue_num_elements(EVEX_EQ_NAME)? @@ -407,10 +412,23 @@ event_executor_main(void *arg) THD *thd; /* needs to be first for thread_stack */ uint i=0, j=0; my_ulonglong cnt= 0; - + DBUG_ENTER("event_executor_main"); DBUG_PRINT("event_executor_main", ("EVEX thread started")); - + + pthread_mutex_lock(&LOCK_evex_main_thread); + if (!scheduler_main_thread_running) + scheduler_main_thread_running= true; + else + { + DBUG_PRINT("event_executor_main", ("already running. thd_id=%d", + evex_main_thread_id)); + pthread_mutex_unlock(&LOCK_evex_main_thread); + my_thread_end(); + pthread_exit(0); + DBUG_RETURN(0); // Can't return anything here + } + pthread_mutex_unlock(&LOCK_evex_main_thread); /* init memory root */ init_alloc_root(&evex_mem_root, MEM_ROOT_BLOCK_SIZE, MEM_ROOT_PREALLOC); @@ -489,7 +507,7 @@ event_executor_main(void *arg) if (!evex_queue_num_elements(EVEX_EQ_NAME)) { - my_sleep(1000000);// sleep 1s + my_sleep(100000);// sleep 0.1s continue; } @@ -652,12 +670,17 @@ finish: err_no_thd: VOID(pthread_mutex_lock(&LOCK_evex_running)); evex_is_running= false; + event_executor_running_global_var= false; VOID(pthread_mutex_unlock(&LOCK_evex_running)); free_root(&evex_mem_root, MYF(0)); sql_print_information("SCHEDULER: Stopped."); #ifndef DBUG_FAULTY_THR + pthread_mutex_lock(&LOCK_evex_main_thread); + scheduler_main_thread_running= false; + pthread_mutex_unlock(&LOCK_evex_main_thread); + my_thread_end(); pthread_exit(0); #endif diff --git a/sql/event_timed.cc b/sql/event_timed.cc index eb97d6c87d6..a47446b7270 100644 --- a/sql/event_timed.cc +++ b/sql/event_timed.cc @@ -593,28 +593,9 @@ Event_timed::load_from_row(MEM_ROOT *mem_root, TABLE *table) et->created= table->field[EVEX_FIELD_CREATED]->val_int(); et->modified= table->field[EVEX_FIELD_MODIFIED]->val_int(); - /* - ToDo Andrey : Ask PeterG & Serg what to do in this case. - Whether on load last_executed_at should be loaded - or it must be 0ed. If last_executed_at is loaded - then an event can be scheduled for execution - instantly. Let's say an event has to be executed - every 15 mins. The server has been stopped for - more than this time and then started. If L_E_AT - is loaded from DB, execution at L_E_AT+15min - will be scheduled. However this time is in the past. - Hence immediate execution. Due to patch of - ::mark_last_executed() last_executed gets time_now - and not execute_at. If not like this a big - queue can be scheduled for times which are still in - the past (2, 3 and more executions which will be - consequent). - */ - set_zero_time(&last_executed, MYSQL_TIMESTAMP_DATETIME); -#ifdef ANDREY_0 table->field[EVEX_FIELD_LAST_EXECUTED]-> get_date(&et->last_executed, TIME_NO_ZERO_DATE); -#endif + last_executed_changed= false; /* ToDo : Andrey . Find a way not to allocate ptr on event_mem_root */ @@ -648,70 +629,164 @@ error: /* - Computes the sum of a timestamp plus interval + Computes the sum of a timestamp plus interval. Presumed is that at least one + previous execution has occured. SYNOPSIS get_next_time(TIME *start, int interval_value, interval_type interval) next the sum start add interval_value to this time + time_now current time i_value quantity of time type interval to add i_type type of interval to add (SECOND, MINUTE, HOUR, WEEK ...) + + RETURNS + 0 OK + 1 Error + + NOTES + 1) If the interval is conversible to SECOND, like MINUTE, HOUR, DAY, WEEK. + Then we use TIMEDIFF()'s implementation as underlying and number of + seconds as resolution for computation. + 2) In all other cases - MONTH, QUARTER, YEAR we use MONTH as resolution + and PERIOD_DIFF()'s implementation + 3) We get the difference between time_now and `start`, then divide it + by the months, respectively seconds and round up. Then we multiply + monts/seconds by the rounded value and add it to `start` -> we get + the next execution time. */ static -bool get_next_time(TIME *next, TIME *start, int i_value, interval_type i_type) +bool get_next_time(TIME *next, TIME *start, TIME *time_now, TIME *last_exec, + int i_value, interval_type i_type) { bool ret; INTERVAL interval; TIME tmp; + longlong months=0, seconds=0; + DBUG_ENTER("get_next_time"); + DBUG_PRINT("enter", ("start=%llu now=%llu", TIME_to_ulonglong_datetime(start), + TIME_to_ulonglong_datetime(time_now))); bzero(&interval, sizeof(interval)); switch (i_type) { case INTERVAL_YEAR: - interval.year= (ulong) i_value; + months= i_value*12; break; case INTERVAL_QUARTER: - interval.month= (ulong)(i_value*3); - break; + /* Has already been converted to months */ case INTERVAL_YEAR_MONTH: case INTERVAL_MONTH: - interval.month= (ulong) i_value; + months= i_value; break; case INTERVAL_WEEK: - interval.day= (ulong)(i_value*7); - break; + /* WEEK has already been converted to days */ case INTERVAL_DAY: - interval.day= (ulong) i_value; + seconds= i_value*24*3600; break; case INTERVAL_DAY_HOUR: case INTERVAL_HOUR: - interval.hour= (ulong) i_value; + seconds= i_value*3600; break; case INTERVAL_DAY_MINUTE: case INTERVAL_HOUR_MINUTE: case INTERVAL_MINUTE: - interval.minute=i_value; + seconds= i_value*60; break; case INTERVAL_DAY_SECOND: case INTERVAL_HOUR_SECOND: case INTERVAL_MINUTE_SECOND: case INTERVAL_SECOND: - interval.second=i_value; + seconds= i_value; break; case INTERVAL_DAY_MICROSECOND: case INTERVAL_HOUR_MICROSECOND: case INTERVAL_MINUTE_MICROSECOND: case INTERVAL_SECOND_MICROSECOND: case INTERVAL_MICROSECOND: - interval.second_part=i_value; + /* + We should return an error here so SHOW EVENTS/ SELECT FROM I_S.EVENTS + would give an error then. + */ + DBUG_RETURN(1); break; } - tmp= *start; - if (!(ret= date_add_interval(&tmp, i_type, interval))) + DBUG_PRINT("info", ("seconds=%ld months=%ld", seconds, months)); + if (seconds) + { + longlong seconds_diff; + long microsec_diff; + + if (calc_time_diff(time_now, start, 1, &seconds_diff, µsec_diff)) + { + DBUG_PRINT("error", ("negative difference")); + DBUG_ASSERT(0); + } + uint multiplier= seconds_diff / seconds; + /* + Increase the multiplier is the modulus is not zero to make round up. + Or if time_now==start then we should not execute the same + event two times for the same time + get the next exec if the modulus is not + */ + DBUG_PRINT("info", ("multiplier=%d", multiplier)); + if (seconds_diff % seconds || (!seconds_diff && last_exec->year)) + ++multiplier; + interval.second= seconds * multiplier; + DBUG_PRINT("info", ("multiplier=%u interval.second=%u", multiplier, + interval.second)); + tmp= *start; + if (!(ret= date_add_interval(&tmp, INTERVAL_SECOND, interval))) + *next= tmp; + } + else + { + /* PRESUMED is that at least one execution took already place */ + int diff_months= (time_now->year - start->year)*12 + + (time_now->month - start->month); + /* + Note: If diff_months is 0 that means we are in the same month as the + last execution which is also the first execution. + */ + /* + First we try with the smaller if not then + 1, because if we try with + directly with +1 we will be after the current date but it could be that + we will be 1 month ahead, so 2 steps are necessary. + */ + interval.month= (diff_months / months)*months; + /* + Check if the same month as last_exec (always set - prerequisite) + An event happens at most once per month so there is no way to schedule + it two times for the current month. This saves us from two calls to + date_add_interval() if the event was just executed. But if the scheduler + is started and there was at least 1 scheduled date skipped this one does + not help and two calls to date_add_interval() will be done, which is a + bit more expensive but compared to the rareness of the case is neglectable. + */ + if (time_now->year==last_exec->year && time_now->month==last_exec->month) + interval.month+= months; + + tmp= *start; + if ((ret= date_add_interval(&tmp, INTERVAL_MONTH, interval))) + goto done; + + /* If `tmp` is still before time_now just add one more time the interval */ + if (my_time_compare(&tmp, time_now) == -1) + { + interval.month+= months; + tmp= *start; + if ((ret= date_add_interval(&tmp, INTERVAL_MONTH, interval))) + goto done; + } *next= tmp; + /* assert on that the next is after now */ + DBUG_ASSERT(1==my_time_compare(next, time_now)); + } - return ret; +done: + DBUG_PRINT("info", ("next=%llu", TIME_to_ulonglong_datetime(next))); + DBUG_RETURN(ret); } @@ -734,6 +809,10 @@ Event_timed::compute_next_execution_time() int tmp; DBUG_ENTER("Event_timed::compute_next_execution_time"); + DBUG_PRINT("enter", ("starts=%llu ends=%llu last_executed=%llu", + TIME_to_ulonglong_datetime(&starts), + TIME_to_ulonglong_datetime(&ends), + TIME_to_ulonglong_datetime(&last_executed))); if (status == MYSQL_EVENT_DISABLED) { @@ -757,29 +836,14 @@ Event_timed::compute_next_execution_time() } goto ret; } - time((time_t *)&now); - my_tz_UTC->gmt_sec_to_TIME(&time_now, now); + my_tz_UTC->gmt_sec_to_TIME(&time_now, current_thd->query_start()); -#ifdef ANDREY_0 - sql_print_information("[%s.%s]", dbname.str, name.str); - sql_print_information("time_now : [%d-%d-%d %d:%d:%d ]", - time_now.year, time_now.month, time_now.day, - time_now.hour, time_now.minute, time_now.second); - sql_print_information("starts : [%d-%d-%d %d:%d:%d ]", starts.year, - starts.month, starts.day, starts.hour, - starts.minute, starts.second); - sql_print_information("ends : [%d-%d-%d %d:%d:%d ]", ends.year, - ends.month, ends.day, ends.hour, - ends.minute, ends.second); - sql_print_information("m_last_ex: [%d-%d-%d %d:%d:%d ]", last_executed.year, - last_executed.month, last_executed.day, - last_executed.hour, last_executed.minute, - last_executed.second); -#endif + DBUG_PRINT("info",("NOW=[%llu]", TIME_to_ulonglong_datetime(&time_now))); /* if time_now is after ends don't execute anymore */ if (!ends_null && (tmp= my_time_compare(&ends, &time_now)) == -1) { + DBUG_PRINT("info", ("NOW after ENDS, don't execute anymore")); /* time_now is after ends. don't execute anymore */ set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME); execute_at_null= TRUE; @@ -807,6 +871,7 @@ Event_timed::compute_next_execution_time() } else { + DBUG_PRINT("info", ("STARTS is future, NOW <= STARTS,sched for STARTS")); /* starts is in the future time_now before starts. Scheduling for starts @@ -825,8 +890,10 @@ Event_timed::compute_next_execution_time() after m_ends set execute_at to 0. And check for on_completion If not set then schedule for now. */ + DBUG_PRINT("info", ("Both STARTS & ENDS are set")); if (!last_executed.year) { + DBUG_PRINT("info", ("Not executed so far. Execute NOW.")); execute_at= time_now; execute_at_null= FALSE; } @@ -834,12 +901,15 @@ Event_timed::compute_next_execution_time() { TIME next_exec; - if (get_next_time(&next_exec, &last_executed, expression, interval)) + DBUG_PRINT("info", ("Executed at least once")); + if (get_next_time(&next_exec, &starts, &time_now, &last_executed, + expression, interval)) goto err; /* There was previous execution */ if (my_time_compare(&ends, &next_exec) == -1) { + DBUG_PRINT("info", ("Next execution after ENDS. Stop executing.")); /* Next execution after ends. No more executions */ set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME); execute_at_null= TRUE; @@ -848,6 +918,7 @@ Event_timed::compute_next_execution_time() } else { + DBUG_PRINT("info",("Next[%llu]",TIME_to_ulonglong_datetime(&next_exec))); execute_at= next_exec; execute_at_null= FALSE; } @@ -856,18 +927,24 @@ Event_timed::compute_next_execution_time() } else if (starts_null && ends_null) { + DBUG_PRINT("info", ("Neither STARTS nor ENDS are set")); /* Both starts and m_ends are not set, so we schedule for the next based on last_executed. */ if (last_executed.year) { - if (get_next_time(&execute_at, &last_executed, expression, interval)) + TIME next_exec; + if (get_next_time(&next_exec, &starts, &time_now, &last_executed, + expression, interval)) goto err; + execute_at= next_exec; + DBUG_PRINT("info",("Next[%llu]",TIME_to_ulonglong_datetime(&next_exec))); } else { /* last_executed not set. Schedule the event for now */ + DBUG_PRINT("info", ("Execute NOW")); execute_at= time_now; } execute_at_null= FALSE; @@ -877,6 +954,7 @@ Event_timed::compute_next_execution_time() /* either starts or m_ends is set */ if (!starts_null) { + DBUG_PRINT("info", ("STARTS is set")); /* - starts is set. - starts is not in the future according to check made before @@ -885,15 +963,24 @@ Event_timed::compute_next_execution_time() */ if (last_executed.year) { - if (get_next_time(&execute_at, &last_executed, expression, interval)) + TIME next_exec; + DBUG_PRINT("info", ("Executed at least once.")); + if (get_next_time(&next_exec, &starts, &time_now, &last_executed, + expression, interval)) goto err; + execute_at= next_exec; + DBUG_PRINT("info",("Next[%llu]",TIME_to_ulonglong_datetime(&next_exec))); } else + { + DBUG_PRINT("info", ("Not executed so far. Execute at STARTS")); execute_at= starts; + } execute_at_null= FALSE; } else { + DBUG_PRINT("info", ("STARTS is not set. ENDS is set")); /* - m_ends is set - m_ends is after time_now or is equal @@ -907,11 +994,13 @@ Event_timed::compute_next_execution_time() { TIME next_exec; - if (get_next_time(&next_exec, &last_executed, expression, interval)) + if (get_next_time(&next_exec, &starts, &time_now, &last_executed, + expression, interval)) goto err; if (my_time_compare(&ends, &next_exec) == -1) { + DBUG_PRINT("info", ("Next execution after ENDS. Stop executing.")); set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME); execute_at_null= TRUE; if (on_completion == MYSQL_EVENT_ON_COMPLETION_DROP) @@ -919,6 +1008,8 @@ Event_timed::compute_next_execution_time() } else { + DBUG_PRINT("info", ("Next[%llu]", + TIME_to_ulonglong_datetime(&next_exec))); execute_at= next_exec; execute_at_null= FALSE; } @@ -927,9 +1018,10 @@ Event_timed::compute_next_execution_time() goto ret; } ret: - + DBUG_PRINT("info", ("ret=0")); DBUG_RETURN(false); err: + DBUG_PRINT("info", ("ret=1")); DBUG_RETURN(true); } @@ -1462,6 +1554,7 @@ Event_timed::spawn_now(void * (*thread_func)(void*)) int ret= EVENT_EXEC_STARTED; static uint exec_num= 0; DBUG_ENTER("Event_timed::spawn_now"); + DBUG_PRINT("info", ("this=0x%lx", this)); DBUG_PRINT("info", ("[%s.%s]", dbname.str, name.str)); VOID(pthread_mutex_lock(&this->LOCK_running)); diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 7ee206a4195..d33db25ca87 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -772,81 +772,6 @@ static bool get_interval_info(const char *str,uint length,CHARSET_INFO *cs, } -/* - Calculate difference between two datetime values as seconds + microseconds. - - SYNOPSIS - calc_time_diff() - l_time1 - TIME/DATE/DATETIME value - l_time2 - TIME/DATE/DATETIME value - l_sign - 1 absolute values are substracted, - -1 absolute values are added. - seconds_out - Out parameter where difference between - l_time1 and l_time2 in seconds is stored. - microseconds_out- Out parameter where microsecond part of difference - between l_time1 and l_time2 is stored. - - NOTE - This function calculates difference between l_time1 and l_time2 absolute - values. So one should set l_sign and correct result if he want to take - signs into account (i.e. for TIME values). - - RETURN VALUES - Returns sign of difference. - 1 means negative result - 0 means positive result - -*/ - -static bool calc_time_diff(TIME *l_time1, TIME *l_time2, int l_sign, - longlong *seconds_out, long *microseconds_out) -{ - long days; - bool neg; - longlong microseconds; - - /* - We suppose that if first argument is MYSQL_TIMESTAMP_TIME - the second argument should be TIMESTAMP_TIME also. - We should check it before calc_time_diff call. - */ - if (l_time1->time_type == MYSQL_TIMESTAMP_TIME) // Time value - days= (long)l_time1->day - l_sign * (long)l_time2->day; - else - { - days= calc_daynr((uint) l_time1->year, - (uint) l_time1->month, - (uint) l_time1->day); - if (l_time2->time_type == MYSQL_TIMESTAMP_TIME) - days-= l_sign * (long)l_time2->day; - else - days-= l_sign*calc_daynr((uint) l_time2->year, - (uint) l_time2->month, - (uint) l_time2->day); - } - - microseconds= ((longlong)days*LL(86400) + - (longlong)(l_time1->hour*3600L + - l_time1->minute*60L + - l_time1->second) - - l_sign*(longlong)(l_time2->hour*3600L + - l_time2->minute*60L + - l_time2->second)) * LL(1000000) + - (longlong)l_time1->second_part - - l_sign*(longlong)l_time2->second_part; - - neg= 0; - if (microseconds < 0) - { - microseconds= -microseconds; - neg= 1; - } - *seconds_out= microseconds/1000000L; - *microseconds_out= (long) (microseconds%1000000L); - return neg; -} - - longlong Item_func_period_add::val_int() { DBUG_ASSERT(fixed == 1); @@ -2031,16 +1956,13 @@ bool Item_date_add_interval::get_date(TIME *ltime, uint fuzzy_date) INTERVAL interval; if (args[0]->get_date(ltime, TIME_NO_ZERO_DATE) || - get_interval_value(args[1],int_type,&value,&interval)) - goto null_date; + get_interval_value(args[1], int_type, &value, &interval)) + return (null_value=1); if (date_sub_interval) interval.neg = !interval.neg; return (null_value= date_add_interval(ltime, int_type, interval)); - - null_date: - return (null_value=1); } diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 85b96d7e690..ac7957d41f1 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -1549,6 +1549,8 @@ void make_truncated_value_warning(THD *thd, const char *str_val, const char *field_name); bool date_add_interval(TIME *ltime, interval_type int_type, INTERVAL interval); +bool calc_time_diff(TIME *l_time1, TIME *l_time2, int l_sign, + longlong *seconds_out, long *microseconds_out); extern DATE_TIME_FORMAT *date_time_format_make(timestamp_type format_type, const char *format_str, diff --git a/sql/sql_show.cc b/sql/sql_show.cc index c8f4d399037..19535f3182a 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -3980,7 +3980,7 @@ fill_events_copy_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) if (!(!wild || !wild[0] || !wild_compare(et.name.str, wild, 0))) DBUG_RETURN(0); - //->field[0] is EVENT_CATALOG and is by default NULL + /* ->field[0] is EVENT_CATALOG and is by default NULL */ sch_table->field[1]->store(et.dbname.str, et.dbname.length, scs); sch_table->field[2]->store(et.name.str, et.name.length, scs); @@ -4000,12 +4000,9 @@ fill_events_copy_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) if (et.expression) { String show_str; - //type + /* type */ sch_table->field[5]->store(STRING_WITH_LEN("RECURRING"), scs); - /* execute_at */ - sch_table->field[6]->set_null(); - /* interval_value */ - //interval_type + if (event_reconstruct_interval_expression(&show_str, et.interval, et.expression)) DBUG_RETURN(1); @@ -4058,9 +4055,10 @@ fill_events_copy_to_schema_table(THD *thd, TABLE *sch_table, TABLE *event_table) sch_table->field[15]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); if (et.last_executed.year) + { + sch_table->field[16]->set_notnull(); sch_table->field[16]->store_time(&et.last_executed,MYSQL_TIMESTAMP_DATETIME); - else - sch_table->field[16]->set_null(); + } sch_table->field[17]->store(et.comment.str, et.comment.length, scs); diff --git a/sql/time.cc b/sql/time.cc index efe1cbf1c09..3c654de23bb 100644 --- a/sql/time.cc +++ b/sql/time.cc @@ -833,4 +833,80 @@ invalid_date: } +/* + Calculate difference between two datetime values as seconds + microseconds. + + SYNOPSIS + calc_time_diff() + l_time1 - TIME/DATE/DATETIME value + l_time2 - TIME/DATE/DATETIME value + l_sign - 1 absolute values are substracted, + -1 absolute values are added. + seconds_out - Out parameter where difference between + l_time1 and l_time2 in seconds is stored. + microseconds_out- Out parameter where microsecond part of difference + between l_time1 and l_time2 is stored. + + NOTE + This function calculates difference between l_time1 and l_time2 absolute + values. So one should set l_sign and correct result if he want to take + signs into account (i.e. for TIME values). + + RETURN VALUES + Returns sign of difference. + 1 means negative result + 0 means positive result + +*/ + +bool +calc_time_diff(TIME *l_time1, TIME *l_time2, int l_sign, longlong *seconds_out, + long *microseconds_out) +{ + long days; + bool neg; + longlong microseconds; + + /* + We suppose that if first argument is MYSQL_TIMESTAMP_TIME + the second argument should be TIMESTAMP_TIME also. + We should check it before calc_time_diff call. + */ + if (l_time1->time_type == MYSQL_TIMESTAMP_TIME) // Time value + days= (long)l_time1->day - l_sign * (long)l_time2->day; + else + { + days= calc_daynr((uint) l_time1->year, + (uint) l_time1->month, + (uint) l_time1->day); + if (l_time2->time_type == MYSQL_TIMESTAMP_TIME) + days-= l_sign * (long)l_time2->day; + else + days-= l_sign*calc_daynr((uint) l_time2->year, + (uint) l_time2->month, + (uint) l_time2->day); + } + + microseconds= ((longlong)days*LL(86400) + + (longlong)(l_time1->hour*3600L + + l_time1->minute*60L + + l_time1->second) - + l_sign*(longlong)(l_time2->hour*3600L + + l_time2->minute*60L + + l_time2->second)) * LL(1000000) + + (longlong)l_time1->second_part - + l_sign*(longlong)l_time2->second_part; + + neg= 0; + if (microseconds < 0) + { + microseconds= -microseconds; + neg= 1; + } + *seconds_out= microseconds/1000000L; + *microseconds_out= (long) (microseconds%1000000L); + return neg; +} + + #endif |