Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted June 18, 2012

Email Job Failures Report to DBA using PowerShell

By Rob Gravelle

MySQL is a bit of a strange animal in terms of scheduled task processing.  While it did introduce its own brand of job scheduling called Events in version 5.1, some Database Administrators (DBAs) feel that it isn't quite ready for prime time where robustness and error handling are concerned.  For those reasons, scripting languages like UNIX shells, PHP, and Wsh have remained the standard.  In today's article, I'd like to present a hybrid solution that uses MySQL Event Scheduling to manage the batch jobs and Windows PowerShell for the error handling.

MySQL Event Weaknesses

There are three prominent weaknesses in MySQL Events as they currently exist.  One is that they have no access to the host operating system. As a result, they cannot write files, run applications, or send out an email when something goes wrong.  Another caveat is that referential errors are not detected until runtime, so if you misspell a table name, you won't find out until the Event runs.  Of course the workaround here is to test your queries before placing them in an Event. Last but not least, error handling is not optimal; all errors go to log files and the Windows Event Log, so there's no way to see them from a console.  But that's OK because PowerShell can make up for the area where MySQL events are lacking.  It can read the Windows Event Log and send emails when an error occurs.

Monitoring Active Processes

The SHOW PROCESSLIST command lists all the currently running processes on the server.  When the Event Scheduler is running or has run, it shows up as well:

Id User             Host           db   Command Time State                        Info
35 root             localhost:1973 NULL Sleep   179  NULL
36 root             localhost:1974 test Query   0    NULL                         "SHOW FULL PROCESSLIST"
37 event_scheduler  localhost      NULL Daemon  6    "Waiting for next activation" NULL

That can be used as a first red flag.  The absence of the Event Scheduler from the list means that they probably aren't running.

A Sample MySQL Event

There are a lot of options that can be set when you create an Event, but we're not going to cover every permutation here today.  Rather, our focus will be on the error handling aspect of Events.  Specifically, this sample will show how to retrieve error information using PowerShell.

Our sample Event will run every thirty minutes and add a row to the process_counter table with a count of the currently running processes:

delimiter |
CREATE EVENT MONITOR_PROCESSLIST
ON SCHEDULE EVERY '30' MINUTE
ON COMPLETION PRESERVE
DO 
BEGIN
   INSERT INTO process_counter
      SELECT CURRENT_TIMESTAMP, COUNT(*)
      FROM   INFORMATION_SCHEMA.PROCESSLIST;
END |
delimiter ;

Monitoring Events

Like SHOW PROCESSLIST, the SHOW EVENTS command returns a list of all running Events on the server.  It gives us more detailed information about each Event, including whether or not the eEvent is recurring, and if so, how often:

Db,Name,Definer,"Time zone",Type,"Execute at","Interval value","Interval field",Starts,Ends,Status,Originator,
 character_set_client,collation_connection,"Database Collation"
 
 test,MONITOR_PROCESSLIST,root@localhost,SYSTEM,RECURRING,NULL,1,MINUTE,"2012-05-31 13:01:21",NULL,
 ENABLED,0,utf8,utf8_general_ci,latin1_swedish_ci

Normally, once an Event has expired, it is immediately dropped so only active and/or recurring Events would show up in the list. You can override this behavior by specifying ON COMPLETION PRESERVE in the Event declaration as we did above.

Error Handling

Now we're getting to the crux of the matter: how to know when something goes wrong.  There is some error handling built into Events via the "declare exit handler" clause:

BEGIN
  declare exit handler for sqlexception 
    insert into event_failures values('event_which_logs_failure',
                                       current_timestamp); 

  INSERT INTO process_counter
  ...

 END |
 

pan>

It's of fairly limited use however because there is no way to retrieve any details about the error; the only thing we can do is log the error to a table via a local insert statement, as above, or by calling a stored routine with our own parameters:

CREATE EVENT e_call_myproc

BEGIN
declare exit handler for sqlexception
    Do Call error_proc('event_which_logs_failure',
                                                          current_timestamp);

A Better Way

Although we can't glean any information about errors from within the Event itself, we can look at the MySQL logs and the Windows Application Event Log.  As it happens, this is an area where PowerShell shines.  By having it run as a Scheduled Task on your O/S, you can let it monitor the MySQL Events for errors. 

Here's a command that retrieves the last 20 MySQL errors from the Windows Application Event Log relating to the Event Scheduler:

PS H:\> Get-Eventlog Application -newest 20 | where {$_.Source -match "MySQL" -and $_.Message -like "Event Scheduler*"} | ft -wrap
 
Index Time          EntryType   Source  InstanceID Message
----- ----          ---------   ------  ---------- -------
6437  May 31 13:10  Information MySQL   3221225572 Event Scheduler: [root@localhost].[test.MONITOR_PR
                                                   OCESSLIST] event execution failed.
                                       For more information, see Help and Support Center
                                                   at http://www.mysql.com.
 
6436 May 31 13:10  Error       MySQL    3221225572 Event Scheduler: [root@localhost][test.MONITOR_PRO
                                                   CESSLIST] Table 'test.process_counter' doesn't exist
                                       For more information, see Help and Support Center
                                                   at http://www.mysql.com.
 
6435 May 31 13:09  Information MySQL    3221225572 Event Scheduler: [root@localhost].[test.MONITOR_PR
                                                   OCESSLIST] event execution failed.
                                       For more information, see Help and Support Center
                                                   at http://www.mysql.com.
 
6434 May 31 13:09  Error       MySQL    3221225572 Event Scheduler: [root@localhost][test.MONITOR_PRO
                                                   CESSLIST] Table 'test.process_counter' doesn't exist
                                       For more information, see Help and Support Center
                                                   at http://www.mysql.com.
 

The Message field is where most of the information is to be found.  I piped the results to the Format-Table cmdlet with the –wrap flag so that the full message text is displayed.  Without that code, the messages are truncated.

As you can see, the Message contains the Event owner ([root@localhost]), the database and Event names ([test.MONITOR_PROCESSLIST]), and the cause of the error (Table 'test.process_counter' doesn't exist).

Emailing Reports

It just so happens that the .NET Framework has some classes for sending emails. The tricky part is determining whether or not we need to send an email.  That's done by piping the errors to the measure-object cmdlet and checking its count property. Anything greater than zero means that errors were found:

$emailFrom  = "MySqlEventMonitor@acme.com"
$emailTo    = "dba@acme.com"
$subject    = "MySQL Event Error Occured"
$smtpServer = "SMTPSERVER.ONPROD"
$errors     = Get-Eventlog Application -newest 20 
               | where {$_.Source    -match "MySQL" 
                   -and $_.EntryType -match "Error" 
                   -and $_.Message   -like  "Event Scheduler*"} 
               | ft -wrap
 
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer)
{
  $smtp = new-object Net.Mail.SmtpClient($smtpServer)
  $msg  = new-object System.Net.Mail.MailMessage($emailFrom, $emailTo,
                                                  $subject, $body)
 
  $smtp.Send($msg)
}
 
if (($errors | measure-object).count -gt 0) {
  sendEmail $emailFrom $emailTo $subject $errors $smtpServer
}
 

Automating the Monitoring Process using the Windows Task Scheduler

To run the script on a recurring basis, you need to set up a task using the Windows Task Scheduler.  Pass the powershell.exe the name of the script you want to run along with some supplementary arguments using the 'Add Arguments' field:

–Noninteractive –Noprofile -command "& 'c:\scripts\mysql_event_monitor.ps1'"

Notice that before you pass the script name you have to prepend an ampersand '&' symbol and enclose the entire script path in double quotes. Omitting any of those will cause your script to not run. For more information on configuring the Windows Task Scheduler to run PowerShell scripts see the Techhead site.

Finally, don't forget to run the command "set-executionpolicy RemoteSigned" on your PowerShell command prompt once to set up the necessary permissions or the script execution will be blocked.

Conclusion

Now that was a fairly basic example of a PowerShell MySQL Event Monitoring Service.  It could be expanded to verify the date and time of errors so as to filter them by day, as well as to remove entries from the log once they've been reported on.  The good news is that now you can use MySQL events in your database with a reasonable degree of confidence in their stability!

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM