Monday, May 13, 2013

Using the MySQL event scheduler

The MySQL event scheduler  is very effective for scheduling different things as you need them.
Below is a simple example of how to use events and also how you can use the event scheduler differently across a master & a slave.

First check the status of your event scheduler:



> show variables like '%event%';
+---------------------------------------------------+-------+
| Variable_name                                     | Value |
+---------------------------------------------------+-------+
| event_scheduler                                   | OFF   |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size      | 10    |
+---------------------------------------------------+-------+



To turn it on you can use the following command or you can also set it in your my.cnf file.

set GLOBAL event_scheduler=ON;


I will create a database just for this demo


create database events_test;
use events_test;



For a test on the master I will use this simple table in the events_test database.


CREATE TABLE `foobar` (
  `time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


I like to have my events run stored procedures because I can test the procedure easily before pushing it into an event.

delimiter //
CREATE PROCEDURE mastereventtest()
     BEGIN
        insert into foobar values (NOW());
     END//
delimiter ;


Now I will have this event run every minute just for this demo



CREATE EVENT mastereventtest
        ON SCHEDULE EVERY 1 MINUTE
        COMMENT 'testing master events'
        DO
        call mastereventtest();





Confirm the that it is in the system.



>  show create event  mastereventtest\G
*************************** 1. row ***************************
               Event: mastereventtest
            sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `mastereventtest` ON SCHEDULE EVERY 1 MINUTE STARTS '2013-05-12 21:25:22' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'testing master events' DO call mastereventtest()
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci






> show events \G
*************************** 1. row ***************************
                  Db: events_test
                Name: mastereventtest
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2013-05-12 21:25:22
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci




I will also create an event on the slave and since I plan on doing that I need to disable the events that I want off. So I will alter the event mastereventtest on the SLAVE server.


ALTER EVENT mastereventtest disable on slave;



I will again create a different table on the slave only under the events_test database, that was replicated


CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);


Again I like to create a procedure to use with my events. This is not required but just makes it easier to test.

delimiter //
CREATE PROCEDURE slaveeventtest()
     BEGIN
        insert into foo values ();
     END//
delimiter ;


This will be the event I run on the slave


CREATE EVENT slaveeventtest
        ON SCHEDULE EVERY 2 MINUTE
        COMMENT 'testing master events'
        DO
        call slaveeventtest();





Confirm that is also made it into the system



> show create event  slaveeventtest\G
*************************** 1. row ***************************
               Event: slaveeventtest
            sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `slaveeventtest` ON SCHEDULE EVERY 2 MINUTE STARTS '2013-05-12 21:14:08' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'testing master events' DO call slaveeventtest()
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci





> show events\G

                Db: events_test
                Name: slaveeventtest
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 2
      Interval field: MINUTE
              Starts: 2013-05-12 21:14:08
                Ends: NULL
              Status: ENABLED
          Originator: 3
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci





You can also query the information schema for event information




> select * from information_schema.EVENTS\G





To debug an event the best location to check is your error log.

Keep in mind that when you check the events on the slave you will see both events



> > show events \G
*************************** 1. row ***************************
                  Db: events_test
                Name: mastereventtest
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2013-05-12 21:25:22
                Ends: NULL
              Status: SLAVESIDE_DISABLED
          Originator: 3
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
                  Db: events_test
                Name: slaveeventtest
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 2
      Interval field: MINUTE
              Starts: 2013-05-12 21:14:08
                Ends: NULL
              Status: ENABLED
          Originator: 3
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
2 rows in set (0.01 sec)






Now did it work?

If you execute a show processlist you will be able to see the current event thread status  and you can read more about the state results here:


You should be seeing : Waiting for next activation
I also have results in the tables:



root@localhost [events_test]> select * from foobar;
+---------------------+
| time_recorded       |
+---------------------+
| 2013-05-12 21:25:22 |
| 2013-05-12 21:26:22 |
| 2013-05-12 21:27:22 |
| 2013-05-12 21:28:22 |
| 2013-05-12 21:29:22 |
| 2013-05-12 21:30:22 |
| 2013-05-12 21:31:22 |
| 2013-05-12 21:32:22 |
+---------------------+
8 rows in set (0.00 sec)

root@localhost [events_test]> select * from foo;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+
8 rows in set (0.00 sec)








To Drop or Disable Events:

DROP EVENT IF EXISTS mastereventtest;
ALTER EVENT mastereventtest disable;