damage to pkg's sqlite data base

[Scott Bennett]

For nearly two weeks I've been stymied by an apparently damaged record
in the sqlite data base used by pkg(8) and pkg-static(8). Unfortunately,
it is a record for a port that is depended upon rather heavily, lang/gcc.
lang/gcc compiled and linked just fine, but any attempt to install the result
ends up like this.

===> Checking if gcc already installed
===> Registering installation for gcc-4.8.4_3
Installing gcc-4.8.4_3...
pkg-static: sqlite error while executing iterator in file pkgdb_iterator.c:931: database disk image is malformed
pkg-static: sqlite error while executing INSERT OR REPLACE INTO files (path, sha256, package_id) VALUES (?1, ?2, ?3) in file pkgdb.c:1722: database disk image is malformed
*** Error code 70

Stop.
make: stopped in /usr/ports/lang/gcc

I'm not familiar with sqlite commands, nor with the structure of the
pkg data base, nor any of several other relevant topics. But I do have
questions:

1) how might the damage have happened?

2) how might such an event be avoided/prevented in the future?

3) most urgently, how can I *fix* it?

4) I was unable to find any instructions for recreating a pkg data
base if the data base gets damaged/destroyed. Is there a way to
do that that I missed?

Thanks again for any help here.


Scott Bennett, Comm. ASMELG, CFIAG
**********************************************************************
* Internet: bennett at sdf.org *xor* bennett at freeshell.org *
*--------------------------------------------------------------------*
* "A well regulated and disciplined militia, is at all times a good *
* objection to the introduction of that bane of all free governments *
* -- a standing army." *
* -- Gov. John Hancock, New York Journal, 28 January 1790 *
**********************************************************************
_______________________________________________
freebs...@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-ports
To unsubscribe, send any mail to "freebsd-port...@freebsd.org"

[Chris H]

On Tue, 12 May 2015 01:17:46 -0500 Scott Bennett <ben...@sdf.org> wrote

> For nearly two weeks I've been stymied by an apparently damaged record
> in the sqlite data base used by pkg(8) and pkg-static(8). Unfortunately,
> it is a record for a port that is depended upon rather heavily, lang/gcc.
> lang/gcc compiled and linked just fine, but any attempt to install the result
> ends up like this.
>
> ===> Checking if gcc already installed
> ===> Registering installation for gcc-4.8.4_3
> Installing gcc-4.8.4_3...
> pkg-static: sqlite error while executing iterator in file
> pkgdb_iterator.c:931: database disk image is malformed pkg-static: sqlite
> error while executing INSERT OR REPLACE INTO files (path, sha256, package_id)
> VALUES (?1, ?2, ?3) in file pkgdb.c:1722: database disk image is malformed
> *** Error code 70
>
> Stop.
> make: stopped in /usr/ports/lang/gcc
>
> I'm not familiar with sqlite commands, nor with the structure of the
> pkg data base, nor any of several other relevant topics. But I do have
> questions:
>
> 1) how might the damage have happened?
>
> 2) how might such an event be avoided/prevented in the future?
>
> 3) most urgently, how can I *fix* it?

You should get a backup made on the nightly cron(8) (periodic(8)).
It'll be located in /var/backups
Off the top of my head I can't remember which of the 2, is which.
But a simple size comparison should tell the tale. :)
Simply rename your (now) corrupt db, and copy the backup over.

I ran into this problem as well, and this was how I was finally
able to overcome it.

Hope this helps!

--Chris

[Matthew Seaman]

On 05/12/15 07:17, Scott Bennett wrote:
> I'm not familiar with sqlite commands, nor with the structure of the
> pkg data base, nor any of several other relevant topics. But I do have
> questions:
>
> 1) how might the damage have happened?

Could be any number of things, but in principle the corruption is
usually due to some IO request that didn't complete successfully. That
can happen if pkg(8) gets killed at exactly the wrong moment, or if your
pkg database is on an NFS mount and the locking isn't working properly.

> 2) how might such an event be avoided/prevented in the future?

It's pretty rare judging by the lack of complaints we've had. So long
as your disk hardware is OK, and you're keeping the pkg DB on a local
disk it should be reliable enough that you don't need to take any
special measures. However the prudent sysadmin will always have
backups, and there are a couple of periodic scripts that will backup
your package database for you. One, as I recall, just copies the
local.sqlite file to /var/backup, while the other uses 'pkg backup' to
create a database dump.

> 3) most urgently, how can I *fix* it?

If your backups don't sort it for you, and you aren't happy to go into
sqlite and mend things by using SQL directly, then you can fix it in the
true heroic fashion:

1) Make sure that you have packages available for everything
installed on your system, either in whatever repo you're using
(preferred), or by creating pkg tarballs of all the packages installed
on your system:

# mkdir /tmp/pkg-backup
# pkg create -a -o /tmp/pkg-backup

(That might be affected by the corruption you've experienced. Watch out
for any error messages.)

2) Generate a list of all the *non-automatic* packages you have
installed:

# pkg query -e "%a == 0" "%n-%v" > /tmp/pkg-list

3) Move aside your corrupted pkg database:

# mv /var/db/pkg/local.sqlite{,.save}

4) Reinstall pkg(8) -- this will reinitialise your database:

# /usr/sbin/pkg bootstrap -f

5) Reinstall all the packages you had previously installed:

# xargs pkg install < /tmp/pkg-backup

or -- if and only if you could create tarballs of everything
without error -- you can reinstall from those packages:

# cd /tmp/pkg-backup
# sed -e 's,$,.txz,' < /tmp/pkg-list | xargs pkg add

This should restore your system to its previous state preserving the
automatic flags on your packages. It should install any dependencies
required and mark them as automatic. You will want to list all the
packages installed before and after and compare the two as a sanity
check. If there are packages that you used to have installed but are no
longer listed, then you'll have the old package files left orphaned on
your drive, which it would be a good idea to clean up. Doing that I
leave as an exercise for the student.

> 4) I was unable to find any instructions for recreating a pkg data
> base if the data base gets damaged/destroyed. Is there a way to
> do that that I missed?

It's not something that needs doing that often. The general approach
will be -- as shown above -- to create a new package database and
reinstall everything. You may or may not want to wipe the contents of
/usr/local before you do that (obviously save your config files from
/usr/local/etc and any precious web content from /usr/local/www, etc.
etc.), depending on how badly the database was damaged, how up-to-date
the system was and whether you have a good idea of what packages you had
installed previously or not.

Cheers,

Matthew

[Scott Bennett]

The problem began much farther back than two days ago, but I should
be able to restore it from two weeks or more in the past and then find
the most recent version that is not corrupted.

> Simply rename your (now) corrupt db, and copy the backup over.

However, if I do that, then what happens to all the ports that have
been updated or added since that version of the data base was backed up?
I have run "portmaster -a" (with some additional options) quite a few
times since the lang/gcc problem first appeared, so an old local.sqlite
will no longer accurately reflect what is currently installed.

>
> I ran into this problem as well, and this was how I was finally
> able to overcome it.
>
> Hope this helps!
>
> --Chris
>
> >
> > 4) I was unable to find any instructions for recreating a pkg data
> > base if the data base gets damaged/destroyed. Is there a way to
> > do that that I missed?

There must be a way to do this, right? I mean, really, it's pretty
fundamental that no new data base be put into production without a way to
rebuild it. The FreeBSD developers haven't really broken so ancient and
basic a principle, have they? So what's the trick? What is the method
to rebuild /var/db/pkg/local.sqlite from scratch based upon the currently
installed ports/packages?

[Chris H]

On Wed, 13 May 2015 00:12:51 -0500 Scott Bennett <ben...@sdf.org> wrote

I whined about it the first time my DB blew up. It's become
corrupted several times since on different boxes/versions. *but*
after the first time, I made it a habit of making a copy of it *before*
embarking on an upgrade, or install of any ports. Seems it's the only
way to save yourself. pkg(8), to the best of my knowledge doesn't
perform a backup prior to any of this. So I *do*.
Annoying, but it beats attempting to recover from a failed port
upgrade/install.

>
>
> Scott Bennett, Comm. ASMELG, CFIAG
> **********************************************************************
> * Internet: bennett at sdf.org *xor* bennett at freeshell.org *
> *--------------------------------------------------------------------*
> * "A well regulated and disciplined militia, is at all times a good *
> * objection to the introduction of that bane of all free governments *
> * -- a standing army." *
> * -- Gov. John Hancock, New York Journal, 28 January 1790 *
> **********************************************************************
> _______________________________________________
> freebs...@freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-ports
> To unsubscribe, send any mail to "freebsd-port...@freebsd.org"

--Chris

--

[andrew clarke]

On Wed 2015-05-13 17:55:26 UTC+1000, andrew clarke (ma...@ozzmosis.com) wrote:

> $ sqlite3 local.sqlite.new
> SQLite version 3.8.10.1 2015-05-09 12:14:55
> Enter ".help" for usage hints.
> sqlite> .read local.sqlite.dump
> sqlite> .quit
>
> Now we can use our newly created database, which should be error-free:
>
> $ sudo cp /var/db/pkg/local.sqlite /var/db/pkg/local.sqlite.backup
> $ sudo mv local.sqlite.new /var/db/pkg/local.sqlite
>
> I don't guarantee any of the above will work. It will depend on how
> much the database is corrupted etc.
>
> You will also need databases/sqlite3 installed, which unfortunately
> isn't provided in the FreeBSD base system. This could be a problem if
> pkg refuses to install anything. In that case I would either run the

Actually I was wrong about this. The pkg command has the sqlite3
interpreter built-in, accessed via "pkg shell", that opens local.sqlite
by default:

$ pkg shell
SQLite version 3.8.8.2 2015-01-30 14:30:45
Enter ".help" for usage hints.
sqlite> .quit

So there is no real need to install databases/sqlite3.

Regards
Andrew

[andrew clarke]

On Tue 2015-05-12 23:47:02 UTC-0700, Chris H (bsd-...@bsdforge.com) wrote:

> I whined about it the first time my DB blew up. It's become
> corrupted several times since on different boxes/versions. *but*
> after the first time, I made it a habit of making a copy of it *before*
> embarking on an upgrade, or install of any ports.

Can you post a link to your message and/or a link to your FreeBSD PR on Bugzilla?

I use FreeBSD on a number of machines (bare metal & virtual) and have
never encountered local.sqlite corruption, and maybe I haven't been
paying attention but I haven't noticed anyone else mention it on the
list until now.

As I said to the OP, if you encounter this problem and you're sure
it's not caused externally (hardware or filesystem corruption) then
it's probably worthwhile making your corrupted local.sqlite available
somewhere for it to be looked at by someone who understands SQLite.

Regards
Andrew

[andrew clarke]

On Wed 2015-05-13 19:00:27 UTC+1000, Peter Jeremy (pe...@rulingia.com) wrote:

> On 2015-May-13 18:12:44 +1000, andrew clarke <ma...@ozzmosis.com> wrote:
> >You can reinstall just those ports. Check /var/log/messages, eg.
> >
> >$ grep pkg /var/log/messages
> >May 12 14:34:38 blizzard pkg: poudriere upgraded: 3.1.4 -> 3.1.6
> >May 12 14:38:08 blizzard pkg: git-lite-2.4.0 installed
> >May 13 08:29:04 blizzard pkg: sqlite3 upgraded: 3.8.9_1 -> 3.8.10.1
> >May 13 08:29:05 blizzard pkg: spamassassin reinstalled: 3.4.1_1 -> 3.4.1_1
> >May 13 08:29:05 blizzard pkg: ca_root_nss upgraded: 3.18.1 -> 3.19
>
> That assumes you have syslog messages back to when you started using pkg.
> syslog was never intended to provide an audit trail.

True. I'd been wondering about why the pkg developers didn't add
logging functionality, but it looks like I just needed to add this to
/etc/syslog.conf:

!pkg
*.* /var/log/pkg.log

then run:

sudo touch /var/log/pkg.log
sudo service restart syslogd

I think that's correct. I'm not sure the syslog.conf entry above will
include pkg-static - maybe it should be "!pkg*" instead? I must admit
I find the syslog.conf man page to be not very helpful.

$ cat /var/log/pkg.log
May 13 20:58:25 blizzard pkg: jive-1.1 deinstalled
May 13 20:58:29 blizzard pkg: jive-1.1 installed

I tend to think pkg.log creation & use should be a normal thing for a
new FreeBSD install.

> >The "trick" is to revert to a known-good backup of the pkg database
> >that's generated daily by /usr/local/etc/periodic/daily/411.pkg-backup
> >in /var/backups/ :
> >
> >-rw-r--r-- 1 root wheel 2207320 2015-05-13 04:20:30 pkg.sql.xz
> >-rw-r--r-- 1 root wheel 2196088 2015-05-12 04:21:24 pkg.sql.xz.2
>
> Assuming that they aren't corrupt. But that's better than nothing. Note
> that the backup is taken every day, whether or not there has been any
> change to the pkg database, so you have 2 days of backups, not the last
> two revisions.

Yes, that's a deficiency in the periodic script. Probably not
difficult to improve either, with the correct use of /usr/bin/diff.

> On 2015-May-13 18:17:12 +1000, andrew clarke <ma...@ozzmosis.com> wrote:
> >Actually I was wrong about this. The pkg command has the sqlite3
> >interpreter built-in, accessed via "pkg shell", that opens local.sqlite
> >by default:
>

> Some experimenting suggests that none of the pragma commands work in
> "pkg shell", so you probably will need to find a copy of sqlite3.

Oh dear, that's unfortunate.

Regards
Andrew

[Chris H]

On Wed, 13 May 2015 02:20:55 -0500 Scott Bennett <ben...@sdf.org> wrote

> "Chris H" <bsd-...@bsdforge.com> wrote:
> > On Wed, 13 May 2015 00:12:51 -0500 Scott Bennett <ben...@sdf.org> wrote
> > > "Chris H" <bsd-...@bsdforge.com> wrote:
> > > > On Tue, 12 May 2015 01:17:46 -0500 Scott Bennett <ben...@sdf.org>
> > > wrote >
> > > > > For nearly two weeks I've been stymied by an apparently damaged
> > > record > > in the sqlite data base used by pkg(8) and pkg-static(8).
> > > Unfortunately, > > it is a record for a port that is depended upon rather
> > > heavily, lang/gcc. > > lang/gcc compiled and linked just fine, but any
> > > attempt to install the > > result ends up like this.
> > > > >
> > > > > ===> Checking if gcc already installed
> > > > > ===> Registering installation for gcc-4.8.4_3
> > > > > Installing gcc-4.8.4_3...
> > > > > pkg-static: sqlite error while executing iterator in file
> > > > > pkgdb_iterator.c:931: database disk image is malformed pkg-static:
> > > sqlite > > error while executing INSERT OR REPLACE INTO files (path,
> > > sha256, > > package_id) VALUES (?1, ?2, ?3) in file pkgdb.c:1722:
> > > database disk image > > is malformed *** Error code 70
> > > > >
> > > > > Stop.
> > > > > make: stopped in /usr/ports/lang/gcc
> > > > >
> > > > > I'm not familiar with sqlite commands, nor with the structure of
> > > the > > pkg data base, nor any of several other relevant topics. But I
> > > do have
>

> If someone knows sqlite well enough to look at the error messages above
> and then give me directions to delete the offending records, then perhaps the
> next attempt to install lang/gcc might actually work. Anyone?

> Chris, thanks for your responses, depressing though they be.
> I have been using portmaster for ages to update my installed ports
> and usually to install them in the first place. Does your suggestion
> mean abandoning portmaster?
No. I'm an avid user of portmaster(8). In fact I intend to be it's
maintainer. :)
> If it is necessary to back local.sqlite up
> before each installation, that would seem to imply that portmaster would
> only be useful in getting a list of ports to be updated, after which each
> port would have to be updated individually, so that local.sqlite could be
> backed up before each one. I have over 1900 ports installed, so that would
> mean just never updating my ports at all, except by rebuilding all of them
> upon upgrading to a new major release of FreeBSD.
I
> Bapt, oh, Bapt, whatever were you folks thinking? Did you really
> leave us no practical recovery method?
In Bapt's humble defense. It's difficult, to impossible for him to fix
a problem w/o decent clues. So I wouldn't go pointing any fingers, just
yet. :) In other words; w/o a cause, what should he fix? :)
What recovery would you suggest? Truth is, if you brushed up on sqlite3
you could create a fresh DB, using output from portmaster(8). Troublesome,
perhaps. But at least an alternative.

[Chris H]

On Wed, 13 May 2015 18:26:11 +1000 andrew clarke <ma...@ozzmosis.com> wrote

> On Tue 2015-05-12 23:47:02 UTC-0700, Chris H (bsd-...@bsdforge.com) wrote:
>
> > I whined about it the first time my DB blew up. It's become
> > corrupted several times since on different boxes/versions. *but*
> > after the first time, I made it a habit of making a copy of it *before*
> > embarking on an upgrade, or install of any ports.
>
> Can you post a link to your message and/or a link to your FreeBSD PR on
> Bugzilla?
>
> I use FreeBSD on a number of machines (bare metal & virtual) and have
> never encountered local.sqlite corruption, and maybe I haven't been
> paying attention but I haven't noticed anyone else mention it on the
> list until now.
>
> As I said to the OP, if you encounter this problem and you're sure
> it's not caused externally (hardware or filesystem corruption) then
> it's probably worthwhile making your corrupted local.sqlite available
> somewhere for it to be looked at by someone who understands SQLite.

That's exactly what I did. In fact, Bapt asked for the DB, which I
happily provided. While I've run into the problem several times,
and on different hardware/FreeBSD versions, I've only mentioned it once,
and it was on the mailing list. On the upside, I (knock on wood) haven't
run into the problem for at least 2 mos. So here's hoping that work
that's been done has [at least] *minimized* the likelihood of DB
corruption. :)

>
> Regards
> Andrew
> _______________________________________________
> freebs...@freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-ports
> To unsubscribe, send any mail to "freebsd-port...@freebsd.org"

--Chris

--

[Baptiste Daroussin]

Coming very late to the discussion I would like to understand what lead you to a
broken database, from what I am aware of the only way to end up with a broken db
is using pkg over nfs without rpc lock properly running (note that the default

nfs client service on freebsd does not start rpc lock...)

pkg work around this case by detecting the filesystem it is running on and
changing the locking system it uses when running on network filesystem so this
problem cannot in theory happen anymore even if one forgot to start rpc lockd.

It remains however one case where this can happen: it is one the host is running
over nfs and it starts a jail, in that case, the jail does not know it is
running over nfs so pkg cannot know it needs to switch the locking system.

Are you in one of those situation, if not can you describe a a bit more your
system?

Now by default pkg creates back up of the db daily to allow recovery, there was
an issue before pkg 1.5 which requires a manual intervention and fixed after pkg
1.5 so it is easier to recover.

sqlite is a pretty solid db I would like to understand how you ended up with a
corrupted db if not what I described above.

Best regards,
Bapt

[Scott Bennett]

I'm short on time right now, so I'll defer my replies to Andrew Clark
and Chris H until late tonight after I (hope to) have had a chance to try
out Andrew's suggested procedure. I do have sqlite installed, though (see
far below).

Chris? You already responded to my question 4) above, which answers
your question. Why are you now asking this? Any data base intended for
production use MUST be accompanied by a method of recreating it before
the data base goes into production. This has been the case as long as
data bases have been used. The Wallendas may walk without nets between
skyscraper roofs, but I assure you that a) not all of them arrive at the
intended destinations and b) normal mortals have a different perspective
on that sort of risk.
Further, if portmaster produces that output *from the corrupted data
base*, then it stands to reason that other cases of corruption that differed
in particulars from this one might prevent portmaster from providing said
output.

> > >
> Coming very late to the discussion I would like to understand what lead you to a
> broken database, from what I am aware of the only way to end up with a broken db
> is using pkg over nfs without rpc lock properly running (note that the default
>
> nfs client service on freebsd does not start rpc lock...)
>
> pkg work around this case by detecting the filesystem it is running on and
> changing the locking system it uses when running on network filesystem so this
> problem cannot in theory happen anymore even if one forgot to start rpc lockd.
>
> It remains however one case where this can happen: it is one the host is running
> over nfs and it starts a jail, in that case, the jail does not know it is
> running over nfs so pkg cannot know it needs to switch the locking system.
>
> Are you in one of those situation, if not can you describe a a bit more your
> system?

FreeBSD hellas 10.1-STABLE FreeBSD 10.1-STABLE #54 r282368: Sun May 3 15:48:37 CDT 2015 bennett@hellas:/usr/obj/usr/src/sys/hellas amd64
I have only one machine running these days, so there is no NFS. At
present, I have no jails set up.
Here are data concerning the relevant drive, which is the boot drive.
There are many other file systems on this machine, but they all involve
external devices and are unrelated to this issue, except for /buildwork,
where WRKDIRPREFIX=/buildwork/ports and CCACHE_DIR=/buildwork/ccache. The
same sort of information for /buildwork is included below.

/dev/mirror/fbsds1a on / (ufs, local)
devfs on /dev (devfs, local, multilabel)
/dev/mirror/fbsds1d on /var (ufs, local)
/dev/mirror/fbsds1e on /usr (ufs, local, soft-updates)
/dev/mirror/fbsds1f on /usr/local (ufs, local)
/dev/mirror/fbsds3a on /usr/home (ufs, local)
/dev/mirror/fbsds3d on /usr/ports (ufs, local, soft-updates)
/dev/ufs/stripework on /buildwork (ufs, local, soft-updates)

mirror/fbsds1 COMPLETE ada0s1 (ACTIVE)
mirror/fbsds3a COMPLETE ada0s3a (ACTIVE)
mirror/fbsds3d COMPLETE ada0s3d (ACTIVE)
ufs/stripework N/A stripe/5x15g1
stripe/5x15g1 UP da0p5
da2p5
da3p5
da5p5
da6p5

Note that the mirrors listed above have only one device in each. At one
point a few weeks ago, there were two devices in each for a bit under
eight hours until the brand-new second drive failed. Unfortunately, it
turns out that the manufacturer does not provide any support for its
internal hard drive products, other than replacement under warranty via
a web page that requires an unsecured web browser to apply for the
replacement. Grrr. Until I get that straightened out and have a second
drive, all of the mirrors listed above on the boot drive are unreplicated.

>
> Now by default pkg creates back up of the db daily to allow recovery, there was
> an issue before pkg 1.5 which requires a manual intervention and fixed after pkg
> 1.5 so it is easier to recover.

One serious problem for recovery is that I don't know when the
corruption actually occurred. All I can tell you is that it must have
happened no earlier than the previous update to lang/gcc (lang/gcc48).
The error messages first appeared with the update to gcc-4.8.4_3.

>
> sqlite is a pretty solid db I would like to understand how you ended up with a
> corrupted db if not what I described above.

So would I because I would seriously like it not to happen again.
Long ago, I used to use portupgrade, but I eventually switched to
portmaster for three reasons: speed (ruby is awfully slow), dougb@
massively reworked portmaster and made it work much more correctly
than portupgrade, and portupgrade's frustrating habit of always deciding
that the package data base had to be rebuilt from nothing, which always
took a long time and failed as often as it succeeded. Because portmaster
now has to use a data base instead of direct evidence, it seems that that
fourth complaint has partly returned to haunt us.
sqlite3-3.8.9_1 is what is installed on my system. I have no idea
which version is incorporated into pkg or pkg-static.

[Chris H]

On Wed, 13 May 2015 16:52:37 -0500 Scott Bennett <ben...@sdf.org> wrote

Portmaster(8) has it's own record keeping system (to a degree). The
information it *knows* about regarding *what's* installed, can
allow you to ask it to produce output that will aid in the
*re*creation of a failed DB. Perhaps not perfectly, but often, good
enough. As to my answer above; I "whined" that it seemed that
(at that time) that having a "single point of failure" (sqlite3), was
not good practice for a base/required part of the system. As there
is no (reasonable) recourse in the event of failure. I still stand
by that. I'm not keen on the idea that the average user *must* learn
[yet another] SQL, in order to effectively maintain their system.
If it were up to me, it'd all be simple ASCII based text. Sure, the
DB is text, but not as easily recovered as other text based record
keeping applications provide. In the end, I'm *extremely* grateful
for all the work Bapt, and Co have put into pkg(8). It has become
[seemingly] *quite* stable. But I think it could do with an option
to [at least] create a DB backup *prior* to commencing with any
operation that will affect the DB. See; "safety net". :)

--Chris

--