Sunday, October 09, 2005

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.

7 comments:

Anonymous said...

Hi,
you may find the documentation about temporal triggers (as they are known in MySQL) here :
http://hristov.com/master_thesis_final.pdf

Cheers,
Andrey

rpbouman said...

Anrey, thank you so much!

I just downloaded your thesis, and scanned through chapters 1,2,3 and some of 5. Excellent, you answered all my questions, and gave me a lot of info I didnt know. For example, I really didnt know about DBMS_SCHEDULER; Also, I was totally unaware of the 3-layered approach used by MSSQL.

Now I'm even more so excited to see it in action. Thanks again!

Anonymous said...

Hi Roland,
I digged up the slides of the thesis defense. They include an example of how to use (a bit patched mysqld, because at the time of the writing in SP "SHOW STATUS" wasn't allowed) to record the health of a server without external agent.

http://hristov.com/thesis_presentation.pdf

rpbouman said...

Thanks Andrey, I just downloaded the slides. Excellent "state of the art slide".

Yesterday, I tried to find out when the first release of the 5.1 server product is planned, but I could not find it. Do you know when?

Anonymous said...

Roland,
I cannot tell exactly. It was developed for 5.0 but then it was late for inclusion in 5.0. Backporting to 5.0 will be possible till some moment of time but only left to the brave users which know what they are doing.

Cheers,
Andrey

rpbouman said...

Ok, thank you Andrey.

I'm not that brave, I think I'll just wait 'till MySQL AB thinks it's ready. But when that's the case, I'll be on it, testing and stuff.

Good luck, and thank you for the replies and sources. I liked reading your thesis a lot, thanks again!

Anonymous said...

Hi!

It will go into the 5.1 tree as soon as it is reviewed.

Cheers,

-Brian

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...