# How to recreate local.sqlite?



## Chris_H (Nov 18, 2014)

OK. I ran into a recent problem with pkg(8). Where it corrupted it's sqlite3(1) database of the locally installed ports /var/db/pkg/local.sqlite. I am aware of the option in pkg(8) to (re)create the database from a backup, `pkg backup -r /path/to/db/backup`.

But given that pkg(8) _itself_ borked the database, and that I need to know I can _reliably_ recreate the database, if/when pkg(8) can't/won't. I was wondering if anyone might know the incantation for doing it with sqlite3(1).

I've already learned MySQL, and PostgreSQL. But sqlite(1) is quite a bit different. 

Thank you for all your time, and consideration.

--Chris


----------



## jb_fvwm2 (Nov 18, 2014)

In what precise way is it corrupted?


----------



## wblock@ (Nov 18, 2014)

There are files in /var/backups.  pkg.sql.xz looks likely.


----------



## Chris_H (Nov 18, 2014)

wblock@. Yes. I have the backup. I've unpacked, and examined it to insure it's the one I want. But would _very_ much like to how to feed it to sqlite3(1).

jb_fvwm2.
It (pkg(8)) no longer knows most of the previously built/installed ports have been built, and installed. It happened during the course of the build/install of a meta-port, in the ports tree. 

The backup is ~30 ports shy of what is currently installed. But it's a *whole lot* easier to reconcile, than nearly starting over. Which is what I'll need to do if I can't (re)install the backup.

I figure I can simply move the bad copy of local.sqlite aside. Then recreate it via some sqlite3(1) incantation.

I hope my need(s) were clearer this time. 

Thank you both, for your replies.

--Chris


----------



## jb_fvwm2 (Nov 18, 2014)

Well, I am sort of in the same situation.  A v9 failed to convert using `pkg2ng` so I copied a local.sqlite from another machine that had just  a few more ports installed and that had been converted.  `pkg install` -- to reinstall all or most important ports -- to register them properly -- wants to install [most of the time, not always ] clearly extra ports, after I deleted a few to save space, as if registering a newer version of one installed needed something not in its build-depends-list nor run-depends-list.   In a few years or sooner, something may transpire where it is more reliable in that particular instance, or I may come across a guide to fix it. Maybe even from this thread.


----------



## wblock@ (Nov 18, 2014)

Chris_H said:


> wblock@. Yes. I have the backup. I've unpacked, and examined it to insure it's the one I want. But would _very_ much like to how to feed it to sqlite3(1).



pkg-backup(8) shows options to dump and restore the database.  Untested by me, though.


----------



## Chris_H (Nov 18, 2014)

wblock@ said:


> pkg-backup(8) shows options to dump and restore the database.  Untested by me, though.


Thank you, wblock@, for your thoughtful reply.
Indeed it does. I even mentioned that in the OP. 
But, as I also mentioned; I was hoping to find out if it's possible to do it with sqlite3(1). The database engine used to make, and keep the data itself. Point being; if pkg(8) is unable to do the job. How else would I, or anyone else recover?
Thus far, the prospects for anyone recovering from such a scenario look pretty bleak. 
Sigh. Looks like I'll need to go to sqlite3(1) school, to learn yet another SQL language.

Thank you again, wblock@, for taking the time to reply.

--Chris


----------



## wblock@ (Nov 18, 2014)

Look at the contents of the backup file.  It looks like it has all the SQL commands to recreate the database.


----------



## Chris_H (Nov 18, 2014)

Hello, wblock@, and thanks for the reply.
Indeed. I noticed that too. But was unsure how to move forward. The best I can figure is
`cd /var/db/pkg`
`mv ./local.sqlite ./_bad.local.sqlite`
`sqlite3 local.sqlite ATTACH PKG.sql`
But am unsure. Still reading sqlite3(1), and it's associated documentation. Hoping to get the correct incantation.

Thanks again, wblock@.

--Chris


----------



## Chris_H (Nov 18, 2014)

OK. Just got brave (or crazy), and gave it a go. Here's what I did
`cd /var/db/pkg`
`mv ./local.sqlite ./_BAD.local.sqlite`
with the backup file in this same directory, as pkg.sql. I then did
`sqlite3 local.sqlite`
Which gave me the sqlite3(1) prompt
`sqlite>`
I then issued
`sqlite> .read pkg.sql`
after some churning. The sqlite3(1) prompt returned. I examined the contents of the directory in another terminal, and discovered there was a new local.sqlite. With a size that I would expect from a DB with as many ports as I had built on this server. So I simply issued
`sqlite> .quit`
While this all looks promising. I won't know until I perform some more investigation. But thought it prudent to at least update my progress here. For others that might be following this, and feeling inclined to reply.

--Chris


----------



## Chris_H (Nov 18, 2014)

Ugh... Well that (the above) didn't do it.
Issuing `pkg info` resulted in 
	
	



```
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:2252: table licenses already exist
```

Thanks for the good times, pkg(8). But I think it's time I replace you with something I can depend on.

--Chris


----------



## wblock@ (Nov 18, 2014)

Rather than trying to re-engineer it, did you try restoring it with the tool intended for the job?


----------



## Chris_H (Nov 18, 2014)

OK. Just for fun. I gave it a try
`# pkg backup -r /var/db/pkg/pkg.sql`

```
Restoring database:
Restoring: 100%
pkg: sqlite error while executing backup step in file backup.c:101: not an error
pkg: sqlite error -- (null)
```
OK fine. I'll just feed you the original, in its unaltered (packed) form.
`# pkg backup -r /var/db/pkg/pkg.sql.xz`

```
Restoring database:
Restoring: 100%
pkg: sqlite error while executing backup step in file backup.c:101: not an error
pkg: sqlite error -- (null)
```
Equally unsatisfying. Interesting too. Because this backup, and the additional one, also created by periodic(8) both have the same size, and I *know* they were copies of fully working, and perfectly valid databases. I queried them both, when they were still active. There was absolutely no evidence of problems. Yet neither of them will be accepted by pkg(8), or, perhaps more accurately; sqlite3(1). 
I was afraid this day would come. Now there appears to be no salvation. 

Thanks for the reply, wblock@. Even if the results were not what I had hoped for.

--Chris


----------



## Chris_H (Nov 18, 2014)

Re-installation of databases/sqlite3 had no positive effect, either. Same (non)error thrown. 
Just thought I'd mention it. Given sqlite3(8) is doing the actual complaining.

--Chris


----------



## wblock@ (Nov 18, 2014)

After the first "non"-error, did you try `pkg info` or something similar to see if that was really a problem?


----------



## Chris_H (Nov 18, 2014)

OK _appears_ I might have gotten to a usable state, using the snapshot of the /var/db/pkg kept along with the local.sqlite backup. It's called pkgdb.bak.tbz, or pkgdb.bak2.tbz, depending how many backup revisions you keep. I simply unpacked the file, which contained the entire tree, including /var. Then I packed up the contents of the pkg/ folder. Deleted the contents of my current pkg/ folder. Then, finally, unpacking the backup copy in there.

It's all too early to tell. But my initial impression, seems promising.

--Chris


----------



## Chris_H (Nov 18, 2014)

wblock@ said:


> After the first "non"-error, did you try `pkg info` or something similar to see if that was really a problem?


Yes. There was nothing returned.

Thanks for the reply, wblock@.

--Chris

OH, and thank you very much for trying!


----------



## Chris_H (Nov 18, 2014)

In the end. What still _really_ bothers me; is _why_ the stated method of restoring one's local.sqlite doesn't seem to be a valid path. Anyone have any additional thoughts? This seems to be a pretty significant discrepancy, no? Also a bit unnerving.

--Chris


----------



## wblock@ (Nov 18, 2014)

Either a bug in the man page or a bug in the program.  Ask on freebsd-ports, or enter a bug report.  Clarification on some of the manual pages would be very welcome.


----------



## Chris_H (Nov 18, 2014)

wblock@ said:


> Either a bug in the man page or a bug in the program.  Ask on freebsd-ports, or enter a bug report.  Clarification on some of the manual pages would be very welcome.


Thanks for the confirmation. I had intended to ask on the lists, but wasn't sure if ports@ would be the best, or not. If I get a confirmation on the list(s). I'll submit a pr(1).

Thanks for all your help, and feedback, wblock@.

--Chris


----------



## pbd (Jul 26, 2016)

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

Worked for me.


----------



## icecoke (Nov 14, 2017)

Thanks @pdb - that:

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

brought me back my corrupted local.sqlite which was *not* working with

`pkg backup -r /var/backups/pkg.sql.xz /var/db/pkg/local.sqlite`

as it seems that backups themself were corrupted, too.


----------



## Andrew_xXx (Jan 11, 2018)

pbd, icecoke and others

I had the same problem by link, but database wasn't correct after this, and pkg(8) wasn't installing:
http://glasz.org/sheeplog/2017/02/freebsd-usrlocalliblibpkgso3-undefined-symbol-utimensat.html
1st of all, I've ran: 
`mv /var/db/pkg/local.sqlite /var/db/pkg/local.sqlite.backup1`
2)Then I installed older version of *pkg* (it possible, if you'll rename old file /var/db/pkg/*local.sqlite *to any backup name).
`pkg install -y pkg`
Type [Y]es.
3) Then I have done this:
`mv /var/db/pkg/local.sqlite /var/db/pkg/local.sqlite.backup2
xzcat /var/backups/pkg.sql.xz | sqlite3 /var/db/pkg/local.sqlite
sqlite3 /var/db/pkg/local.sqlite
sqlite> PRAGMA user_version=30;
sqlite> .quit`
4) But database wasn't opening, because it was older version of *pkg*, than it was before updating.
This cmd helped me to repair database of installed packages:
`pgk2ng`


----------



## SirDice (Jan 11, 2018)

Andrew_xXx said:


> This cmd helped me to repair database of installed packages:
> pgk2ng


That command doesn't do anything anymore. It was used to convert the old package registrations to PKGNG. As there are no old package registrations on 10 and above this command won't do anything.

Probably the primary reason why you're getting the utimesat error is because you're trying to run a recent pkg(8) on an unsupported FreeBSD version.


----------



## paulbeard (Apr 21, 2019)

Hmm. Looks like there isn't much to be done if pkg sh*its the bed? None of the steps defined above (dumping and restoring the local.sqlite db) work any better now than as noted above.
I have been forced to reinstall pkg from source and then use 
	
	



```
portmaster --no-confirm `cat ~installed-ports`
```
 to rebuild it all. Looks like pkg has reached the same level of maturity as the old portupgrade/pkgdb combo…


----------

