pkg database error

D

driker

I rebooted my system and began to get an error when I attempt to update my packages. The error is:

Code: 
pkg-static: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2407: no such table: packages

I've searched everywhere and attempted reinstalling pkg as well as restoring from the automated backup and it usually ends up with either the same error or a file not found error. All of my packages are still installed but I can no longer update them. I have also attempted using portupgrade which fails with the same error. I cannot even reinstall the packages to have them built into a new database as it ends with the same error.

Are there any solutions to this? I appreciate any suggestions.
 
I apologize for the time it took me to respond. I am running Freebsd 11.1-RELEASE-p6

I used pkg install -y pkg to reinstall pkg.
 
I'll be honest: pkg complaining about tables not being present is new to me, but I am having a hard time believing that this can happen out of the blue. Something must have triggered this.

One way to check up on this (be very careful): # pkg shell. Then issue the .database command, followed by the .tables command. And finally .exit.

This should point you to /var/db/pkg/local.sqlite and please share the list of tables. Or share the whole output of course.
 
I was installing packages before the reboot and had to reboot because I was having graphical issues. I probably should've given more time to finish the installations before rebooting.

The output from the requested commands are as follows:

Code: 
SQLite version 3.15.2 2016-11-28 19:13:37
Enter ".help" for usage hints.
sqlite> .database
seq  name             file                                                     
---  ---------------  ----------------------------------------------------------
0    main             /var/db/pkg/local.sqlite                                 
sqlite> .tables
licenses            pkg_licenses        pkg_licenses_assoc
sqlite> .exit
 
Time to check up on your backups because that database is severely corrupted. Check /var/backups to see if it contains something like pkg.sql.xz.

You might be able to salvage some information (unlikely at this point I think) but... You could try pkg info -ao > software_list. I doubt that this will work but if it does you'd at least have a list of all your installed software. Optionally add -q to those parameters, then you can feed the list directly into tools such as Portmaster (only useful if you installed ports manually).

Then try to restore your backup and run the check again. Compare the list against each other and you should have found any differences.
 
I've checked /var/backups and the directory is completely empty.

I attempted the cmd pkg info -ao > software_list as requested and received the same error
Code: 
pkg: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2407: no such table: packages

There isn't a way to have pkg rebuild the database? That was what I was hoping when I reinstalled pkg using the above mentioned method. I may just have to backup my files and reinstall as a last resort.
 
Andrew Bateman said:
There isn't a way to have pkg rebuild the database? That was what I was hoping when I reinstalled pkg using the above mentioned method. I may just have to backup my files and reinstall as a last resort.
Click to expand...
No, because there's no way for PKG to determine the connection between a port and any installed files. Just go to a random port directory and you'll see this for yourself, at best you'll find Makefile, distinfo and pkg-descr, none of which contain information about the files being installed by that port.

(edit): Are you using Portmaster? If you do then look into /var/db/pkg, does it have any directory entries? Those can help you to determine what software got installed. Also, less reliable: /var/db/ports. These will contain the options for any ports you configured ( make config). Probably not usable but still worth looking into.

This is also why several cronjobs create backups of the most important system files in /var/backups. If that directory is empty then this means that your system probably hasn't been up for very long (a daily periodic job backs up your passwd and aliases files). My laptop showed the same problem (very dated backups) so I ended up writing my own backup script which was ran at least daily by firing it up during reboot (and then checking for a previous run).

But back to the issue at hand: your only option is to restore the database from a backup or to reinstall all the software on your system. However, I'd also try to look closely into when these problems manifested themselves and why. This is the first time for that I come across errors like these and I'm pretty certain that this isn't some kind of problem within PKG. Something must have triggered this.
 
The system wasn't up for too long. Occasional use and configuration over a couple of weeks. I think I may just end up reinstalling FreeBSD as I cannot install any packages or ports. It returns an error stating that the table packages does not exist.

I do appreciate all of the help. I'm not sure exactly what I did to cause this as I've been working sporadically but I'll just have to be more careful and make sure I setup backups right away.
 
Andrew Bateman said:
I'm not sure exactly what I did to cause this as I've been working sporadically but I'll just have to be more careful and make sure I setup backups right away.
Click to expand...
That's assuming you did something. I'd also keep a look out on any disk errors or other nasty possible issues. Good luck!
 
Check disk didn't show anything and for some reason I never did snapshots on var. It's a learning experience, I guess. I'm just hoping I can reinstall and keep my files without messing that up lol.

Thank you.
 
I run into this recently after a freebsd-update, then pkg upgrade, and the backups in `/var/backups/` did not help as all gave back the same error, which makes me wonder if this is because a new version on `pkgng` expects some different schema or whatever, or perhaps the backups or corrupt already...


In any case a cool feature of pkg is pkg shell so what I did to at least install back the packages I add was to do something crazy...
1. backup and wipe the current db in `/var/db/pkg/`
2. copy one of the backups to you're home dir or /tmp/ whatever you prefer, and decompress (`unxz pkg.XXX.xz`)
3. This is the part where we do something crazy but we are out of options anyway: `pkg shell < pkg.sql` where pkg.sql is the file we just decompressed.
4. You'll see errors but ignore them, you can now use `pkg shell` and the db will have the `packages` and possibly the `deps` table to, so I run a querry to find all `packages` that are not in `deps`, (I'm not sure this is the best way to only get the packages that I really installed, but it seems to work, however you wouldn't mind knowing what would be the best query to only get the packages I installed)
5. Put the output in a file, line by line (you only need the package names)
6. Delete the current pkg db in `/var/db/pkg/`, no need for a backup of this.
6. `cat pkg-list.txt | xargs pkg-static install -y`


This installed all the packages I add before, and `pkg info`is happy :)
 
Last edited:
miguelc thousands and millions and billions of thanks to you! Your steps help me, after database disk image is malformed error.

But for me it needs your first 3 steps to repair all.

Thanks!
 
miguelc said:
I run into this recently after a freebsd-update, then pkg upgrade, and the backups in `/var/backups/` did not help as all gave back the same error, which makes me wonder if this is because a new version on `pkgng` expects some different schema or whatever, or perhaps the backups or corrupt already...


In any case a cool feature of pkg is pkg shell so what I did to at least install back the packages I add was to do something crazy...
1. backup and wipe the current db in `/var/db/pkg/`
2. copy one of the backups to you're home dir or /tmp/ whatever you prefer, and decompress (`unxz pkg.XXX.xz`)
3. This is the part where we do something crazy but we are out of options anyway: `pkg shell < pkg.sql` where pkg.sql is the file we just decompressed.
4. You'll see errors but ignore them, you can now use `pkg shell` and the db will have the `packages` and possibly the `deps` table to, so I run a querry to find all `packages` that are not in `deps`, (I'm not sure this is the best way to only get the packages that I really installed, but it seems to work, however you wouldn't mind knowing what would be the best query to only get the packages I installed)
5. Put the output in a file, line by line (you only need the package names)
6. Delete the current pkg db in `/var/db/pkg/`, no need for a backup of this.
6. `cat pkg-list.txt > xargs pkg-static install -y`


This installed all the packages I add before, and `pkg info`is happy :)
Click to expand...
cat pkg-list.txt | xargs pkg-static install -y might work better. Thanks for this, I somehow lost my whole package database but not much has changed since the 3AM backup and that's intact. I used grep INSERT pkg.dump | cut -f2 -d"'" to get the pkg names.
 
You must log in or register to reply here.
Back
Top