Mysql – Where EVENTS history are logged/recorded in MySql

eventjobsMySQL

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?

Best Answer

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.

Related Solutions

Sql-server – SSIS Package Validation Error when Executed From Windows Task Scheduler

I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.

Summary of solution:

SQL Job with one Job Step
The Job Step executes the SSIS package

To run a SQL Job with a custom you must also create:

an instance-level Credential
a Proxy Account which should be mapped to the Credential

I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!

For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.

Hope this helps someone else in the same situation -

SQL Server Jobs – Can You Schedule a Job Inside Another Job?

In more research and diving into the msdn site documentation I have found and am now using.

sp_add_schedule, sp_attach_schedule and sp_detach_schedule, sp_delete_schedule

so when the job sees that others are running ATM I am doing this:

exec msdb.dbo.sp_add_schedule [MSDN link][1]
   @schedule_name = @TempScheduleName,
   @enabled = 1,
   @freq_interval = 1,
   @freq_type = 1,
   @freq_subday_type = 4,
   @freq_recurrence_factor = 1,
   @freq_subday_interval = 5,
   @active_start_date = @todaysdate,
   @active_start_time = @todaystime (plus 5min)


exec msdb.dbo.sp_attach_schedule [MSDN Link][2]
   @job_name = @JobName',
   @schedule_name = @TempScheduleName

then the next time the job is run this is also done to remove the temp schedule:

   exec msdb.dbo.sp_detach_schedule
    @job_name = @JobName,
    @schedule_name = @TempScheduleName

   exec msdb.dbo.sp_delete_schedule
   @schedule_name = @TempScheduleName

Best Answer

Related Solutions

Sql-server – SSIS Package Validation Error when Executed From Windows Task Scheduler

SQL Server Jobs – Can You Schedule a Job Inside Another Job?

Related Question