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

[Ryan Schmidt]

I'm having a little trouble getting my Buildbot eight installation back up and running after an SSD failure. The disk died so completely that it cannot be seen by the OS anymore so I am trying to restore from my backups which were made using the macOS Time Machine feature.

I have checked my three most recent backups and in each of them the Buildbot state.sqlite file is corrupted somehow. Buildbot would not start with this database and the sqlite3 command line program says the "database disk image is malformed" when I try to do anything with it. 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.

I understand that the state.sqlite file only stores scheduled builds, but I would like to recover it if I can because the disk failure occurred shortly after a week-long unplanned weather-related outage so there was a huge backlog of scheduled builds yet to be completed.

I tried using sqlite3's ".recover" command to save the data out of the malformed database into a new one. At first glance this appeared to work, creating a new database that was almost as large as the malformed one. Buildbot started ok with this database and the web interface showed a plausible set of scheduled builds waiting in the various queues. But it didn't work correctly: my configuration is such that each build first triggers a build in another queue, and it gets to the point of claiming to have triggered the other build and waits for it to finish, but the other build has not in fact been scheduled, so it's now stuck.

Does this failure mode ring a bell with anyone or indicate an area of the database that I should look at to see if some critical table or data is missing?

Is there a Buildbot script or command I can run to verify not just that the database is now correctly-formed to sqlite's requirements but also that all of the data is present and cross-referenced correctly per Buildbot's requirements?

Is there any other better way to recover data from a malformed sqlite database or to determine in what way it is malformed?