Tuesday, May 29, 2007

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.

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.

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,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 UDFgcc -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 valuecollect2: ld returned 1 exit statusmake: *** [install] Error 1ERROR: You need libmysqlclient development software installedto be able to compile this UDF, on Debian/Ubuntu just run:apt-get install libmysqlclient15-dev

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

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.

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?

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:

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

InstallationPlace the shared library binary in an appropriate location.

After much trial an error, it now resides in

/usr/local/mysql/lib/mysql/lib_mysqludf_systhe 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.herrmsg.h my_dbug.h my_pthread.h mysql_version.h sslopt-case.hkeycache.h my_dir.h my_sys.h mysqld_ername.h sslopt-longopts.hm_ctype.h my_getopt.h my_xml.h mysqld_error.h sslopt-vars.hm_string.h my_global.h mysql.h raid.h typelib.hmy_alloc.h my_list.h mysql_com.h readline.hmy_attribute.h my_net.h mysql_embed.h sql_common.h

sh-3.2# ./install.shCompiling the MySQL UDFgcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.solib_mysqludf_sys.c:40:23: error: my_global.h: No such file or directorylib_mysqludf_sys.c:41:20: error: my_sys.h: No such file or directorylib_mysqludf_sys.c:43:19: error: mysql.h: No such file or directorylib_mysqludf_sys.c:44:21: error: m_ctype.h: No such file or directorylib_mysqludf_sys.c:45:22: error: m_string.h: No such file or directorymake: *** [install] Error 1ERROR: You need libmysqlclient development software installedto be able to compile this UDF, on Debian/Ubuntu just run:apt-get install libmysqlclient15-dev

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.

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.

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.

/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 valuecollect2: ld returned 1 exit statusmake: *** [install] Error 1ERROR: You need libmysqlclient development software installed to be able to compile this UDF, on Debian/Ubuntu just run:apt-get install libmysqlclient15-dev

Error received:lib_mysqludf_sys.c:40:23: error: my_global.h: No such file or directorylib_mysqludf_sys.c:41:20: error: my_sys.h: No such file or directorylib_mysqludf_sys.c:43:19: error: mysql.h: No such file or directorylib_mysqludf_sys.c:44:21: error: m_ctype.h: No such file or directorylib_mysqludf_sys.c:45:22: error: m_string.h: No such file or directorymake: *** [install] Error 1

Error received:lib_mysqludf_sys.c:40:23: error: my_global.h: No such file or directorylib_mysqludf_sys.c:41:20: error: my_sys.h: No such file or directorylib_mysqludf_sys.c:43:19: error: mysql.h: No such file or directorylib_mysqludf_sys.c:44:21: error: m_ctype.h: No such file or directorylib_mysqludf_sys.c:45:22: error: m_string.h: No such file or directorymake: *** [install] Error 1

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.

Search This Blog

About Me

I'm Roland Bouman (@rolandbouman on twitter). I'm a software (web) application developer and I work on both the front end as well as the back end. I do data modeling, database design, ETL, Analytics, and Business Intelligence.