MySQL Event Scheduler - Is it Enterprise Ready?
I have been taking a serious look at the event scheduler in MySQL 5.1, and I thought I would share some of my initial impressions. There is no doubt that having an event scheduler is a great bonus for the database. It’s usefulness in the enterprise, however, may be limited. While we are preparing to move forward with it, it hasn’t been without struggle. I will describe the pros and cons we’ve encountered so far.
The first point I want to make is what I believe to be the single most important reason we want to use the event scheduler - portability. In the absence of a proper scheduler, most people have been relying on cron + scripting to run scheduled tasks in the database. This is a relatively straightforward approach on a simple architecture. On our active/passive cluster it proved to be a little problematic at first. We had a copy of each script on both nodes, and just uncommented the crontab on the passive node. Every time there was a fail over, we’d have to make sure to get back into both nodes and either comment or uncomment the crontab. Soon we got smart and had our scripts check for the mysql.sock file before proceeding. But still, we have two copies of every script and we need to make sure they are in sync. With the event scheduler, all this now resides inside the database itself. When the cluster fails over and the database moves, the events move with it. We have one copy of the event in production at any given time.
Initial setup and creating your first event is simple. This could arguably be simpler than setting up replication. An experienced DBA should be able to spend 5 minutes looking at the documentation and have a working event within another 5 minutes. And as far as actual scheduling is concerned, there are a plethora of options. I have events recurring hourly, daily, monthly, and every 15 minutes. As for the contents of an event, you aren’t limited to simple SQL statements - you can use flow constructs, and you can do compound statements using BEGIN and END. About the only thing you can’t do is feed an event an input variable or take an output from it. Since I am replacing cron jobs with my events, I already have stored procedures set up to do the heavy lifting, so my events just simply call a stored procedure.
Now that the events are scheduled, the next task is to see that they ran. That’s really where my trouble started. How do I monitor events and make sure they are working? I googled it. I didn’t come up with much. I saw a last executed field in the SHOW EVENTS syntax - this isn’t easily accessible for scripting, and I’m not going to be manually checking my events all day long. A closer look at the docs showed that “A record of events executed on the server can be read from the MySQL Server’s error log”.
I smelled trouble in paradise. I reached out for my pager and grabbed it just as it started going off. My log scraper periodically checks to make sure the error log is empty. There should never be anything in that file - the presence of a single line of text means that bad things have happened. Spurious logging to the error log is a pet peeve of mine. The error log is for errors. Don’t send me false positives (Oh, that’s a known error, you can ignore it) and don’t send me non-errors. There’s a general log for that. Better yet, let’s create an event log. And let’s have the option to turn that off and on and also log it to a table (new 5.1 features for slow and general log that are very welcome). So the log scraper has been modified and now looks for specific contents in the error log - I’m not too happy about this, but it works. I’m sure it will bite me in the backside when some other new feature logs a critical error that my scraper does not see.
The last thing I want to do is to add some robustness to my events. After all, with events and stored procedures my database is more like an application unto itself. We need error handling! In our cron jobs we are using perl. If the job calls a stored procedure and subsequently gets an error, perl captures the error and sends it to us an email. I want the event scheduler to do the same thing. The first thing I did was get horribly sidetracked and make a UDF that allows mysql to send email. But in trying to implement it within the event scheduler I get into the old catch22. If I handle the exception, I can’t access SQLCODE or SQLERROR from within MySQL. It’s the same issue with error handling in stored procedures. I do have the option to include exception handling for every possible exception within event, but that’s really not that realistic. I can write a general exception handler that sends me an email, but when I do that, I lose the detail about the exception. Having a notice that there was an error does me no good if I can’t see what that error was. It’s kind of sad that every client I can think of can capture and handle mySQL errors this way, yet the internal server processes cannot.
I realize that the error handling is a problem with MySQL server internals and it’s not fair to ding the event scheduler implementation because of it. The logging thing is another issue, but it’s hardly a deal kill. Our implementation isn’t the best case scenario, but weighing the balance against cron jobs and perl scripts, we’re probably dead even. Once 5.1 becomes stable I look forward to using it.
