-
タイトル
SQLITE3 database gets corrupted. Error "SQLSTATE[HY000]: General error: 11 database disk image is malformed" -
説明
This knowledge article shows how to fix a corrupt sqlite3 database in SSB.
In the example /opt/ssb/var/sql/archive.db will be used, as that is the most common db file can being corrupted.
-
原因
An sqlite3 database can became corrupt on SSB (based on service requests) in the following cases. (Root cause is unknown.)- During the archiving when the database is written.
- Using the archive cleanup script provided in the KB261845, which deletes records from the database.
-
対策
NOTE:
The error "11 database disk image is malformed" is general.
The resolution of the two known issues is different.
From the error we can not identify the root cause.
Different root causes need different solutions.Verify the issue and the database file
- Search the system logs for a message like the following.
2019-09-30T06:12:41-07:00 tus3secssbpin01 ssb/logspace[29328]: ERROR (root@localhost) Error remounting archive directories; details='Database query failed - query='SELECT DISTINCT policy, container_name FROM archives WHERE container_id=\'13886215195b9013131232e\'', error='SQLSTATE[HY000]: General error: 11 database disk image is malformed', adapter='sqlite', host='', port='', username='', database='/opt/ssb/var/sql/archive.db''
- Double-check the name of the corrupt database. See above in bold.
Resolution 1
- Log in to the core shell.
- Check the integrity of the database.
cd /opt/ssb/var/sql
sqlite3 archive.db "PRAGMA integrity_check"
If the following error is displayed, the database is corrupt.
Error: database disk image is malformed
- Make a backup of the database.
cp archive.db archive.db.bak
- Fix the database. (Creating a dump to the file fix_archive.db)
sqlite3 archive.db .dump | grep -v '^ROLLBACK' > /tmp/fixed_archive.sql
sqlite3 fixed_archive.db < /tmp/fixed_archive.sql
mv archive.db archive.db.corrupt
mv fixed_archive.db archive.db
sqlite3 archive.db "PRAGMA integrity_check"
WARNING!
If the "Error: near line 4: cannot commit - no transaction is active" error is showed the database wasn't fixed.
Continue with the following commands.sqlite3 archive.db "vacuum;"
sqlite3 archive.db ".dump" | sqlite3 fixed_archive.db
- Check the integrity again with the same command as above. Now, it should show "ok".
sqlite3 fix_archive.db "PRAGMA integrity_check"
- Overwrite the old file with the fixed one.
cp fixed_archive.db archive.db
- Wait until the next update of the database file is done by SSB and remove the backup file.
(In case of archiving a manual archiving can be run to verify the resolution.)