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)

Compiling and deploying the shared library

Creating the function

Test the function

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

Acknowledgements

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

3 thoughts on “Creating a MySQL plugin to produce an integer timestamp

  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. Very nice, but you ignore the possible error return value from gettimeofday. I have used versions of Linux on which it occasionally failed.

    1. 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 *

Please enter. * Time limit is exhausted. Please reload CAPTCHA.