Skip to main content

Build Wine rpm with 32 bit application support

Wine is a software to allow running Windows applications in Linux, MAC etc. platforms. It is available for installation from package managers like yum (RHEL, CentOS) and apt (Ubuntu). You can find more details on how it works in Wine wiki . But the default Wine package available from package manager does not have support for 32 bit Windows applications. This was the case for me. In Redhat Enterprise Linux 7.3, the wine package did not contain support for 32 bit windows applications. So the only option was to build a separate rpm of wine which will include this support. All the steps are executed on a RHEL 7.3 VM (x86_64). Step 1 Download and run shell script which will make wine 64 and 32 support for RHEL: https://github.com/zma/usefulscripts/blob/master/script/install-wine-i686-centos7.sh It accepts a version no. as CLI parameter e.g. 2.0.3 The script installs wine in /usr/local/ directory by default. We can verify the files that are being copied for wine using "...

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:
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.

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
    5. It's the same thing as linux. First find where your php is. Shortly in command you should do "php your_php_file.php"

      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
    2. how can i set execute permission for mysql user for executing command/program..

      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
    2. You need to use semaphores to achieve that. This is the classical consumer/producer pattern : http://en.wikipedia.org/wiki/Producer%E2%80%93consumer_problem#Using_semaphores

      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
    3. Hi, I have the same error when i try to run a python script
      Can you please help us!!

      Thank you!

      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
    2. tried using mkdir/tmp/test_dir..its not creating any folder..how to solve this plz let me know

      Delete
    3. I have the same problem on centos 7.
      When i run command select sys_exec('/usr/bin/php /tmp/test.php');
      get the error: Could not open input file: /tmp/test.php
      file test.php has owner mysql MariaDB and permission set to 777

      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
  19. how to install UDF on webserver and localhost

    ReplyDelete
    Replies
    1. UDF is to be installed in database. I did not understand what you mean by "webserver and localhost".

      Delete
  20. hi,
    i have create trigger for After update .

    I am trying to run php file using trigger.

    here is my code :

    BEGIN


    DECLARE cmd CHAR(255);
    DECLARE result int(10);


    SET cmd=CONCAT('/usr/bin/php','/var/www/html/magento/test.php');
    SET result = sys_exec(cmd);


    END

    but error come :

    #1305 - FUNCTION magento.sys_exec does not exist

    when use sys_eval then error is:

    #1305 - FUNCTION magento.sys_eval does not exist.

    i have use ubuntu - mysql

    its urgent for me.
    can you please reply fast.

    thanks in advance

    ReplyDelete
  21. Is it possible to do this without installing external libraries? Great article by the way

    ReplyDelete
  22. When i try the command below, it return -1. Kindly advice. Thanks

    mysql> select sys_exec('ls / > /tmp/out.txt');
    +---------------------------------+
    | sys_exec('ls / > /tmp/out.txt') |
    +---------------------------------+
    | -1 |
    +---------------------------------+
    1 row in set (0.05 sec)

    ReplyDelete
  23. Hi Great article ..Im unable to compile it on my system here's the output I am getting and would appreciate any tips / suggestions :
    #./install.sh
    gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
    /usr/bin/ld: /tmp/cccmS8dL.o: relocation R_X86_64_32 against `.rodata' can not be used when making a shared object; recompile with -fPIC
    /tmp/cccmS8dL.o: could not read symbols: Bad value
    collect2: error: ld returned 1 exit status
    make: *** [install] Error 1
    ERROR: You need libmysqlclient development software installed
    to be able to compile this UDF, on Debian/Ubuntu just run:
    apt-get install libmysqlclient15-dev

    My Operating system is Centos 7 though :
    localhost.localhost 3.10.0-123.el7.x86_64 #1 SMP Mon Jun 30 12:09:22 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

    ReplyDelete
  24. Hello, very usefull article.

    Can I with mysql trigger, call a method in a jar that is already running? Here is what i want to do. I have a web site in PHP and when a user logs in succesfully, I want a mysql trigger invoke a java method from a class that is already running and wait to be awaked.

    Thanks in advance

    ReplyDelete
  25. Good Article!.

    I am looking to do the same in Windows system running mysql (xampp) The library is for linux. Any help ?

    Thanks in advance.

    ReplyDelete
  26. i am not able to call external program from trigger using sys_exec()

    ReplyDelete
  27. I dont know if anybody still got the "ERROR: You need libmysqlclient development software installed" but I found a working solution :

    yum install mysql-devel -y

    ReplyDelete
  28. We've been trying to use sys_exec without success.

    We installed lib_mysqludf_sys successfully. But when we define a trigger in a mysql database (using phpmyadmin in a linux environment, it does not trigger as desired the execution of a given function (testesito) located on a specific directory. We have tried several syntaxes:

    BEGIN
    DECLARE cmd CHAR(255);
    DECLARE resultado int(10);
    SET cmd='/home/sgroup/viticontrol/testesito';
    SET resultado = sys_exec(cmd);
    END

    we also tried other commands:

    SET cmd=CONCAT('cd /home/sgroup/viticontrol/testesito');
    SET cmd=CONCAT('cd /home/sgroup/viticontrol/', './testesito');

    None give an error. the shall trigger occurs when the table is updated. But when we change the value of a given line of the table, the trigger does not execute as desired the executable.

    Any hint?

    ReplyDelete
    Replies
    1. is 'testesito' a shell script?
      in that case please run 'bash /home/sgroup/viticontrol/testesito' as the command.
      Please do let me know if it works

      Delete
  29. Hello ,there,

    I am new to the UDF thing please help me to install with it MYSQL and how to call the UDF function in Trigger

    ReplyDelete
  30. Hi,

    I want to execute triger when data insert in table. I have run your triger code but I am getting sys_exe dose not exist. if any other way to execute this command please let me know.

    Thank you

    ReplyDelete
  31. Suggesting an edit to the blog motto: "I hope some of them are _just_ crazy enough for you to try out."
    (cf. http://tvtropes.org/pmwiki/pmwiki.php/Main/CrazyEnoughToWork )

    ReplyDelete
  32. Is there a way that i can publish the changes to the external python application?? ( changes > ./python.py )

    ReplyDelete
  33. Hello, how can i execute a java file?

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

    ReplyDelete
  35. Which MySQL version supports as it is working on 5.0 but not in above 5.0..please suggest if it is true?
    As somebody ask here also http://answerguru.net/798/how-to-execute-call-an-external-program-from-mysql-trigger?show=1396#c1396

    but no one answered, please help

    ReplyDelete
  36. sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/

    sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

    you need to execute above commands in linux prompt after you install the lib_mysqludf_sys library successfully

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

    ReplyDelete
  38. Hello,
    I am not able to install apt-get install libmysqlclient15-dev
    but i am able to install apt-get install libmysqlclient-dev
    after that given shell script is not running please help me.
    but i am able to install apt-get install libmysqlclient-dev
    after that given shell script is not running please help me.
    i have ubuntu 64 system
    but when i going to install apt-get install libmysqlclient15-dev then its show me unable to locate package.

    ReplyDelete
    Replies
    1. instead of installing libmysqlclient15-dev change your makefile and add those two flags to the gcc command -DMYSQL_DYNAMIC_PLUGIN -fPIC

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

    ReplyDelete
  40. I want to execute an sh scrpipt but nothing is happening when I execute the following code:

    DELIMITER @@

    CREATE TRIGGER autohome1
    AFTER INSERT ON prueba
    FOR EACH ROW
    BEGIN
    DECLARE cmd VARCHAR(255);
    DECLARE result int(10);
    SET cmd=CONCAT('sh /home/pi/Desktop/Py_Script_Auto_Home/autohome.sh');
    SET result = sys_exec(cmd);
    END;
    @@
    DELIMITER ;

    I just want to execute the script, and I dont mind about returning values or whatever, should I still be using the sys_exec funcition??.

    Thanks beforehand.

    ReplyDelete
  41. Taking a drug test can be a scary thought, even if you don’t do drugs. Numerous pieces of advice on the web may cater to audiences with varying needs. But they might create confusion, too. The whole thing can be a messy quagmire of myths, quick fixes, and even worse, some very bad advice.So, what do you do when a drug test is around the corner? If you often consume substances that could get you into trouble on a drug test but want to get clean for your job interview, you may want to find proper guidance.Detoxification is one of the primary methods to prepare your body for a hassle-free drug test. But first, you must understand how most drug tests work in order to successfully detoxify your body and prepare it in the best way possible.Note: This article isn’t meant for cocaine or heroin addicts trying to pass a drug test. In this article, we specifically talk about weed-lovers – occasional or habitual – and how they can beat the test without raising any alarm.#2. Most labs accept urine samples between 90 and 100 degrees Fahrenheit. Finally, the deposit must have the specific gravity of real piss. It can’t have any unusual sediments at the bottom or separate when left to sit.When employing the use of synthetic urine to pass a drug test, you sneak your pee into the facility undetected. Then, once you’re in the bathroom, simply pour your prepared sample of fake piss into the container instead of urinating into it. As long as you don’t experience any leaks or spills, it can truly be that straightforward.

    ReplyDelete
  42. can you explain me how to do that using windows, instead of linux?

    ReplyDelete

Post a Comment