Support for scheduled tasks in MySQL 5.1

I received a very interesting comment on my previous post, "Scheduling procedure execution in MySQL using SLEEP() and EXECUTE"

In 5.1 we have the following support:

SCHEDULE 
[ schedule-name ] 
{ START TIME start-time | BETWEEN start-time AND end-time } 
[ EVERY period { HOURS | MINUTES | SECONDS } ] 
[ ON { ( day-of-week, ... ) | ( day-of-month, ... ) } ] 
[ START DATE start-date ] 

So we are looking at scheduled event support directly in the database.

Cheers,

-Brian

(I assume Brian is actually Brian Aker from MySQL AB. If not, could the real Brian Aker correct me on that?)

I think this is very, vey interesting news. And I like the idea of defining scheduled tasks through a kind DDL statement too. Oracle jobs are different: you've got a package (basically, a container of types, variables, cursors, procedures, functions) called DBMS_JOB. You call procedures in this package to define the job, and this results in some data being written to a table, wich is used later on to run the jobs. Of course, the solution I presented was modelled after that.

I do hope that this syntax will allow us to have a job run, say, every 1st of the month. Oracle's DBMS_JOB can do this, because it allows you to specify the next date for job running in the form of a string that is evaluated as a date expression. So in Oracle,

    DBMS_JOB.SUBMIT(
        job       => v_job_id
    ,   what      => 'p_calculate_salaries'
    ,   next_date => to_date('2005-11-01','YYYY-MM-DD')
    ,   interval  => 'add_months(sysdate,1)'
    );

would run the job on the first of november for the first time, and each time the job runs, the next date to run the job would be calculated by evaluating the expression passed to the interval parameter. In this case, the current date plus one month.

Maybe the [ ON { ( day-of-week, ... ) | ( day-of-month, ... ) } ] could deal with a month?

But even the MySQL SCHEDULE syntax won't allow this, it still think it's a cool feature.

I also hope the scheduled tasks will be accessible through the data dictionary, or at least in the mysql database. I think that this willl probably be te case, I mean, MySQL will have to store the data associated with the schedule somewhere, so why not a table.

Knowing this functionality will become available in the near future, I probably won't be doing too much about my schedular. I'll probably build a small example, and make some additions available I made yesterday.

Anyway, I had fun building it. After all it is the first-not-totally-useless thing I built in MySPL, and it gave me to opportunity to work with SLEEP(), PREPARE and EXECUTE. And I learned to use GET_LOCK() and RELEASE_LOCK(). Maybe not too hot if you're a regular MySQL developer, but very useful when you aren't (yet) like me.