Its been bugging me for over 2 years now… I have two main databases (3 in total, but the third is pretty static – its the major airports in the world).
The first is a list of ‘all’ aircraft in the world. Its about 380,000 aircraft, just the big stuff mostly, not every little privately owned Cessna, there is not much interest in all that fluff.
The list has the aircraft ICAO, registration (which can change, but the ICAO should stay with the aircraft for its life), type, interest (0 for civilian and 10 for military – don’t ask, it’s just the way it is/has been), year and any owner information (ie, USAF for example).
Its a bug to maintain and has been taking me about 12 hours for Node-RED to import the CSV file, repair it, then insert it into the MySQL. In other words, I did not do it very often.
It was also a hybrid of data that I have been scraping off the floor of the Internet…. Well, I happen to find the guy that maintains the ADSB Exchange version of their database and like all things ADSBEx, its open source and maintained.
So, it now takes me about 2 minutes to pull it down and import it. Yay. I’m going to be a lot more compelled to keep it updated!
Second database is my rolling 24 hours of messages.
Its self-built by Node-RED and just is.
Anyway, the problem with both is that have been running on Garys gifted PC from about 8 years ago, so yeah, the version of MySQL was a bit long in the tooth.
Last week, a Node-RED update broke the connection. I could not do any inserts, so I had to walk back the node version.
Also, I knew it getting hit pretty often and its all just noise on the network with a tiny bit of latency.
Long story short, I took Friday off to relax a bit and fix both databases by migrating them to MariaDB running on my Windows PC, the same PC that Node-RED is running on.
Honestly, I should have done it 2 years ago when I first started to be unhappy with how it was built out…. But you use what you have on hand – 7 years ago when I first made the databases, it was MySQL on the Linux PC.
It seems a tiny bit faster, but the main thing is its a lot easier to maintain and I can now plan to store more data (the avgeeks are asking for a rolling 30 days).
I can also start to think about running some reports against the data as most reporting software seems to run on Windows.
Every time I get a Windows update reboot, I check if there are any Node-RED nodes or NodeJS updates and do them, then do the Windows update/reboot and thus kill two birds with one stone.
Last week, Node-RED did not come back up cleanly.
The MySQL would not insert and the dashboard node failed with a very helpful ‘none’ error.
I spent a few hours on it but could not figure it out, so the site was down over night.
Shower thought the next day – install a version or two back of the two trouble nodes.
The glitch there was that the MySQL node is wrapped up some generic node package and usually you can see past versions by looking at the node GitHub repo.
Not this time… I have NEVER had to go to a package NPM page…. Never.. well, for the MySQL node, thats where you need to go to find the old versions.
Anyway, long story short, not sure why stuff broke, but its caused me to re-think my backups and what I need to not have stress like that again.
