Xaprb

How I built the NOW_USEC() UDF for MySQL

with 20 comments

#include <my_global.h>
#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);
   char *now_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;
}

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[20]; /* e.g. "2006-04-27 17:10:52" */
  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 = 26;

  return(usec_time_string);
}

baron@tigger now_usec $ make
gcc -fPIC -Wall -I/usr/include/mysql -shared -o now_usec.so now_usec.cc
baron@tigger now_usec $ sudo cp now_usec.so /lib
baron@tigger now_usec $ mysql test
mysql> create function now_usec returns string soname 'now_usec.so';
Query OK, 0 rows affected (0.00 sec)

mysql> select now_usec();
+----------------------------+
| now_usec()                 |
+----------------------------+
| 2007-10-23 10:28:13.862116 | 
+----------------------------+

20 Responses to 'How I built the NOW_USEC() UDF for MySQL'

Subscribe to comments with RSS

1. Hi Baron,

   thanks for sharing this code! And, thanks for the link to the UDF repository page ;-)

   I just wanted to let you know that the code sample does not render the #include lines properly – you probably should escape the angle brackets

   #include <stdio.h>

   

   Roland Bouman

   31 Oct 07 at 8:45 pm
2. Ooh, good catch. Weird, I thought it usually handled that inside ‘pre’ tags. Thanks.

   

   Xaprb

   31 Oct 07 at 9:21 pm
3. Hi Baron,

   “I thought it usually handled that inside ‘pre’ tags”

   The best thing of pre is that it treats all white space as significant white space:

   http://www.w3.org/TR/REC-html40/struct/text.html#h-9.3.4

   However, the contents are still parsed as HTML. The greater than sign is usually printed literally but most parsers assume that the less than sign is the start of a new tag.

   A trick that sometimes work is to put everything inside pre inside a CDATA section:

   <pre>&lt![CDATA[

   #include <stdio.h>

   ...

   ]]></pre>

   

   Roland Bouman

   1 Nov 07 at 3:42 am
4. Right, what I meant was I thought I had WordPress configured to automatically entity-ify inside pre.

   

   Xaprb

   1 Nov 07 at 8:33 pm
5. Thanks for this great UDF. I need to profile some stored procedures, and this will do just the trick.

   

   Justin Swanhart

   25 Feb 08 at 3:08 am
6. For readers’ edification: Justin wrote about how to compile this UDF on Mac OS 10.4 here: http://swanhart.livejournal.com/116981.html

   

   Xaprb

   25 Feb 08 at 10:37 pm
7. Note: Since this is C++ i had to compile this with g++ . gcc also builds the library successful but when creating the function mysql returns an error like “undefined symbol: __gxx_personality_v0″

   

   Marian Neubert

   9 Jul 08 at 8:00 am
8. Yeah, sorry about that — WP has a bug with plus signs. I think it urldecodes them into spaces or something. No time to upgrade….

   

   Xaprb

   9 Jul 08 at 8:53 am
9. Handy function, however I’m curious as to the decision to make NOW_USEC() return a dynamic value (i.e. recomputed on every call) rather than a constant value like NOW().

   And, how complex would it be to modify the udf sources to enable functionality like NOW() 0, i.e. returning a decimal (or double) when used in a numeric context?

   

   Nyna

   23 Jul 08 at 8:01 am
10. Those weren’t needed for my testing, so I never thought about it.

    

    Xaprb

    23 Jul 08 at 8:57 am
11. I had just written my own, and first c program, when I found your solution thanks to the personality comment.

    I’m using this for my audit trail. Sometimes updates to a single record happen so quickly that this level of precision is necessary.

    Now that I’ve got this working happily with my stored procedures I expect I’ll need to write supporting functions built around your code. Thank you!

    

    Tom Anderson

    23 Oct 08 at 1:13 am
12. Hi,

    For the query SELECT NOW_USEC();, I have the following result : 0x323030392D30322D32362031323A30333A31312E393430383639

    Did you ever seen this bug ? Can you advice me for solving this issue ?

    Best regards
    Luc Michalski

    

    Michalski Luc

    26 Feb 09 at 7:05 am
13. That value is the hex representation of the string. Try this: select 0x323030392D30322D32362031323A30333A31312E393430383639;

    The answer I get is “2009-02-26 12:03:11.940869″

    So MySQL is casting the result for some reason. I do not know why. I would bring this up on the internals list at MySQL.

    

    Xaprb

    26 Feb 09 at 11:04 am
14. Was playing with this today, and 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 [http://bernardodamele.blogspot.com/2009/01/mysql-udf-and-apparmor.html MySQL UDF and AppArmor].

    This 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. From where I could define the function and tested after restarting the system to make sure that the function survives a restart.

    

    php-trivandrum

    21 Jun 09 at 12:16 pm
15. [...] 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 [...]

    

    PHP Trivandrum » MySQL UDF – the first taste

    21 Jun 09 at 12:37 pm
16. This has been very helpful. Thanks very much.

    

    rogerwebmaster

    28 Jan 10 at 11:58 pm
17. HELP!

    I followed the instruction from top to bottom and I was able to make the function work. However, when I restarted the Ubuntu server, the UDF cannot be called anymore. I cant even create or delete UDFs afterwards. When deleting UDFs it says : function does not exist

    When creating, it says: Cant find symbol ‘[symbolname]‘ from library.

    Did I miss anything? I need immediate help. I am stuck with this for days now.

    Thanks

    

    rogerwebmaster

    10 Feb 10 at 5:13 am
18. @Marian Neubert,@Xaprb,

    I tried but met undefined symbol: __gxx_personality_v0
    with both gcc and g++

    any idea ?

    

    cscetbon

    11 Mar 10 at 7:08 pm
19. @cscetbon
    I had the same problem on OSX: http://swanhart.livejournal.com/116981.html

    

    Justin Swanhart

    11 Mar 10 at 11:51 pm
20. im not a window program but tried to covert the code from linux to windows but i hit the wall that “gettimeofday” function is not supported on windows and need to find an alternative function and almost rewrite the whole code. and kind of giving it up…
    does any one have converted this code to windows compatible?
    kind regards
    SpringBean

    

    SpringBean

    28 Jul 11 at 5:08 am

Leave a Reply