pkg db error

[Nutz]

Hello All,

I'm facing a issue with my pkg database sqlite.
I'm not able to install any package.

Here the error message :

pkg: sqlite error while executing INSERT OR IGNORE INTO licenses(name) VALUES(?1) in file pkgdb.c:1614: table licenses has no column named name

I tried to upgrade & restore db:

pkg upgrade -f
pkg-static upgrade -f
pkg backup -r pkg.sql.xz

i did a truss with pkg install command :

sqlite error while executing INSERT OR IGNORE INTO licenses(name) VALUES(?1) in file pkgdb.c:1614: table licenses has no column named namewrite(2,"sqlite error while executing INS"...,138) = 138 (0x8a)

write(2,"\n",1)                     = 1 (0x1)
fstat(8,{ mode=-rw-r--r-- ,inode=103570,size=688128,blksize=131072 }) = 0 (0x0)
fstatat(4,"repo-custom.sqlite",{ mode=-rw-r--r-- ,inode=103570,size=688128,blksize=131072 },0x0) = 0 (0x0)
close(8)                     = 0 (0x0)
close(3)                     = 0 (0x0)
close(4)                     = 0 (0x0)
exit(0x4a)                   
process exit, rval = 74

So i just went into that database and tried to create the missing table :

sqlite> CREATE TABLE licenses (id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE);
Error: table licenses already exists

now, i ran out of idea...db seems to be okay..but not...

 

[SirDice]

pkg backup -r pkg.sql.xz

Have you tried an older backup from /var/backups/? The version you restored may have been corrupted already when it was backed up.

So i just went into that database and tried to create the missing table :

The table itself isn't missing. It's complaining about a missing 'name' column in that table.

Just so we have all the information, what version of FreeBSD and what version of pkg(8)?

 

[Nutz]

Hi Sirdice,

yep, i tried three differents versions with the same issue :/

EDIT: Now, i can't even restore DB
I tried to reinstall pkg from ports but i got the same error.

 

[T-Daemon]

pkg backup -r pkg.sql.xz might be unreliable. I, for example don’t get a functional database at all with that method, try the following approach from this thread posting (works for me):

mv /var/db/pkg/local.sqlite /var/db/pkg/local.sqlite.backup
xzcat /var/backups/pkg.sql.xz | sqlite3 /var/db/pkg/local.sqlite

 

[Nutz]

Hi T-daemon,

I'm gonna try your idea.

SirDice, you are right, as usual, i didnt read the message clearly...
I tried to ALTER the table but seems to not work on sqlite3 neither to import/export data with "headers on".

EDIT: so for the fun, i copied an db from one similar server and thats' work.
I restored db again and i now i got a new error messages ! now, i know the pkg backup -r works :d
Let's try manual restore, now and i got the same error...

EDIT2: So i didnt find a good way to restore my db, so i re-copied db local but this one is not sync with the system.
The only thing that make me afraidthat's i dont know why the old db is being corrupted.