From owner-freebsd-ports-bugs@FreeBSD.ORG Thu Jun 19 22:22:53 2014 Return-Path: Delivered-To: freebsd-ports-bugs@FreeBSD.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) (using TLSv1 with cipher ADH-AES256-SHA (256/256 bits)) (No client certificate requested) by hub.freebsd.org (Postfix) with ESMTPS id 880DE19D for ; Thu, 19 Jun 2014 22:22:53 +0000 (UTC) Received: from kenobi.freebsd.org (kenobi.freebsd.org [IPv6:2001:1900:2254:206a::16:76]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id 6E71C2171 for ; Thu, 19 Jun 2014 22:22:53 +0000 (UTC) Received: from bugs.freebsd.org ([127.0.1.118]) by kenobi.freebsd.org (8.14.8/8.14.8) with ESMTP id s5JMMrY7061933 for ; Thu, 19 Jun 2014 23:22:53 +0100 (BST) (envelope-from bugzilla-noreply@freebsd.org) From: bugzilla-noreply@freebsd.org To: freebsd-ports-bugs@FreeBSD.org Subject: [Bug 191200] New: Request re-open Bug 176296: ports-mgmt/pkg: sqlite: table licenses already exists Date: Thu, 19 Jun 2014 22:22:53 +0000 X-Bugzilla-Reason: AssignedTo X-Bugzilla-Type: new X-Bugzilla-Watch-Reason: None X-Bugzilla-Product: Ports Tree X-Bugzilla-Component: Individual Port(s) X-Bugzilla-Version: Latest X-Bugzilla-Keywords: X-Bugzilla-Severity: Affects Some People X-Bugzilla-Who: agifford@infowest.com X-Bugzilla-Status: Needs Triage X-Bugzilla-Priority: --- X-Bugzilla-Assigned-To: freebsd-ports-bugs@FreeBSD.org X-Bugzilla-Target-Milestone: --- X-Bugzilla-Flags: X-Bugzilla-Changed-Fields: bug_id short_desc product version rep_platform op_sys bug_status bug_severity priority component assigned_to reporter Message-ID: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 7bit X-Bugzilla-URL: https://bugs.freebsd.org/bugzilla/ Auto-Submitted: auto-generated MIME-Version: 1.0 X-BeenThere: freebsd-ports-bugs@freebsd.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: Ports bug reports List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 19 Jun 2014 22:22:53 -0000 https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=191200 Bug ID: 191200 Summary: Request re-open Bug 176296: ports-mgmt/pkg: sqlite: table licenses already exists Product: Ports Tree Version: Latest Hardware: Any OS: Any Status: Needs Triage Severity: Affects Some People Priority: --- Component: Individual Port(s) Assignee: freebsd-ports-bugs@FreeBSD.org Reporter: agifford@infowest.com This comment was appended to the "FIXED" (but it is NOT fixed) bug #176296. When one dumps the /var/db/pkg/local.sqlite package database and then attempts to restore the database from a dump, the "pkg: sqlite: table licenses already exists" bug rears its ugly head. Here was the comment I added to the original bug report: 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! -- You are receiving this mail because: You are the assignee for the bug.