From owner-freebsd-ports@FreeBSD.ORG Sat May 16 07:58:32 2015 Return-Path: Delivered-To: freebsd-ports@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits)) (No client certificate requested) by hub.freebsd.org (Postfix) with ESMTPS id 8ADCBDCB for ; Sat, 16 May 2015 07:58:32 +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 6EA161600 for ; Sat, 16 May 2015 07:58:31 +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 t4G7wElX027971 (using TLSv1/SSLv3 with cipher DHE-RSA-AES256-GCM-SHA384 (256 bits) verified NO); Sat, 16 May 2015 07:58:14 GMT Received: (from bennett@localhost) by sdf.org (8.14.8/8.12.8/Submit) id t4G7wE1m001028; Sat, 16 May 2015 02:58:14 -0500 (CDT) From: Scott Bennett Message-Id: <201505160758.t4G7wE1m001028@sdf.org> Date: Sat, 16 May 2015 02:58:14 -0500 To: "bapt@freebsd.org andrew clarke" Subject: [SOLVED] Re: damage to pkg's sqlite data base Cc: freebsd-ports@freebsd.org References: <201505120617.t4C6HkA3019096@sdf.org> <20150513075526.GA18834@ozzmosis.com> <201505160452.t4G4qgDi009246@sdf.org> <20150516065927.GM11201@ivaldir.etoilebsd.net> In-Reply-To: <20150516065927.GM11201@ivaldir.etoilebsd.net> 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 07:58:32 -0000 Baptiste Daroussin wrote: > On Fri, May 15, 2015 at 11:52:42PM -0500, Scott Bennett wrote: > > 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! > > > I gave you the step in my previous mail, after loading the good dump run Oops. Sorry, but I missed that bit. > pragma user_version="31"; > YES!! That worked, and gcc-4.8.4_3 is now installed. Now to see whether X11 will start up again. Thank you very much, Bapt, and also to Andrew Clarke for the rest of the procedure. I will file these messages away in case this ever plagues me again. Perhaps a "pkg fixdb" could be added that would do something like this? 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 * **********************************************************************