Pkg sqlite issue

[geminux]

Hello,

I have an issue with pkg. I tried to install some extra packages from freebsd using pkg as explain here : https://doc.pfsense.org/index.php/Installing_FreeBSD_Packages

On one pfsense box, the "pkg" command failed with an sqlite error (I no longer have the exact error msg, sry).

Now:
"pkg" command does nothing (no output)
"pkg update" seems to work fine (normal output, no error message)
"pkg install XXX" always say that package XXX does not exist.

I'm stuck. how can I reset the pkg config/database (or the whole pkg package?) and restart the pkg setup/update process?

(pfsense version : 2.2.2 i386 nanobsd)

Thanks

[geminux]

Hi,

I finaly find the initial error message :

pkg install gsed bash nano
Updating FreeBSD repository catalogue...
Fetching meta.txz: 100%    944 B   0.9kB/s    00:01    
Fetching packagesite.txz: 100%    5 MiB   1.3MB/s    00:04    
Processing entries: 100%
FreeBSD repository update completed. 23936 packages processed.
pkg: sqlite error while executing INSERT INTO pkg_search SELECT id, name || '-' || version, origin FROM packages;CREATE INDEX packages_origin ON packages(origin COLLATE NOCASE);CREATE INDEX packages_name ON packages(name COLLATE NOCASE);CREATE INDEX packages_uid_nocase ON packages(name COLLATE NOCASE, origin COLLATE NOCASE);CREATE INDEX packages_version_nocase ON packages(name COLLATE NOCASE, version);CREATE INDEX packages_uid ON packages(name, origin);CREATE INDEX packages_version ON packages(name, version);CREATE UNIQUE INDEX packages_digest ON packages(manifestdigest); in file pkgdb.c:2333: disk I/O error
pkg: sqlite error while executing RELEASE SAVEPOINT REPO in file pkgdb.c:1170: no such savepoint: REPO
Unable to update repository FreeBSD
All repositories are up-to-date.
pkg: sqlite error while executing SELECT count(name) FROM sqlite_master WHERE type='table' AND name='repodata'; in file pkgdb.c:2358: database is locked
Repository FreeBSD contains no repodata table, need to re-create database
pkg: Repository FreeBSD cannot be opened. 'pkg update' required
pkg: Cannot get an advisory lock on a database, it is locked by another process

I think I tried to run the pkg command while pfsense was in the process of of re-installing packages

Any clue ?

[doktornotor]

@geminux:

I'm stuck. how can I reset the pkg config/database (or the whole pkg package?) and restart the pkg setup/update process?

+1. It's just unbelivable. I ended up with pkg that thinks it's installed but it's not - see https://redmine.pfsense.org/issues/4670

First the PBI überjunk, now this. Is there really noone in FreeBSD that could produce some package manager that's not braindead? I think you can try to nuke /var/db/pkg and run pkg update - then you'll run into issues with files from packages that already exist but the pkg does not know about them, that is if you actually managed to install something before. This seems to be an issue even with the bootstrapped pkg itself, so even if you did not manage to install anything yet. Did not have time to play with that yet since I've filed the bug.

@geminux:

I think I tried to run the pkg command while pfsense was in the process of of re-installing packages

Any clue ?

Unlikely. 2.2.x uses the PBI garbage for the packages you install from System - Packages. Not pkg(ng).

Good luck.

[geminux]

@doktornotor:

I think you can try to nuke /var/db/pkg and run pkg update - then you'll run into issues with files from packages that already exist but the pkg does not know about them, that is if you actually managed to install something before

I just tried and it works well !! Thank you !!