Home > Code Snippets > MySQL UDF – the first taste

MySQL UDF – the first taste

Recently for Saturn SPL, as part of a prototype discussion, I was considering a fam based trigger for a process queue. We were already familiar with queueing processes in a mysql table, with auto_increment primary key, this should not be a problem. But when the number of processes increase, the table size too increases and finally we will be forced to remove those already processed. Also more than this, we wanted the table to me as compact as possible, meanwhile should be as accurate as possible. MySQL could not (AFAIK) stamp rows with mico seconds.

A dig on the Internet landed me on the NOW_USEC(), and gave me a spark. While playing with this today, got confused why mysql was telling

Can’t open shared library ‘now_usec.so’ (errno: 22 now_usec.so: failed to map segment from shared object: Permission denied)

And found MySQL UDF and AppArmor, by Bernard which pointed me in the right direction, and after a series of digging into the apparmor profiles and configurations finally, I added

/lib/** mr to the /etc/apparmor.d/usr.sbin.mysqld

After that I could define the function and tested after restarting the system to make sure that the function survives a restart.

Then proceded to modify the function by just bare cut and paste to get t_usec() which returns the usec as string. Well those who have learned C/C++ would frown on me, this is just a joke for me, since I just need to show that this works. Any further enhancements or mods would be done by guys who know more C/C++ than me. To make the long story short, I am reproducing the modified code here, with due respect to the original coder ‘Xaprb‘.


#include <my_sys.h>
#include <mysql.h>
 
#include <stdio.h>
#include <sys/time.h>
#include <time.h>
#include <unistd.h>
 
extern "C" {
   my_bool now_usec_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
   my_bool t_usec_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
   char *now_usec(
               UDF_INIT *initid,
               UDF_ARGS *args,
               char *result,
               unsigned long *length, char *is_null, char *error);
   char *t_usec(
               UDF_INIT *initid,
               UDF_ARGS *args,
               char *result,
               unsigned long *length, char *is_null, char *error);
}
 
my_bool now_usec_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
   return 0;
}
 
my_bool t_usec_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
   return 0;
}
 
char *t_usec(UDF_INIT *initid, UDF_ARGS *args, char *result,
               unsigned long *length, char *is_null, char *error) {
  struct timeval tv;
  char *usec_time_string = result;
 
  gettimeofday (&tv, NULL);
  sprintf(usec_time_string, "%ld", tv.tv_usec);
 
  *length = 8;
 
  return(usec_time_string);
}
 
char *now_usec(UDF_INIT *initid, UDF_ARGS *args, char *result,
               unsigned long *length, char *is_null, char *error) {
 
  struct timeval tv;
  struct tm* ptm;
  char time_string[15]; /* e.g. "20060427171052" */
  char *usec_time_string = result;
  time_t t;
 
  /* Obtain the time of day, and convert it to a tm struct. */
  gettimeofday (&tv, NULL);
  t = (time_t)tv.tv_sec;
  ptm = localtime (&t);   
 
  /* Format the date and time, down to a single second.  */
  strftime (time_string, sizeof (time_string), "%Y%m%d%H%M%S", ptm);
 
  /* Print the formatted time, in seconds, followed by a decimal point 
   *      and the microseconds.  
   */
  sprintf(usec_time_string, "%s.%06ld\n", time_string, tv.tv_usec);
 
  *length = 21;
 
  return(usec_time_string);
}
 

The output of tests

mysql> replace into process_queue values ('ACV_Full_page.pdf', 3, now(), t_usec() * 1);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from process_queue order by Updated_Secs,Updated_uSecs;
+--------------------------+----------+---------------------+---------------+
| File_Name                | Site_Key | Updated_Secs        | Updated_uSecs |
+--------------------------+----------+---------------------+---------------+
| Muthoot_Layout-Small.pdf |        2 | 2009-06-21 11:08:05 |        928946 | 
| MM_Layout-Small.pdf      |        2 | 2009-06-21 11:09:36 |        318326 | 
| GM_Layout-Full_page.pdf  |        3 | 2009-06-21 11:10:28 |         56051 | 
| KOL_Full_page.pdf        |        3 | 2009-06-21 11:16:57 |        508484 | 
| ACV_Full_page.pdf        |        3 | 2009-06-21 21:44:48 |        426591 | 
+--------------------------+----------+---------------------+---------------+
5 rows in set (0.00 sec)

mysql> desc process_queue;
+---------------+-----------------------+------+-----+-------------------+-------+
| Field         | Type                  | Null | Key | Default           | Extra |
+---------------+-----------------------+------+-----+-------------------+-------+
| File_Name     | varchar(50)           | NO   | PRI |                   |       | 
| Site_Key      | tinyint(3) unsigned   | NO   | PRI | 0                 |       | 
| Updated_Secs  | timestamp             | NO   | MUL | CURRENT_TIMESTAMP |       | 
| Updated_uSecs | mediumint(8) unsigned | YES  |     | NULL              |       | 
+---------------+-----------------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)

Categories: Code Snippets Tags:
  1. No comments yet.
  1. No trackbacks yet.

thirty five − thirty two =