Tuesday, May 29, 2007

The MySQL UDF Repository: lib_mysqludf_sys

If you haven't seen my previous post on the MySQL User Defined Function Repository, take a minute to check it out!!

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.

One of the coolest (and dangerous!) things is of course 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:

pabloj said...

Is there a Windows binary available?

rpbouman said...

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.

jbalint said...

do you plan to return the stdout instead of the return code? :)

rpbouman said...

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

Dmitry Avtonomov said...

hi!
i've built the win32 binaries, and sent you the dll file by email (to mysqludfs@gmail.com )

rpbouman said...

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)

Anonymous said...

I think sys_exec don't work on linux 64bit version.

rpbouman said...

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

Anonymous said...

Hi,

Can you advise on how to get this working on freebsd?
thanks

Anonymous said...

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

rpbouman said...

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.

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

rpbouman said...

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

Anonymous said...

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

rpbouman said...

hi Alex!

sure, just use the standard SQL Jedi skills:

SELECT sys_exec(CONCAT('mail.sh ', person.email))
FROM person;

Anonymous said...

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

rpbouman said...

Hi Gunther!

thanks. I hope it will be useful to you.

regards,

Roland

Anonymous said...

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?

rpbouman said...

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(.....);

Anonymous said...

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

Anonymous said...

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

Pieter said...

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

rpbouman said...

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.

helices said...

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.

rpbouman said...

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

helices said...

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.

helices said...

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

rpbouman said...

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

helices said...

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?

Yves said...

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

rpbouman said...

Yves, thanks for chiming in! Much appreciated :)

Anonymous said...

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

rpbouman said...

@Anonymous, did you read the error messages?

It looks like you don't have gcc installed. That's the c compiler.

Anonymous said...

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

Anonymous said...

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

rpbouman said...

Hi @Anonymous, maybe this will give you a clue:

http://askubuntu.com/questions/398929/unable-to-load-shared-object-wrong-elf-class-elfclass32

rpbouman said...

Wild guess is you compiled to a 32 bit library but the deployment platform is 64bit.

Anonymous said...

Is there a Windows binary available?

rpbouman said...

Hi @Anonymous,

nope - no binaries.

This might help:

http://rpbouman.blogspot.nl/2007/09/creating-mysql-udfs-with-microsoft.html

Anonymous said...

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

Alassane said...

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?

rpbouman said...

@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

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...