Invoke shell from MySQL trigger

No I am not insane, and after a long days search over the wide Internet, even google admitted defeat, there seemed to be no way to do this. Finally I had already dropped the idea or even let off the thing altogether. But recently for another project I needed to check in for an entirely different requirement and stumbled on the fact. And yes I checked it, voila the shell invoke from mysql trigger is possible.

Very simple, though I used some code from here and there, and some other ideas too. Also I am blessed with plenty of dedicated servers, as well most of our office desktops are running Ubuntu, it was not a difficult matter for me to identify a test machine and do the tests. What I was trying to achieve was to post a calculated value to a remote web application when a row was updated in our mysql db. Well the best method was to invoke a curl or lynx cli command from the after insert , after update trigger on the table. But alas the mysql server was not capable of doing this. But we achieved this with the help of gamin (a famd alternative), and the famd php extension.

Started by installing php-fam using

pecl install fam ( boo.. fam extension is beta ),

apt-get install libgamin-dev,

since we already had the base dependencies, once the devel libraries were installed, i did

pecl install channel://pecl.php.net/fam-5.0.1

Okay that did it, and out of practice, though pecl suggested that I add extension=fam.so to php.ini, I created a new /etc/php5/cli/conf.d/fam.ini, and added the same there.

<?php
 
/* Enum constants fam code */
$famMessag  = array('','Changed','Deleted''StartExecuting''StopExecuting''Created''Moved''Acknowledge''Exists''EndExist');
 
echo 
"open connetion to Fam:\n";
$f fam_open();
echo 
"monitoring events:\n";
$rs fam_monitor_directory  $f '/tmp/trigger' );
 
while (
$rd fam_next_event($f)){
   echo 
"\t" 'Got FAM Event (' $rd['code'] . '), file: ' $rd['filename'] . ', event: ' $famMessag[$rd['code']] . ";\n";
   if(
== $rd['code']){
    
$ouFile '/tmp/trigger/' $rd['filename'];
    
$data = array('data' => file_get_contents($ouFile),'file' => $rd['filename']);
    echo 
"Delete file, to avoid junk!\n";
    
unlink($ouFile); 
 
    echo 
"Posting data (".strlen($data['data'])." bytes)!\n";
    
$curl curl_init();
    
// Option settings for curl
    
curl_setopt($curl,CURLOPT_URL,'https://testing.sil.lab/jiju/t.php');
    
curl_setopt($curl,CURLOPT_POST1);
    
curl_setopt($curl,CURLOPT_HTTPHEADER,array("Connection: keep-alive","Expect:"));
    
curl_setopt($curl,CURLOPT_POSTFIELDS,$data);
    
curl_setopt($curl,CURLOPT_RETURNTRANSFER,1);
 
    
$response curl_exec($curl);
    
curl_close($curl);
 
   }
}
 
fam_close($f);

The above code along with a trigger which writes a string of the aggregate using the “select into outfile” using a session variable for the filename, the following was the output.

open connetion to Fam:
monitoring events:
	Got FAM Event (8), file: /tmp/trigger, event: Exists;
	Got FAM Event (9), file: /tmp/trigger, event: EndExist;
	Got FAM Event (5), file: t1.txt, event: Created;
Delete file, to avoid junk!
Posting data (11 bytes)!
	Got FAM Event (1), file: t1.txt, event: Changed;
	Got FAM Event (2), file: t1.txt, event: Deleted;

Phew! the success is giving me greater opportunities to increase the response of applications which use both php and mysql and is hosted on dedicated or virtual dedicated boxes. The availability of famd and famd api is critical. The gamin page shows a possiblity using the python bindings.

On an after thought, when hunting for UDFs, I came to know about the UDF Repository for MySQL, but there are complications, like permitting mysql to read the <udf>.so in apparmor on ubuntu. Upgrade of mysql may break the udf. And the complications involved when one does not know about udf authoring. Still from a professional point, this is just another method to achieve the desired operation.

Making a trigger in mysql fire a shell command, Trigger invoke shell command from Mysql, MySQL Trigger fire shell command, Trigger external command from MySQL