We have an “untouchable” application here, a huge Java whatsit which, among a gazillion other things, stores hostnames, IP, and hardware addresses in a MySQL database. My task is “simple”: plug a BIND nameserver onto that and ensure a timely addition of Address (A) resource records into a DNS zone.

I’m suggesting we implement this as a two-part process:

1. The first part will be a MySQL User Defined Function (UDF) – a function in a shared object that can be dynamically added to (and removed from) MySQL. The function will record a database operation (update, insert, delete) in a file on the file system. (Somewhere along the line, MySQL renamed these to Loadable Functions to better indicate what they actually are.)
2. The second part will handle these notification files asynchronously and issue the dynamic DNS updates. Here again, I’m not going to “poll” a filesystem directory – instead relying on the inotify subsystem informing me when a file is created by the UDF. (The UDF could update the DNS directly, but I prefer keeping a bit of code that runs in MySQL’s address space as simple as possible.)

User Defined Function in MySQL

Adding a UDF to MySQL is not terribly difficult. You basically have to

• Create a shared object containing your function(s).
• Create a MySQL function to load the shared object into MySQL’s address space.
• Use the UDF.

Here’s the source of my function. (Some of you may recall I showed basically the same UDF in Appendix F. of my book, where we used that to add a zone clause to a configuration file when a new zone is added to a database back-end.)

/* tmod.c (C)2011 by Jan-Piet Mens.  MySQL UDF 
 * gcc -Wall -fPIC -I/usr/include/mysql -shared -o /usr/lib64/tmod.so tmod.c
 */

#include <stdio.h>
#include <string.h>
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <m_string.h>
#include <time.h>

#define TOUCHDIR    "/tmp/tmod"    // must be writable by mysql!
#define BLEN        1024

static pthread_mutex_t LOCK_tmod;

/* mysql> CREATE FUNCTION tmod RETURNS INTEGER SONAME "tmod.so";  */

my_bool tmod_init(UDF_INIT *iid, UDF_ARGS *args, char *message)
{
    if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT) {
        strmov(message,"single arg must be STRING");
        return 1;
    }

    iid->max_length = BLEN;
    iid->maybe_null = 0;

    pthread_mutex_init(&LOCK_tmod, MY_MUTEX_INIT_SLOW);
    return 0;
}

void tmod_deinit(UDF_INIT *iid)
{
    pthread_mutex_destroy(&LOCK_tmod);
}

longlong tmod(UDF_INIT *iid, UDF_ARGS *args, char *isnull, char *error)
{
    uint len;
    char yaml[BLEN], path[BLEN], pfmt[64];
    FILE *fp;
    struct timeval tv;
    struct tm *tm;

    if (!args->args[0] || !(len = args->lengths[0])) {
        *isnull = 1;
        return 0;
    }

    len = (len >= sizeof(yaml)) ? sizeof(yaml) - 1 : len;

    memcpy(yaml, args->args[0], len);
    yaml[len] = 0;

    pthread_mutex_lock(&LOCK_tmod);

    /* Construct filename */
    gettimeofday(&tv, NULL);
    tm = localtime(&tv.tv_sec);
    strftime(pfmt, sizeof(pfmt) - 1, "%%s/%F.%H:%M:%S.%%06u", tm);
    sprintf(path, pfmt, TOUCHDIR, tv.tv_usec);

    if ((fp = fopen(path, "w")) != NULL) {
        fprintf(fp, "%s\n", yaml);
        fclose(fp);
    }

    pthread_mutex_unlock(&LOCK_tmod);
    return 1L;
}

The UDF I’m creating here returns a single integer value because I don’t need more, but a UDF is free to return a string. The function takes a string and writes that into a new file on the file system. The filename is constructed from the current timestamp and the file’s content is the string passed to it.

MySQL database trigger

After building the UDF I add it and a required database trigger to the database. I’m discussing INSERT only, but you get the idea of what is required for UPDATE and DELETE:

CREATE FUNCTION tmod RETURNS INTEGER SONAME "tmod.so";

DROP TRIGGER IF EXISTS tmodTrigINS;

DELIMITER $$
CREATE TRIGGER tmodTrigINS AFTER INSERT ON t
FOR EACH ROW
BEGIN
    SET @error = tmod(CONCAT("---\nop: ins\nid: ", NEW.id,
                      "\nmac: ", NEW.mac, "\nip: ", NEW.ip,
		      "\nhost: ", NEW.hostname));
END $$
DELIMITER ;

That completes the preparation: we’ve loaded the UDF into MySQL and have created a trigger that will fire the UDF upon inserting a row. The trigger formats the column values as a YAML string using MySQL’s CONCAT() function. Will it blend?

INSERT INTO t VALUES (NULL, '00:00:00:01:02:03', '192.168.1.98', 'www');
Query OK, 1 row affected (0.00 sec)

SELECT * FROM t;
+----+-------------------+--------------+----------+
| id | mac               | ip           | hostname |
+----+-------------------+--------------+----------+
|  3 | 00:00:00:01:02:03 | 192.168.1.98 | www      | 
+----+-------------------+--------------+----------+

As soon as I insert the row into the database table, a new file shows up in the drop directory: cat 2011-08-03.18:14:10.974320

---
op: ins
id: 3
mac: 00:00:00:01:02:03
ip: 192.168.1.98
host: www

I create similar triggers for UPDATE and DELETE operations. So after each database manipulation on the table, I get a new YAML file created for it. But how do I process those files in a timely fashion?

Inotify and Python

Thanks to Linux’ inotify subsystem, we can easily react to files created in the drop directory by the UDF. I’ll show you an example using pyinotify, but you could just as well use any other tool with inotify support. (You may wish to look at the incron, lsyncd, pirsyncd, or watchdog projects for inspiration.)

The Python program notif.py follows:

#!/usr/bin/env python

import pyinotify
import yaml
import dns.update
import dns.query
import dns.tsigkeyring
from pprint import pprint as pp

config = {
  'dnsserver'  : '127.0.0.1',
  'dnsdomain'  : 'example.net',
  'ttl'        : 3600,
  'dnskeyname' : 'doll',
  'dnskeyblob' : '3Bwrezcc5 ... 1HYstfzmPDQ=='
}

def dnsupdate(nodename, ip, dnsdomain="example.net"):
    keyring = dns.tsigkeyring.from_text(
        { config['dnskeyname'] : config['dnskeyblob'] });

    update = dns.update.Update(dnsdomain,
                keyring = keyring,
                keyname = config['dnskeyname'],
                keyalgorithm = 'hmac-sha1')

    domain = '%s.%s.' % (nodename, dnsdomain)
    update.replace(domain, config['ttl'], 'A', ip)
    response = dns.query.udp(update, config['dnsserver'], timeout=10)
    print dns.rcode.to_text(response.rcode())

def dofile(filename):
    y = yaml.load(open(filename, 'r'))
    pp(y)
    if y['op'] == 'ins':
        dnsupdate(y['host'], y['ip'])

if __name__ == "__main__":

    wm = pyinotify.WatchManager()
    mask = pyinotify.IN_CLOSE_WRITE

    class EventHandler(pyinotify.ProcessEvent):
        def process_IN_CLOSE_WRITE(self, event):
            print "Gotcha:", event.pathname
            dofile(event.pathname)

    handler = EventHandler()
    notifier = pyinotify.Notifier(wm, handler)

    wdd = wm.add_watch('/tmp/tmod', mask, rec=False)

    notifier.loop()

The program sets up a notification handler (basically the tutorial example) waiting for events of type IN_CLOSE_WRITE which are fired when writable files have been closed (i.e. writing to the file is complete). When an event is received, the EventHandler() calls dofile() to open the YAML file and process it. In this case, processing means check what type of database operation (insert, update, delete) was recorded and proceed to update the DNS accordingly. (The example above handles DNS add/replace only.)

Gotcha: /tmp/tmod/2011-08-03.18:14:10.974320
{'host': 'www',
 'id': 3,
 'ip': '192.168.1.98',
 'mac': '00:00:00:01:02:03',
 'op': 'ins'}
NOERROR

Please note:

• There is no way of returning an error to MySQL at this point: the database server has long completed its data manipulation operation and is busy doing other things. :)
• The notif.py should really clean up the files it has processed, e.g. by moving them into a “done” directory or simply unlink()ing them.
• The spurious NOERROR above, is the result of the dynamic DNS update.

This architecture is a bit complex but in contrast to having a program poll the database for changes (and having to remember which updates it has already processed, particularly when it is first launched or restarted after a crash) this method is “restartable” in that we handle files in a file system which remain in place even if notif.py decides to die sometime. (A second similar utility would be needed to handle start-up.)

To test the final prototype, I fired off a batch of 1,000 INSERTS to MySQL, and nothing got lost: my UDF created 1,000 files in its drop directory, my little pyinotify-based utility fired off 1,000 signed dynamic DNS updates, and the BIND name server we’re talking to added 1,000 new resource records to its zone.