Skip site navigation (1)Skip section navigation (2)
Date:      Fri, 15 May 2015 23:52:42 -0500
From:      Scott Bennett <bennett@sdf.org>
To:        mail@ozzmosis.com
Cc:        freebsd-ports@freebsd.org, bapt@freebsd.org
Subject:   Re: damage to pkg's sqlite data base
Message-ID:  <201505160452.t4G4qgDi009246@sdf.org>
In-Reply-To: <20150513075526.GA18834@ozzmosis.com>
References:  <201505120617.t4C6HkA3019096@sdf.org> <20150513075526.GA18834@ozzmosis.com>

next in thread | previous in thread | raw e-mail | index | archive | help
andrew clarke <mail@ozzmosis.com> 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         *
**********************************************************************



Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?201505160452.t4G4qgDi009246>