# sqlite error during update



## erdos (Feb 15, 2020)

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.

```
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?


----------



## Martin Paredes (Feb 17, 2020)

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)


```
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#
```


----------



## erdos (Feb 17, 2020)

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?


----------



## richardtoohey2 (Feb 17, 2020)

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).


----------



## SirDice (Feb 17, 2020)

erdos said:


> ```
> pkg: http://pkg.FreeBSD.org/FreeBSD:12:i386/quarterly/meta.txz: No address record
> ```



You're not connected or your name resolving is broken. 




erdos said:


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


Ctrl-Z does NOT quit applications, it puts them on hold in the background.


----------



## erdos (Feb 21, 2020)

Martin Paredes said:


> In my case (updating OPNsense), it was a problem with the index of the database of `pkg` system
> ...



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'

```
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?


----------



## SirDice (Feb 24, 2020)

Your package database seems to be severely corrupted. Restore from backup, you may be able to find one in /var/backup/.


----------



## erdos (Feb 25, 2020)

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?


----------

