I’ve used actual databases on RPi, like MySQL, and they work well but aren’t very tolerant to power interruptions and abrupt shut-downs. I want to avoid data corruption issues and make my solution a bit more solid-state and resilient.
Does anyone know of a really rock-solid (impossible to corrupt) dbase?
Because I simply want to save-out some User Prefs and State information I don’t really need a dbase, but I’m curious if anyone’s struggled with this and found any simple or elegant solutions.
I think most people looking for that kind of resiliency use db clusters with multiple machines. I think most dbs can be tweaked to be more resilient at the expense of some speed, but I can’t recall anything that claims to be bullet proof. The problem is multi layer and the database lives on the file system. So how do you guarantee the file system if there is a crash or someone pulls the plug. It’s a difficult problem.
There’s nothing inherently unstable about a raspberry pi. The company I worked for a couple of years ago had a MySQL database running on a Raspberry Pi collecting data from a bunch of barcode scanner stations. It ran 24 hours a day 7 days a week. It was on a UPS.
As long as you can keep the Raspberry Pi power constant they are quite good little data collectors.
My issue is the ‘appliance’-like application, where a user can unplug and kill power w/out consideration for ‘Shutdown…’ options. The RPi environment is very very stable, and I concur it can run for months at a time w/out any concerns. It’s simple the settings and state data I’m hoping to capture.
Well, you could custom build a battery power solution for that. If someone yanks the power the battery would keep it running just to allow it to shutdown. It would be some additional work, but I imagine that would be something you could sell as a product as well as long as you could keep it fairly cheap.
I am showing my age, as this is what I did in the 1980’s when databases were not as stable. Just for fun, lets use this example with an SQLite database.
The main database is named “main.sqlite”
The user adds new data, and the program wants to save the data. First copy the main.sqlite database to mainbackup.sqlite
Write the new user data to mainbackup.sqlite
When the data has been added to mainbackup.sqlite successfullly, copy mainbackup.sqlite to main.sqlite
Repeat steps 1-4 ad nauseum
If there was a power outage, or something happened before, during, or after the writing of the database, there will always be a valid copy of the data base either as main.sqlite or mainbackup.sqlite.
The regular database rules apply, such as sanitizing data, etc, etc…
The RaspBerry is not for ‘appliance’-like applications. It has an OS that can be corrupted if you don’t do the ‘Shutdown…’, Not a DB, The File system itself.
As for the database, leave a copy on the Rasp, use a centraliced MySql
I appreciate your notes Pedro.
Eugene I’ve done similar stuff, even doing a timed backup using the dbase management tools built into db’s. Once you write the code and it works then that kind of solution works well and then you only have to deal with disc maintenance issues - not a big deal I suppose.
In the old days Xojo (Realbasic) used to have it’s one built-in micro dbase that was easy to incorporate. Too bad it’s gone
I’ll continue to tinker with testing the corruption tolerance of SQLite and others. If I have any incredible success I’ll be sure to post about it. Thanks for everyone’s feedback.
SQLite is the replacement for the built-in DB that was used by old (pre-2005) versions of REALBasic. SQLite is highly resistant to corruption. This document describes Ways to avoid corruption, few of which seem to apply to your situation:
