8

I'm currently writing my Main Assignment on my last semester at my study (IT-Engineering with Networking) and currently working with MySQL.

My question is: Is it possible to execute a Shell script/Command from within a MySQL Trigger/Procedure? Or can it be done from a CASE statement? I've been searching around the internet and read that it's inadvisable to do it. But I need a script to check a table in a database for alerts and then warn people if there is any. If there is anyway else this could be done, then I'm open for ideas. Any input will be appreciated :)

capacop
  • 83
  • 1
  • 1
  • 3
  • Mysql trigger/procedures only knows mysql commands and for them shell command will be like an `alian script`. However within a shell script mysql command could be executed. – Abhik Chakraborty Apr 23 '15 at 10:43
  • That sounds wildly inefficient. You're already in the database: you want to fork a shell script to connect to the database? Anyway, I found an interesting questions about [sending email from mysql](http://stackoverflow.com/questions/387483/how-to-send-email-from-mysql-5-1). – glenn jackman Apr 23 '15 at 10:58
  • Thanks for the link. It's looks interesting. What I want is the script to check everytime there is a new row and if there is any it has to call a VOIP phone. I already have the script for the phone running. But i can't find anyway to include MySQL in a script to check for those empty rows and then call if there is any. – capacop Apr 23 '15 at 11:05

1 Answers1

8

You can read this blog for triggering a shell script from MySQL: https://patternbuffer.wordpress.com/2012/09/14/triggering-shell-script-from-mysql/. To summarize, two options are presented:

  1. Polling. To improve performance, a trigger could record the change in another table which you poll instead.
  2. MySQL UDF. Write your own plugin, and beware of security implications!

I think for your requirement just write a python/php/perl script which will connect your MySQL DB and query the alert table for any alert and accordingly show warning message on the screen or send email/sms warning.

Roger Dueck
  • 615
  • 7
  • 16
Pranab Sharma
  • 729
  • 10
  • 12
  • Thanks for the link! :) I managed to poll the data from the table whenever a new row appears, and now it works! – capacop Apr 23 '15 at 12:56