Friday, April 29, 2011

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

3 comments:

Daniƫl van Eeden said...

Events are better than cron because:
- They're in your database backup.
- They're replicated (and disabled on the slave)
- They'll work on UNIX and Windows.

The only issue is that you can't send alert emails or snmptraps from MySQL easily. I've started an UDF to do that, but it's only in a very very early development stage.
udf-smtp.c

How do you manage alerting for your events?

Anonymous said...

A workaround for that bug can probably be done with GET_LOCK so that only one instance of event may be executed. Just check IS_USED_LOCK in your event procedure and if that returns 1, then don't run your job. Of course make sure your event calls GET_LOCK/FREE_LOCK with a named lock.

Anonymous said...

Hello friend .......... Nice blog . Keep it up..........
Edegra