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;
