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!
Posts
23 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
Post a Comment