Saturday, May 26, 2007

MySQL User Defined Function Repository

Welcome to the MySQL UDF Repository! The MySQL UDF repository aims to offer an organized collection of open source MySQL user defined functions.

There are numerous MySQL UDFs available in the public domain. However, most UDFs appear standalone. Often, binaries are not included: only the source code is available. In many cases, it is not straightforward to compile binaries for different platforms. Likewise, documentation and installation instructions are either scarce or missing. Also, there is little consistency with regard to naming conventions, error handling and error reporting.

The MySQL UDF Repository tries to improve that situation.

Goals

Here's a list of things the MySQL UDF Repository aims to offer:

  • Libraries that contain a coherent collection of UDFs instead of standalone, ad hoc functions.

  • Consistent approach with regard to naming conventions, error handling and error reporting.

  • Downloads of both source as well as binaries for popular platforms.

  • Online documentation that covers compilation, installation, implementation and examples.

  • Supporting scripts to compile and install and test the libraries.

  • All libraries released using a permissive open source license

How to participate


If you want to contribute or if you want to be kept up to date of this project, please mail to mysqludfs@gmail.com.

All response and feedback is welcome. When it turns out that there is a sufficient amount of interested people, we'll setup mailinglists and/or rss feeds.

25 comments:

Anonymous said...

I've written a some UDF functions to generate XML directly in MySQL. It's based on SQL/XML, but unfortunately it doesn't have the same syntax since I could only define functions.

I've created a project for it on sourceforge. Little people found it yet though. Perhaps you can add a link to it.

http://libmyxql.sourceforge.net

Unknown said...

Hi Arnold,

Yeah I saw your UDF collection, it's great! I am thinking of adding an xml library to the repository too, using the same semantics as lib_mysql_json, see:

http://xcdsql.org/MySQL/UDF/lib_mysqludf_json/lib_mysqludf_json.html

So, instead of having functions that require {name,value} pairs, you can simply re-use the expression text to generate a name, for example:

select json_object(
customer_id
, first_name
, last_name
, last_update
) as customer
from customer
where customer_id =1;


yields this result:

+---------------------------------------------------------------------------------------+
| customer |
+---------------------------------------------------------------------------------------+
| {customer_id:1,first_name:"MARY",last_name:"SMITH",last_update:"2006-02-15 04:57:20"} |
+---------------------------------------------------------------------------------------+

By treating all expressions that start with the prefix "json_ " as special, you can easily nest calls:

select json_object(
f.last_update
, json_members(
'film'
, json_object(
f.film_id
, f.title
, f.last_update
)
, 'category'
, json_object(
c.category_id
, c.name
, c.last_update
)
)
) as film_category
from film_category fc
inner join film f
on fc.film_id = f.film_id
inner join category c
on fc.category_id = c.category_id
where f.film_id =1;


yields a string representing the following JSON object (indentation added for readability):

{
last_update:"2006-02-15 05:03:42"
, film:{
film_id:1
, title:"ACADEMY DINOSAUR"
, last_update:"2006-02-15 05:03:42"
}
, category:{
category_id:6
, name:"Documentary"
, last_update:"2006-02-15 04:46:27"
}
}

That said, your library is of high quality, so it would be great if you could join the club:

http://groups.google.com/group/mysql-udf-repository

Welcome!

jean pierre porre said...

I was looking for a percentil udf function...and found yours, it seems to be the only one that works for large recordset (i have a 120000+ recordset), however i lack of knowledge in order to modify for my especific use. This is the thing: i have a table with a column for area, another one for product, and the price, i would like to know the percentil of each product un the table in reference to the area of this product, somthing like this could work Select area, product, price, function(area,product) as percentil from table where ..., can you help me?

rpbouman said...

Hi Jean Pierre,

I am not sure what function you are reffering too. I never wrote a UDF for percentiles.

I did write about other ways of computing the percentiles, you can search them:


http://rpbouman.blogspot.com/search?q=percentile

Anonymous said...

How would you call a UDF function from a trigger?
Can you guve an example?
Thank you kindly.

rpbouman said...

Hi Anonymous,

see:
http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html

vishakha Naiknimbalkar said...

I Need to call php application from mysql trigger
But getting problems in UDF functions sys_exce
Please guide me how to do it

rpbouman said...

@vishakha Naiknimbalkar,

please, this is a very unhelpful comment. Problems? what problems? Can you call it from outside a trigger?

Fil said...

Hello,

I installed lib_mysqludf_sys and I currently use sys_exec to execute a perl script that loads the data into the database.

I have a scheduled procedure that loads the data from text files and then manipulates it. My problem is that it starts manipulating the data while the data is still in the process of being loaded.
How can I tell the server (through the procedure) to wait for sys_exec to end before starting doing something else ?

Thank you

rpbouman said...

Hi Fil,

sys_exec and sys_eval are a thin wrapper around the C system call (http://en.wikipedia.org/wiki/System_(C_Standard_Library)) To the best of my knowledge, this should block until the shell command is executed. So, the first thing to find out is why you're experiencing side effects from what seems to be asynchronous processing.

I think you should first exclude the possibility that the command you pass to sys_exec starts a new process. If that is the case, it's up to you to find another way to do whatever you're doing in the shell command. If that is not the case, it seems reasonable to suspect there is some flaw in your scheduling logic. I don't know if there is something in place to prevent your scheduled procedure from being re-entered, but if that is not the case, you should add some kind of lock to prevent the process from being started while a previous instance is still running.

I hope this helps,

kind regards.

aris_sety said...

Hi Roland,

Thank's for your all detail explanation about UDF, those make me easy to implement UD[aggregate]F.

Can I create UD[aggregate]F in MySQL cluster environment?

rpbouman said...

Hi aris_sety,

thanks for the kind words.

Using UDFs on MySQL Cluster is not much different from using them on a single node, but there's a few things to keep in mind, and you'll have to take care of a few things yourself.

You'll have to manually copy the shared object to all SQL nodes, and I think you'll have to run tthe CREATE FUNCTION statement yourself too, at least, I don't think that that propagates transparently across the SQL nodes (but I may be wrong about that)

Anonymous said...

Hi Roland,
I have created a Shared Object mysql_udf.so but when i tried to create a function in mysql 5.0.46 it says as

ERROR 1126 (HY000): Can't open shared library 'mysql_udf.so' (errno: 22 mysql_udf.so: cannot open shared object file: No such file or directory)

I placed the mysql_udf.so in /mysql/bin directory.

Thanks,
KumsManju

rpbouman said...

@anonymous, what is your CREATE FUNCTION statement? My initial guess is that you mistyped the filename. Please use absolute path.

Anonymous said...

Hi Roland,

CREATE FUNCTION inet6_ntop RETURNS STRING SONAME "mysql_udf.so";
I chkd no mistyping. Think the problem is with the path. I tried with some of combinations but all in vain.

Thanks,
KumsManju

rpbouman said...

Hi KumsManju,

try putting the .so file in /usr/lib (for 32bit systems) or /usr/lib64 (for 64 bit systems)

HTH

Anonymous said...

Hi Roland,
Thats works well. I am having another doubt is there any way to instruct mysql to look for mysql_udf.so in directory other than /usr/lib.

First of all i like to thank u for your blogs. I learnt a lot from it. Very much useful.

Thanks,
KumsManju.

rpbouman said...

Hi KumsManju,

thanks for the kind words. I'm glad you find the blog useful.

"is there any way to instruct mysql to look for mysql_udf.so in directory other than /usr/lib."

In MySQL 5.0, mysql just relies on your platform's linker. In linux that will probably be ld. You should refer to its documentation to figure this out.

In MySQL 5.1 you can only load plugins from the plugin directory. You can specify the locatin of this directory explicitly as a mysql server variable. See

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_plugin_dir

Anonymous said...

Hi Roland,

Thanks for the quick response and for the useful info. Hope i can figure it out soon.
Thanks a lot.

Thanks
KumsManju

Manish Kumar Gupta said...

How to install the DLL in windows 7 64 Bit os with 5.6.11-enterprise-commercial-advanced.

I have copied the DLL files to plugins directory and trying to create function but im getting "Error Code: 1126. Can't open shared library "......

My Plugin Directory is C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin

Manish

rpbouman said...

Hi,

sorry, no idea. Contact your support.

Anonymous said...

Hello Roland. This blog is interesting as I'm attempting to write a UDF to open files and add them to my database. Unfortunately, the links to the various file locations in the blog are broken. Have they been moved?

rpbouman said...

Hi Anonymous!

The UDF repository sources are now all on github:

https://github.com/mysqludf

HTH

Cassiano said...



I'm trying it with Ubuntu 64 without success...

My MakeFile I've changed to this:

LIBDIR=/usr/lib/mysql/plugin

install:
gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.so -fPIC

I've added this in /etc/mysql/my.conf

plugin_dir = /usr/lib/mysql/plugin

Changed the permissions of /usr/lib/mysql/plugin

$ sudo chown mysql: mysql -R /var/lib/mysql/plugin

Restarted mysql

sudo service mysql restart

When I run you example command:

mysql> SELECT sys_exec ('touch /var/lib/mysql/test.txt ');
+---------------------------------------------+
| sys_exec ('touch /var/lib/mysql/test.txt ') |
+---------------------------------------------+
| 32512 |
+---------------------------------------------+
1 row in set (0.00 sec)

or

mysql> SELECT sys_eval('id');
+----------------+
| sys_eval('id') |
+----------------+
| NULL |
+----------------+
1 row in set (0.40 sec)

or

mysql> SELECT sys_eval('cp /home/cassiano/Desktop/index.html /home/cassiano/');
+------------------------------------------------------------------+
| sys_eval('cp /home/cassiano/Desktop/index.html /home/cassiano/') |
+------------------------------------------------------------------+
| |
+------------------------------------------------------------------+
1 row in set (0.02 sec)

Nothing happens...
I've already checked /var/log/mysql/error.log and it is empty

Any ideas?

I've already opened an issue at Github https://github.com/mysqludf/lib_mysqludf_sys/issues/4

rpbouman said...

Usually it's apparmor that prevents sys_exec from working.

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