Date: Sat, 16 May 2015 02:58:14 -0500 From: Scott Bennett <bennett@sdf.org> To: "bapt@freebsd.org andrew clarke" <mail@ozzmosis.com> Cc: freebsd-ports@freebsd.org Subject: [SOLVED] Re: damage to pkg's sqlite data base Message-ID: <201505160758.t4G7wE1m001028@sdf.org> In-Reply-To: <20150516065927.GM11201@ivaldir.etoilebsd.net> References: <201505120617.t4C6HkA3019096@sdf.org> <20150513075526.GA18834@ozzmosis.com> <201505160452.t4G4qgDi009246@sdf.org> <20150516065927.GM11201@ivaldir.etoilebsd.net>
next in thread | previous in thread | raw e-mail | index | archive | help
Baptiste Daroussin <bapt@freebsd.org> wrote:
> On Fri, May 15, 2015 at 11:52:42PM -0500, Scott Bennett wrote:
> > 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!
> >
> 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 <mail@ozzmosis.com>
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 *
**********************************************************************
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?201505160758.t4G7wE1m001028>