Troubleshooting the MySQL Event Scheduler

I ran into a rather frustrating issue the past few days regarding the MySQL Event Scheduler that had me pulling my hair out. I spent hours upon hours trying to diagnose problems as to why I 1) couldn't create events in the first place, and 2) once I could create them, why they wouldn't run. To hopefully save you my headache, here's the solution:

Can't Create Events

In order for events to run via the MySQL Scheduler, the scheduler itself has to be enabled. It's a service that's not enabled by default. There are a few ways to do this. One, you can go into PhpMyAdmin (if you use this), select "Events", and then select "On" under "Event Scheduler Status".

Now, this works great momentarily, until either you restart the MySQL service or restart your web server. Upon restart, this slider will switch back to "Off". To enable it permanently, navigate to:

/etc/mysql/mysql.conf.d

Then, edit the mysqld.cnf file. Somewhere underneath the [mysqld] header, add the following line:

event_scheduler = on  

Like so:

[mysqld]
#
# * Basic Settings
#
user        = mysql  
pid-file    = /var/run/mysqld/mysqld.pid  
socket        = /var/run/mysqld/mysqld.sock  
port        = 3306  
basedir        = /usr  
datadir        = /var/lib/mysql  
tmpdir        = /tmp  
lc-messages-dir    = /usr/share/mysql  
skip-external-locking  
event_scheduler = on  

Be sure to restart the mysql service, and you should be in business. Now, check back on PhpMyAdmin and your Event Scheduler Status should be set to "On".

sudo service mysql restart  

Correct Syntax

I had problems with syntax for a while there, too. Everytime I attempted to create an event, either via the PhpMyAdmin GUI, or straight from the command line, I would get syntax errors. Most people probably need to have recurring events, but I only wanted to schedule one-time changes. This was the syntax that finally 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 is fairly self-explanatory, but this will create a scheduled event that will run just once at 10:30am on 10/22/2016. When the event has fired off and is done, it will not preserve the event (meaning, it'll be deleted), and the event is currently "Enabled" to run once it hits this date and time. Then, the last line, beginning with "DO" will run your desired code.

Created events either don't show, or have "disabled" as the status

This was the one that REALLY got me. I was so frustrated as to why my events would either not show up at all under the "Events" tab in PhpMyAdmin, or if they would show up, they would be set to "DISABLED", even though I explicity marked them as enabled.

In our above SQL syntax, I found that if I would change

ON COMPLETION NOT PRESERVE ENABLE  

to

ON COMPLETION PRESERVE ENABLE  

my event would at least show up (which is a start), but no matter what I tried, it would never say "ENABLED". Then, I stumbled across this beautiful piece of information in the MySQL documentation:

AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp, which must include both the date and time, or must be an expression that resolves 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.

Which made me wonder, "Am I getting any errors?" Sure enough, SHOW WARNINGS\G showed me this error:

mysql> SHOW WARNINGS\G  
*************************** 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 explains why our message is both not appearing (because it's being dropped immediately after creation), and why our event is not firing if it DOES show up. My time was set wrong.

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:30am my time, my events were being scheduled to fire 6 hours in the past, meaning they would just be dropped and/or disabled.

Head back on over to the mysqld.cnf file, edit it, and then add the following line:

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

Because I'm Mountain Time, I use -6:00 for my UTC timestamp, but you'll obviously need to edit that accordingly. A list of possible values can be found here and here. After saving and closing out that file, run a simple

sudo service mysql restart  

and we'll be back in business. Now, log back into your MySQL instance and run a SELECT NOW(); and you should be showing the correct time:

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.