Creating a MySQL plugin to produce an integer timestamp

This article shows how to create a MySQL-plugin that can be used to create a function which can in turn be used in stored procedures. The function will produce an integer value representing the time (to the nearest usec).

I’m working on an article for conflict detection/resolution when using MySQL Cluster asynchronous replication which requires an integer column to store a timestamp for comparison purposes. In fact, it doesn’t actually need the timestamp to represent an absolute or even a relative point in time – all it cares about is that the if the function is called twice on 2 different hosts that the 2nd call will always result in a larger number than the 1st. Obviously, in a production environment the times on the 2 hosts would need to be kept in sync.

The c code (inttime.c)

#include <mysql.h>
#include <sys/time.h>

my_bool inttime_init(UDF_INIT *initid,UDF_ARGS *args, char *message) {
  return 0;
}

void inttime_deinit(UDF_INIT *initid) {};

unsigned long int inttime(UDF_INIT *initid, UDF_ARGS *args, char *result,
                          unsigned long  *length, char *is_null, char *error)
{
  struct timeval tv;
  gettimeofday(&tv,(void *)0);
  return ((double)tv.tv_usec)+tv.tv_sec*1000000;
}

Compiling and deploying the shared library

[billy@ws1 timestamp]$ gcc -I/usr/local/mysql/include/ -fPIC -shared -o inttime.so inttime.c
[billy@ws1 timestamp]$ cp inttime.so /usr/local/mysql/lib/plugin/

Creating the function

mysql> create function inttime RETURNS REAL SONAME 'inttime.so';

Test the function

mysql> select inttime();select inttime();
+------------------+
| inttime()        |
+------------------+
| 1250080524270706 |
+------------------+
1 row in set (0.00 sec)

+------------------+
| inttime()        |
+------------------+
| 1250080524270833 |
+------------------+
1 row in set (0.00 sec)

Note that the results are different and the second result is always larger than the first – function is fit for purpose :)

Using the function from a stored procedure

mysql> CREATE TRIGGER updateTAB1 BEFORE UPDATE ON TAB1 FOR EACH ROW SET NEW.ts = inttime();

Acknowledgements

I used the c code found at http://lists.mysql.com/internals/33702 as the starting point.





3 comments

  1. Great post. I recently dealt with something like this myself, and I opted for creating a trigger that grabbed the UNIX_TIMESTAMP of a TIMESTAMP field which was automatically filled on insert. A bit ugly, but it worked.

  2. Mark Callaghan says:

    Very nice, but you ignore the possible error return value from gettimeofday. I have used versions of Linux on which it occasionally failed.

    • admin says:

      Hi Mark,

      a fair point – I’m using this for a proof of concept rather than for a commercial system and so I can afford to be relaxed about robustness. Some hardening might be required for a real deployment.

      Regards, Andrew.

Leave a Reply

Your email address will not be published. Required fields are marked *