From nobody Tue Aug 30 20:08:43 2022 X-Original-To: ports@mlmmj.nyi.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mlmmj.nyi.freebsd.org (Postfix) with ESMTP id 4MHJKf38H9z4bDmB for ; Tue, 30 Aug 2022 20:08:54 +0000 (UTC) (envelope-from vince@unsane.co.uk) Received: from unsane.co.uk (tunnel7249-pt.tunnel.tserv5.lon1.ipv6.he.net [IPv6:2001:470:1f08:110::2]) by mx1.freebsd.org (Postfix) with ESMTP id 4MHJKb5Fz1z3Txd for ; Tue, 30 Aug 2022 20:08:51 +0000 (UTC) (envelope-from vince@unsane.co.uk) Received: from [192.168.1.16] (vhoffman.plus.com [81.174.148.213]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by unsane.co.uk (Postfix) with ESMTPSA id 7C4CF30061 for ; Tue, 30 Aug 2022 21:08:44 +0100 (BST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=unsane.co.uk; s=251017; t=1661890124; bh=OC6urkNiEW285h0GJSZud/Bjza+J8aNGgghZmk/BxIM=; h=Date:Subject:To:References:From:In-Reply-To; b=dYP9vmwHXbkiI4Tvav7Q6s9p02RMHn6KG7ihwSrAZmZdY19SGaywQQviMSUqKeoim fq9IldAyG8Txf6jOxSyYHFxMncuRKHB/Hhn1KqU7i5v0K5b5jf6WYEd2LvvpqzXixA VqjCjP6JKKNeFIaOHLgKnyPSc5YTebChKxixsoHk= Content-Type: multipart/alternative; boundary="------------PckQvrnmewDHb0l0VAEp4Zk0" Message-ID: <10ec58a0-7c48-a27b-1775-883657f7f812@unsane.co.uk> Date: Tue, 30 Aug 2022 21:08:43 +0100 List-Id: Porting software to FreeBSD List-Archive: https://lists.freebsd.org/archives/freebsd-ports List-Help: List-Post: List-Subscribe: List-Unsubscribe: Sender: owner-freebsd-ports@freebsd.org X-BeenThere: freebsd-ports@freebsd.org MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Thunderbird/102.2.0 Subject: Re: Recover from a corrupt pkg DB To: ports@freebsd.org References: <20220830172613.bl4q3ynadyktstgw@shelly.nomadlogic.org> <0BDF281F-B485-4EE6-B0A5-698F5B134C99@gushi.org> From: Vincent Hoffman In-Reply-To: <0BDF281F-B485-4EE6-B0A5-698F5B134C99@gushi.org> X-Rspamd-Queue-Id: 4MHJKb5Fz1z3Txd X-Spamd-Bar: / Authentication-Results: mx1.freebsd.org; dkim=pass header.d=unsane.co.uk header.s=251017 header.b=dYP9vmwH; dmarc=pass (policy=none) header.from=unsane.co.uk; spf=fail (mx1.freebsd.org: domain of vince@unsane.co.uk does not designate 2001:470:1f08:110::2 as permitted sender) smtp.mailfrom=vince@unsane.co.uk X-Spamd-Result: default: False [0.80 / 15.00]; VIOLATED_DIRECT_SPF(3.50)[]; R_SPF_FAIL(1.00)[-all]; NEURAL_HAM_LONG(-1.00)[-1.000]; NEURAL_HAM_MEDIUM(-1.00)[-1.000]; NEURAL_HAM_SHORT(-1.00)[-1.000]; DMARC_POLICY_ALLOW_WITH_FAILURES(-0.50)[]; R_DKIM_ALLOW(-0.20)[unsane.co.uk:s=251017]; MIME_GOOD(-0.10)[multipart/alternative,text/plain]; RCVD_NO_TLS_LAST(0.10)[]; MLMMJ_DEST(0.00)[ports@freebsd.org]; MIME_TRACE(0.00)[0:+,1:+,2:~]; FROM_EQ_ENVFROM(0.00)[]; ASN(0.00)[asn:6939, ipnet:2001:470::/32, country:US]; DMARC_POLICY_ALLOW(0.00)[unsane.co.uk,none]; RCVD_VIA_SMTP_AUTH(0.00)[]; RCVD_COUNT_TWO(0.00)[2]; FROM_HAS_DN(0.00)[]; ARC_NA(0.00)[]; DKIM_TRACE(0.00)[unsane.co.uk:+]; TO_MATCH_ENVRCPT_ALL(0.00)[]; TO_DN_NONE(0.00)[]; PREVIOUSLY_DELIVERED(0.00)[ports@freebsd.org]; RCPT_COUNT_ONE(0.00)[1]; MID_RHS_MATCH_FROM(0.00)[] X-ThisMailContainsUnwantedMimeParts: N This is a multi-part message in MIME format. --------------PckQvrnmewDHb0l0VAEp4Zk0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 30/08/2022 18:45, Dan Mahoney (Ports) wrote: > >> On Aug 30, 2022, at 10:26 AM, Pete Wright wrote: >> >> On Tue, Aug 30, 2022 at 09:36:52AM -0700, Kevin Oberman wrote: >>> I had a crash while building ffmpeg on a new system. The build had not >>> begun, so I don't think ffmpeg is really an issue. portmaster(8) was >>> walking the dependency tree when the system panicked and, after a reboot >>> and full fsck, I get the error: >>> pkg: sqlite error while executing PRAGMA user_version; in file >>> pkgdb.c:2357: database disk image is malformed > So, I don’t know what tools are available in sqlite3 to dump a malformed db file and reimport it. I think that’s probably beyond the scope of this, but as I’ve in the past hit pkg issues, I’ve always wanted to know if there was an online description of the schema somewhere. (Obviously, you could peruse the schema on a second non-corrupt system, but that doesn’t describe how it’s used). https://wiki.freebsd.org/pkgng/CharterAndRoadMap#Database_Schema Not a description of it but an explaination of why its not really available online, that said https://github.com/freebsd/pkg/blob/master/libpkg/pkgdb.c contains it as part of pkgdb_init Vince > >>> Is there any way to rebuild the DB or, if not, how do I remove it and start >>> from scratch? > I mean, this is a *very* brute force approach, but if you know precisely what packages you had installed, you can always just reinstall pkg and pkg install -f those packages. > >> I ran into a similar issue a while back, I discovered I was able to >> recover by using one of the automated backups generated at: >> /var/backups/pkg.sql.xz* >> >> I can't remember the details, but I think it involved replacing >> repo-FreeBSD.sqlite in /var/db/pkg/. > I’ve dealt with this on another OS that did things far, far worse (RPM, in the days before Yum). It always stymied me that there was no online tool that could look at all the files you had in /usr/local/*, look at checksums of historical packages, and glean a “hey, it looks like these are the packages you had installed, but *these files* are the ones that don’t match any checksum on record.” > > -Dan > > > > --------------PckQvrnmewDHb0l0VAEp4Zk0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 30/08/2022 18:45, Dan Mahoney (Ports) wrote:


On Aug 30, 2022, at 10:26 AM, Pete Wright <pete@nomadlogic.org> wrote:

On Tue, Aug 30, 2022 at 09:36:52AM -0700, Kevin Oberman wrote:

I had a crash while building ffmpeg on a new system. The build had not
begun, so I don't think ffmpeg is really an issue. portmaster(8) was
walking the dependency tree when the system panicked and, after a reboot
and full fsck, I get the error:
pkg: sqlite error while executing PRAGMA user_version; in file
pkgdb.c:2357: database disk image is malformed

So, I don’t know what tools are available in sqlite3 to dump a malformed db file and reimport it.  I think that’s probably beyond the scope of this, but as I’ve in the past hit pkg issues, I’ve always wanted to know if there was an online description of the schema somewhere.  (Obviously, you could peruse the schema on a second non-corrupt system, but that doesn’t describe how it’s used).

https://wiki.freebsd.org/pkgng/CharterAndRoadMap#Database_Schema

Not a description of it but an explaination of why its not really available online, that said

https://github.com/freebsd/pkg/blob/master/libpkg/pkgdb.c

contains it as part of pkgdb_init

Vince 



Is there any way to rebuild the DB or, if not, how do I remove it and start
from scratch?

I mean, this is a *very* brute force approach, but if you know precisely what packages you had installed, you can always just reinstall pkg and pkg install -f those packages.


I ran into a similar issue a while back, I discovered I was able to
recover by using one of the automated backups generated at:
/var/backups/pkg.sql.xz*

I can't remember the details, but I think it involved replacing
repo-FreeBSD.sqlite in /var/db/pkg/.

I’ve dealt with this on another OS that did things far, far worse (RPM, in the days before Yum).  It always stymied me that there was no online tool that could look at all the files you had in /usr/local/*, look at checksums of historical packages, and glean a “hey, it looks like these are the packages you had installed, but *these files* are the ones that don’t match any checksum on record.”

-Dan
--------------PckQvrnmewDHb0l0VAEp4Zk0--