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

Ryan Schmidt buildbot at ryandesign.com
Tue Mar 2 13:58:17 UTC 2021


On Mar 1, 2021, at 03:23, Dr. Mark Asbach wrote:

>> and I will certainly use Postgres at that time, and will have to investigate how to do Postgres backups then as well.
> 
> Just a short feedback on this: Postgres does allow non-blocking dumps, so you can replace that Sqlite „vacuum“ with a pg_dump:
> 
> https://www.postgresql.org/docs/current/backup-dump.html
> 
>> Dumps created by pg_dump are internally consistent, meaning, the dump represents a snapshot of the database at the time pg_dump began running. pg_dump does not block other operations on the database while it is working. (Exceptions are those operations that need to operate with an exclusive lock, such as most forms of ALTER TABLE.)

Thanks, I'll keep that in mind.

I found it difficult to find relevant information in the database since there are so many relationships between tables and constructing the select statements manually is tedious and not knowing what criteria to use to exclude the information about completed builds that I did not care about.

Instead I found a helpful page explaining how to enable debug logging for sqlalchemy which I know buildbot uses:

https://stackoverflow.com/questions/27748053/how-to-log-sql-statements-and-rows-returned-in-sqlalchemy-to-aid-in-debugging

I added these lines near the top of my master.cfg:

import logging
logging.basicConfig()
logger = logging.getLogger('sqlalchemy.engine')
logger.setLevel(logging.DEBUG)

Then the SQL statements and the results were logged into the twistd.log. Then all I had to do was visit each of the builder pages in the buildbot web interface and save off the results from the log; this gave me for example the properties of pending forced build requests which aren't shown in the web interface.

I've now created a new state.sqlite database and am scheduling new builds for the ones that were lost, and I'll be sure to back it up properly in the future.

Thanks everyone for your help and explanations.



More information about the users mailing list