damage to pkg's sqlite data base
Baptiste Daroussin
bapt at freebsd.org
Wed May 13 22:12:13 UTC 2015
On Wed, May 13, 2015 at 04:52:37PM -0500, Scott Bennett wrote:
> 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).
>
> Baptiste Daroussin <bapt at freebsd.org> wrote:
> > 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.
>
> 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 at 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.
>
pkg incorporates a sqlite3 3.8.9 version but that is not a problem as all is
compatible.
if your date were corrupted during the last backups then the backups will be
empty.
Try
xzcat /var/backups/pkg.sql.xz | head -1
if the output is:
pragma user_version= 31;
then via command line:
unxz /var/backup/pkg.sql.xz | sqlite3 /var/db/local.sqlite
If not then
unxz /var/backup/pkg.sql.xz | sqlite3 /var/db/local.sqlite
sqlite3 /var/db/local.sqlite "pragma user_version= 31;"
That should just work
pkg info should now show you your packages.
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/20150514/b9e13a29/attachment.sig>
More information about the freebsd-ports
mailing list