Home Monitoring Upgrade (Part 2) – HSQLDB to MySQL

As mentioned in my previous post (see http://blog.v-s-f.co.uk/2017/04/home-monitoring-upgrade/), the first task I have is to migrate from HSQLDB to MySQL.

Because the system logs data every minute while I have power in the house and I want to minimise downtime when I actually have a fully working upgrade, I’ve experimented with a copy of the live HSQL database.

Once I’d copied it from my server to my laptop I then attempted to view the data in notepad++ – yeah, not a particularly smart move! NP++ cannot handle files over about 100M. It also turns out (having more’d the .data file) that the data is not in a readable state. I had a performance issue with a select query a long time back and changed the table to cached.

So, luckily at work a colleague had introduced us all to a great database tool called SQL Workbench. It can work with most databases and unlike SQuirreL, it doesn’t crash when looking at the work DB2 database.

Using SQL Workbench, I’ve then created a script file which creates the new consolidated table and loads data from the old tables in to the new + drops the old tables. The end result is a 122,398KB HSQLDB script file which is human readable.

Next step was to get MySQL running on my server. Instead of installing it directly though, there’s a Docker image available.

My first few attempts at inserting the data from the HSQLDB file in to the MySQL database were less than impressive! One of the attempts had the server running flat out (100% cpu) for over an hour when I finally decided that it was probably not going to complete the import this year and nuked it!

So having learnt a few lessons about not using single row inserts(!), but batching them in to multi-row inserts of 100,000 and a few MySQL deafult parameter increases (although I’m not sure if these are necessary as the batch inserts seemed to make most difference), I was finally able to import the data. It still took a few minutes from running the MySQL container to it being available – but that’s significantly better than running for hours importing the data!!

Now that I have the necessary scripts and knowledge to migrate the data, the next part is re-writing the application that receives the Arduino data, uploads to PVOutput and serves the hot water display Arduino.

Home Monitoring Upgrade

I’ve been monitoring stats from my meter, weather and hotwater tank for over two years now (see http://blog.v-s-f.co.uk/2015/04/home-monitoring-home-made-reborn/) and the application now needs an upgrade.

I now want to log more data from the weather station (temperature and humidity). This should be as simple as adding two new columns to the HSQLDB, changing the application to write in to the two new fields and adding two new fields to the service definition, but it’s not quite that straight forward…

The old app uses an out dated version of Mule on Tomcat in Docker and it’s far too heavy weight for what it needs to be. Therefore it’s time to give it a revamp.

It’s also occured to me recently that instead of storing the data in five separate tables (one for generation, upload info, hotwater, meter and weather data), why not store it in one table. This saves a significant amount of space as there are four less records per minute and it makes adding new columns for additional data sources relatively quick. The HSQLDB that I’ve been using for a while now is over 400M!

So the first task, which is possibly the biggest, is to migrate the data from the five tables in HSQLDB to a single table and then stop using HSQLDB and migrate to MySQL. Why MySQL – it’s actually quite a performant database, it’s free and easy to get running.

Why the news about Tesco Bank today doesn’t surprise me!

There’s a headline on the BBC that has caught my eye today – “Tesco Bank blames ‘systematic sophisticated attack’ for account losses” – and I’m not at all surprised given my experience back in 2009 when I had a Tesco Bank credit card account.

In 2006, before I got my first proper (full time) job, I opened a credit card account with Tesco. Once the card arrived and all was well, I proceeded to set up online banking as it’s the most convenient method of operating accounts and gave the new account a unique password – as I do for all my accounts!

Roll on 2009 – I got home from work one day to find an envelope from Tesco Bank with a copy (not the original) of one of my statements… A brief “Huh?” moment and thought nothing of it, shredded it and forgot about it.

Then a while later I received a second copy of one of my statements and alarm bells rang!!

I rang Tesco Bank, asked why I’d recevied a copy of my statement, to which the advisor said they didn’t know, but I ordered it. I asked the advisor what the date and time was and I was given a date and time while I was sitting in a clients meeting room regarding a project we were working on… I was not impressed. I questioned how it was possible given that I was in that meeting with no phone or internet and was advised that perhaps I’d given my account details to someone else!! To which I replied, you have a problem with security and I want to close my account now.

I was never asked any further questions by the advisor and no one ever contacted me afterwards, so I can only assume the advisor thought I was nutty… but I know for a fact that the account details were never shared, unique and very unlikely to have been guessed.