My So-Called Blog

If you’ve been using the Event Scheduler that was introduced in MySQL 5.1.6, there are some changes in the workings of the event_scheduler server variable. These changes are fairly simple — but not trivial — and were just committed to the 5.1 tree a few days ago. They will definitely be part of the official 5.1.12 release — which should be out just about any day now — so you’ll want to be aware of them, and how they’ll affect the behaviour of the Event Scheduler, before you upgrade.

This variable determines whether the Event Scheduler is available, and whether it’s actually running. In MySQL 5.1.12, it can be used in either of two ways:

1. a server option that can be used from the command line (--event-scheduler), or in the my.cnf file (event-scheduler)
2. a global variable event_scheduler that you can get and set within mysql or any other MySQL client.

As a server option, it can take one of three values:

• OFF: The Event Scheduler is available, but the event scheduler thread does not run, and no scheduled events are executed. This is the default state of the Event Scheduler.
• ON: The Event Scheduler is active, and events are executed according to their schedules.
• DISABLED: The Event Scheduler is not available, and cannot be made available without restarting the MySQL Server.

As a global variable, event_scheduler can only be toggled between ON and OFF.

You can read the Event Scheduler’s state from a client app using either of these two queries:

• SELECT @@GLOBAL.event_scheduler; (you can actually just use @@event_scheduler instead of @@GLOBAL.event_scheduler here, but the value is always the same as the global value).
• SHOW VARIABLES LIKE 'event%' ;

You can set it from a MySQL client with the following restrictions:

• The current value must be one of ON or OFF. If the current value is DISABLED, then any attempt to set it from the client fails with an error. In other words — if MySQL was started with the --event-scheduler=DISABLED command-line option, or if event-scheduler=DISABLED is present in my.cnf — then you can’t change event_scheduler at all from mysql (or any other MySQL client).
• You cannot set event_scheduler to DISABLED from a MySQL client, either. You must restart the server, making the change from the command line or in my.cnf before doing so.
• event_scheduler must be set as a global variable. If you attempt to set it as a session variable (either implicitly or explicitly, using SESSION), then the SET statement will fail with an error.
• The previous item implies that you must have the SUPER privilege to change the value of event_scheduler at all, just as with any other global variable in MySQL.

This variable/option is no longer numeric (as in 5.1.11), but rather enumerated. It is possible to use 0 instead of OFF, and 1 instead of ON, but I don’t recommend it. This is because, there’s no numeric alias for DISABLED, and trying to set event_scheduler to any value other than 0 or 1 now causes an error, as an esteemed colleague of mine discovered while I was away on holiday, before I had a chance to update the Manual. The output from the SHOW or SELECT statements shown above will always yield one of the values ON, OFF, or DISABLED…

…As soon as the fix for Bug #22662 — now pending — is merged, that is. Sorry about that, Andrey. ;)