Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I just setup some stored procedures to run in the event scheduler using CREATE EVENT.

I'm trying to find where (if somewhere) the history of it running is stored. I looked at the docs but couldn't find anything.

Is there some table or log where I can see that my scheduled events successfully ran?

Thanks!

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

There is no log for scheduled events, unless they throw exceptions... in which case, you'll find an error written to the standard MySQL "hostname.err" error log. If there isn't an error, and they aren't still running, then they processed. Each event gets a new thread id in the processlist each time it fires and the thread is destroyed when the event is done.

One option you could use is creating a table for the purpose, and inserting log entries into that table from within the body of the event, or the procedure that the event calls.

Inside a running event, the CONNECTION_ID() function returns the id of the thread where the event is running, and you can use this function in queries that do your logging, if that's useful, such as inserting a log entry including that value when the event starts, and updating that log entry when the event is almost done.

Another option is a bit of a hack (some would say), but it also works -- you can generate a "warning" in the MySQL error log from within the event or procedure fired by the event by raising a SQL warning at the end of the event. Note that this only writes to the error log from within an event, and that's because there is no client connected.

SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'hey, this job ran';

On some of my systems where the MySQL error log is monitored by an external process "tailing" the error log file, I have scheduled events whose only purpose is to write a warning like this to the error file periodically, so that the external process never goes more than so many minutes without seeing "something" in the often-quiet error log, to keep it happy that it is properly seeing the correct log file growing.

share|improve this answer
    
Thank you. I'm a Sql Server guy and Sql Server has tools to manage this kind of historic data. I'm only using MySql more like a "hard user" now and the conclusion I'm making is that it's pretty simple and plain in the sense that it does not provide a lot of management stuff that (IMO) should be built in. That being said, is there any "plugin" or best-known way to do this without doing it manually like you propose? –  tucaz Oct 15 '13 at 17:08
    
No history is kept of scheduled events. The mcafee audit plugin seems like a good bet for something that would have access to the right part of the internals to build an event scheduler history, but that's a guess. I'm not aware of a best-known way. In my systems, some events have their own history tables, others write to the error log. I'm not entirely sure what all the management stuff is that you're referring to since I haven't touched a SQL Server in years (woohoo!), but if you use a mouse to administer your DBMS, something's wrong. (IMHO) :) –  Michael - sqlbot Oct 16 '13 at 0:31
    
In SqlServer you can use the GUI (Sql Management Studio) or look for the job history in a system table (technet.microsoft.com/en-us/library/ms174997.aspx) which is what I was looking for –  tucaz Oct 16 '13 at 1:47
    
Yes, a similar table could come in handy. I don't know the internals well enough to know what the obstacles might be to writing a plugin to accomplish something along those lines but it might make an interesting project. At the moment, though, I'm not aware of any mechanism to accommodate this. –  Michael - sqlbot Oct 16 '13 at 2:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.