Today, I did a major structural overhaul of the site, and filled up the roadmap. I have also just added a new library to the project. This library
lib_mysqludf_sys contains a number of functions that allows one to interact with the operating system.sys_exec- executes an arbitrary command, and can thus be used to launch an external application.sys_get- gets the value of an environment variable.sys_set- create an environment variable, or update the value of an existing environment variable.
sys_exec
mysql> select sys_exec('ls / > /tmp/out.txt');
+---------------------------------+
| sys_exec('ls / > /tmp/out.txt') |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.00 sec)
Enjoy!
44 comments:
Is there a Windows binary available?
Hi Pabloj,
no - not yet. Send a mail to
mysqludfs@gmail.com
and I'll let you know as soon as I have one.
I used the ANSI C compliant
system()
function which should work on windows too:
http://msdn2.microsoft.com/en-us/library/277bwbdz(VS.80).aspx
I am currently looking for people that want to participate in building binaries, so if you want to help out, please step forward.
do you plan to return the stdout instead of the return code? :)
Jess,
that is a Great Idea! I will look into it tomorrow or so.
BTW, it would be great if you could drop me a mail at mysqludfs@gmail.com so I can add you to the list of interested people. The idea is to gather a group of guys and gals that have feature requests like these.
I hope we can start an open discussion to drive this project further.
Thanks in advance, and kind regards,
Roland
hi!
i've built the win32 binaries, and sent you the dll file by email (to mysqludfs@gmail.com )
Dimitry: this is great! Please join our group at
http://groups.google.com/group/mysql-udf-repository/
thanks in advance!
(I'll put up a link in the repository to your binaries)
I think sys_exec don't work on linux 64bit version.
@Anonymous,
this is not helpful. If you don't give any information what you tried and what made you draw this conclusion, it is impossible for anybody to try to help.
Anyway, we had some reports of people running with AppArmor - this seems to prevent execution of sys_exec and sys_eval. You should be able to configure AppArmor so it allows execution of these UDFs. Hope this helps.
Hi,
Can you advise on how to get this working on freebsd?
thanks
answering my own questin !
on freeBSD
gcc -fPIC -Wall -I/usr/local/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
Hi @Anonymous,
Thanks for your information on compiling it for freebsd. This info is most welcome - I never tried and I don't currently have access to a freebsd environment.
kind regards,
Roland.
Hi,
Tried to install lib_mysqludf_sys_0.0.3 on CentOS 5.5 64-bit using install.sh and got the following error:
Compiling the MySQL UDF
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
/usr/bin/ld: /tmp/cc8lGuBy.o: relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC
/tmp/cc8lGuBy.o: could not read symbols: Bad value
collect2: 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
Installed mysql packages:
yum list installed|grep mysql
mysql.x86_64 5.1.47-1.el5.remi installed
mysql-devel.x86_64 5.1.47-1.el5.remi installed
mysql-libs.x86_64 5.1.47-1.el5.remi installed
mysql-server.x86_64 5.1.47-1.el5.remi installed
mysqlclient15.x86_64 5.0.67-1.el5.remi installed
mysqlclient15-devel.x86_64 5.0.67-1.el5.remi installed
php-mysql.x86_64 5.3.2-1.el5.remi installed
The lib_mysqludf_sys.so object is 32-bit, all my files are 64-bit. Can I use a 32-bit plugin in a 64-bit DB?
Thank you!
Gunther
OK, added the -fPIC parameter to the command line and I was able to compile w/o error. Will check if it is working. Thanks - Gunther
One more remark, the install.sh is placing the compiled library into the /usr/lib directory, while the MySQL 5.1 manual states that UDF objects must be located in the server's plugin directory (e.g. /usr/lib/mysql/plugin/).
Gunther
Hi Gunther!
I am not sure about the 64bit / 32bit issue. It seems to me plugins for a 64 bit server should be compiled as 64bit, but I never tried. Unfortunately, I don't know enough about gcc to know what to do to try .
Regarding the installation: as far as i recall, lib_mysqludf_sys isn't packaged in any way. It should be, but it isn't. So wherever you specify the output path of gcc, will be the place where the binaries are placed. So you'll have to move them to the right place manually.
HTH,
Roland
Hi Roland, is there a way to pass parameters such as column values to the script?
Something like
SELECT sys_exec("/var/lib/myscript.php "+some_param).
Great job!
Thanks,
Alex
hi Alex!
sure, just use the standard SQL Jedi skills:
SELECT sys_exec(CONCAT('mail.sh ', person.email))
FROM person;
Hi Roland,
After compiling with the -fPIC parameter, the 64-bit object will be automatically created.
Quick correction for the UDF object location on 64-bit CentOS 5.5:
/usr/lib64/mysql/plugin/lib_mysqludf_sys.so
#file /usr/lib64/mysql/plugin/lib_mysqludf_sys.so
/usr/lib64/mysql/plugin/lib_mysqludf_sys.so: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), not stripped
I tested sys_exec and it is working fine.
Thanks!
Gunther
Hi Gunther!
thanks. I hope it will be useful to you.
regards,
Roland
Hi Roland,
Would there be any reason why I would not be able to pass variables to a script run by sys_exec? I was trying to run this:
set @fname=(select ru.First_Name from live_review_users ru join live_review_checkout rc on ru.id=rc.id where rc.Expired='N');
set @lname=(select ru.Last_Name from live_review_users ru join live_review_checkout rc on ru.id=rc.id where rc.Expired='N');
set @email=(select LOWER(email_address) from dba.live_review_users where First_Name=@fname and Last_Name=@lname);
set @pass=(select password from dba.live_review_password);
insert into live_review_emails(email_address,password) values(@email,@pass);
select sys_exec('/home/mysql/cronjobs/checkout_email.sh @email @pass');
It runs fine if I replace the @email and @pass with strings, but does not run with variables?
Hi Anonymous,
sys_exec takes a string argument that has to be a valid command for the OS where MySQL is running. If you want to pass parameters to that command, you have to use regular string manipulation to create a complete command line. Like so:
sys_exec(
concat(
'/home/mysql/cronjobs/checkout_email.sh'
, ' ', @email
, ' ', @pass
)
)
I notice you use SELECT to call the function. That works, but it will also generate a row. This may not be what you want. In that case, use DO:
DO sys_exec(.....);
Hi Roland!
I would very much like to use your sys_exec function, but I've spent hours already trying to get it installed and can't seem to get past this wall.
Please get me going in the right direction!
Thanks in advance!
Kevin
Installation
Place the shared library binary in an appropriate location.
After much trial an error, it now resides in
/usr/local/mysql/lib/mysql/lib_mysqludf_sys
the result is the same when I run the install .sh I get the following.
As I've said I've moved this to different locations and tried adding to the path.
I see the header files in and have added this directory to the path but I still get the same message
ls /usr/local/mysql/include/mysql/
decimal.h my_config.h my_no_pthread.h mysql_time.h sql_state.h
errmsg.h my_dbug.h my_pthread.h mysql_version.h sslopt-case.h
keycache.h my_dir.h my_sys.h mysqld_ername.h sslopt-longopts.h
m_ctype.h my_getopt.h my_xml.h mysqld_error.h sslopt-vars.h
m_string.h my_global.h mysql.h raid.h typelib.h
my_alloc.h my_list.h mysql_com.h readline.h
my_attribute.h my_net.h mysql_embed.h sql_common.h
sh-3.2# ./install.sh
Compiling the MySQL UDF
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
lib_mysqludf_sys.c:40:23: error: my_global.h: No such file or directory
lib_mysqludf_sys.c:41:20: error: my_sys.h: No such file or directory
lib_mysqludf_sys.c:43:19: error: mysql.h: No such file or directory
lib_mysqludf_sys.c:44:21: error: m_ctype.h: No such file or directory
lib_mysqludf_sys.c:45:22: error: m_string.h: No such file or directory
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
For my Ubuntu 11.04 (64 bit) I had to edit the Makefile and change
LIBDIR=/usr/lib to LIBDIR=/usr/lib/mysql/plugin
Also make sure that gcc has the -fPIC option ie:
gcc -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.so
Hi Roland,
I installed UDF but when I do your command I do not get 0 as result. This is my ouput with sys_eval:
mysql> select sys_eval('ls / > /tmp/out.txt');
+---------------------------------+
| sys_eval('ls / > /tmp/out.txt') |
+---------------------------------+
| NULL |
+---------------------------------+
1 row in set (0.00 sec)
Do you have an idea?
Pieter
Pieter,
could be many things. If you have apparmor, this may prevent sys_exec from actually running the command.
Alternatively, depending on your OS, `ls` may not be a real system command.
Check the C documentation for the system() function - see if that helps.
lib_mysqludf_sys works well; but, I've found an anomaly I hope you can explain, if not work around.
I have a data source that writes to a table, one field of which requires further parsing to be useful.
So, I wrote a Trigger that calls lib_mysqludf_sys:sys_exec, which passes a key (ID) to a php script.
That php script does get called. However, one of the first thing it does is SELECT the field to be parsed for the ID argument.
Unfortunately, called this way, the SELECT returns nothing, nothing at all.
Run from CLI as user:mysql, the script selects the field, modifies it and updates 6 fields in that record, as expected.
I have compared ENV in the UDF called script and run as user:mysql - nothing different, except for trivial things like LS_COLORS
I had the UDF call a bash script that exec'ed the php script - no difference.
Yes, ID is actually passed to the scripts.
No, I haven't figured out how to generate any errors.
Please, advise. Thank you.
@helices, let me see if I get this correctly:
1) you have a table and a trigger that calls a php script via sys_exec
2) in the php script, you attempt to select the row that is currently being processed by the trigger in an attempt to modify that row
?
If so the problem is that your trigger is in a transaction that is separate from the one used by the php script. The script cannot see the currently processed row since the transactions are isolated from one another.
This is normal behavior.
To get around it, have your php script return all target values as text in some convenient format. Then when the script returns, parse out those fields and assign them to the record.
In other words, don't add the extra layer of data access by selecting the row you want to manipulate since that row simply doesn't exist yet.
Yes, I get that.
I thought a trigger set AFTER INSERT meant literally that?
Why doesn't it work when UDF calls a shell script that exec's the php script?
Would sys_eval do what I want? I can echo 1 prior to my exec ...
What would trigger your suggested: "parse out those fields and assign them to the record" ?
Also, perhaps, you don't understand my whole task. I have a new inserted record with many fields, two of which are ID and MESSAGE
ID uniquely identifies this record and MESSAGE is a long string, several substrings of which are interesting, but difficult to parse within mysql
Currently, I use your UDF to write to a file the whole CLI I want to run. A cron job runs every 5 minutes, sources (. file) the file, which executes each line and updates appropriate records.
This is a kludge for two reasons: [1] it must be run synchronously, and [2] it requires two processes instead of one
What am I missing?
Thank you.
Isn't Trigger AFTER INSERT supposed to allow this?
Also, I've also called a shell script from the UDF, which exec's my php script - shouldn't that then run on a fully inserted record?
Would sys_eval work? I could echo 1 prior to exec
My current kludge is to write each full CLI string to a file, which a cron job periodically moves and sources
My issues with this kludge are:
[1] synchronous rather than asynchronous action
[2] two separate processes rather than one
"Isn't Trigger AFTER INSERT supposed to allow this?"
No. AFTER INSERT is after the row is insert, but before the statement ends. So, before the transaction ends. In A non-transactional storage engine, the row might be visible outside the trigger, but in a transactional engine, it won't (or it's a bug). This is (of course) irrespective of the auto-commit setting (since that still works on statement level)
"
Also, I've also called a shell script from the UDF, which exec's my php script - shouldn't that then run on a fully inserted record?
Would sys_eval work? I could echo 1 prior to exec
"
I do not know what you mean by these remarks.
"My current kludge is to write each full CLI string to a file, which a cron job periodically moves and sources"
Have you tried implementing my suggestion? Parsing out the results from your php script really shouldn't be that hard, plenty of options on the mysql side like comma separated list or XML.
My question about sys_eval vs sys_exec harkens back to my desire to do all of this from the trigger, rather than a 2nd process
I hoped that by exec'ing my php script that the Trigger would close and complete record insertion; that the exec'ed script could select and update that record.
Are you saying that I can update the record from the trigger; but, I cannot select it?
Other than that, do I understand your suggestion? pass the id and field to be parsed to my script, create new field data and have another script update the record?
@helices: A somewhat late response. You can solve the problem by executing the trigger in background like
SET cmd=CONCAT("nohup /usr/bin/php"," /var/www/tsa/site_scripts/triggered_balanced.php ", " 1427 ", OLD.tsa_id, " >>/var/www/tsa/log/BAL_1427.log 2>&1 &");
SET result=sys_exec(cmd);
If you run the php script directly from the trigger, the record stays locked by the trigger. There is also the risk of modifying a record in the table which triggers an update, so be thoughtful about the use of sys_exec.
Yves, thanks for chiming in! Much appreciated :)
Hi Roland,
For MariaDB is there a different way to use it ?
[root@ip-10-0-102-218 lib_mysqludf_sys-master]# ./install.sh
Compiling the MySQL UDF
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
make: gcc: Command not found
make: *** [install] Error 127
ERROR: You need libmysqlclient development software installed
to be able to compile this UDF, on Debian/Ubuntu just run:
apt-get install libmysqlclient15-dev
thanks
@Anonymous, did you read the error messages?
It looks like you don't have gcc installed. That's the c compiler.
Hi Roland,
apologies pasted the wrong error
/usr/bin/ld: /tmp/cc8J4M2G.o: relocation R_X86_64_32 against `.rodata' can not be used when making a shared object; recompile with -fPIC
/tmp/cc8J4M2G.o: could not read symbols: Bad value
collect2: 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
Thanks
Hi Roland,
I have gotten past a few errors now but 64 bit installs are a pain. Can you help me with this final one. After asking for the password it enters
ERROR 1126 (HY000) at line 29: Can't open shared library 'lib_mysqludf_sys.so' (errno: 2, /usr/lib64/mysql/plugin/lib_mysqludf_sys.so: wrong ELF class: ELFCLASS32)
ERROR: unable to install the UDF
thanks
Hi @Anonymous, maybe this will give you a clue:
http://askubuntu.com/questions/398929/unable-to-load-shared-object-wrong-elf-class-elfclass32
Wild guess is you compiled to a 32 bit library but the deployment platform is 64bit.
Is there a Windows binary available?
Hi @Anonymous,
nope - no binaries.
This might help:
http://rpbouman.blogspot.nl/2007/09/creating-mysql-udfs-with-microsoft.html
Hi @Roland
Tried to recompile with 64bits command in Makefile.
Command attempted:
gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/mysql/plugin/lib_mysqludf_sys.so -L/usr/lib64/libstdc++.so.6.0.13
Error received:
lib_mysqludf_sys.c:40:23: error: my_global.h: No such file or directory
lib_mysqludf_sys.c:41:20: error: my_sys.h: No such file or directory
lib_mysqludf_sys.c:43:19: error: mysql.h: No such file or directory
lib_mysqludf_sys.c:44:21: error: m_ctype.h: No such file or directory
lib_mysqludf_sys.c:45:22: error: m_string.h: No such file or directory
make: *** [install] Error 1
Context:
Red Hat Enterprise Linux Server release 6.8 (Santiago)
Any idea where issue could be coming from?
Thanks,
Alassane
Hi @Roland,
Experiencing issue with 64bits recompilation (Red Hat Enterprise Linux Server release 6.8).
Command attempted (edited from Makefile):
gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/mysql/plugin/lib_mysqludf_sys.so -L/usr/lib64/libstdc++.so.6.0.13
Error received:
lib_mysqludf_sys.c:40:23: error: my_global.h: No such file or directory
lib_mysqludf_sys.c:41:20: error: my_sys.h: No such file or directory
lib_mysqludf_sys.c:43:19: error: mysql.h: No such file or directory
lib_mysqludf_sys.c:44:21: error: m_ctype.h: No such file or directory
lib_mysqludf_sys.c:45:22: error: m_string.h: No such file or directory
make: *** [install] Error 1
Any idea where issue could be coming from?
@Alassane,
I haven't updated the code in ages. Might very well be changes in organisation of the header files at the MySQL end. check if the .h files are still around? Good starting point would be the example udf s that ship with mysql.
HTH<
Roland
Post a Comment