damage to pkg's sqlite data base

Wed May 13 13:47:43 UTC 2015

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.
> 
> 
>                                   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         *
> **********************************************************************
> _______________________________________________
> freebsd-ports at freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-ports
> To unsubscribe, send any mail to "freebsd-ports-unsubscribe at freebsd.org"


--Chris

--