• unknown's avatar
    A set of changes aiming to make the Event Scheduler more user-friendly · fa1d637e
    unknown authored
    when there are no up-to-date system tables to support it:
     - initialize the scheduler before reporting "Ready for connections".
       This ensures that warnings, if any, are printed before "Ready for
       connections", and this message is not mangled.
     - do not abort the scheduler if there are no system tables
     - check the tables once at start up, remember the status and disable
       the scheduler if the tables are not up to date.
       If one attempts to use the scheduler with bad tables,
       issue an error message.
     - clean up the behaviour of the module under LOCK TABLES and pre-locking
       mode
     - make sure implicit commit of Events DDL works as expected.
     - add more tests
    
    
    Collateral clean ups in the events code.
    
    This patch fixes Bug#23631 Events: SHOW VARIABLES doesn't work 
    when mysql.event is damaged
    
    
    mysql-test/r/events.result:
      Update results.
    mysql-test/r/events_bugs.result:
      Update results.
    mysql-test/r/events_restart_phase1.result:
      Update results.
    mysql-test/r/events_restart_phase2.result:
      Update results.
    mysql-test/r/events_restart_phase3.result:
      Update results.
    mysql-test/r/events_scheduling.result:
      Update results.
    mysql-test/r/events_time_zone.result:
      Update results.
    mysql-test/t/events.test:
      Add new tests for tampering with mysql.event and some more
      tests for sub-statements, LOCK TABLES mode and pre-locking.
    mysql-test/t/events_bugs.test:
      Move the non-concurrent part of test for Bug 16420 to this file.
    mysql-test/t/events_restart_phase1.test:
      Rewrite events_restart_* tests to take into account that now
      we check mysql.event table only once, at server startup.
    mysql-test/t/events_restart_phase2.test:
      Rewrite events_restart_* tests to take into account that now
      we check mysql.event table only once, at server startup.
    mysql-test/t/events_restart_phase3.test:
      Rewrite events_restart_* tests to take into account that now
      we check mysql.event table only once, at server startup.
    mysql-test/t/events_scheduling.test:
      Add more coverage for event_scheduler global variable.
    mysql-test/t/events_time_zone.test:
      Move the non-concurrent part of the tests for Bug 16420 to 
      events_bugs.test
    sql/event_data_objects.cc:
      Move update_timing_fields functionality to Event_db_repository.
      Make loading of events from a table record more robust to tampering
      with the table - now we do not check mysql.event on every table open.
    sql/event_data_objects.h:
      Cleanup.
    sql/event_db_repository.cc:
      Now Event_db_repository is responsible for table I/O only.
      All the logic of events DDL is handled outside, in Events class please
      refer to the added test coverage to see how this change affected
      the behavior of Event Scheduler.
      Dependency on sp_head.h and sp.h removed.
      Make this module robust to tweaks with mysql.event table.
      Move check_system_tables from events.cc to this file
    sql/event_db_repository.h:
      Cleanup declarations (remove unused ones, change return type to bool
      from int).
    sql/event_queue.cc:
      Update to adapt to the new start up scheme of the Event Scheduler.
    sql/event_queue.h:
      Cleanup declarations.
    sql/event_scheduler.cc:
      Make all the error messages uniform:
      [SEVERITY] Event Scheduler: [user][schema.event] message
      Using append_identifier for error logging was an overkill - we may 
      need it only if the system character set may have NUL (null character)
      as part of a valid identifier, this is currently never the case, 
      whereas additional quoting did not look nice in the log.
    sql/event_scheduler.h:
      Cleanup the headers.
    sql/events.cc:
      Use a different start up procedure of Event Scheduler:
      - at start up, try to check the system tables first.
      If they are not up-to-date, disable the scheduler.
      - try to load all the active events. In case of a load error, abort
      start up.
      - do not parse an event on start up. Parsing only gives some information
      about event validity, but far not all.
      Consolidate the business logic of Events DDL in this module.
      Now opt_event_scheduler may change after start up and thus is protected
      by LOCK_event_metadata mutex.
    sql/events.h:
      Use all-static-data-members approach to implement Singleton pattern.
    sql/mysqld.cc:
      New invocation scheme of Events. Move some logic to events.cc.
      Initialize the scheduler before reporting "Ready for connections".
    sql/set_var.cc:
      Clean up sys_var_thd_sql_mode::symbolic_mode_representation
      to work with a LEX_STRING.
      Move more logic related to @@events_scheduler global variable to Events
      module.
    sql/set_var.h:
      Update declarations.
    sql/share/errmsg.txt:
      If someone tampered with mysql.event table after the server has
      started we no longer give him/her a complete report what was actually 
      broken. Do not send the user to look at the error log in such case,
      as there is nothing there (check_table_intact is not executed).
    sql/sp_head.cc:
      Update to a new declaration of 
      sys_var_thd_sql_mode::symbolic_mode_representation
    sql/sql_db.cc:
      New invocation scheme of Events module.
    sql/sql_parse.cc:
      Move more logic to Events module. Make sure that we are consistent
      in the way access rights are checked for Events DDL: always
      after committing the current transaction and checking the system tables.
    sql/sql_show.cc:
      Update to the new declarations of 
      sys_var_thd_sql_mode::symbolic_mode_representation
    sql/sql_test.cc:
      New invocation scheme of events.
    sql/table.cc:
      mysql.event is a system table.
      Update check_table_intact to be concurrent, more verbose, and less smart.
    sql/table.h:
      Add a helper method.
    mysql-test/r/events_trans.result:
      New BitKeeper file ``mysql-test/r/events_trans.result''
    mysql-test/t/events_trans.test:
      New BitKeeper file ``mysql-test/t/events_trans.test'':
      test cases for Event Scheduler that require a transactional
      storage engine.
    fa1d637e
events_scheduling.result 3.2 KB
CREATE DATABASE IF NOT EXISTS events_test;
USE events_test;
SET GLOBAL event_scheduler=OFF;
Try agian to make sure it's allowed
SET GLOBAL event_scheduler=OFF;
SHOW VARIABLES LIKE 'event_scheduler';
Variable_name	Value
event_scheduler	OFF
SET GLOBAL event_scheduler=1;
SHOW VARIABLES LIKE 'event_scheduler';
Variable_name	Value
event_scheduler	ON
SET GLOBAL event_scheduler=0;
SHOW VARIABLES LIKE 'event_scheduler';
Variable_name	Value
event_scheduler	OFF
SET GLOBAL event_scheduler=ON;
Try again to make sure it's allowed
SET GLOBAL event_scheduler=ON;
SHOW VARIABLES LIKE 'event_scheduler';
Variable_name	Value
event_scheduler	ON
SET GLOBAL event_scheduler=DISABLED;
ERROR 42000: Variable 'event_scheduler' can't be set to the value of 'DISABLED'
SHOW VARIABLES LIKE 'event_scheduler';
Variable_name	Value
event_scheduler	ON
SET GLOBAL event_scheduler=-1;
ERROR 42000: Variable 'event_scheduler' can't be set to the value of '-1'
SHOW VARIABLES LIKE 'event_scheduler';
Variable_name	Value
event_scheduler	ON
SET GLOBAL event_scheduler=2;
ERROR 42000: Variable 'event_scheduler' can't be set to the value of '2'
SHOW VARIABLES LIKE 'event_scheduler';
Variable_name	Value
event_scheduler	ON
SET GLOBAL event_scheduler=5;
ERROR 42000: Variable 'event_scheduler' can't be set to the value of '5'
SHOW VARIABLES LIKE 'event_scheduler';
Variable_name	Value
event_scheduler	ON
CREATE TABLE table_1(a int);
CREATE TABLE table_2(a int);
CREATE TABLE table_3(a int);
CREATE TABLE table_4(a int);
CREATE TABLE T19170(s1 TIMESTAMP);
SET GLOBAL event_scheduler=ON;
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 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
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 < 3, '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 < 3, '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'
"Should be preserved"
SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_NAME;
EVENT_NAME	STATUS
two_time	DISABLED
DROP EVENT two_time;
DROP TABLE table_1;
DROP TABLE table_2;
DROP TABLE table_3;
DROP TABLE table_4;
DROP DATABASE events_test;
SET GLOBAL event_scheduler=OFF;