recover from database "lock" error/corruption

[rmarabini]

The following describes how to fix projects.sqlite database

sqlite3 kk_007908_XmippProtExtractParticles/particles.sqlite
SQLite version 3.45.3 2024-04-15 13:34:05
Enter ".help" for usage hints.
sqlite> PRAGMA integrity_check;
Parse error: database is locked (5)

• Make a backup of all the sqlite3 databases (project.sqlite in this case)

• make integrity check of sqlite database. The following line checks all database in the projects but takes for ever

  find . -maxdepth 2 -iname "*.sqlite" -print0 -exec sqlite3 '{}' 'PRAGMA integrity_check;' ';'

• create new database in which unload all uncorrupted data and then load it back.

sqlite3 project.sqlite
.mode insert
.output kk.sql
.dump
.exit

• restore data

rm programs.sqlite
{ echo "PRAGMA synchronous = OFF ;"; cat kk.sql; } | grep -v -e TRANSACTION -e ROLLBACK -e COMMIT >kk2.sql
sqlite3 project.sqlite ".read kk2.sql"