CodeSamplez.com

Programming, Web development, Cloud Technologies

  • Facebook
  • Google+
  • RSS
  • Twitter
  • 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
Home Development 2 Useful Tips For Using Gearman From MySQL UDF

2 Useful Tips For Using Gearman From MySQL UDF

Rana Ahsan April 1, 2014 1 Comment


 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 🙂

Related

Filed Under: Development Tagged With: gearman, mysql

About Rana Ahsan

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

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.

Email Subscription

Never miss any programming tutorial again.

Popular Tutorials

  • PHP HTML5 Video Streaming Tutorial
  • How To Work With JSON In Node.js / JavaScript
  • Generate HTTP Requests using c#
  • How To Work With C# Serial Port Communication
  • Facebook C# API Tutorials
  • LinQ To SQL Database Update Operations In C#
  • How To Work With CodeIgniter Pagination
  • How To Work With Multithreaded Programming In C#.NET Application
  • Get Facebook C# Api Access Token
  • How To Work With Codeigniter Caching In PHP

Recent Tutorials

  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read
  • Getting Started With Golang Unit Testing
  • Getting Started With Big Data Analytics Pipeline
  • NodeJS Tips And Tricks For Beginners
  • Apple Push Notification Backend In NodeJS
  • Web Based Universal Language Translator, Voice/Text Messaging App
  • How To Dockerize A Multi-Container App From Scratch

Recent Comments

  • intolap on PHP HTML5 Video Streaming Tutorial
  • manishpanchal on PHP HTML5 Video Streaming Tutorial
  • Rana Ghosh on PHP HTML5 Video Streaming Tutorial
  • ld13 on Pipe Email To PHP And Parse Content
  • Daniel on PHP HTML5 Video Streaming Tutorial

Archives

Resources

  • CodeSamplez.com Demo

Tags

.net apache api audio aws c# cache cloud server codeigniter deployment doctrine facebook git github golang htaccess html5 http image java javascript linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty ssh tfs thread tips ubuntu unit-test utility web application wordpress wpf

Copyright © 2010 - 2021 · CodeSamplez.com ·

Copyright © 2021 · Streamline Pro Theme on Genesis Framework · WordPress · Log in