[users at bb.net] state.sqlite database disk image is malformed

[Ryan Schmidt]

On Feb 27, 2021, at 03:53, Dr. Mark Asbach wrote:

> Hi Ryan,
> 
>> This surprises me because for all of its faults I was under the impression that Time Machine does at least back up file contents correctly. But I don't know whether an sqlite database is in a consistent state on disk while it is being used e.g. by Buildbot.
> 
> Unfortunately, unless you lock your database before a backup, copying/backing up an open Sqlite database file will not work. See https://sqlite.org/backup.html.

Thanks, that explains it.

It would have been nice to know this before data loss occurred rather than after. It would be good if the buildbot documentation contained prominent information about any unusual steps beyond just copying files (such as locking the database) that are required for a valid backup of a running system, or links to complete instructions that may be available from the various database makers, or at the very least a warning that just copying database files will not result in a viable backup and encouraging readers to research the topic on their own (although I am trying to research it now and am finding it frustratingly difficult). If the information is already in the documentation, I didn't see it when I was originally setting up buildbot, and searching docs.buildbot.net for the word "backup" just now returned no results. Database administrators may already know about these special considerations for backups, but someone setting up buildbot doesn't necessarily have or want to acquire all of the knowledge and skills that a database administrator has.

Since buildbot will be running when the backup occurs, it sounds like what I should do is lock the database, make a filesystem copy of it (or possibly use "vacuum into" somehow), unlock the database, and then allow Time Machine to perform a normal backup. But looking at https://sqlite.org/backup.html I still don't understand how to do this. It provides sample C code, but I'm not interested in writing or maintaining C code to perform a database backup; I just want a set of commands I can type in the shell. That page says I could "Establish a shared lock on the database file using the SQLite API (i.e. the shell tool)" but does not explain what commands to run to do that.

Is running this command sufficient:

sqlite3 state.sqlite 'vacuum into "file:state.sqlite.bak";'

Does that automatically establish a shared lock on the database before the vacuum and unlock it after? If not, how do I do that?


>> Is there any other better way to recover data from a malformed sqlite database or to determine in what way it is malformed?
> 
> Not that I know of any unsupervised way to sanitise a broken buildbot database. But you could have a look at the database yourself and see if there is anything that looks broken and remove those records manually. As you’re on a mac, have a look at Liya https://apps.apple.com/de/app/liya/id455484422?mt=12.

Thanks for the suggestion. That looks like a great app and I'll give it a try. I have previously used an app called Base http://menial.co.uk/base/ but I like that Liya is free and also supports PostgreSQL and MySQL databases.