pkg-static: sqlite error after update to 25.7

[FraLem]

Hi,

After the update to 25.7 we face sqlite error on the package database "database disk image is malformed".

Attached please find logs from the update and pkg bootsrap commands.

After backup of  /var/db/pkg/local.sqlite its recovery has not succeeded.

Any suggestion would be appreciated.

*************************************************
Updating OPNsense repository catalogue...
OPNsense repository is up to date.
All repositories are up to date.
Updating OPNsense repository catalogue...
OPNsense repository is up to date.
All repositories are up to date.
pkg-static: sqlite error while executing iterator in file pkgdb_iterator.c:1110: database disk image is malformed
Checking for upgrades (0 candidates): . done
Processing candidates (0 candidates): . done
pkg-static: sqlite error while executing END TRANSACTION; in file pkgdb.c:2333: database disk image is malformed
Checking integrity... done (0 conflicting)
Your packages are up to date.
pkg-static: sqlite error while executing iterator in file pkgdb_iterator.c:1110: database disk image is malformed
pkg-static: sqlite error while executing END TRANSACTION; in file pkgdb.c:2333: database disk image is malformed
Checking integrity... done (0 conflicting)
Nothing to do.
pkg-static: sqlite error while executing iterator in file pkgdb_iterator.c:1183: database disk image is malformed
Checking all packages:
pkg-static: sqlite error while executing iterator in file pkgdb_iterator.c:1183: database disk image is malformed
Checking all packages... done
pkg-static: sqlite error while executing iterator in file pkgdb_iterator.c:1110: database disk image is malformed
Nothing to do.
Nothing to do.
Starting web GUI...done.
pkg: sqlite error while executing iterator in file pkgdb_iterator.c:1110: database disk image is malformed
Installation out of date. The update to opnsense-25.7.1_1 is required.



*******************************************

root@OPNsense:~ # pkg bootstrap -f
The package management tool is not yet installed on your system.
Do you want to fetch and install it now? [y/N]: y
Bootstrapping pkg from https://pkg.opnsense.org/FreeBSD:14:amd64/25.7/latest, please wait...
Verifying signature with trusted certificate pkg.opnsense.org.20250710... done
Installing pkg-1.19.2_5...
package pkg is already installed, forced install
pkg-static: sqlite error while executing INSERT OR REPLACE INTO packages( origin, name, version, comment, desc, message, arch, maintainer, www, prefix, flatsize, automatic, licenselogic, time, manifestdigest, dep_formula, vital)VALUES( 'ports-mgmt/pkg', 'pkg', '1.19.2_5', 'Package manager', 'Package management tool', NULL, 'FreeBSD:14:amd64', 'pkg@FreeBSD.org', 'https://github.com/freebsd/pkg';, '/usr/local', 16880765, 0, 1, NOW(), NULL, NULL, 0 ) in file pkgdb.c:1633: database disk image is malformed

Failed to install the following 1 package(s): /tmp//pkg.pkg.8kdK0k

[franco]

> database disk image is malformed

Is all you really need to know.

# opnsense-bootstrap

at your earliest convenience and done.


Cheers,
Franco

[FraLem]

Many thanks for the tip.

Unfortunately after running opnsense-bootstrap and reloading the backup configuration we are not able to update the version, sqlite error persists as well as missing  /usr/local/etc/pkg/repos/OPNsense.conf.

Trying to run a second time  opnsense-bootstrap shows "package database non-existent"

Does it look like a hardware/SDD issue?


**********************

Enter an option: 12

Fetching change log information, please wait... Missing /usr/local/etc/pkg/repos/OPNsense.conf
Missing /usr/local/etc/pkg/repos/OPNsense.conf
fetch: /sets/changelog.txz: No such file or directory

This will automatically fetch all available updates and apply them.

Missing /usr/local/etc/pkg/repos/OPNsense.conf
Missing /usr/local/etc/pkg/repos/OPNsense.conf
Proceed with this action? [y/N]: y

Missing /usr/local/etc/pkg/repos/OPNsense.conf
Missing /usr/local/etc/pkg/repos/OPNsense.conf
Starting web GUI...done.
pkg: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2333: no such table: packages
pkg: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2333: no such table: packages
Usage: pkg version [-IPR] [-hoqvU] [-l limchar] [-L limchar] [-Cegix pattern]
                    [-r reponame] [-O origin|-n pkgname] [index]
        pkg version -t <version1> <version2>
        pkg version -T <pkgname> <pattern>

For more information see 'pkg help version'.
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
Updating FreeBSD-kmods repository catalogue...
FreeBSD-kmods repository is up to date.
All repositories are up to date.
pkg: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2333: no such table: packages
pkg: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2333: no such table: packages
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
Updating FreeBSD-kmods repository catalogue...
FreeBSD-kmods repository is up to date.
All repositories are up to date.
pkg: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2333: no such table: packages
pkg: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2333: no such table: packages
Missing /usr/local/etc/pkg/repos/OPNsense.conf


********************************

root@OPNsense:~ # opnsense-bootstrap
This utility will attempt to turn this installation into the latest
OPNsense 25.7 release.  All packages will be deleted, the base
system and kernel will be replaced, and if all went well the system
will automatically reboot.

Proceed with this action? [y/N]: y
fetch: https://github.com/opnsense/core/archive/stable/25.7.tar.gz: size of remote file is not known
/tmp/opnsense-bootstrap/core.tar.gz                     11 MB 8262 kBps    02s
pkg: package database non-existent
pkg: No packages installed
pkg: No packages installed
mkdir: /usr/local/etc/pkg: No such file or directory
*** Error code 1

Stop.
make[1]: stopped in /tmp/opnsense-bootstrap/core-stable-25.7/src
*** Error code 1

Stop.
make: stopped in /tmp/opnsense-bootstrap/core-stable-25.7
root@OPNsense:~ #


***********************************************

Regards

[franco]

> Does it look like a hardware/SDD issue?

Well, opnsense-bootstrap did quickly run into failures and more missing files that are fundamental are missing. It's not entire plausible that this is just a single glitch that is easily recovered.


Cheers,
Franco