• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Featured
    • C# Tutorials
      • LinQ Tutorials
      • Facebook C# API Tutorials
    • PHP Tutorials
      • CodeIgniter Tutorials
    • Amazon AWS Tutorials
  • Categories
    • Programming
    • Development
    • Database
    • Web Server
    • Source Control
    • Management
    • Project
  • About
  • Write
  • Contact

CodeSamplez.com

Programming, Web development, Cloud Technologies

You are here: Home / Development / 2 Useful Tips For Using Gearman From MySQL UDF

2 Useful Tips For Using Gearman From MySQL UDF

April 1, 2014 by Rana Ahsan 1 Comment

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 in the way. 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 SetUp Gearman MySQL Extension:

You can download the MySQL UDF from gearman download page. After downloading, 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 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 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");
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 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};
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: to format data as JSON string and decode it inside gearman worker. I am not claiming it as the best, but 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 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 ‘SELECT’ query! But gearman comes up only with ‘SELECT’ type UDF query; now what! Would you use some temporary table to update and run the update command on it with 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 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
        );
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 comment, and I will try to help if I can. Happy coding 🙂

Share If Liked

  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pocket (Opens in new window)

You may also like

Filed Under: Development Tagged With: gearman, mysql

About Rana Ahsan

Rana is a passionate software engineer/Technology Enthusiast.
Github: ranacseruet

Reader Interactions

Comments

  1. Lulo says

    April 12, 2018 at 7:52 am

    Hello, Ali,

    I hope you can help me,
    Im trying to use gearman mysql udf and Im having character set issues with foreign characters.

    I am executing gman_do function from mysql and it returns null if the result column contains some not english character like spanish accents like á é ó.

    This is what I do with a simple query:

    select gman_do(‘shard_query_worker’,'{“sql”:”select operadora from COSTOROAMING where pais=”KAZKT” limit 1;”,”schema_name”:”default”}’);

    +—————————————————————————————————————————————————–+
    | gman_do(‘shard_query_worker’,'{“sql”:”select operadora from COSTOROAMING where pais=”KAZKT” limit 1;”,”schema_name”:”default”}’) |
    +—————————————————————————————————————————————————–+
    | {“result”:”{\”resultset\”:{\”fields\”:[{\”type\”:250,\”name\”:\”operadora\”}],\”rows\”:[[null]]},\”errors\”:false,\”sql\”:\”\”,\”has_rows\”:true}”} |
    +—————————————————————————————————————————————————–+

    Note that I get “null”

    When executed with mysql the result is::

    mysql> select operadora from mediador.COSTOROAMING where pais=’KAZKT’;
    +———————–+
    | operadora |
    +———————–+
    | KaRTel Kazajstán GSM |
    +———————–+

    There is an á character in the original data, which confuses gman_do.

    How can I fix it?

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Follow Us

  • Twitter
  • Facebook

Top Posts & Pages

  • How To Work With JSON In Node.js / JavaScript
    How To Work With JSON In Node.js / JavaScript
  • PHP HTML5 Video Streaming Tutorial
    PHP HTML5 Video Streaming Tutorial
  • How To Work With C# Serial Port Communication
    How To Work With C# Serial Port Communication
  • Facebook C# API Tutorials
    Facebook C# API Tutorials
  • LinQ Query With Like Operator
    LinQ Query With Like Operator
  • Using Supervisord Web Interface And Plugin
    Using Supervisord Web Interface And Plugin
  • Get Facebook C# Api Access Token
    Get Facebook C# Api Access Token
  • Getting Started With UDP Programming in Java
    Getting Started With UDP Programming in Java
  • Utilizing Config File In C#.NET Application
    Utilizing Config File In C#.NET Application
  • Generate HTTP Requests using c#
    Generate HTTP Requests using c#

Recent Posts

  • Building Auth With JWT – Part 2
  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read

Tags

.net angularjs apache api audio auth authenticatin aws c# cloud server codeigniter deployment docker doctrine facebook git github golang htaccess html5 http javascript jwt linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty socket.io tfs tips unit-test utility web application wordpress wpf

Footer

Archives

Follow Us

  • Twitter
  • Facebook

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Copyright © 2023