MySQL pushing metadata changes with Node.JS and MQTT

From day 1 that I started using a database to store the device metadata, I had to restart my Home Automation system to refresh the metadata. Now, with a more distributed system with multiple processes running on multiple systems, this became a “problem”. So lets automate this distributed Home Automation system.

The most common changes to the metadata are changes in hardware addresses (when one of my Oregon Scientific sensors got new batteries) or completely new devices that have been added to the database.  And with multiple processes running somewhere I felt the need for ‘event driven propagation of altered metadata’ – which means that when I’m finished modifying the metadata, 1 single action should suffice to automatically update the whole system.

I’ve already developed a metadata provider for that – a Node.JS script that can query the MySQL server and that creates and publishes JSON objects of the metadata. But I didn’t like the prospect of having to manually trigger this metadata provider after I finished changing the metadata. The first thing that popped up in my mind were triggers on changing data in the database. MS SQL has them as well as MySQL. I never used triggers with MS SQL though; from what I can remember it was too much hassle to get it working, so I never implemented it – maybe it’s time to revisit triggers… I keep track of the changes I make in the metadata by updating a timestamp in the database, so that would be a good ‘trigger’, right?

I once read about User Defined Functions (UDF) being able to make system calls – let’s see if we can get this working!

The first thing I did was creating a new VirtualBox Ubuntu Server 12.04 LTS Virtual Machine, cause I didn’t want to fiddle with this on my semi-production MySQL instance. After the VM was up and running and Node.JS was installed from source, MySQL was installed, I found an article that explained pretty much what I had to do.

First I needed to install the lib_mysqludf_sys library. The accompanying install script told me I had to install libmysqlclient15-dev, so I did. I also read that the C source had to be compiled with the -DMYSQL_DYNAMIC_PLUGIN parameter so I changed the Makefile as well. Still no luck – and then I saw that the resulting .so file was copied to the wrong directory. MySQL show variables like ‘plugin_dir’ showed me that the file should be in /usr/lib/mysql/plugin but it wasn’t, so I copied it there manually. After that, the install script (which also executes the MySQL CREATE FUNCTION statements) ran fine.

Next: a trigger on the metadata version table/row that should be executed after the UPDATE:
BEGIN
DECLARE cmd CHAR(255);
DECLARE result int(10);
SET cmd=CONCAT('/home/robert/local/bin/node /home/robert/signal_metadata.js');
SET result = sys_exec(cmd);
END

BTW, I use Toad for MySQL – very nice & handy tool!

Fine… now a small Node.JS script to make it complete:
var mqtt = require('mqtt');
client = mqtt.createClient(1883, '192.168.10.13');
client.publish('metadata', 'changed');
client.end();

Done! Unfortunately it took me some time to find out that AppArmor was bugging me:

Jan 22 23:17:18 ubuntuvm kernel: [ 59.408794] type=1400 audit
(1390429038.266:9): apparmor="DENIED" operation="exec" parent=1039
profile="/usr/sbin/mysqld" name="/bin/dash" pid=1331 comm="mysqld"
 requested_mask="x" denied_mask="x" fsuid=106 ouid=0

Okay… typically one of those things you only forget once in your life 😉 After disabling it everything worked like it should.

So now the metadata which is stored in the MySQL database will be automatically propagated throughout the whole system: the MySQL trigger on the ‘metadata version’ table executes a Node.JS script, this script publishes to my MQTT broker, the metadata provider picks up this message and starts recreating all the JSON objects based on the new metadata stored in the MySQL server and publishes those objects so that all the processes (wherever they are) that need metadata to do their job, will be updated automagically – nice!

Metadata message