sqlite error during update

E

erdos

hi, i'm running 12.0-release-p5 on a HP desktop PC. Today when I tried to update Kodi, the system failed and rebooted.

During the reboot process, core crash file was created and dumped into /var/crash/ folder. I deleted the core, tried to rerun
pkg update and freebsd-update fetch install, but received the following error.
Code: 
root@oblivion:/var/crash # pkg update
Updating FreeBSD repository catalogue...
pkg: sqlite error while executing iterator in file pkgdb_iterator.c:1080: database disk image is malformed
pkg: [URL]http://pkg.FreeBSD.org/FreeBSD:12:i386/quarterly/meta.txz[/URL]: No address record
repository FreeBSD has no meta file, using default settings
pkg: [URL]http://pkg.FreeBSD.org/FreeBSD:12:i386/quarterly/packagesite.txz[/URL]: No address record
Unable to update repository FreeBSD
Error updating repositories!
any idea how to fix this?
 
Last edited by a moderator:
In my case (updating OPNsense), it was a problem with the index of the database of pkg system

What is the output of the .selftest command inside of SQLite?

Upgrade failed (pkg-static: sqlite error: database disk image is malformed)

Rich (BB code): 
root@OPNsense# cp /var/db/pkg/local.sqlite  ./local.sqlite.backup
root@OPNsense# sqlite3 /var/db/pkg/local.sqlite
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> .selftest
Missing SELFTEST table - default checks only
1: Expected: [ok]
1:      Got: [*** in database main ***
Multiple uses for byte 3681 of page 1482|row 3380 missing from index sqlite_autoindex_files_1|row 7439 missing from index sqlite_autoindex_files_1|row 7440 missing from index sqlite_autoindex_files_1|row 7441 missing from index sqlite_autoindex_files_1|row 15880 missing from index files_package_id|row 15880 missing from index sqlite_autoindex_files_1]
1 errors out of 1 tests
sqlite> REINDEX;
sqlite> .selftest
Missing SELFTEST table - default checks only
0 errors out of 1 tests
sqlite> .quit
root@OPNsense#
 
Thanks, I followed steps above and was able to go past 'pkg update'

it looks that my local.sqlite is damaged. I did a flurry string of fix following online instructions, in the process, I recreated the local.sqlite file with my regular username,

but when running 'freebsd-update fetch',
"fetching metadata signature for 12.0-RELEASE from update4.freebsd.org... done.
Fetching metadata index... done.
Inspecting system.. done
'Preparing to download files... done.
(END)

Then it stuck and I have to 'Control + Z' to quit.

when i run 'pkg instal kodi', error is
'pkg: /var/db/pkg/local.sqlite wrong user or group ownership (expected 0/0 versus actual 1001/1001)

crash log created during reboot -
'/var/crash/core.txt.9'

"BFD: /boot/kernel/kernel: invalid relocation type 42
BFD: /boot/kernel/kernel: invalid relocation type 42
BFD: /boot/kernel/kernel: invalid relocation type 42
/dev/stdin:1: Error in sourced command file:
Cannot access memory at address 0x65657246
BFD: /boot/kernel/kernel: invalid relocation type 42
BFD: /boot/kernel/kernel: invalid relocation type 42
BFD: /boot/kernel/kernel: invalid relocation type 42
/dev/stdin:1: Error in sourced command file:
Cannot access memory at address 0x65657246
/dev/stdin:1: Error in sourced command file:
Cannot access memory at address 0x65657246
Unable to find matching kernel for /var/crash/vmcore.9"

how to fix this?
 
Can't answer all of it but

pkg: /var/db/pkg/local.sqlite wrong user or group ownership (expected 0/0 versus actual 1001/1001)

0/0 is user/group root/wheel. You said earlier you created it under your own login - that will be user/group 1001/1001.

So I think that you will need to change the user & group on the local.sqlite file to root/wheel.

But that might be the least of your problems if you can't reboot.

From your original message - No address record suggests DNS or network errors.

Maybe separate this into two tasks - can you complete the freebsd-update and reboot? Once that is cleaned and working, next look at the pkg issue(s).
 
Martin Paredes said:
In my case (updating OPNsense), it was a problem with the index of the database of pkg system
...
Click to expand...

Thanks! I fixed the sqlite error during 'pkg update' and upgraded system to 12.1 release

now i'm on 12.1 release, but receiving following error whenever i run 'pkg upgrade'
Code: 
root@oblivion:/var/db/pkg # pkg upgrade
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
pkg: sqlite error while executing CREATE TABLE licenses (id INTEGER PRIMARY KEY, license TEXT NOT NULL UNIQUE );CREATE TABLE pkg_licenses_assoc (package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE ON UPDATE CASCADE, license_id INTEGER REFERENCES licenses(id) ON DELETE RESTRICT ON UPDATE RESTRICT, PRIMARY KEY (package_id, license_id));CREATE VIEW pkg_licenses AS SELECT origin, license FROM packages INNER JOIN pkg_licenses_assoc ON packages.id = pkg_licenses_assoc.package_id INNER JOIN licenses ON pkg_licenses_assoc.license_id = licenses.id;CREATE TRIGGER license_insert INSTEAD OF INSERT ON pkg_licenses FOR EACH ROW BEGIN INSERT OR IGNORE INTO licenses(license) values (NEW.license);INSERT INTO pkg_licenses_assoc(package_id, license_id) VALUES ((SELECT id FROM packages where origin = NEW.origin), (SELECT id FROM categories WHERE name = NEW.name));END; in file pkgdb.c:2477: table licenses already exists
I'm not sure what this means. and how to fix it?
 
Last edited by a moderator:
Your package database seems to be severely corrupted. Restore from backup, you may be able to find one in /var/backup/.
 
I used 'pkg update -f' to update the pkg.


Then I tried the following to restore the sqlite database:

mv /var/db/pkg/local.sqlite /var/db/pkg/local.sqlite.backup
xzcat /var/backups/pkg.sql.xz.7 | sqlite3 /var/db/pkg/local.sqlite
sqlite3 /var/db/pkg/local.sqlite
sqlite> PRAGMA user_version=30;
sqlite> .quit


Still, I received error while 'pkg upgrade'.

root@oblivion:/var/db/pkg # pkg upgrade
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
pkg: sqlite error while executing CREATE TABLE licenses (id INTEGER PRIMARY KEY, license TEXT NOT NULL UNIQUE );CREATE TABLE pkg_licenses_assoc (package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE ON UPDATE CASCADE, license_id INTEGER REFERENCES licenses(id) ON DELETE RESTRICT ON UPDATE RESTRICT, PRIMARY KEY (package_id, license_id));CREATE VIEW pkg_licenses AS SELECT origin, license FROM packages INNER JOIN pkg_licenses_assoc ON packages.id = pkg_licenses_assoc.package_id INNER JOIN licenses ON pkg_licenses_assoc.license_id = licenses.id;CREATE TRIGGER license_insert INSTEAD OF INSERT ON pkg_licenses FOR EACH ROW BEGIN INSERT OR IGNORE INTO licenses(license) values (NEW.license);INSERT INTO pkg_licenses_assoc(package_id, license_id) VALUES ((SELECT id FROM packages where origin = NEW.origin), (SELECT id FROM categories WHERE name = NEW.name));END; in file pkgdb.c:2477: table licenses already exists


'/var/backups/pkg.sql.xz.7' is a backup dated before the problem occurred. so I suppose the latest 'pkg' is at fault corrupting my sqlite db?
 
You must log in or register to reply here.
Back
Top