pkg-update: locking issues on concurrent invokes

[fichtner]

Please bear with me :)

pkg-update may be called by other commands internally so having a bit of automation around the package database and checking for updates it can happen that pkg-update appears to run concurrently but its sqlite database locking is not safe so it starts to produce at least two types of spurious errors.

On the FreeBSD repo:

# pkg update -f &; pkg update -f
[1] 10995
Updating FreeBSD repository catalogue...
Updating FreeBSD repository catalogue...
Fetching meta.conf: 100%    163 B   0.2kB/s    00:01    
Fetching meta.conf: 100%    163 B   0.2kB/s    00:01    
Fetching packagesite.pkg: 100%    7 MiB   7.2MB/s    00:01    
Fetching packagesite.pkg: 100%    7 MiB   7.2MB/s    00:01    
Processing entries: 100%Processing entries:   0%
FreeBSD repository update completed. 34086 packages processed.
Processing entries: 100%
FreeBSD repository update completed. 34086 packages processed.
pkg: sqlite error while executing DROP TABLE repo_update; in file pkgdb.c:2333: malformed database schema (packages_origin) - invalid rootpage
All repositories are up to date.
All repositories are up to date.
[1]    Done                          pkg update -f

On the OPNsense repo (much smaller):

# pkg update -f & ; pkg update -f
[1] 22162
Updating OPNsense repository catalogue...
Updating OPNsense repository catalogue...
Fetching meta.conf: 100%    163 B   0.2kB/s    00:01    
    163 B   0.2kB/s    00:01    
Fetching packagesite.pkg: 100%  238 KiB 243.4kB/s    00:01    
Fetching packagesite.pkg: 100%  238 KiB 243.4kB/s    00:01    
pkg: sqlite error while executing CREATE TABLE packages (id INTEGER PRIMARY KEY,origin TEXT,name TEXT NOT NULL,version TEXT NOT NULL,comment TEXT NOT NULL,desc TEXT NOT NULL,osversion TEXT,arch TEXT NOT NULL,maintainer TEXT NOT NULL,www TEXT,prefix TEXT NOT NULL,pkgsize INTEGER NOT NULL,flatsize INTEGER NOT NULL,licenselogic INTEGER NOT NULL,cksum TEXT NOT NULL,path TEXT NOT NULL,pkg_format_version INTEGER,manifestdigest TEXT NULL,olddigest TEXT NULL,dep_formula TEXT NULL,vital INTEGER NOT NULL DEFAULT 0);CREATE TABLE deps (origin TEXT,name TEXT,version TEXT,package_id INTEGER REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,UNIQUE(package_id, name));CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE );CREATE TABLE pkg_categories (package_id INTEGER REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,category_id INTEGER REFERENCES categories(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, category_id));CREATE TABLE licenses (id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE);CREATE TABLE pkg_licenses (package_id INTEGER REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,license_id INTEGER REFERENCES licenses(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, license_id));CREATE TABLE option (option_id INTEGER PRIMARY KEY,option TEXT NOT NULL UNIQUE);CREATE TABLE option_desc (option_desc_id INTEGER PRIMARY KEY,option_desc TEXT NOT NULL UNIQUE);CREATE TABLE pkg_option (package_id INTEGER NOT NULL REFERENCES packages(id) ON DELETE CASCADE ON UPDATE CASCADE,option_id INTEGER NOT NULL REFERENCES option(option_id) ON DELETE RESTRICT ON UPDATE CASCADE,value TEXT NOT NULL,PRIMARY KEY(package_id, option_id));CREATE TABLE pkg_option_desc (package_id INTEGER NOT NULL REFERENCES packages(id) ON DELETE CASCADE ON UPDATE CASCADE,option_id INTEGER NOT NULL REFERENCES option(option_id) ON DELETE RESTRICT ON UPDATE CASCADE,option_desc_id INTEGER NOT NULL REFERENCES option_desc(option_desc_id) ON DELETE RESTRICT ON UPDATE CASCADE,PRIMARY KEY(package_id, option_id));CREATE TABLE pkg_option_default (package_id INTEGER NOT NULL REFERENCES packages(id) ON DELETE CASCADE ON UPDATE CASCADE,option_id INTEGER NOT NULL REFERENCES option(option_id) ON DELETE RESTRICT ON UPDATE CASCADE,default_value TEXT NOT NULL,PRIMARY KEY(package_id, option_id));CREATE TABLE shlibs (id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE );CREATE TABLE pkg_shlibs_required (package_id INTEGER NOT NULL REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,shlib_id INTEGER NOT NULL REFERENCES shlibs(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, shlib_id));CREATE TABLE pkg_shlibs_provided (package_id INTEGER NOT NULL REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,shlib_id INTEGER NOT NULL REFERENCES shlibs(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, shlib_id));CREATE TABLE annotation (annotation_id INTEGER PRIMARY KEY,annotation TEXT NOT NULL UNIQUE);CREATE TABLE pkg_annotation (package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE ON UPDATE RESTRICT,tag_id INTEGER NOT NULL REFERENCES annotation(annotation_id) ON DELETE CASCADE ON UPDATE RESTRICT,value_id INTEGER NOT NULL REFERENCES annotation(annotation_id) ON DELETE CASCADE ON UPDATE RESTRICT,UNIQUE (package_id, tag_id));CREATE TABLE pkg_conflicts (package_id INTEGER NOT NULL REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,conflict_id INTEGER NOT NULL,UNIQUE(package_id, conflict_id));CREATE TABLE provides(    id INTEGER PRIMARY KEY,    provide TEXT NOT NULL);CREATE TABLE pkg_provides (package_id INTEGER NOT NULL REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,provide_id INTEGER NOT NULL REFERENCES provides(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, provide_id));CREATE TABLE requires(    id INTEGER PRIMARY KEY,    require TEXT NOT NULL);CREATE TABLE pkg_requires (package_id INTEGER NOT NULL REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,require_id INTEGER NOT NULL REFERENCES requires(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, require_id));PRAGMA user_version=2014; in file pkgdb.c:2333: attempt to write a readonly database
Unable to create repository OPNsense
Unable to update repository OPNsense
Error updating repositories!
pkg: sqlite error while executing CREATE TABLE packages (id INTEGER PRIMARY KEY,origin TEXT,name TEXT NOT NULL,version TEXT NOT NULL,comment TEXT NOT NULL,desc TEXT NOT NULL,osversion TEXT,arch TEXT NOT NULL,maintainer TEXT NOT NULL,www TEXT,prefix TEXT NOT NULL,pkgsize INTEGER NOT NULL,flatsize INTEGER NOT NULL,licenselogic INTEGER NOT NULL,cksum TEXT NOT NULL,path TEXT NOT NULL,pkg_format_version INTEGER,manifestdigest TEXT NULL,olddigest TEXT NULL,dep_formula TEXT NULL,vital INTEGER NOT NULL DEFAULT 0);CREATE TABLE deps (origin TEXT,name TEXT,version TEXT,package_id INTEGER REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,UNIQUE(package_id, name));CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE );CREATE TABLE pkg_categories (package_id INTEGER REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,category_id INTEGER REFERENCES categories(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, category_id));CREATE TABLE licenses (id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE);CREATE TABLE pkg_licenses (package_id INTEGER REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,license_id INTEGER REFERENCES licenses(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, license_id));CREATE TABLE option (option_id INTEGER PRIMARY KEY,option TEXT NOT NULL UNIQUE);CREATE TABLE option_desc (option_desc_id INTEGER PRIMARY KEY,option_desc TEXT NOT NULL UNIQUE);CREATE TABLE pkg_option (package_id INTEGER NOT NULL REFERENCES packages(id) ON DELETE CASCADE ON UPDATE CASCADE,option_id INTEGER NOT NULL REFERENCES option(option_id) ON DELETE RESTRICT ON UPDATE CASCADE,value TEXT NOT NULL,PRIMARY KEY(package_id, option_id));CREATE TABLE pkg_option_desc (package_id INTEGER NOT NULL REFERENCES packages(id) ON DELETE CASCADE ON UPDATE CASCADE,option_id INTEGER NOT NULL REFERENCES option(option_id) ON DELETE RESTRICT ON UPDATE CASCADE,option_desc_id INTEGER NOT NULL REFERENCES option_desc(option_desc_id) ON DELETE RESTRICT ON UPDATE CASCADE,PRIMARY KEY(package_id, option_id));CREATE TABLE pkg_option_default (package_id INTEGER NOT NULL REFERENCES packages(id) ON DELETE CASCADE ON UPDATE CASCADE,option_id INTEGER NOT NULL REFERENCES option(option_id) ON DELETE RESTRICT ON UPDATE CASCADE,default_value TEXT NOT NULL,PRIMARY KEY(package_id, option_id));CREATE TABLE shlibs (id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE );CREATE TABLE pkg_shlibs_required (package_id INTEGER NOT NULL REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,shlib_id INTEGER NOT NULL REFERENCES shlibs(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, shlib_id));CREATE TABLE pkg_shlibs_provided (package_id INTEGER NOT NULL REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,shlib_id INTEGER NOT NULL REFERENCES shlibs(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, shlib_id));CREATE TABLE annotation (annotation_id INTEGER PRIMARY KEY,annotation TEXT NOT NULL UNIQUE);CREATE TABLE pkg_annotation (package_id INTEGER REFERENCES packages(id) ON DELETE CASCADE ON UPDATE RESTRICT,tag_id INTEGER NOT NULL REFERENCES annotation(annotation_id) ON DELETE CASCADE ON UPDATE RESTRICT,value_id INTEGER NOT NULL REFERENCES annotation(annotation_id) ON DELETE CASCADE ON UPDATE RESTRICT,UNIQUE (package_id, tag_id));CREATE TABLE pkg_conflicts (package_id INTEGER NOT NULL REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,conflict_id INTEGER NOT NULL,UNIQUE(package_id, conflict_id));CREATE TABLE provides(    id INTEGER PRIMARY KEY,    provide TEXT NOT NULL);CREATE TABLE pkg_provides (package_id INTEGER NOT NULL REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,provide_id INTEGER NOT NULL REFERENCES provides(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, provide_id));CREATE TABLE requires(    id INTEGER PRIMARY KEY,    require TEXT NOT NULL);CREATE TABLE pkg_requires (package_id INTEGER NOT NULL REFERENCES packages(id)  ON DELETE CASCADE ON UPDATE CASCADE,require_id INTEGER NOT NULL REFERENCES requires(id)  ON DELETE RESTRICT ON UPDATE RESTRICT,UNIQUE(package_id, require_id));PRAGMA user_version=2014; in file pkgdb.c:2333: disk I/O error
Unable to create repository OPNsense
Unable to update repository OPNsense
Error updating repositories!

pkg version is 1.19.2, but I can test on newer version (even though test is trivial). With pointer in the right direction I can also try and see if this can be fixed.

[fichtner]

Noticed 654c36f introduced an flock() and that consecutive ops->open and ops->create calls between the update processes are impossible to lock from the database end so preliminary creation of a /var/db/pkg/REPO.sqlite-lock file seems to do the trick: one instance fails, but the other updates and so consecutive calls (e.g. pkg-rquery) are fine and don't cause a cascade of more updates being attempted inline by other tools since the database is gone and in need of an update. POC PR tomorrow.

[fichtner]

The lock was working but still clobbering the database... surprisingly the nasty bit appears to be the recovery that messes with the other end which also recreated the database and this makes both of them fail eventually:

pkg/libpkg/repo/binary/update.c

Lines 565 to 569 in 507aaa8

	/* restore the previous db in case of failures */
	if (rc != EPKG_OK && rc != EPKG_UPTODATE) {
	unlink(name);
	rename(path, name);
	}