Andrew Morgan on Databases

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.