Sunday, December 25, 2011

Call an external program from MySQL trigger

MySQL is the choice of many when it comes to database. Its free and quite robust.
During one of our prototype implementations we had a requirement of calling some external processes when there is a change in a MySQL table. MySQL triggers are provided for the same purpose. They get executed when the table is changed in certain ways that is specified by the programmer. Now it is very easy (rather trivial) to do some thing in other MySQL database tables when trigger gets fired. But, our requirement was to call a C program.

Fortunately MySQL provides a way to implement your own functions, its called User Defined Functions (UDF). The "how to" is here. Now that we know, how to define your own functions and call them from MySQL events, we need to write our logic in a C program by following the interface provided by MySQL and we are done.

Wait a minute. That is already done by somebody. They have made a library of UDFs. One of them, LIB_MYSQLUDF_SYS does exactly what we want. It allows you to call an external program from MySQL procedure/trigger. Once you download and untar the files, you'll get an install.sh. Just need to run it but, make sure you have gcc and make before that. The install script copies the library shared object file to /usr/lib. In some 64 bit systems it may be /usr/lib64. Also you need to check MySQL plugin_dir property which points to the directory where the .so file should be placed.


Once the UDF is installed successfully, we can use 2 functions to call an external program -

1. sys_exec : Returns the exit code of the external program.
2. sys_eval : Returns the output from the external program.

Here is a code snippet which show an example:

DELIMITER @@

CREATE TRIGGER Test_Trigger 
AFTER INSERT ON MyTable 
FOR EACH ROW 
BEGIN
 DECLARE cmd CHAR(255);
 DECLARE result int(10);
 SET cmd=CONCAT('sudo /home/sarbac/hello_world ','Sarbajit');
 SET result = sys_exec(cmd);
END;
@@
DELIMITER ;


I have a hello_world program (/home/sarbac/hello_world) which accepts 1 command line argument and writes to a file.
Don't forget to return int from the main function if you are using sys_exec().

Deepti Sharma has thoroughly investigated this approach. I thank her for that.