Alert for MySQL jobs

Question

This might be either a simple or exceedingly complex question and searching has produced no results.

I use Sequel Pro on OSX to run a lot of queries some of which can take, e.g. >8 hours. A lot of times I can't predict how long a job will take, however. I was wondering if there was a way either in MySQL in general, OSX, MySQLWorkBench, or Sequel Pro to set up a pop-up alert/e-mail/ANYTHING that will alert me to when the query is complete. This might sound like an odd request but it would be extremely helpful to know as I like to do other tasks when my jobs are running in the background. I am well aware that this might just not be possible.

Any insights would be much appreciated,

Ben

Answer 1

Just to give you an idea. Since you're on a Mac you can use osascript to produce a pop-up window like this

$ osascript -e 'tell app "System Events" to display dialog "Hello World" buttons "OK" default button 1'

Now you can create a simple script to execute your sql jobs. Let's call it mysqlnotify

#!/bin/bash
/usr/local/mysql/bin/mysql -uusername -puserpassword dbname < $1 > $2
/usr/bin/osascript -e 'tell app "System Events" to display dialog "Your SQL job has finished" buttons "OK" default button 1 with title "mysqlnotify"'

Make it executable

$ chmod +x ./mysqlnotify

Then create your SQL query in your favorite editor/IDE (Sequel Pro, MySQL Workbench, TextMate,... whatever have you) and save it to a file. Let's call it sleep.sql

SELECT 'Your results', SLEEP(5) FROM dual;

To execute your query open a new terminal window and do

$ /path/to/mysqlnotify sleep.sql sleep.txt

When your query is done working you'll get a pop-up window

+----------------------------+
| mysqlnotify                |
+----------------------------+
| Your SQL job has finished  |
|                            |
|                     | OK | |
+----------------------------+

And results of your query will be saved in sleep.txt