2 Useful Tips For Using Gearman From MySQL UDF

So, are you offloading time-consuming tasks by sending them to separate distributed cloud computing instances to gain performance via the gearman job server? That’s great news! What if this needs to be performed on some creation/update of new/existing MySQL database table entries? Will you run another script to track it? Of course, that is not a good solution at all. So, then MySQL events and/or triggers come up. Can we send a job to gearman from MySQL UDF? Yes, we do. We will see how to do this with two valuable tips as well.

This is not a beginner’s tutorial on gearman. If you are new, you should consider reading the official Getting Started with gearman documentation before proceeding.

Download And Set Up Gearman MySQL Extension:

You can download the MySQL UDF from the gearman download page. After downloading, you should read the readme.txt file included there as the basic documentation. About installation, follow the configure, make and make install commands; I won’t be covering that part in detail here. Also, remember to create the UDF functions and map them to the corresponding “.so” file.

Calling To Gearman From MySQL UDF:

Now is the time to send the job to the German server. Remember to write a simple German worker running with the necessary logging to test in your preferred programming language.

You have to set up the gearman server address within your MySQL DB as below:

SELECT gman_servers_set("127.0.0.1:4730");
Code language: CSS (css)

Use your server address/port here. This doesn’t get saved permanently, so you will need to execute it every time your MySQL server starts/restarts.

Now, execute one of the UDFs that suits your needs. To run a background task in asynchronous mode, you can do something like the following:

SELECT gman_do_background("event_name", CONCAT(column1,',',column2)) FROM mytable_name WHERE {condition};
Code language: JavaScript (javascript)

Tip 1: Sending More Complex Data To Gearman:

Using commas or such other symbols as a delimiter might not be a good solution all the time as there could be data that has much in it
And parsing data would be difficult in such situations. I have come up with a better solution: format data as a JSON string and decode it inside a gearman worker. I am not claiming it to be the best, but it is working fine for my purpose. If you have any other better solution, I would love to hear about that. So, here it goes:

SELECT gman_do_background("gearmanEventName",
                	CONCAT("[",
                              GROUP_CONCAT(
                                   CONCAT("{'id':'",id,"'"),
                                   CONCAT(",'comlumn1':'", comlumn1,"'"),
                                   CONCAT(",'column2':'", column2,"'"),
                                   CONCAT(",'long_data':'",long_data,"'"),
                                   CONCAT(",'json_string':'", json_string),"'}"
                              )
                         ,"]")
                )
                FROM `table_name`
                WHERE `id` = NEW.id
Code language: JavaScript (javascript)

Tip 2: Call Gearman From MySQL Trigger Implementation:

In case of the requirement to send data whenever any MySQL table row is updated(insert/edit/delete), using a trigger is a wise idea. If you are performing a synchronous call to gearman and using the return data to update the DB table, it’s OK. But what if you want to trigger a background call and nothing else? Wait! Inside trigger implementation, MySQL won’t let you execute a ‘SELECT‘ query! But gearman comes up only with a ‘SELECT‘ type UDF query; now what? Would you use some temporary table to update and run the update command on it while embedding the gearman select query? Extra overhead. I was able to come up with a much simpler solution. If you have noticed, the ‘gman_do_background’ UDF call for gearman from MySQL select query returns the’ BLOB’ result. So, if we assign the return value to a temporary MySQL variable, the trigger implementation will be accepted! Here is a code example:

DECLARE tmp BLOB;
SET tmp = (
                SELECT gman_do_background("gearmanEventName", dataColumn)
                FROM `my_table`
                WHERE `id` = NEW.id
        );
Code language: JavaScript (javascript)

Anything Else?

I hope the above tips will help you to some extent. Are you having any trouble or have any other use case where you can’t find a good solution? Let me know in the comments, and I will try to help if I can. Happy coding 🙂