Category Archives: mysql

UPS for Raspberry Pi 2

Pi 2Being a Sunday I was lying in bed early this morning pondering life when I was reminded that we had 2 short power cuts in a row the night before due to me overdoing the electric heaters here in our cave in Spain where we’re having a short break.  The laptop of course survived no problem due to internal battery but the Raspberry Pi went stone cold dead on me.

It occurred to me as I load more and more onto this tiny computer including a MYSQL database, that such cuts could do damage to the microSD card and indeed people have said that occasionally they go bad.

It occurred to me as I was sitting starting at one of the many unused “emergency power” solutions I have for my phone lying around that one of these would make an ideal “uninterruptable power supply” for the Pi. Indeed a very trivial script attached to one of the input pins could easily monitor the incoming power and do a nice, graceful shut down in the event of power loss.

I have one of those “Juicebar” devices, half the size of the Pi that accepts USB power and/or solar charge and with maybe 200ma capability could easily keep the Pi running for half an hour or more. Aside from the obvious advantage of protecting against power loss, I have no doubt these units will also protect to some extent from power surges.

And so that’s the plan. One wasted piece of kit lying around doing nothing could now save the bacon next time we have a power cut. These devices can be had for as little as £2.99 from Ebay (minus the solar panel) so really it just makes sense.

Up market at £10, the solar chargers come with 10,000maH capacity – that’s well over a day of operation and the solar panel can actually make a small contribution to the running costs!

Update March 26,2015: Just as has been mentioned it has come to my attention that many designs of these power systems will NOT allow you to use them in this way - you'll have to test. I have 4 such supplies - two will act as uninteruptables, the other two will not charge and be used at the same time (which seems a bit daft).

Update June 24,2015:  - this one’s a winner – no guarantees though.


Graphing Wonders

Tonight I’ve been building on my earlier work on graphing, if you read a few blogs back you’ll know I got some basic graphing running on the Raspberry Pi 2, taking MQTT data from a sensor and storing it in MYSQL.

Well that was fine apart from the graphs having no anti-aliasing and hence looking a bit naff. The big problem was I could not get my head around how to handle missing items- if you have a sensor sending MQTT messages on a regular basis, well, you might have any number of reasons why some might be missed. If relying on waking up for a solar installation or just you’ve been in there and been messing with the unit or upgrading it – you could end up instead of a nice set of regular readings, some chunks missing!

Well, as of this evening the whole graphics library is in the bin, saving me some room in the PI. I discovered – which for all except proper commercial use can be had for free. They use JQUERY and 2 highcharts libraries all of which are on CDNs and hence you only need 3 links to access them.

I suggest you go look at DEMOS, BASIC LINE, TIME DATA WITH IRREGULAR INTERVALS. It’s wonderful. ONE PHP/Javascript page does this.

Not only do you get a relatively simple page but it’s HTML5, the imagery is really nice etc. AND you’re looking at filling in with data including the time-date and your reading…. either one graph – or many lines superimposed and if the latter, colouring the different lines and matching them up is all done automatically, you don’t have to lift a finger. Took me less than an hour to take the Javascript code they supply, bang some PHP in there to populate the table with data from mysql and produce nice graphs on demand all done inside the Raspberry Pi. Pretty much all modern browsers will support the output…including the naff browser that comes with the Pi itself.

Here’s a sample – remembering that the data from the two lines were at completely different intervals and one had more samples than the other! All scaling, smoothing etc. is done completely automatically.


And if you don’t like the idea of relying on external libraries you can of course download the lot onto your Pi. Personally, I look at it this way, if the Internet isn’t working, I can’t access the graphs anyway! Hopefully now I’m using my un-interruptible power supply idea that will happen less and less (the bottom of the range Juicebar was involuntarily tested this evening – I forgot to plug it in – it powered the Pi for nearly 2 hours).

I ended up bringing everything into a PHP function with 2 arguments – the title of the line – and the SQL statement needed to get the results. I store all of my temperature readings with 3 fields – the value, a timedate stamp and the location – so I can easily select for example all front room readings from X date to Y  date. The titles and data above are just dummy data I entered into the database. I have 5 months worth of internal, external temperature and internal humidity data available to import from Grovestreams as soon as I figure out how to do it.

If you look at their examples here you’ll see the source code is simply not that hard and dropping in some PHP to replace the static figures takes no time. This has saved me so much time and effort.

After some messing about, automating commas in the right place and struggling with the ridiculous fact that PHP doesn’t by default recognise global variables in a function (EH!!!?!!?!?!) I finally reduced the effort to adding lines to the graph to this..

drawgraph("Autumn","select value,UNIX_TIMESTAMP(logged) as logging from readings where location='office'");
drawgraph("Spring","select value,UNIX_TIMESTAMP(logged) as logging from readings where location='kitchen'");

That’s it… as many lines as needed.  I’m sure once you look at this if it’s of use you’ll come up with your own scheme using your own preferred language.

I really do need to back up this Pi. There has to be a better way than turning it off for several hours to copy the SD…


Pi Graphing Progress

Making great progress with the Raspberry Pi despite not knowing Linux from a hole in the ground a week ago.  We’re at our place in Spain for a short break with limited facilities (I’ve ordered a USB scope for the next time we’re here).

As of this morning, I’ve managed to get Node-Red running on the Pi 2 (well, the more important modules anyway including Twitter, email, ports and MQTT and I can turn the facility on and off as a service. A hell of a lot of the modules have dependencies that don’t seem to want to load).  I also have MYSQL in there.

I have just finished a PHP page running on Apache with JP-Graphs (free and wonderful) all on the Pi 2, taking data from MYSQL and plotting.


So what you’re looking at above is dummy data on the Pi, served on a Pi web page – the blue line is coming from the MYSQL database but after I’ve finished writing this and entering more dummy data all 3 will come from the database. The next stage will be to take in MQTT message from ESP8266 devices which will be sent to mysql every minute but logged less frequently until I find how more about how the Pi uses the SD memory – bearing in mind this stuff has limited write life. writing rules for temperature control will be a different matter as I’ve no gut feeling for the scripting language yet.

Coming along quite nicely – between that and the discovery that I can get NETIO to talk to Node-Red, I might not need OpenHab after all.

On the Arduino side we’re looking at making a development of what we call the Aiduino, an Atmega1284-based 5v board, taking it down to 3v3 and sticking an ESP-12 on the end – why? Because I have it on good authority that we’re near to getting some code to handle both MQTT and serial control for the ESPs… which makes matching them with Arduino good sense – the Arduino can handle the intensive stuff like fading serial LEDs without interfering with incoming MQTT messages.  Very exciting stuff.  

Don’t ask me how I installed Node-Red, it was bodged but before long the Node-Red site itself will have updated info for Pi 2 no doubt.

It did not help that the Node-red block “mysql” doesn’t exactly have millions of examples – but with a little help from the one of the guys maintaining it – I cracked that, too. Here’s an example of getting a value into a mysql database from MQTT.



So I have an MQTT node taking in from the topic “testtemperature”. It passes this through a function node which turns that into a SQL INSERT. Here’s the code in the “function” block…(with luck this will save some of you a lot of messing about)

var newMsg = { payload: msg.payload };
newMsg.topic="insert into readings (location,value) values (\"Kitchen\","+newMsg.payload+")";
return newMsg;

And the final bit the MQTT node – it is simply expecting the query in the incoming topic – it simply connects to the local mysql database and runs that query.

A few values added using mqtt-spy….

mqtt spy

Note that last one at 99… and the updated graph shows this! This is real and working, not a dummy.


I’m storing a title and a value in the database – so I can extract the latest values for each of 3 different inputs along with an automatic timestamp. I’ve not quite figured out how to ensure they all match up time-wise yet… but it’s a start….


Flashing Lights on Raspberry Pi 2

So – having managed to get WordPress up and running on the P2 I thought it might be nice to step back toward the original plan and get some home control running. I like PHP so I thought that it might be nice to have a PHP page with some security running – with buttons. I stumbled on an article that suggested the first thing to do is get phpMyAdmin running – so that you can mess around with a database on the Pi.

phpMyAdminSure enough, 10 minutes after discovering this article I was up and running with phpMyAdmin in a browser. Amazing.

Then this link – which takes a little longer than 10 minutes…

So that gets you a nice web page with which you can control pins – once you have logged in of course.

And all of that is great, but I don’t like the constant polling of the database every 5 seconds – from my brief exposure to this, the PHP page accepts buttons and returns the status – a background script looks at the database and sets GPIO accordingly.  Put it this way it’s never going to replace OpenHab but at least out of the process you get a little experience of all of this, not to mention phpMyAdmin – and in my case a little better understanding of moving files and privileges around in the Pi… and it’s nice to know this older stuff works in the new model! I did have issues with the higher pins not doing anything but I’m far to new to this device to claim it’s anything other than potential finger trouble or some difference with Pi 2 perhaps…