maintainer-feedback requested: [Bug 262337] ports-mgmt/pkg restore fails when created by periodic in /var/backups

From: <bugzilla-noreply_at_freebsd.org>
Date: Fri, 04 Mar 2022 10:44:33 UTC
Bugzilla Automation <bugzilla@FreeBSD.org> has asked freebsd-pkg (Nobody)
<pkg@FreeBSD.org> for maintainer-feedback:
Bug 262337: ports-mgmt/pkg restore fails when created by periodic in
/var/backups
https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=262337



--- Description ---
Running 12.3-Stable and pkg 1.17.5_1.
When Restoring the pkg.sql from /var/backups (handled by
/usr/local/etc/periodic/daily/411.pkg-backup) Restore fails. When backing up
via pkg backup -d and restoring this there is no error. Tried this on 3
machines, same behaviour.

=== root@nbsdrosen (pts/8) /var/db/pkg 11(2) -> pkg -ddddd backup -r pkg.sql 
DBG(1)[5616]> pkg initialized
DBG(4)[5616]> Pkgdb: running 'PRAGMA user_version;'
DBG(4)[5616]> Pkgdb: executing 'PRAGMA foreign_keys = ON;'
DBG(4)[5616]> Pkgdb: executing 'PRAGMA mmap_size=268435456;'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO mtree(content)
VALUES(?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR REPLACE INTO packages(
origin, name, version, comment, desc, message, arch, maintainer, www, prefix,
flatsize, automatic, licenselogic, mtree_id, time, manifestdigest, dep_formula,
vital)VALUES( ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, (SELECT
id FROM mtree WHERE content = ?14), NOW(), ?15, ?16, ?17 )'
DBG(4)[5616]> Pkgdb: preparing statement 'UPDATE deps SET origin=?1, version=?2
WHERE name=?3;'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO deps (origin, name,
version, package_id) VALUES (?1, ?2, ?3, ?4)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO files (path, sha256,
package_id) VALUES (?1, ?2, ?3)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR REPLACE INTO files (path,
sha256, package_id) VALUES (?1, ?2, ?3)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO
directories(path) VALUES(?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO
pkg_directories(package_id, directory_id, try) VALUES (?1, (SELECT id FROM
directories WHERE path = ?2), ?3)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO
categories(name) VALUES(?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO
pkg_categories(package_id, category_id) VALUES (?1, (SELECT id FROM categories
WHERE name = ?2))'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO licenses(name)
VALUES(?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_licenses(package_id,
license_id) VALUES (?1, (SELECT id FROM licenses WHERE name = ?2))'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO users(name)
VALUES(?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_users(package_id,
user_id) VALUES (?1, (SELECT id FROM users WHERE name = ?2))'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO groups(name)
VALUES(?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_groups(package_id,
group_id) VALUES (?1, (SELECT id FROM groups WHERE name = ?2))'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO script(script)
VALUES (?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_script(script_id,
package_id, type) VALUES ((SELECT script_id FROM script WHERE script = ?1), ?2,
?3)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO option (option)
VALUES (?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_option(package_id,
option_id, value) VALUES (?1, (SELECT option_id FROM option WHERE option =
?2),?3)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO shlibs(name)
VALUES(?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO
pkg_shlibs_required(package_id, shlib_id) VALUES (?1, (SELECT id FROM shlibs
WHERE name = ?2))'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO
pkg_shlibs_provided(package_id, shlib_id) VALUES (?1, (SELECT id FROM shlibs
WHERE name = ?2))'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO
annotation(annotation) VALUES (?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR ROLLBACK INTO
pkg_annotation(package_id, tag_id, value_id) VALUES (?1, (SELECT annotation_id
FROM annotation WHERE annotation = ?2), (SELECT annotation_id FROM annotation
WHERE annotation = ?3))'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO
pkg_annotation(package_id, tag_id, value_id) VALUES ( (SELECT id FROM packages
WHERE name = ?1 ), (SELECT annotation_id FROM annotation WHERE annotation =
?2), (SELECT annotation_id FROM annotation WHERE annotation = ?3))'
DBG(4)[5616]> Pkgdb: preparing statement 'DELETE FROM pkg_annotation WHERE
package_id IN (SELECT id FROM packages WHERE name = ?1) AND tag_id IN (SELECT
annotation_id FROM annotation WHERE annotation = ?2)'
DBG(4)[5616]> Pkgdb: preparing statement 'DELETE FROM annotation WHERE
annotation_id NOT IN (SELECT tag_id FROM pkg_annotation) AND annotation_id NOT
IN (SELECT value_id FROM pkg_annotation)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_conflicts(package_id,
conflict_id) VALUES (?1, (SELECT id FROM packages WHERE name = ?2))'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_provides(package_id,
provide_id) VALUES (?1, (SELECT id FROM provides WHERE provide = ?2))'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO
provides(provide) VALUES(?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'UPDATE packages SET manifestdigest=?1
WHERE id=?2;'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO config_files(path,
content, package_id) VALUES (?1, ?2, ?3);'
DBG(4)[5616]> Pkgdb: preparing statement 'UPDATE config_files SET content=?1
WHERE path=?2;'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_requires(package_id,
require_id) VALUES (?1, (SELECT id FROM requires WHERE require = ?2))'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO
requires(require) VALUES(?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO
lua_script(lua_script) VALUES (?1)'
DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO
pkg_lua_script(lua_script_id, package_id, type) VALUES ((SELECT lua_script_id
FROM lua_script WHERE lua_script = ?1), ?2, ?3)'
Restoring database:
Restoring: 100%
pkg: sqlite error while executing backup step in file backup.c:98: not an error
pkg: sqlite error -- (null)

Files created by pkg backup -d (pkg_backup.sql) and created by the periodic
(pkg.sql) differ:
-rw-r--r--   1 root  wheel  - 78270464	4 März 11:42 pkg_backup.sql
-rw-r--r--   1 root  wheel  - 51963917	4 März 03:04 pkg.sql