From owner-freebsd-ports@FreeBSD.ORG Sat May 16 04:53:06 2015 Return-Path: Delivered-To: freebsd-ports@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [8.8.178.115]) (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits)) (No client certificate requested) by hub.freebsd.org (Postfix) with ESMTPS id 6F0424B7; Sat, 16 May 2015 04:53:06 +0000 (UTC) Received: from sdf.lonestar.org (mx.sdf.org [192.94.73.24]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "mx.sdf.org", Issuer "SDF.ORG" (not verified)) by mx1.freebsd.org (Postfix) with ESMTPS id 8615314FD; Sat, 16 May 2015 04:53:05 +0000 (UTC) Received: from sdf.org (IDENT:bennett@otaku.freeshell.org [192.94.73.9]) by sdf.lonestar.org (8.14.8/8.14.5) with ESMTP id t4G4qg7b020308 (using TLSv1/SSLv3 with cipher DHE-RSA-AES256-GCM-SHA384 (256 bits) verified NO); Sat, 16 May 2015 04:52:42 GMT Received: (from bennett@localhost) by sdf.org (8.14.8/8.12.8/Submit) id t4G4qgDi009246; Fri, 15 May 2015 23:52:42 -0500 (CDT) From: Scott Bennett Message-Id: <201505160452.t4G4qgDi009246@sdf.org> Date: Fri, 15 May 2015 23:52:42 -0500 To: mail@ozzmosis.com Subject: Re: damage to pkg's sqlite data base Cc: freebsd-ports@freebsd.org, bapt@freebsd.org References: <201505120617.t4C6HkA3019096@sdf.org> <20150513075526.GA18834@ozzmosis.com> In-Reply-To: <20150513075526.GA18834@ozzmosis.com> User-Agent: Heirloom mailx 12.5 6/20/10 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-BeenThere: freebsd-ports@freebsd.org X-Mailman-Version: 2.1.20 Precedence: list List-Id: Porting software to FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 16 May 2015 04:53:06 -0000 andrew clarke wrote: > On Tue 2015-05-12 01:17:46 UTC-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 > > "database disk image is malformed" is an error from SQLite, the > underlying database library that pkg uses, not pkg itself. > > If you can confidently rule-out hardware or filesystem error then > presumably there is a glitch in SQLite that causes it to corrupt the > database it's writing to. It shouldn't happen, and is evidently very > rare judging from the lack of FreeBSD PRs about it. > > SQLite is quite popular and is used by Mozilla Firefox & Google Chrome > internally. > > It's possible pkg did something to trigger a bug in SQLite, so it may > be worthwhile uploading your local.sqlite to a web site somewhere for > one of the pkg developers to investigate, and file a PR with a link to > the file. > > A bit of Googling indicates a fix may be possible, along the lines of: > > $ sqlite3 /var/db/pkg/local.sqlite > SQLite version 3.8.10.1 2015-05-09 12:14:55 > Enter ".help" for usage hints. > sqlite> pragma integrity_check; > ok > > [sqlite may give an error here, but you can hopefully keep going...] > > sqlite> .mode insert > sqlite> .output local.sqlite.dump > sqlite> .dump > sqlite> .quit > > $ ls -l local.sqlite.dump > -rw-r--r-- 1 ozzmosis ozzmosis 10113463 2015-05-13 17:24:46 local.sqlite.dump > > Note that the database dump is simply a text file: > > $ file local.sqlite.dump > local.sqlite.dump: ASCII text > > We can then recreate the database from the dump we just made: > > $ sqlite3 local.sqlite.new > SQLite version 3.8.10.1 2015-05-09 12:14:55 > Enter ".help" for usage hints. > sqlite> .read local.sqlite.dump > sqlite> .quit > > Now we can use our newly created database, which should be error-free: > > $ sudo cp /var/db/pkg/local.sqlite /var/db/pkg/local.sqlite.backup > $ sudo mv local.sqlite.new /var/db/pkg/local.sqlite > > I don't guarantee any of the above will work. It will depend on how > much the database is corrupted etc. > > You will also need databases/sqlite3 installed, which unfortunately As noted before, I have that installed on my system already. > isn't provided in the FreeBSD base system. This could be a problem if > pkg refuses to install anything. In that case I would either run the > above sqlite3 commands on another machine (or a jail?) and sort it out > there, or run the sqlite3 binary from the > /usr/ports/databasess/sqlite3 directory without installing it, or if > that's not possible, make a backup of local.sqlite, delete > local.sqlite, install sqlite3 from ports (or pkg install), then work > on fixing the corrupt database. > > Obviously another option is to simply declare pkg bankruptcy. Get a > list of all your installed packages (with "pkg info -ao > pkglist.txt"), > delete the corrupt local.sqlite then reinstall your packages. > Okay. Here's what happened. After recreating and reloading the data base, I reran the "pragma integrity_check;". See the results below. Note that "lsl" is aliased to "/bin/ls -FGWblg" in the output shown here. To summarize, the pre-dump errors about missing rows are not present in the post-reload "pragma integrity_check;" messages. However, an attempt to install lang/gcc still fails with messages complaining about a duplicate table. Script started on Fri May 15 23:15:51 2015 hellas# sqlite3 /var/db/pkg/local.sqlite SQLite version 3.8.9 2015-04-08 12:16:33 Enter ".help" for usage hints. sqlite> pragma integrity_check; row 108317 missing from index sqlite_autoindex_files_1 row 218482 missing from index sqlite_autoindex_files_1 row 300709 missing from index sqlite_autoindex_files_1 row 300710 missing from index sqlite_autoindex_files_1 wrong # of entries in index sqlite_autoindex_files_1 sqlite> .mode insert sqlite> .output local.sqlite.dump sqlite> .dump sqlite> .quit hellas# dirs /var/db/pkg hellas# file local.sqlite.dump local.sqlite.dump: ASCII text, with very long lines hellas# lsl local.sqlite.dump -rw-r--r-- 1 root wheel 71218571 May 15 23:17 local.sqlite.dump hellas# tail local.sqlite.dump CREATE TRIGGER scripts_delete INSTEAD OF DELETE ON scripts FOR EACH ROW BEGIN DELETE FROM pkg_script WHERE package_id = old.package_id AND type = old.type; DELETE FROM script WHERE script_id NOT IN (SELECT DISTINCT script_id FROM pkg_script);END; CREATE VIEW options AS SELECT package_id, option, value FROM pkg_option JOIN option USING(option_id); CREATE TRIGGER options_update INSTEAD OF UPDATE ON options FOR EACH ROW BEGIN UPDATE pkg_option SET value = new.value WHERE package_id = old.package_id AND option_id = ( SELECT option_id FROM option WHERE option = old.option );END; CREATE TRIGGER options_insert INSTEAD OF INSERT ON options FOR EACH ROW BEGIN INSERT OR IGNORE INTO option(option) VALUES(new.option);INSERT INTO pkg_option(package_id, option_id, value) VALUES (new.package_id, (SELECT option_id FROM option WHERE option = new.option), new.value);END; CREATE TRIGGER options_delete INSTEAD OF DELETE ON options FOR EACH ROW BEGIN DELETE FROM pkg_option WHERE package_id = old.package_id AND option_id = ( SELECT option_id FROM option WHERE option = old.option );DELETE FROM option WHERE option_id NOT IN ( SELECT DISTINCT option_id FROM pkg_option );END; CREATE UNIQUE INDEX packages_unique ON packages(name); CREATE UNIQUE INDEX deps_unique ON deps(name, version, package_id); CREATE INDEX pkg_digest_id ON packages(origin, manifestdigest); PRAGMA writable_schema=OFF; COMMIT; hellas# lsl local.sql* -rw-r--r-- 1 root wheel 107429888 May 12 23:46 local.sqlite -rw-r--r-- 1 root wheel 107429888 May 1 23:26 local.sqlite.bad -rw-r--r-- 1 root wheel 107429888 May 11 17:40 local.sqlite.bad2 -rw-r--r-- 1 root wheel 71218571 May 15 23:17 local.sqlite.dump hellas# mv local.sqlite.{,.bad3} hellas# sqlite3 local.sqlite.new SQLite version 3.8.9 2015-04-08 12:16:33 Enter ".help" for usage hints. sqlite> .read local.sqlite.dump sqlite> .quit hellas# file local.sqlite.new local.sqlite.new: SQLite 3.x database hellas# mv local.sqlite{.new,} hellas# sqlite3 /var/db/pkg/local.sqlite SQLite version 3.8.9 2015-04-08 12:16:33 Enter ".help" for usage hints. sqlite> pragma integrity_check; ok sqlite> .quit hellas# dirs /var/db/pkg hellas# pushd /usr/ports/lang/gcc /usr/ports/lang/gcc /var/db/pkg hellas# make install ===> Installing for gcc-4.8.4_3 ===> gcc-4.8.4_3 depends on file: /usr/local/bin/as - found ===> gcc-4.8.4_3 depends on file: /usr/local/share/java/ecj-4.5.jar - found ===> gcc-4.8.4_3 depends on executable: indexinfo - found ===> gcc-4.8.4_3 depends on shared library: libgmp.so - found (/usr/local/lib/libgmp.so) ===> gcc-4.8.4_3 depends on shared library: libmpfr.so - found (/usr/local/lib/libmpfr.so) ===> gcc-4.8.4_3 depends on shared library: libmpc.so - found (/usr/local/lib/libmpc.so) ===> Checking if gcc already installed pkg-static: sqlite error while executing CREATE TABLE licenses (id INTEGER PRIMARY KEY, license TEXT NOT NULL UNIQUE );CREATE TABLE pkg_licenses_assoc (package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE ON UPDATE CASCADE, license_id INTEGER REFERENCES licenses(id) ON DELETE RESTRICT ON UPDATE RESTRICT, PRIMARY KEY (package_id, license_id));CREATE VIEW pkg_licenses AS SELECT origin, license FROM packages INNER JOIN pkg_licenses_assoc ON packages.id = pkg_licenses_assoc.package_id INNER JOIN licenses ON pkg_licenses_assoc.license_id = licenses.id;CREATE TRIGGER license_insert INSTEAD OF INSERT ON pkg_licenses FOR EACH ROW BEGIN INSERT OR IGNORE INTO licenses(license) values (NEW.license);INSERT INTO pkg_licenses_assoc(package_id, license_id) VALUES ((SELECT id FROM packages where origin = NEW.origin), (SELECT id FROM categories WHERE name = NEW.name));END; in file pkgdb.c:2333: table licenses already exists *** Error code 74 Stop. make: stopped in /usr/ports/lang/gcc hellas# exit exit Script done on Fri May 15 23:26:20 2015 Any ideas on how to correct this new, duplicate table error situation? Thanks again for all the efforts to help! 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 * **********************************************************************