Monday, January 26, 2009

Repository for MySQL UDFs: new sys_eval() function

Thanks to Bernardo Damele, the lib_mysqludf_sys package hosted by the Repository for MySQL UDFs can now boast a new sys_eval() function. You can read the details here on his blog. Bernardo also created a similar function for PostgreSQL.

Thank you, Bernardo!!!

sys_eval() is quite like sys_exec(): both functions can execute an arbitrary command string. The difference is that sys_eval() returns the output of the command, whereas sys_exec() returns the exit code. Go and check it out for yourself!

23 comments:

Daniele Medri said...

Roland,

I have a request for udf stat:

- a C version of quantile()

you could take a look to the code from the R quantile() function. It's already available in C and it's GPL code. - www.r-project.org

rpbouman said...

Daniele,

thanks for the seggestion!

I'll consider it, but I can't promise when I'll get round to it. I'll be leaving for a holiday not too far from now, might take a month before I get back on to this.

Of course, you could have a go at it yourself. The UDF inerface isn't that hard, and if the actual quantile code is already, well, perhaps you can cobble it up yourself, and I will be happy to include it in the udf stat package.

kind regards!

(don't hesitate to p.m. me in case you want to discuss the subject)

Jo Yo said...

On a similar note, I wrote JsMap in my collection of UDFs. I mapped in SpiderMonkey to evaluate JavaScript. JsMap in my UDF collection

rpbouman said...

Jo Yo, that's great! At some point I had some experimental code to run js UDFs based on v8, it's great to see you have something released. Cool!

javier said...

hi,

i am trying to compile for centos 5 (64 bits), but only get error, exists 64 bit compile version?

thanks for all

rpbouman said...

Hi javier,

I just created some code. AFAIC, there is nothing that keeps you from compiling it for a 64bit architecture.

You are not giving me enough information to determine what the problem is, so i am afraid I cannot help you.

Guillaume HEU said...

On a 64Bit RedHat Linux ES i try to install lib_mysqludf_sys.so with Makefile Like this :
gcc -Wall -I/usr/include/mysql -Os -shared lib_mysqludf_sys.c -fPIC -o lib_mysqludf_sys.so
strip -sx lib_mysqludf_sys.so
cp -f lib_mysqludf_sys.so /usr/lib/lib_mysqludf_sys.so

Install seems to be good, after login root on mysql i obtain "can't open shared library 'lib_mysqludf_sys.so'"
Mysql 5.0.77 x86_64 redhat-linux-gnu

have you got an idea

rpbouman said...

Hi Guillaume!

what happens if you put the .so in the location where mysql's libraries are?

Guillaume HEU said...

.so is already in /usr/lib/lib_mysqludf_sys.so
I use the Jmap makeFile, to add -fPIC option for compilation, and just change gcc-4... by gcc in command line
thanks for your reply

Guillaume HEU said...

No, problem is not solved, i just said Hi, because i didn't does it before

rpbouman said...

Guillaume, do you have something like AppArmor? That is known to prevent sys_exec from running.

Guillaume HEU said...

Sorry, But AppArmor is not installed on this server, and SElinux is disabled

Guillaume HEU said...

Have you got an other Idea, where can i find an other UDF .so file to add a other function to Mysql or how can i debug make function

rpbouman said...

Guillaume,

sorry - I have no idea.

Bleeber said...

Guillaume,

Make sure you are also passing the -m64 flag to GCC to compile code for the 64bit arch.

Devang said...

Dear Sir;

I am using Red Hat ES 2.6.18-238.el5 #1 SMP Sun Dec 19 14:22:44 EST 2010 x86_64 x86_64 x86_64 GNU/Linux.

Project compiled successfully after adding two switches -fPIC and -m64 but not able to add UDF to mysql and getting below error.

After root password enter step.

ERROR 1126 (HY000) at line 29: Can't open shared library 'lib_mysqludf_sys.so' (errno: 22 lib_mysqludf_sys.so: wrong ELF class: ELFCLASS32)
ERROR: unable to install the UDF

Anonymous said...

64 useres compile it this way:

gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so -L/usr/lib/x86_64-linux-gnu/libstdc++.so.6

rpbouman said...

Christian,

thanks for this contribution!

David F said...

Hello all

Just want to add a new info:
I've followed the tip from Christian, to add the flag '-fPIC' inside the Makefile.
But, also, I had to change the destination directory from '/usr/lib/lib_mysqludf_sys.so' to '/usr/lib/mysql/plugin/lib_mysqludf_sys.so'

Anyway, thank you for the help.

David F said...

Hello all

Just want to add a new info:
I've followed the tip from Christian, to add the flag '-fPIC' inside the Makefile.
But, also, I had to change the destination directory from '/usr/lib/lib_mysqludf_sys.so' to '/usr/lib/mysql/plugin/lib_mysqludf_sys.so'

Anyway, thank you for the help.

Tisia said...

To everyone who otherwise would spend 2 days on this problem debugging, and rebuilding the plugin, reading logs and searching the internet.

UBUNTU users, switch off AppArmor! Well even if you do not switch it off enable complain mode for MySQL.

I kept getting this result from MySQL

mysql> select sys_eval('id');
+----------------+
| sys_eval('id') |
+----------------+
| |
+----------------+

root@marton-desktop:/tmp# sudo apparmor_status
apparmor module is loaded.
19 profiles are loaded.
19 profiles are in enforce mode.
/sbin/dhclient
/usr/bin/evince
/usr/bin/evince-previewer
/usr/bin/evince-previewer//launchpad_integration
/usr/bin/evince-previewer//sanitized_helper
/usr/bin/evince-thumbnailer
/usr/bin/evince-thumbnailer//sanitized_helper
/usr/bin/evince//launchpad_integration
/usr/bin/evince//sanitized_helper
/usr/lib/NetworkManager/nm-dhcp-client.action
/usr/lib/connman/scripts/dhclient-script
/usr/lib/cups/backend/cups-pdf
/usr/lib/lightdm/lightdm/lightdm-guest-session-wrapper
/usr/lib/lightdm/lightdm/lightdm-guest-session-wrapper//chromium_browser
/usr/lib/telepathy/mission-control-5
/usr/lib/telepathy/telepathy-*
/usr/sbin/cupsd
/usr/sbin/mysqld
/usr/sbin/tcpdump
0 profiles are in complain mode.
4 processes have profiles defined.
4 processes are in enforce mode.
/sbin/dhclient (1797)
/usr/lib/telepathy/mission-control-5 (2783)
/usr/sbin/cupsd (974)
/usr/sbin/mysqld (1114)
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.

sudo apt-get install apparmor-utils

root@marton-desktop:/tmp# sudo aa-complain /usr/sbin/mysqld
Setting /usr/sbin/mysqld to complain mode.
root@marton-desktop:/tmp# sudo apparmor_status
apparmor module is loaded.
19 profiles are loaded.
18 profiles are in enforce mode.
/sbin/dhclient
/usr/bin/evince
/usr/bin/evince-previewer
/usr/bin/evince-previewer//launchpad_integration
/usr/bin/evince-previewer//sanitized_helper
/usr/bin/evince-thumbnailer
/usr/bin/evince-thumbnailer//sanitized_helper
/usr/bin/evince//launchpad_integration
/usr/bin/evince//sanitized_helper
/usr/lib/NetworkManager/nm-dhcp-client.action
/usr/lib/connman/scripts/dhclient-script
/usr/lib/cups/backend/cups-pdf
/usr/lib/lightdm/lightdm/lightdm-guest-session-wrapper
/usr/lib/lightdm/lightdm/lightdm-guest-session-wrapper//chromium_browser
/usr/lib/telepathy/mission-control-5
/usr/lib/telepathy/telepathy-*
/usr/sbin/cupsd
/usr/sbin/tcpdump
1 profiles are in complain mode.
/usr/sbin/mysqld
4 processes have profiles defined.
3 processes are in enforce mode.
/sbin/dhclient (1797)
/usr/lib/telepathy/mission-control-5 (2783)
/usr/sbin/cupsd (974)
1 processes are in complain mode.
/usr/sbin/mysqld (1114)
0 processes are unconfined but have a profile defined.

And VOILA:

mysql> select sys_eval('id');
+--------------------------------------------------+
| sys_eval('id') |
+--------------------------------------------------+
| uid=118(mysql) gid=128(mysql) groups=128(mysql)
|
+--------------------------------------------------+

I debugged it down to the level of popen("ls", "r") even wrote a simple C program to test it with the mysql user and it worked, but not from the server. It was driving me crazy. I hope I'll save a few innocent souls from this.

Best regards
Marton

Tisia said...

To everyone who otherwise would spend 2 days on this problem debugging, and rebuilding the plugin, reading logs and searching the internet.

UBUNTU users, switch off AppArmor! Well even if you do not switch it off enable complain mode for MySQL.

I kept getting this result from MySQL

mysql> select sys_eval('id');
+----------------+
| sys_eval('id') |
+----------------+
| |
+----------------+

root@marton-desktop:/tmp# sudo apparmor_status
apparmor module is loaded.
19 profiles are loaded.
19 profiles are in enforce mode.
/sbin/dhclient
/usr/bin/evince
/usr/bin/evince-previewer
/usr/bin/evince-previewer//launchpad_integration
/usr/bin/evince-previewer//sanitized_helper
/usr/bin/evince-thumbnailer
/usr/bin/evince-thumbnailer//sanitized_helper
/usr/bin/evince//launchpad_integration
/usr/bin/evince//sanitized_helper
/usr/lib/NetworkManager/nm-dhcp-client.action
/usr/lib/connman/scripts/dhclient-script
/usr/lib/cups/backend/cups-pdf
/usr/lib/lightdm/lightdm/lightdm-guest-session-wrapper
/usr/lib/lightdm/lightdm/lightdm-guest-session-wrapper//chromium_browser
/usr/lib/telepathy/mission-control-5
/usr/lib/telepathy/telepathy-*
/usr/sbin/cupsd
/usr/sbin/mysqld
/usr/sbin/tcpdump
0 profiles are in complain mode.
4 processes have profiles defined.
4 processes are in enforce mode.
/sbin/dhclient (1797)
/usr/lib/telepathy/mission-control-5 (2783)
/usr/sbin/cupsd (974)
/usr/sbin/mysqld (1114)
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.

sudo apt-get install apparmor-utils

root@marton-desktop:/tmp# sudo aa-complain /usr/sbin/mysqld
Setting /usr/sbin/mysqld to complain mode.
root@marton-desktop:/tmp# sudo apparmor_status
apparmor module is loaded.
19 profiles are loaded.
18 profiles are in enforce mode.
/sbin/dhclient
/usr/bin/evince
/usr/bin/evince-previewer
/usr/bin/evince-previewer//launchpad_integration
/usr/bin/evince-previewer//sanitized_helper
/usr/bin/evince-thumbnailer
/usr/bin/evince-thumbnailer//sanitized_helper
/usr/bin/evince//launchpad_integration
/usr/bin/evince//sanitized_helper
/usr/lib/NetworkManager/nm-dhcp-client.action
/usr/lib/connman/scripts/dhclient-script
/usr/lib/cups/backend/cups-pdf
/usr/lib/lightdm/lightdm/lightdm-guest-session-wrapper
/usr/lib/lightdm/lightdm/lightdm-guest-session-wrapper//chromium_browser
/usr/lib/telepathy/mission-control-5
/usr/lib/telepathy/telepathy-*
/usr/sbin/cupsd
/usr/sbin/tcpdump
1 profiles are in complain mode.
/usr/sbin/mysqld
4 processes have profiles defined.
3 processes are in enforce mode.
/sbin/dhclient (1797)
/usr/lib/telepathy/mission-control-5 (2783)
/usr/sbin/cupsd (974)
1 processes are in complain mode.
/usr/sbin/mysqld (1114)
0 processes are unconfined but have a profile defined.

And VOILA:

mysql> select sys_eval('id');
+--------------------------------------------------+
| sys_eval('id') |
+--------------------------------------------------+
| uid=118(mysql) gid=128(mysql) groups=128(mysql)
|
+--------------------------------------------------+

I debugged it down to the level of popen("ls", "r") even wrote a simple C program to test it with the mysql user and it worked, but not from the server. It was driving me crazy. I hope I'll save a few innocent souls from this.

Best regards
Marton

Unknown said...

Marton Tatai.

Ty bro, you are awesome!

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