Home > Code Snippets, mysql, PHP > Invoke shell from MySQL trigger

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,'http://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

  1. July 14th, 2009 at 13:57 | #1

    Hi,
    It’s a cool trick, but you are not really invoking the shell from a trigger. If you want to do that, you can use MySQL Proxy with a trick similar to the one described in this post.
    http://datacharmer.blogspot.com/2008/03/using-event-scheduler-with-os-commands.html

    Cheers

    Giuseppe

  2. October 20th, 2009 at 03:56 | #2

    I think there is a much easier way to do this:

    http://forums.mysql.com/read.php?99,170973,257815#msg-257815

    Cheers!

  3. October 20th, 2009 at 04:01 | #3

    @Patrick hayes
    A note about the script detailed above – it will break inside phpmyadmin – but should work everywhere else.

  4. October 24th, 2009 at 08:33 | #4

    Great.. I just tested it and confirmed before even approving your comment. And this does bring in more opportunity for us. Thanks

  5. October 24th, 2009 at 12:58 | #5

    @Patrick hayes
    It is specifically written there, that you should use the mysql command line tool..

  6. Jonathan
    June 28th, 2010 at 19:32 | #6

    Nice idea. Could you publish the aforementioned trigger too?

  7. eMarv
    August 4th, 2010 at 20:27 | #7

    Hi,

    @author – what you mean: ““select into outfile” using a session variable for the filename”

    Thanx

  1. No trackbacks yet.

− seven = one