After upgrading perl I wanted to adjust the dependencies # pkg set -yo lang/perl5.14:lang/perl5.16 pkg: sqlite: columns package_id, origin are not unique (pkgdb.c:3424) I looked at the schema and it seemed that package_id was already part of a primary precluding the possibility of non uniqueness. So I did "echo .dump|sqlite3 local.sqlite|sqlite3 new.db" and "mv local.sqlite local.sqlite.bak && mv new.db local.sqlite" and attempted it again. Now I got this message: pkg: sqlite: table licenses already exists Fix: I suppose I could somehow re-create the db, but it would be good if this gets somehow fixed so that no one else gets bitten by this. Its probably also a good idea to offer the user a database repair when any kind of sqlite errors appear. If nothing else a simple dump/restore often helps. Schema checks can be added later. How-To-Repeat: I can only guess that at some point the database schema was changed and pkg doesn't know how to update the schema.
Responsible Changed From-To: freebsd-ports-bugs->bapt Over to maintainer.
What version of pkg are you using, I do suspect a very old version, is it the case? What pkg -v gives you?
State Changed From-To: open->closed No feedback from submitter
I have what appear to be identical issues running FreeBSD 9.2 (FreeBSD 9.2-RELEASE-p2 #5) using pkg version 1.2.7_3: There are two ways to easily reproduce this by manually dumping and restoring the local pkg database in /var/db/pkg/local.sqlite as follows: 1) Using the "pkg shell" command to dump the database and restore, and 2) using the sqlite3 CLI tool (from the sqlite3-3.8.4.3 port): NOTE: Please IGNORE the "pkg: sqlite: no such table: main.packages" error--that's included just to show that a zero-byte empty database exists. # cd /var/db/pkg # pkg info pkg pkg-1.2.7_3 Name : pkg Version : 1.2.7_3 Installed on : Thu Jun 19 15:54:17 MDT 2014 Origin : ports-mgmt/pkg Architecture : freebsd:9:x86:64 Prefix : /usr/local Maintainer : unknown WWW : http://wiki.freebsd.org/pkgng Comment : Package manager Shared Libs required: libpkg.so.1 Shared Libs provided: libpkg.so.1 Flat size : 7.05MiB Description : Package management tool WWW: http://wiki.freebsd.org/pkgng # ls -l local.sqlite -rw-r--r-- 1 root wheel 3752960 Jun 19 15:54 local.sqlite # cp local.sqlite local.sqlite.bak # echo .dump | pkg shell > local.sqlite.dump # cat /dev/null > local.sqlite # ls -l local.sqlite* -rw-r--r-- 1 root wheel 0 Jun 19 15:58 local.sqlite -rw-r--r-- 1 root wheel 3752960 Jun 19 15:57 local.sqlite.bak -rw-r--r-- 1 root wheel 2545522 Jun 19 15:57 local.sqlite.dump # pkg info pkg: sqlite: no such table: main.packages # cat local.sqlite.dump | pkg shell # pkg info pkg: sqlite: table licenses already exists # echo .dump | pkg shell > local.sqlite.dump_bad # diff local.sqlite.dump local.sqlite.dump_bad # ls -l local.sqlite* -rw-r--r-- 1 root wheel 3686400 Jun 19 16:00 local.sqlite -rw-r--r-- 1 root wheel 3752960 Jun 19 15:57 local.sqlite.bak -rw-r--r-- 1 root wheel 2545522 Jun 19 15:57 local.sqlite.dump -rw-r--r-- 1 root wheel 2545522 Jun 19 16:01 local.sqlite.dump_bad # cp local.sqlite.bak local.sqlite # pkg info sqlite3 sqlite3-3.8.4.3 Name : sqlite3 Version : 3.8.4.3 Installed on : Thu Jun 19 15:54:17 MDT 2014 Origin : databases/sqlite3 Architecture : freebsd:9:x86:64 Prefix : /usr/local Maintainer : unknown WWW : https://www.sqlite.org/ Comment : SQL database engine in a C library Options : DIRECT_READ : off EXTENSION : on FTS4 : on ICU : off MEMMAN : off METADATA : on RTREE : on RTREE_INT : off SECURE_DELETE : on SOUNDEX : off STAT3 : off STAT4 : off THREADS : on TS0 : off TS1 : on TS2 : off TS3 : off UNICODE61 : off UNLOCK_NOTIFY : on UPD_DEL_LIMIT : off URI : on URI_AUTHORITY : off Shared Libs required: libsqlite3.so.8 Shared Libs provided: libsqlite3.so.8 Flat size : 2.00MiB Description : SQLite is an SQL database engine in a C library. Programs that link the SQLite library can have SQL database access without running a separate RDBMS process. The distribution comes with a standalone command-line access program (sqlite) that can be used to administer an SQLite database and which serves as an example of how to use the SQLite library. WWW: https://www.sqlite.org/ # echo .dump | sqlite3 local.sqlite > local.sqlite.dump2 # rm local.sqlite # pkg info # ls -l local.sqlite ls: local.sqlite: No such file or directory # sqlite3 local.sqlite < local.sqlite.dump2 # pkg info pkg: sqlite: table licenses already exists # echo .dump | sqlite3 local.sqlite > local.sqlite.dump2_bad # diff local.sqlite.dump2 local.sqlite.dump2_bad # ls -l local.sqlite* -rw-r--r-- 1 root wheel 3686400 Jun 19 16:07 local.sqlite -rw-r--r-- 1 root wheel 3752960 Jun 19 15:57 local.sqlite.bak -rw-r--r-- 1 root wheel 2545522 Jun 19 15:57 local.sqlite.dump -rw-r--r-- 1 root wheel 2545522 Jun 19 16:07 local.sqlite.dump2 -rw-r--r-- 1 root wheel 2545522 Jun 19 16:08 local.sqlite.dump2_bad -rw-r--r-- 1 root wheel 2545522 Jun 19 16:01 local.sqlite.dump_bad # cp local.sqlite.bak local.sqlite # pkg info bash-4.3.11_2 The GNU Project's Bourne Again SHell ...output of the 74 installed ports clipped and shortened for brevity... tw_cli-9.5.4 3ware storage controllers management CLI # ls -l local.sqlite* -rw-r--r-- 1 root wheel 3752960 Jun 19 16:10 local.sqlite -rw-r--r-- 1 root wheel 3752960 Jun 19 15:57 local.sqlite.bak -rw-r--r-- 1 root wheel 2545522 Jun 19 15:57 local.sqlite.dump -rw-r--r-- 1 root wheel 2545522 Jun 19 16:07 local.sqlite.dump2 -rw-r--r-- 1 root wheel 2545522 Jun 19 16:08 local.sqlite.dump2_bad -rw-r--r-- 1 root wheel 2545522 Jun 19 16:01 local.sqlite.dump_bad # THIS TELLS ME that something ELSE is going on. There is some item of data that exists OUTSIDE of the SQLITE dumped data that is causing this issue. The dumps done via "pkg shell" and via "sqlite3" all result in identical dumps. And after restoration from the initial dumps of the working database, even though the "restored-but-not-working" database DOES NOT WORK, one can still dump that "restored-but-not-working" database and the dump is IDENTICAL to the dump of the working database. Is the "pkg" tool using some sort of magical sqlite3 database creation parameter that's resulting in some sort of metadata that exists OUTSIDE of the SQLITE dump? I need to be able to dump and restore the package SQLITE database safely without losing any usability. PLEASE FIX THIS!
*** Bug 191200 has been marked as a duplicate of this bug. ***
Reopening in case bapt wants to take a look.
this has been fixed in the mean time