Solved pkg: sqlite error while executing INSERT INTO files (path, sha256, package_id) VALUES (…) in file pkgdb.c:1825: database disk image is malformed

[grahamperrin]

FreeBSD 13.0-RELEASE-p2. Malformation arose whilst testing UFS file system resilience.

pkg: sqlite error while executing INSERT INTO files (path, sha256, package_id) VALUES (?1, ?2, ?3) in file pkgdb.c:1825: database disk image is malformed

Re: <https://github.com/freebsd/pkg/issues/633> (2013) I removed /var/db/pkg/repo-FreeBSD.sqlite, this does not resolve the issue. I see three other files:

1. FreeBSD.meta
2. local.sqlite
3. local.sqlite-journal

Is it save to (also) remove local.sqlite and its journal?

With reference to a 2021-03-12 commit:

<https://github.com/freebsd/pkg/blob/cd8f896d23d55516737f5886a7f69a6e1cc3c77d/libpkg/pkgdb.c#L1825>

 

[grahamperrin]

Re: <https://forum.opnsense.org/index.php?topic=15868.msg72664#msg72664> I wondered about using databases/sqlite3 to diagnose and maybe fix local.sqlite.
Unfortunately the port can not be installed (because the database disk image is malformed).

 

[covacat]

build it from source or jus fetch the package manually with fetch and extract it to tmp and run from there
if you build from ports do not make install and run it from work/stage/...

 

[grahamperrin]

… fetch the package manually with fetch and extract it to tmp and run from there …

… "libedit.so.0" not found …

I did previously also extract libedit to /tmp

… if you build from ports do not make install …

make build succeeded then I ran /usr/ports/databases/sqlite3/work/stage/usr/local/bin/sqlite3 but commands such as .help and .quit don't work, so I shouldn't trust it to fix a database. on the database. .selftest following .recover found the database still malformed.

Solution
I decided to remove the database and not worry about it. I plan to push this machine to breaking point, it's all disposable.

 

[covacat]

use LD_LIBRARY_PATH
export LD_LIBRARY_PATH=/tmp/usr/local/lib/

 

[grahamperrin]

Thanks, message crossed paths with edition to my previous post, the database could not be recovered.

 

[grahamperrin]

… whilst testing UFS file system resilience. …

Without reopening this topic (leaving it Solved): I found another irreparable database after the test that resulted in FreeBSD bug 256712. In this case:

• there was no local.sqlite-journal
• as before, I removed local.sqlite then (from memory) reinstalled all non-automatic packages.