Date: Wed, 13 May 2015 02:20:55 -0500 From: Scott Bennett <bennett@sdf.org> To: freebsd-ports@freebsd.org, bsd-lists@bsdforge.com Cc: bapt@freebsd.org Subject: Re: damage to pkg's sqlite data base Message-ID: <201505130720.t4D7KtMd016553@sdf.org> In-Reply-To: <5f1749d3e664fbb487aa98f160e6cf92@ultimatedns.net> References: <201505120617.t4C6HkA3019096@sdf.org> <0dcbdcb9fdfdebc53a2563b11494f61b@ultimatedns.net> <201505130512.t4D5Cpqa011080@sdf.org> <5f1749d3e664fbb487aa98f160e6cf92@ultimatedns.net>
next in thread | previous in thread | raw e-mail | index | archive | help
"Chris H" <bsd-lists@bsdforge.com> wrote:
> On Wed, 13 May 2015 00:12:51 -0500 Scott Bennett <bennett@sdf.org> wrote
> > "Chris H" <bsd-lists@bsdforge.com> wrote:
> > > On Tue, 12 May 2015 01:17:46 -0500 Scott Bennett <bennett@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? 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.
Bapt, oh, Bapt, whatever were you folks thinking? Did you really
leave us no practical recovery method?
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 *
**********************************************************************
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?201505130720.t4D7KtMd016553>