EVENTs - The hidden MySQL 5.1+ feature

MySQL 5.1 and up has EVENTs, but this features seems to be little used. I started using them some time ago, and once I was using them, I found them more and more useful. There are a few bugs with them, I reported this one for example, and this alone tells me that probably not too many use this feature.

I will post a few example soon of things I use EVENTs for, but right now, let me tell you a few basics:

• One IMPORTANT reason I like them: In comparison to, say, crontab jobs, I run them inside the database itself, which I do not have to put MySQL passwords in files or scripts on the server. BIG advantage.
• For anything but the most simple of actions, write a procedure and then call the procedure from the EVENT, instead of having everything in the event itself, this just makes things difficult to manage.
• Error handling: the MySQL server error-log is your friend!
• Using the INFORMATION_SCHEMA tables instead of SHOW commands, where possible, making writing events easier and in many cases, this is the only way to do things. Simple cursors on INFORMATION_SCHEMA tables is the way to go. And have a second look at the INFORMATION_SCHEMA tables, there has been a whole bunch added over time since 5.1.
• The syntax is sometimes weird, but once you get used to it, it works.
• Have a look at the bug mentioned above. It may well bite you during development (which is one reason I recommened using a procedure at first, and then one this works as expcted, create the EVENT that calls this).
• For a DBA, events are cool and useful, and easier to use an crontab and also cross platform. As long as what you are doing can be done in a procedure, chances are that EVENTs are better than crotab jobs.
• The event scheduler must be running for EVENTS to work, and this is done by setting the variable event_scheduler to 1. Note that this is a dynamic variable, so this is valid syntax:
  SET GLOBAL event_scheduler=1;

/Karlsson
See you with some more event information soon