2 Useful Tips For Using Gearman From MySQL UDF

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

This is not a beginners tutorial on gearman. If you are new, then you should consider reading the official getting started with gearman documentation first before proceeding.

Download And SetUp Gearman MySQL Extension:

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

Calling To Gearman From MySQL UDF:

Now is the time to send job to gearman server. Remember to write a simple gearman worker running with 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");

Use your own 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 need. To run a background task in asynchronous mode, you can do something like as below:

SELECT gman_do_background("event_name", CONCAT(column1,',',column2)) FROM mytable_name WHERE {condition};

Tip 1: Sending More Complex Data To Gearman:

Using comma or such other symbol as delimiter might not a good solution all the time as there could be data that has such in it
and parsing data would be difficult in such situations. I have come up with a better solution about it, to format data as JSON string and decode it inside gearman worker. I am not claiming it as best, but working fine for my purpose. If you have any other better solution, I will 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

Tip 2: Call Gearman From MySQL Trigger Implementation:

In case of requirement to send data whenever any mysql table row is updated(insert/edit/delete), using trigger is a wise idea. If you are performing a synchronous call to gearman and using the return data to update db table, it’s totally OK.But, what if you just want to trigger a background call and nothing else. Wait! inside trigger implementation, mysql won’t let you execute ‘SELECT’ query! But gearman comes up only with ‘SELECT’ type UDF query, now what! Would you use some temporary table to update and run update command on it with embedding the gearman select query? Extra overhead. I did 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 ‘BLOB’ result. So, if we just assign the return value to a temporary MySQL variable, the trigger implementation will get accepted! Here is a code example:

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

Anything Else?

Hope the above tips will help you in 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 comment and I will try to help if I can. Happy coding 🙂