Troubleshooting the MySQL Event Scheduler

I recently encountered a frustrating issue with the MySQL Event Scheduler that had me tearing my hair out. I spent countless hours trying to figure out why I couldn't create events and, even when I...

I recently encountered a frustrating issue with the MySQL Event Scheduler that had me tearing my hair out. I spent countless hours trying to figure out why I couldn't create events and, even when I could create them, why they wouldn't run. But fear not, I've found the solution and I'm here to save you from the same headache!

Can't Create Events

To run events using the MySQL Scheduler, you need to enable the scheduler itself. By default, this service is not enabled. There are a few ways to enable it. One option is to go into PhpMyAdmin (if you use it), select "Events", and then turn on the "Event Scheduler Status".

Troubleshooting the MySQL Event Scheduler Image: Troubleshooting the MySQL Event Scheduler

However, this method only works temporarily. Whenever you restart the MySQL service or your web server, the scheduler status will switch back to "Off". If you want to enable it permanently, follow these steps:

  1. Navigate to /etc/mysql/mysql.conf.d.
  2. Open the mysqld.cnf file.
  3. Under the [mysqld] header, add the following line: event_scheduler = on.
  4. Save the file and restart the MySQL service.

Once you've done this, check back in PhpMyAdmin and your Event Scheduler Status should be set to "On".

sudo service mysql restart

Correct Syntax

I also faced issues with syntax for a while. Every time I tried to create an event, either via the PhpMyAdmin GUI or the command line, I would encounter syntax errors. While most people may need recurring events, I only wanted to schedule one-time changes. After much trial and error, I finally found the syntax that worked for me:

CREATE EVENT Test1 ON SCHEDULE AT '2016-10-22 10:30:00' ON COMPLETION NOT PRESERVE ENABLE DO UPDATE users SET isFeatured = 1 WHERE id = 117;

Most of the above syntax is self-explanatory. This example will create a scheduled event that runs only once at 10:30 AM on October 22, 2016. After the event has executed, it will not be preserved (meaning it will be deleted), and the event is currently "Enabled" to run at the specified date and time. The last line, starting with "DO", will execute your desired code.

Created Events Either Don't Show or Have "Disabled" as the Status

Troubleshooting the MySQL Event Scheduler Image: Troubleshooting the MySQL Event Scheduler

This is the issue that really had me scratching my head. I couldn't understand why my events wouldn't show up under the "Events" tab in PhpMyAdmin, or if they did show up, why they would be labeled as "DISABLED" even though I had explicitly enabled them.

In our previous SQL syntax, I discovered that if I changed ON COMPLETION NOT PRESERVE ENABLE to ON COMPLETION PRESERVE ENABLE, at least my events would show up (which is a start). But no matter what I tried, they would never show as "ENABLED". Then, I stumbled upon this crucial piece of information in the MySQL documentation:

AT timestamp is used for a one-time event. It specifies that the event executes only once at the date and time given by timestamp, which must include both the date and time or be an expression that evaluates to a datetime value. You may use a value of either the DATETIME or TIMESTAMP type for this purpose. If the date is in the past, a warning occurs.

This made me wonder, "Am I getting any errors?" And sure enough, SHOW WARNINGS revealed this error message:

mysql> SHOW WARNINGS;
*************************** 1. row ***************************
Level: Note
Code: 1588
Message: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.

I then thought, "But wait, I always set my events to be scheduled in the future. How could it be in the past?" This error message clarified why my events weren't appearing (because they were being dropped immediately after creation) and why they wouldn't fire if they did show up. It turned out that my system time was incorrect.

Change Time Zone

mysql> SELECT NOW();
+-+
| NOW()                |
+-+
| 2016-10-22 16:59:35 |
+-+
1 row in set (0.00 sec)

Even though it was only 10:30 AM in my time zone, the events were being scheduled to run 6 hours in the past, resulting in them being dropped and/or disabled.

To fix this, go back to the mysqld.cnf file, edit it, and add the following line:

default-time-zone='-6:00'

Since I'm in the Mountain Time zone, I use -6:00 for my UTC timestamp, but you'll need to adjust it accordingly. You can find a list of possible values here and here. After saving and closing the file, run the following command to restart the MySQL service:

sudo service mysql restart

Now, log back into your MySQL instance and run SELECT NOW(); to ensure that the correct time is displayed:

mysql> SELECT NOW();
+-+
| NOW()                |
+-+
| 2016-10-22 11:02:16 |
+-+
1 row in set (0.00 sec)

Creating events should now be a piece of cake. With the MySQL instance now having the correct time, events should work as expected as long as they're scheduled in the future.

1