# pkg db error



## Nutz (Apr 17, 2020)

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 (Apr 17, 2020)

Nutz said:


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



Nutz said:


> 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 (Apr 17, 2020)

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 (Apr 17, 2020)

`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 (Apr 17, 2020)

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.


----------

