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>