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

Staircase project, part 6: finished!

And it looks great too!

This post should have been made 6 weeks ago or so, but I just couldn’t find the time with all the other things I wanted (and some which I just had) to do. But this week  I realized that delaying this post any longer would probably mean it would never be made, so I sat down, read my last post about this project to see where I had ended.

The Arduino Ethernet has been replaced by an Arduino Duemilanove, because when I found out how easy it is to flash an Arduino from a Raspberry Pi, the Arduino Ethernet wasn’t the best tool for the job anymore – an Arduino Duemilanove was a better choice actually – powered by a USB cable between the Arduino and a RPi meant that I didn’t need a power supply and flashing became a breeze. Another great plus was that I could now use Serial() on the Arduino instead of Ethernet.

So how does this contraption work? The Raspberry Pi that’s near the Arduino runs a Node.JS script. This script basically controls the ‘state’ in which the Arduino is.  With the serialport module this Node script can send commands to the Arduino like ‘reset’, ‘on’, ‘off’, ‘day’, ‘night’, ‘demo’, ‘stop’.

The reset command triggers a ‘reboot’ of the Arduino by jumping to address 0. The on/off commands are used to tell the Arduino whether it should act on the light barriers being broken or not, day and night tell the Arduino whether it’s day or night in our world (which has it’s effect on the brightness of the LED strips), and the demo command… well, you can do amazing stuff with 13 individual LED strips! 😉

And of course the Arduino is Serial.print()-ing everything it does: it reports when a light barrier triggers, changes in brightness levels of the LED strips and so on.

The Node.JS script on the Raspberry is partly controlled by the rules engine I use (for day- and night-mode obviously, and for ‘off’ when nobody’s at home.

But not everything went as smooth as I thought it would. After installing everything near the stairs (RPi, Arduino, 16-channel LED driver) something continuously triggered the IR light barriers! What the …? I checked every wire, and again, and again… but I couldn’t find anything wrong, so the only thing I could do was go back in time and reconstruct everything to how it was when it did work. From there I moved forward again until the problem came back. Conclusion, after a really long Sunday: interference. I used UTP cable (a single pair) to connect the IR light barrier LEDS to the Velleman MK120 PCB – this worked well during testing in the lab, but not in the cupboard under the stairs! Too many cables over there that influenced the signal on the unshielded wires I used. Stupid, I should have known this, I guess. I just have to face the fact that I’m not good at this kind of things… after replacing the wire with a piece of shielded microphone wire everything worked again.

Some last minor adjustments in the code here and there like the interval between each LED strip switching on and the project was finished – everything worked just the way I wanted it to, the visual result is impressive and worth all the time & energy that were needed to complete it all.

And here’s the long awaited video and some pictures I made of the end result; enjoy!

sc1sc0