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:
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
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!
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?
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
How would you call a UDF function from a trigger?
Can you guve an example?
Thank you kindly.
Hi Anonymous,
see:
http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html
I Need to call php application from mysql trigger
But getting problems in UDF functions sys_exce
Please guide me how to do it
@vishakha Naiknimbalkar,
please, this is a very unhelpful comment. Problems? what problems? Can you call it from outside a trigger?
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
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.
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?
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)
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
@anonymous, what is your CREATE FUNCTION statement? My initial guess is that you mistyped the filename. Please use absolute path.
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
Hi KumsManju,
try putting the .so file in /usr/lib (for 32bit systems) or /usr/lib64 (for 64 bit systems)
HTH
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.
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
Hi Roland,
Thanks for the quick response and for the useful info. Hope i can figure it out soon.
Thanks a lot.
Thanks
KumsManju
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
Hi,
sorry, no idea. Contact your support.
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?
Hi Anonymous!
The UDF repository sources are now all on github:
https://github.com/mysqludf
HTH
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
Usually it's apparmor that prevents sys_exec from working.
Post a Comment