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:scriptsmysql_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!
