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 "...
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.
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.
good article, but how to run external php file?
ReplyDeleteAs 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().
DeleteDELIMITER @@
DeleteCREATE 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
Use this instead -
DeleteSET 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.
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?
DeleteIt's the same thing as linux. First find where your php is. Shortly in command you should do "php your_php_file.php"
DeleteHi, nice information. can you help me with something?
ReplyDeleteI 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 )
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.
DeleteVery useful article. I salute you :)
ReplyDeleteBut is there another way if I don't have root access to the server (eg. Shared Hosting) ?
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.
Deletehow can i set execute permission for mysql user for executing command/program..
DeleteHI,
ReplyDeleteI 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.
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.
Deleteno 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
ReplyDeleteHeres 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.
Thanks...Sarbijit
ReplyDeleteIt 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.
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.
Deletei 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.
ReplyDeleteIf 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.
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.
DeleteYou 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
DeleteGood article. I've google it and find this very useful.
ReplyDeleteThanks!
Hi Sarabjit ,
ReplyDeleteAs 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
Yes, you are correct. UDF is not safe and does not get rolled back. It is only advised if nothing else is possible.
DeleteFor 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
you have mentioned that i need to edit "makefile" and need to update libdir to /usr/lib64 ...how should i do this
Deletewhen 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 *
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.
DeleteYou 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.
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHi, can you help me to run a python script?
ReplyDeleteSET cmd=CONCAT('/usr/bin/python', '/script/test.py');
error and will not run the script
Thanks!
John
Can you check the mysql error log and mention the actual error message?
DeleteHi, thanks
Delete[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
Hi, I have the same error when i try to run a python script
DeleteCan you please help us!!
Thank you!
This comment has been removed by the author.
ReplyDeleteThanks you
ReplyDeleteIts 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 ;
GREAT THANKS :) It works
ReplyDeleteHi ,
ReplyDeleteI 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 ?
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".
DeleteTry using "mkdir /tmp/test_dir" as a command to check if your UDF is getting called at least.
tried using mkdir/tmp/test_dir..its not creating any folder..how to solve this plz let me know
DeleteI have the same problem on centos 7.
DeleteWhen 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
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 ...
ReplyDeleteUse this UDF for modern 64-bit based systems : https://github.com/rghose/lib_mysqludf_sys
ReplyDeleteWorked smoothly on centos with the changes.
how to install UDF on webserver and localhost
ReplyDeleteUDF is to be installed in database. I did not understand what you mean by "webserver and localhost".
Deletehi,
ReplyDeletei 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
Is it possible to do this without installing external libraries? Great article by the way
ReplyDeleteWhen i try the command below, it return -1. Kindly advice. Thanks
ReplyDeletemysql> select sys_exec('ls / > /tmp/out.txt');
+---------------------------------+
| sys_exec('ls / > /tmp/out.txt') |
+---------------------------------+
| -1 |
+---------------------------------+
1 row in set (0.05 sec)
Hi Great article ..Im unable to compile it on my system here's the output I am getting and would appreciate any tips / suggestions :
ReplyDelete#./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
Hello, very usefull article.
ReplyDeleteCan 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
Good Article!.
ReplyDeleteI am looking to do the same in Windows system running mysql (xampp) The library is for linux. Any help ?
Thanks in advance.
i am not able to call external program from trigger using sys_exec()
ReplyDeleteI dont know if anybody still got the "ERROR: You need libmysqlclient development software installed" but I found a working solution :
ReplyDeleteyum install mysql-devel -y
We've been trying to use sys_exec without success.
ReplyDeleteWe 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?
is 'testesito' a shell script?
Deletein that case please run 'bash /home/sgroup/viticontrol/testesito' as the command.
Please do let me know if it works
Hello ,there,
ReplyDeleteI am new to the UDF thing please help me to install with it MYSQL and how to call the UDF function in Trigger
Hi,
ReplyDeleteI 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
Suggesting an edit to the blog motto: "I hope some of them are _just_ crazy enough for you to try out."
ReplyDelete(cf. http://tvtropes.org/pmwiki/pmwiki.php/Main/CrazyEnoughToWork )
Is there a way that i can publish the changes to the external python application?? ( changes > ./python.py )
ReplyDeleteHello, how can i execute a java file?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteWhich MySQL version supports as it is working on 5.0 but not in above 5.0..please suggest if it is true?
ReplyDeleteAs 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
sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
ReplyDeletesudo 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
This comment has been removed by the author.
ReplyDeleteHello,
ReplyDeleteI 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.
instead of installing libmysqlclient15-dev change your makefile and add those two flags to the gcc command -DMYSQL_DYNAMIC_PLUGIN -fPIC
DeleteThis comment has been removed by the author.
ReplyDeleteI want to execute an sh scrpipt but nothing is happening when I execute the following code:
ReplyDeleteDELIMITER @@
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.
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.
ReplyDeletecan you explain me how to do that using windows, instead of linux?
ReplyDelete