Since I started using Duplicati a few years ago, I have struggled with the occasional SQLite database corruption. Mostly due to problems in indexes, such as referred in this thread, but just a week ago I had to completely dump and recreate the SQLite database. I’ve had this type of problems about once a year. Fortunately none of them have resulted (as of yet) in actual backup loss or corruption (at least as far as I can tell).
I do not rule out the possibility that I am just an unlucky case, or that the problem sits with my environment (although periodic disk and memory diagnostics haven’t revealed any issue, and my machine is just a standard Windows PC backing up data from an SSD to an external drive). However, similar issues in other projects that rely on SQLite can easily be found online.
Are there plans to support alternative database engines somewhere in the future? Or is Duplicati very tightly integrated with SQLite? Do any of you share concerns about the reliability of SQLite, or does it generally work without major problems?
Do these problems come up at random, or during stop/powerfail/reboot/etc.? My SQLite also behaves, however the system is generally up, and I’m rarely using the CommandLine version (so no Ctrl-C risk).
Even beyond moving being a lot of work, there’s a question of what to get that’s easy enough for users.
Ideally it’s no extra work for user (who is not likely a DBA). An embedded SQL DB would fill the bill well. Often that means it’s SQLite. Whatever is used should also be portable to everything Duplicati runs on, which includes small devices such as NAS systems, and people are asking for iPhone/Android support.
And speaking of support, Duplicati is already enough of a DIY integration for backend. Let’s not add DB.
My experience with SQLite failing is usually due to sudden shutdowns.
I have had to start some backups from scratch a year or so ago. If I recall correctly, a breaker tripped in the middle of a backup job. It didn’t recover, and the database recreate just never finished, even after weeks of letting it run.
I’d love to have MySQL or PostgreSQL support since I could host them in a manner that wouldn’t get corrupted like SQLite has.
That said, I’d rather it be a side project for those of us comfortable with that kind of setup, not something I’d want the main devs to work on, unless they really wanted to.
I’d have poked at building it myself, but I have no C# experience.
As of now, I am running Duplicati 2.0.5.1_beta_2020-01-18. I have been on the beta channel since at least November 2018 (before that I was on the canary for a while).
I’ve had a malfunction caused due to a system crash once (which is understanble given that SQLite is just an engine working on top of the filesystem, which in my case is just regular NTFS). The other times, including the last instance in which it was necessary to dump and rebuild the database, no defective shutdown had occurred. In fact, I am starting and stopping Duplicati manually, to be sure that I control the window where SQLite databases could be open, so the window for corruption due to faulty shutdown is pretty narrow (I would think).
I agree that configuring Duplicati is no breeze and setting up a database host would certainly not make it for most users. I wouldn’t consider it myself if I weren’t losing faith in SQLite . I will follow any developments regarding MySQL/PostgreSQL/MariaDB support with interest.
An unrelated but perhaps significant advantage to having such an option, would also be facilitating managing Duplicati deployments on multiple machines.
Is this a Windows service Stop, a desktop TrayIcon right-click Quit, or something else?
The good news is that even total loss of DB should get fixed by Recreate if remote is OK.
The Recreate works better (at least in most cases) in 2.0.5.1 than it used to in 2.0.4.5/23.
TrayIcon “Quit” (and start by way of Start Menu shortcut). Automatic startup is disabled.
This is a good feature I was unaware of. Does this operation completely recreate the database state, including previous versions of files, deleted files still in the backup set, etc.? If affirmative, it is a lot easier than having to locate the SQLite database and fiddling with a distinct SQLite toolset.
The remote has all file data and metadata, and all versions that are still kept by retention policy.
This goes into the DB but you lose old logs and some internal history that you’d not likely see…
Ability to create a partial temporary DB is also used if you run a Direct restore from backup files.
Because that’s a Disaster Recovery path, I sometimes Recreate to make sure recreate is OK…
Manually moving the current DB aside first is good, just in case Recreate doesn’t come through.
What’s supposed to happen, ideally, is that the small dlist and dindex files should be enough for recreate. What actually happens sometimes (but less than before) is that dblock files download.
These large files slow things down, but it’s an exhaustive search for information that’s missing…
One caution about this is that the icon disappears before Duplicati actually goes down.
I just did Quit right after a backup started. Icon went down, backup ran to usual end…
I’m not sure how hard Windows might kill Duplicati (eventually) on shutdown or restart.
In theory SQLite isn’t supposed to corrupt (especially to a non-openable state) anyway.
Duplicati doesn’t do well on hard stops, but this is improved in post-Beta Canary builds.
While very obviously “should” does not equal “is”, theoretically, it “should” be impossible to corrupt SQLITE with just a ‘sudden shut down’. It is pretty robust, but clearly issues have happened. The DB is not supposed to be come ‘physically corrupted.’ The ACID guarantee is that uncommited writes are lost, no more.
While it is ‘speculative’, what I am suspecting could be going on in at least some of these cases is something that is intrinsic to all DBMS: the ‘kernel’ of the database has to have blind faith the OS and hardware “aren’t lying” about certain critically important things like locking behaviour and buffer flush, and no realistic way to check that. Maybe something which the OS/Hardware “told” SQlite was safely fsynched to disk really wasn’t quite after all.
I wonder if SQLITE’s more advanced WAL mode would be more resistent to issues? According to the SQLITE manual: https://sqlite.org/wal.html
WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken.
WAL mode is more similar to how ‘pro’ databases work. Compare:
This is interesting, specially as it seems to be much easier to implement than integrating alternative database backends. Perhaps it could be first presented as an advanced option on canary builds? I would gladly act as beta tester for such a feature, perhaps there are others interested too.
I am considering posting an issue/feature request on Github to this effect.
Or maybe Canary tester, or maybe developer-level. Beta is the most stable release there is now.
Thanks for volunteering in some way. That’s the limiting factor on how rapidly progress happens.
I was actually thinking of the canary/experimental channel (still adjusting to the mindset that the main release is actually still a beta, although a rather stable one at that in my opinion, SQLite corruptions notwithstanding).
I have seen on the issue you linked that an automated bot mentioned this thread on the issue (due to your link). I will take the initiative and comment on the context of the reference there.
. I will follow any developments regarding MySQL/PostgreSQL/MariaDB support with interest.