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

[Dr. Mark Asbach]

Hi Ryan,

> 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?

Yes, this command is sufficient and the way to go for backing up Buildbot databases (for scenarios with much larger databases, incremental alternatives might be necessary, but this is a very quick operation for the amount of data stored by buildbot).

It does establish a shared lock, if that is technically possible, i.e. you are safe when running this on the machine where buildbot runs. When storing the sqelite file on a network drive and running the command from another machine, you will most likely run into trouble as locking on network drives is quirky (see https://www.sqlite.org/howtocorrupt.html#_filesystems_with_broken_or_missing_lock_implementations).

> 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).

As often when software is written by developers for developers, there is no one noticing these missing bits of information. I would assume that the buildbot developers are so used to database topics, that they just didn’t think of what could go wrong for anyone not as accustomed to the details.

This could be a great opportunity to improve the docs by writing that missing chapter and putting up a pull request (https://docs.buildbot.net/current/developer/pull-request.html). Unfortunately, I’m currently in the middle of moving my household and quite busy with putting all my stuff into cardboard boxes (kind of a physical VACUUM INTO :-)).

Keeping fingers crossed and thanks for bringing up the topic on this list. It might already help others in the future.

Greetings from Cologne,
Mark
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.buildbot.net/pipermail/users/attachments/20210228/5256d9b5/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2928 bytes
Desc: not available
URL: <http://lists.buildbot.net/pipermail/users/attachments/20210228/5256d9b5/attachment.bin>