damage to pkg's sqlite data base

Wed May 13 14:50:11 UTC 2015

On Wed, May 13, 2015 at 06:48:12AM -0700, Chris H wrote:
> On Wed, 13 May 2015 02:20:55 -0500 Scott Bennett <bennett at sdf.org> wrote
> 
> > "Chris H" <bsd-lists at bsdforge.com> wrote:
> > > On Wed, 13 May 2015 00:12:51 -0500 Scott Bennett <bennett at sdf.org> wrote
> > > > "Chris H" <bsd-lists at bsdforge.com> wrote:
> > > > > On Tue, 12 May 2015 01:17:46 -0500 Scott Bennett <bennett at 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?
> > 
> > > > > > 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. :)
> > > > 
> > > >      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?
> > > 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.
> > 
> >      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.
> > 
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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 181 bytes
Desc: not available
URL: <http://lists.freebsd.org/pipermail/freebsd-ports/attachments/20150513/c3d576a5/attachment.sig>