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.

33 comments:

  1. good article, but how to run external php file?

    ReplyDelete
    Replies
    1. As long as you can run the PHP file from command line, it can be run from the trigger. Frame the command to be run, inside CONCAT function. Then call sys_exec().

      Delete
    2. DELIMITER @@

      CREATE TRIGGER Test_Trigger
      AFTER INSERT ON MyTable
      FOR EACH ROW
      BEGIN

      DECLARE cmd CHAR(255);
      DECLARE result int(10);
      SET cmd=CONCAT('home/test/beta/score.php');
      SET result = sys_exec(cmd);

      END;
      @@
      DELIMITER ;

      please help me, trigger is run success fully but php file not run.
      i have put simple insert command on score.php file

      Delete
    3. Use this instead -
      SET cmd=CONCAT('/usr/bin/php ', '/home/test/beta/score.php');

      Usually we run PHP from command line like this -

      $ /usr/bin/php /home/test/beta/score.php

      You should follow the same syntax. Also make sure you have PHP installed in your system.

      Delete
    4. the file that I wanna open don't stay in this file /usr/bin/php...it's on c:/ directory...how can i open it?

      Delete
  2. Hi, nice information. can you help me with something?

    I am running this on windows, y use the same library but .dll. but i have a trouble.

    this is mi line (i want to open a .exe and i am trying with the calculator):
    SET cmd='cmd "cmd.exe /c start c:\\windows\\system32\\calc.exe"';

    but when i run int, mysql freezes. i dont have errors.
    and i think that this line is bad... help me please.

    (i try with this too SET cmd='c:\\windows\\system32\\calc.exe"';, and the same happen )

    ReplyDelete
    Replies
    1. I don't have much idea on windows. Your syntax looks correct though. Please check MySQL log for the possible error messages related to MySQL freezing problem.

      Delete
  3. Very useful article. I salute you :)
    But is there another way if I don't have root access to the server (eg. Shared Hosting) ?

    ReplyDelete
    Replies
    1. The command/program you want to execute via MySQL UDF, should have execute permission for mysql user. It is not necessary to have root privilege for that.

      Delete
  4. HI,
    I should say ur suggestion is very good.
    However,i tried it and the trigger was successfully executed on execution of insert statement .

    However,it failed to invoke my program
    Here is the trigger.

    mysql> show triggers;
    +--------------+--------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+
    | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer |
    +--------------+--------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+
    | Test_Trigger | INSERT | example | BEGIN
    DECLARE cmd CHAR(255);
    DECLARE result int(10);
    SET cmd=CONCAT('sudo /root/pid/a.out ','catch.txt');
    SET result = sys_exec(cmd);
    END | AFTER | NULL | | root@localhost |
    +--------------+--------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+

    Table example

    mysql> desc example;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id | int(11) | YES | | NULL | |
    | data | varchar(20) | YES | | NULL | |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.09 sec)


    file trying to execute

    #include
    #include
    #include


    int main(int argc,char *argv[])
    {
    FILE *fp;
    int i;
    if(argc!=2)
    {
    fprintf(stderr,"usage %s ",argv[0]);
    exit(0);
    }

    fp=fopen(argv[1],"w+");
    if(fp==NULL)
    {
    fprintf(stderr,"file opening failed\n");
    exit(0);
    }
    else
    {
    for (i=0; i<=10; ++i)
    fprintf(fp, "%d, %d\n", i, i*i);

    }
    fclose(fp);
    return 0;
    }


    should generate a catch.txt file in the same folder.

    ReplyDelete
    Replies
    1. My guess is that your mysql user does not have sudo privileges. To run the command using sudo, mysql user needs to be added to /etc/sudoers file for password less sudo access.

      Delete
  5. no it has sudo access ...how can i say that is cuz i wrote a similar trigger based on insert into example(the same table) but to invoke ANOTHER program in the same /pid/directory

    Heres the program

    #include
    #include
    #include


    int main(int argc,char *argv[])
    {
    FILE *fp;
    int i;
    if(argc!=2)
    {
    fprintf(stderr,"usage %s ",argv[0]);
    exit(0);
    }

    fp=fopen(argv[1],"w+");
    if(fp==NULL)
    {
    fprintf(stderr,"file opening failed\n");
    exit(0);
    }
    else
    {
    for (i=0; i<=10; ++i)
    fprintf(fp, "%d, %d\n", i, i*i);

    }
    fclose(fp);
    return 0;
    }


    it will take catch.txt as an argument and generate catch.txt file with a few lines added to it.Normally,catch.txt should be generated in the same directory.However,when the program is invoked after inserting in example table(i.e execting trigger) .the file catch.txt is generated in the plugin_dir directory configured in my.cnf.However,the point is if one program runs why not another.

    ReplyDelete
  6. Thanks...Sarbijit

    It works now...But i have my prog which send kill signal to my main program.The prog which sends signal is in a directory with root group and permission butas this program is executed through mysql trigger.I guess the program owns the capabilities of mysql user.

    My root user in mysql have sudo privileges.
    Host: %
    User: root
    Password: 43e9a4ab75570f5b
    Select_priv: Y
    Insert_priv: Y
    Update_priv: Y
    Delete_priv: Y
    Create_priv: Y
    Drop_priv: Y
    Reload_priv: Y
    Shutdown_priv: Y
    Process_priv: Y
    File_priv: Y
    Grant_priv: Y
    References_priv: Y
    Index_priv: Y
    Alter_priv: Y
    Show_db_priv: Y
    Super_priv: Y
    Create_tmp_table_priv: Y
    Lock_tables_priv: Y
    Execute_priv: Y
    Repl_slave_priv: Y
    Repl_client_priv: Y
    Create_view_priv: Y
    Show_view_priv: Y
    Create_routine_priv: Y
    Alter_routine_priv: Y
    Create_user_priv: Y
    ssl_type:
    ssl_cipher:
    x509_issuer:
    x509_subject:
    max_questions: 0
    max_updates: 0
    max_connections: 0
    max_user_connections: 0
    6 rows in set (0.00 sec)


    how even when i send kill(pid,SIGUSR1)
    i receive operation not permitted.This is the only hurdle i am facing right now!!.

    How can i send a kill signal from one user/underpriviledged user to a root user programatically.

    Thanks ur help is highly appriciated.

    ReplyDelete
    Replies
    1. Sending kill signal from a underprivileged process to a root process is not possible, as per my knowledge. But, there are Linux forms in internet that may help you.

      Delete
  7. i searched linux forums the same reply not possible.But i stumbled upon shared memory as a viable option.However,after a client changes a portion of a shared memory the server has to continiously poll for changes which is what i dont want.

    If any changes are made to the shared portion by client which is executed with trigger.The server should have an asynchrounously capabilty to know that a portion of shared memory has been altered and signal the same to server.

    The server will then carry out its process from there.
    Is there any way it can be acheived asynchronously with shared memory or as a matter of fact with any IPC.

    ReplyDelete
    Replies
    1. I can not think of any way that does not require polling. Probably you can run some other process to do the polling work and send SIGNAL to your process.

      Delete
  8. Good article. I've google it and find this very useful.

    Thanks!

    ReplyDelete
  9. Hi Sarabjit ,

    As far as i know implementing this UDF will not be safe for the database because what if the transaction rollback later, will the execution be undone ?

    However , can you please tell me how should i compile it for 64 bit.
    I tried using many commands , but not succeed yet

    ReplyDelete
    Replies
    1. Yes, you are correct. UDF is not safe and does not get rolled back. It is only advised if nothing else is possible.

      For compiling in 64 bit, you need to edit the Makefile. Update the LIBDIR to /usr/lib64 and the gcc call as below -

      gcc -Wall -fPIC -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.so

      Delete
    2. you have mentioned that i need to edit "makefile" and need to update libdir to /usr/lib64 ...how should i do this

      when i run this command i get an error :

      gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so -fPIC

      gcc: error: lib_mysqludf_sys.c: No such file or directory gcc: fatal error: no input files compilation terminated

      also i have no mysql folder in this path : /usr/include/mysql *

      Delete
    3. When you download the tar file for LIB_MYSQL_UDF, you get all source files along with a Makefile to compile them. I was pointing to the same Makefile.
      You need not run the gcc command directly. The Makefile contains similar gcc call, just edit that.

      /usr/include/mysql directory stores the mysql client development headers. If it is not present, please install development headers for mysql. Or replace the path with actual path in your system, if headers already exist.

      Delete
  10. This comment has been removed by the author.

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Hi, can you help me to run a python script?

    SET cmd=CONCAT('/usr/bin/python', '/script/test.py');

    error and will not run the script

    Thanks!
    John

    ReplyDelete
    Replies
    1. Can you check the mysql error log and mention the actual error message?

      Delete
    2. Hi, thanks

      [108430.002545] type=1400 audit(1368801081.054:203): apparmor="DENIED" operation="exec" parent=10260 profile="/usr/sbin/mysqld" name="/bin/dash" pid=12622 comm="mysqld" requested_mask="x" denied_mask="x" fsuid=106 ouid=0

      Delete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Thanks you

    Its worked for me

    I called external shell script using triggers

    here is the code I have used:

    DELIMITER $$
    CREATE TRIGGER tg1 AFTER INSERT ON `test`

    BEGIN

    DECLARE cmd CHAR(255);
    DECLARE result int(10);
    SET cmd=CONCAT('/var/www/html/test.sh');
    SET result = sys_exec(cmd);
    END $$
    DELIMITER ;

    ReplyDelete
  15. GREAT THANKS :) It works

    ReplyDelete
  16. Hi ,

    I am trying to use lib_mysqludf_sys to use sys_exec to call a C executable.
    I am logged into mysql as root. and I am logged into linux with my id.
    The C program "hw" when executed creates a file abc.txt and writes content into it .
    But when I am using it with triggers as mentioned by you it does not seem to run. My trigger is given below :

    DELIMITER @@

    CREATE TRIGGER Test_Trigger
    AFTER INSERT ON users
    FOR EACH ROW
    BEGIN
    DECLARE cmd CHAR(255);
    DECLARE result int(10);
    SET cmd=CONCAT('/home/netraprasanna/hw');
    SET result = sys_exec(cmd);
    END;
    @@
    DELIMITER ;

    Can you please point out what is the error ?

    ReplyDelete
    Replies
    1. I can't be sure but, it seems that your mysql user (mysql server runs from that user account) does not have read/write permission in "/home/netraprasanna".
      Try using "mkdir /tmp/test_dir" as a command to check if your UDF is getting called at least.

      Delete
  17. Hi, I have some troubles with lib_mysqludf_sys; it always returns a code 32512 ... - i compiled the source on a 64 bit Ubuntu ... Thanks for your help ...

    ReplyDelete
  18. Use this UDF for modern 64-bit based systems : https://github.com/rghose/lib_mysqludf_sys

    Worked smoothly on centos with the changes.

    ReplyDelete