If you are reading this blog post, you likely already know the purpose of user-defined functions (UDF) in MySQL. If you need a quick refresher on UDF’s take a look at the MySQL reference manual.  You may have created your own UDFs as well.…

Facebook Twitter LinkedIn

I was wondering if it is possible to write a MySQL User Defined Function (UDF) in Go.  
So I tried and I got a very basic UDF working.

mysql> SELECT udf_fileexists_go("/etc/hosts");
+---------------------------------+
| udf_fileexists_go("/etc/hosts") |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT udf_fileexists_go("/nonexistend");
+-----------------------------------+
| udf_fileexists_go("/nonexistend") |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)

This is nowhere near production quality, so be careful.

The code is here:https://github.com/dveeden/udf_fileexists_go/blob/master/udf_fileexists_go.go. …

What is JSON

JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL

It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don’t solve the really tough …

What is JSON
JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL
It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don't solve the really tough …

One of the great features of Shard-Query is the ability to use MySQL proxy to access resultsets transparently. While this is a great tool, many people have expressed reservations about using MySQL Proxy, an alpha component in their production environment.

I recognize that this is a valid concern, and have implemented an alternate method of retrieving resultsets directly in the MySQL client, without using a proxy. This means that any node can easily act as the “head” node without any extra daemon, instead of having to run many proxies.

The sq_helper() routine has been checked into the git repository and is available now.

The function takes a few parameters:

• sql to run
• shard-query schema name (empty string or null for default schema)
• schema to store temp table in
• temp table name (where results are sent to)
• return result (boolean, 1 returns …

MySQL meets NoSQL with JSON UDF

I recently got back from FOSDEM, in Brussels, Belgium. While I was there I got to see a great talk by Sveta Smirnova, about her MySQL 5.7 Labs release JSON UDF functions. It is important to note that while the UDF come in a 5.7 release it is absolutely possible to compile and use the UDF with earlier versions of MySQL because the UDF interface has not changed for a long time. However, the UDF should still be considered alpha/preview level of quality and should not be used in production yet! For this example I am using Percona Server 5.6 with the UDF.

That being said, the proof-of-concept that I’m about to present here uses only one JSON function (JSON_EXTRACT) and it has worked well enough in my testing to present my idea here. The JSON functions will probably be GA sometime soon anyway, and this is a useful test of the JSON_EXTRACT function. …

New version of JSON UDF functions has been just released. This is maintenance release which only contains fix for 4 bugs:

74090/19821398 `INSTALL_JSONUDFS` ISSUES A WARNING DUE TO TRUNCATION OF `VERSION_COMPILE_OS`
74091/19692634 `JSON_DEEP_MERGE`: DEEP CRASH IN `MERGE_TREE`
74092/19692628 CRASH WITH SECOND-HAND NULL IN PARAMETERS
74097/19693198 `CREATE_NEW_VALUE`: STACK BUFFER OVERFLOW, %LLD CAN'T FIT IN CHAR[20]

Also 0.3 branch becomes stable branch and no new features will be pushed to this branch in future. Previous stable branch (0.2) removed from MySQL Labs website.

…

In my previous post about geo-spatial search in MySQL I described (along with other things) how to use geo-distance functions. In this post I will describe the geo-spatial distance functions in more details.

If you need to calculate an exact distance between 2 points on Earth in MySQL (very common for geo-enabled applications) you have at least 3 choices.

• Use stored function and implement haversine formula
• Use UDF (user defined function) for haversine (see below)
• In MySQL 5.6 you can use st_distance …

As author of MySQL JSON functions I am also interested in how development goes in another parties. JSON functions and operators in PostgreSQL, indeed, have great features. Some of them, such as operators, I can not do using UDF functions only. But lets see how these functions are interchangeable.

Note: all PostgreSQL examples were taken from PostgreSQL documentation.
First topic is syntax sugar.

In short, during an optimization or a MySQL query involving a lot of replace(replace(replace…))) I decided to compile PREG as lib_mysqludf_preg from UDF Repository for MySQL in a view to use PCRE functions directly in MySQL.

Too bad MySQL still cant not use functions for index creation, but this is not the theme of this post.

So, fist install MariaDB 10.0.6:
set the MariaDB 10.0.6 repo, I used the ftp mirror at ftp.ulak.net.tr

root@seik-centos-01:[Wed Dec 18 23:55:12][/tmp]$ cat /etc/yum.repos.d/MariaDB.repo 
# MariaDB 10.0 CentOS repository list - created 2013-12-18 18:07 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
#baseurl = http://yum.mariadb.org/10.0/centos6-amd64
baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/mariadb-10.0.6/yum/centos6-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1 …