How to install MySQL Gearman UDF on Ubuntu/Debian Linux

First you need to make sure you have libgearman installed, if you installed Gearman following one of my previous articles here you should be fine. If you haven't installed Gearman or libgearman yet, click here to install it first.

Assuming that you installed gearman in /opt/gearman/, you need to download the MySQL UDF client library first. Click here to find the latest source files and then replace with the file URL below.

$ wget http://launchpad.net/gearman-mysql-udf/trunk/0.5/+download/gearman-mysql-udf-0.5.tar.gz
$ tar xzf gearman-mysql-udf-0.5.tar.gz
$ cd gearman-mysql-udf-0.5
$ ./configure --prefix=/opt/mysql_udf --with-libgearman-prefix=/opt/gearman/
$ make
# make install

Now, you have installed the MySQL gearman UDF in /opt/mysql_udf/lib. You just need to let MySQL know about the location of the newly installed library. As of MySQL 5.0.67, copy the shared object to server's plugin directory and name it libgearman_mysql_udf.so. This directory is given by the value of the plugin_dir system variable.

Prior to MySQL 5.0.67, or if the value of plugin_dir is empty, the shared object should be placed in a directory such as /usr/lib that is searched by your system's dynamic (runtime) linker, or you can add the directory in which you place the shared object to the linker configuration file (for example, /etc/ld.so.conf).

On many systems, you can also set the LD_LIBRARY or LD_LIBRARY_PATH environment variable to point at the directory where you have the files for your UDF. Please make sure that whatever method you choose to locate UDFs for MySQL, it does not disappear after system restart.

Create Gearman functions for MySQL:

On MySQL command line, do the following:

mysql> use mysql;
mysql> CREATE FUNCTION gman_do RETURNS STRING SONAME  "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_do_high RETURNS STRING SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_do_low RETURNS STRING SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_do_background RETURNS STRING SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_do_high_background RETURNS STRING SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_do_low_background RETURNS STRING SONAME "libgearman_mysql_udf.so";
mysql> CREATE AGGREGATE FUNCTION gman_sum RETURNS INTEGER SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_servers_set RETURNS STRING SONAME "libgearman_mysql_udf.so";

You need to connect to a running Gearman job server from MySQL:

mysql> SELECT gman_servers_set("192.168.1.3:7004,192.168.1.4:7004");

You can also specify functions with hostnames so that UDF always connects to the listed Gearman job servers for specified functions.

mysql> SELECT gman_servers_set("192.168.1.3:7004,192.168.1.4:7004", "index");

Testing your Gearman MySQL UDF installation

I am going to use the example I explained in Generating prime nos in PHP using multiprocessing - Geaman with PHP client Part I. Create a Gearman PHP worker using the following code and run the worker (similar to how we ran the other worker explained in tutorial).

<?php
$gmworker= new GearmanWorker();
$gmworker->addServer();
$gmworker->addFunction("prime_nos_mysql", "print_primes_mysql");

while($gmworker->work())
{
  if ($gmworker->returnCode() != GEARMAN_SUCCESS)
  {echo "return_code: ".$gmworker->returnCode()."n";  break; }
}
function print_primes_mysql($job)
{
  $load    = $job->workload();
  $result  = '';
  for ($i = 0; $i <= (int)$load; $i++)
  {
    if(($i % 2) != 1) continue;
    $d = 3;
    $x = sqrt($i);
    while($i % $d != 0 && $d < $x) $d += 2;
    if(((($i % $d)==0 && $i != $d) * 1) == 0) $result .= $i.',';
  }
  return $result;
}
?>

Now, run the following in mysql cli.

mysql> SELECT gman_do("prime_nos_mysql", '43');

The output should be similar to the following:

mysql> SELECT gman_do("prime_nos_mysql", '43');
+----------------------------------------+
| gman_do("prime_nos_mysql", '43')       |
+----------------------------------------+
| 1,3,5,7,11,13,17,19,23,29,31,37,41,43, |
+----------------------------------------+
1 row in set (0.02 sec)

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production.

Comments (write a comment):

good day, i really learnt from your tutorial.
The only issue i have is that my mysql is returning a null set...........


mysql> SELECT gman_do("prime_nos_mysql", '43');
+----------------------------------+
| gman_do("prime_nos_mysql", '43') |
+----------------------------------+
| NULL |
+----------------------------------+
1 row in set (0,02 sec) Posted by: femi on Sep 11, 2015

@femi: have you made sure Gearman servers are running?

What version of Gearman, MySQLd are you using? Posted by: syed on Oct 13, 2015

leave a comment on tutorial leave a comment