• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

How to recreate local.sqlite?

Chris_H

Aspiring Daemon

Thanks: 111
Messages: 829

#1
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
 
Last edited:

jb_fvwm2

Daemon

Thanks: 171
Messages: 1,672

#2
In what precise way is it corrupted?
 

wblock@

Administrator
Staff member
Administrator
Moderator
Developer

Thanks: 3,558
Messages: 13,856

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

Chris_H

Aspiring Daemon

Thanks: 111
Messages: 829

#4
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

Daemon

Thanks: 171
Messages: 1,672

#5
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.
 

Chris_H

Aspiring Daemon

Thanks: 111
Messages: 829

#7
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. :confused:
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@

Administrator
Staff member
Administrator
Moderator
Developer

Thanks: 3,558
Messages: 13,856

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

Chris_H

Aspiring Daemon

Thanks: 111
Messages: 829

#9
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

Aspiring Daemon

Thanks: 111
Messages: 829

#10
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

Aspiring Daemon

Thanks: 111
Messages: 829

#11
Ugh... Well that (the above) didn't do it.
Issuing pkg info resulted in
Code:
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@

Administrator
Staff member
Administrator
Moderator
Developer

Thanks: 3,558
Messages: 13,856

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

Chris_H

Aspiring Daemon

Thanks: 111
Messages: 829

#13
OK. Just for fun. I gave it a try
# pkg backup -r /var/db/pkg/pkg.sql
Code:
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
Code:
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. :mad:

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

--Chris
 

Chris_H

Aspiring Daemon

Thanks: 111
Messages: 829

#14
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@

Administrator
Staff member
Administrator
Moderator
Developer

Thanks: 3,558
Messages: 13,856

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

Chris_H

Aspiring Daemon

Thanks: 111
Messages: 829

#16
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

Aspiring Daemon

Thanks: 111
Messages: 829

#17
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

Aspiring Daemon

Thanks: 111
Messages: 829

#18
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@

Administrator
Staff member
Administrator
Moderator
Developer

Thanks: 3,558
Messages: 13,856

#19
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

Aspiring Daemon

Thanks: 111
Messages: 829

#20
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

Active Member

Thanks: 33
Messages: 199

#21
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

Member

Thanks: 5
Messages: 94

#22
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

New Member


Messages: 1

#23
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

Administrator
Staff member
Administrator
Moderator

Thanks: 5,508
Messages: 25,688

#24
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.