libpkg, sqlite and database problems prevent building any packages

[Thomas Mueller]

On 05/08/2013 14:30, Thomas Mueller wrote:
> I could see my pkg client is out of date, but how do I update it?
>
> Attempts to update all gave me those error messages.  Installation fails.

Matthew Seaman responded:

> Your package database has got into an inconsistent state.  pkg(8) is
> attempting to auto-update the schema to the latest version, but failing
> because it's trying to remove an 'infos' column from a table where that
> column has apparently already been removed.  (Likely this situation has
> come about because pkg got killed in the middle of doing this update
> previously.)

> How happy are you to get down'n'dirty with the source code and running
> SQL from the command line?  If you look at the updates pkg is attempting
> to run shown here:

>    https://github.com/freebsd/pkg/blob/master/libpkg/private/db_upgrades.h

> would you be capable of looking at the DB schema, working out which of
> those updates had been applied, aplying any outstanding ones by hand and
> then setting the user version to 19 by:

>    sql> PRAGMA user_version = 19 ;

?

> If not, check in /var/backups for a good copy of your local.sqlite
> database and try and restore from there.  Unfortunately, there's no
> guarantee that any backup copy doesn't have the same inconsistencies as
> your live copy.

> We need to make pkg(8) databases more resilient to the effects of SIGHUP
> or similar while they are elbows-deep in the bowels of the DB schema...

Trying to do what you suggest would in all likelihood be an exercise in futility, especially when I am very tired from being unable to sleep.

I didn't realize pkgng was so non-sturdy.

I wonder what would happen if I delete /var/db/pkg/local.sqlite, or perhaps better, move it to another location and name so as not to burn my bridges.

But anything so I can upgrade pkg.

Does pkgng have no means of recovery?  Can pkg regenerate a database, and from what?

My /var/backups on the USB-stick 9.2-BETA2 amd64 installation shows


total 280
-rw-r--r--  1 root  wheel    1674 Apr  2  2012 aliases.bak
-rw-r--r--  1 root  wheel     436 Apr  2  2012 group.bak
-rw-------  1 root  wheel    1500 Apr  2  2012 master.passwd.bak
-rw-r--r--  1 root  wheel  269144 Dec 13  2012 pkgdb.bak.tbz
-rw-r--r--  1 root  wheel     129 Jul 16  2012 pkgdb.bak.tbz.2


pkg.bak.tbz looks like the database from the old format.

My /var/backups on the USB-stick 9.1-STABLE i386 installation shows


total 140
-rw-r--r--  1 root  wheel    1688 Jan 20  2013 aliases.bak
-rw-r--r--  1 root  wheel    1674 Jun  6  2012 aliases.bak2
-rw-r--r--  1 root  wheel     481 Jan 17  2013 group.bak
-rw-r--r--  1 root  wheel     449 Jul 13  2012 group.bak2
-rw-------  1 root  wheel    1761 Jan 17  2013 master.passwd.bak
-rw-------  1 root  wheel    1500 Jun  6  2012 master.passwd.bak2
-rw-r--r--  1 root  wheel    8524 May 28 03:01 pkgdb.bak.tbz
-rw-r--r--  1 root  wheel     130 Jan 19  2013 pkgdb.bak.tbz.2
-rw-r--r--  1 root  wheel  100352 May 28 04:29 pkgng.db


pkgng.db looks like the new format, but that was before I built wine and dependencies.

Tom