damage to pkg's sqlite data base

[Chris H]

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

--