Page MenuHome GnuPG
Create Task
Maniphest T7941

gpg: keydb_search_first failed: SQL error
Open, LowPublic
Actions

Description

gpg 2.5.14 fails on first key listing if keys were generated in previous version.

GnuPG 2.5.13

root@b02bef684bbe:~# gpgconf -V
* GnuPG 2.5.13 (b39a0298112de853cc7c0833ed1c366330a225ef)
GNU/Linux

* Libgcrypt 1.11.2 (737cc636)
version:1.11.2:10b02:1.56:13800:
cc:110400:gcc:11.4.0:
ciphers:arcfour:blowfish:cast5:des:aes:twofish:serpent:rfc2268:seed:camellia:idea:salsa20:gost28147:chacha20:sm4:aria:
pubkeys:dsa:elgamal:rsa:ecc:kyber:
digests:crc:gostr3411-94::md4:md5:rmd160:sha1:sha256:sha512:sha3:tiger:whirlpool:stribog:blake2:sm3:
rnd-mod:getentropy:
cpu-arch:x86:amd64:
mpi-asm:amd64/mpih-add1.S:amd64/mpih-sub1.S:amd64/mpih-mul1.S:amd64/mpih-mul2.S:amd64/mpih-mul3.S:amd64/mpih-lshift.S:amd64/mpih-rshift.S:
mpi-powm:fixed-window
hwflist:intel-cpu:intel-fast-shld:intel-bmi2:intel-ssse3:intel-sse4.1:intel-pclmul:intel-aesni:intel-rdrand:intel-avx:intel-avx2:intel-fast-vpgather:intel-rdtsc:
fips-mode:n:::
rng-type:standard:1:3030000:1:
compliance:::

* GpgRT 1.56 (b3b68bde07b02dd2b6d78356ac90baa652a309fa)

* nPth 1.8 (64905e765aad9de6054ef70a97fc30bd992ce999)

* Libassuan 3.0.2 (0f84595a4bc706d3afb969d59618244c7db3b59f)

* KSBA 1.6.7 (b14e68b)

* NTBTLS 0.3.2 (2c38007)

root@b02bef684bbe:~# 
root@b02bef684bbe:~# ps aux | grep -iE 'gpg|gnupg'
root        3712  0.6  0.1 152700  3856 ?        Ssl  06:11   0:01 gpg-agent --homedir /root/.gnupg --daemon
root        3787  0.0  0.0   3472  1884 pts/1    S+   06:14   0:00 grep --color=auto -iE gpg|gnupg
root@b02bef684bbe:~# gpg --list-secret-keys
[keyboxd]
---------
sec   ed448/0xD508AB8C71652DCE 2025-11-20 [SC]
      D508AB8C71652DCE1261504AF5BD63FC1CA362C37834310BF15833759A84DF4E
uid                   [ultimate] test01
ssb   ky1024_cv448/0xCDF1B9FE6D3E4C12 2025-11-20 [E]
      CDF1B9FE6D3E4C120F5DC81493E5EDEFE8CACE3F335561FE22388BC96852F633

root@b02bef684bbe:~#

GnuPG 2.5.14

root@b02bef684bbe:~# gpgconf -V
* GnuPG 2.5.14 (4d993c37d9b0b9262f859c23fea2e8da561f4639)
GNU/Linux

* Libgcrypt 1.11.2 (737cc636)
version:1.11.2:10b02:1.56:13800:
cc:110400:gcc:11.4.0:
ciphers:arcfour:blowfish:cast5:des:aes:twofish:serpent:rfc2268:seed:camellia:idea:salsa20:gost28147:chacha20:sm4:aria:
pubkeys:dsa:elgamal:rsa:ecc:kyber:
digests:crc:gostr3411-94::md4:md5:rmd160:sha1:sha256:sha512:sha3:tiger:whirlpool:stribog:blake2:sm3:
rnd-mod:getentropy:
cpu-arch:x86:amd64:
mpi-asm:amd64/mpih-add1.S:amd64/mpih-sub1.S:amd64/mpih-mul1.S:amd64/mpih-mul2.S:amd64/mpih-mul3.S:amd64/mpih-lshift.S:amd64/mpih-rshift.S:
mpi-powm:fixed-window
hwflist:intel-cpu:intel-fast-shld:intel-bmi2:intel-ssse3:intel-sse4.1:intel-pclmul:intel-aesni:intel-rdrand:intel-avx:intel-avx2:intel-fast-vpgather:intel-rdtsc:
fips-mode:n:::
rng-type:standard:1:3030000:1:
compliance:::

* GpgRT 1.56 (b3b68bde07b02dd2b6d78356ac90baa652a309fa)

* nPth 1.8 (64905e765aad9de6054ef70a97fc30bd992ce999)

* Libassuan 3.0.2 (0f84595a4bc706d3afb969d59618244c7db3b59f)

* KSBA 1.6.7 (b14e68b)

* NTBTLS 0.3.2 (2c38007)

root@b02bef684bbe:~# ps aux | grep -iE 'gpg|gnupg'
root        3870  0.0  0.1  78972  2484 ?        Ss   06:16   0:00 gpg-agent --homedir /root/.gnupg --daemon
root        3886  0.0  0.0   3472  1852 pts/1    S+   06:17   0:00 grep --color=auto -iE gpg|gnupg
root@b02bef684bbe:~#


root@b02bef684bbe:~# gpg --list-secret-keys
gpg: keydb_search_first failed: SQL error
root@b02bef684bbe:~# 


root@b02bef684bbe:~# gpg --list-secret-keys
[keyboxd]
---------
sec   ed448/0xD508AB8C71652DCE 2025-11-20 [SC]
      D508AB8C71652DCE1261504AF5BD63FC1CA362C37834310BF15833759A84DF4E
uid                   [ultimate] test01
ssb   ky1024_cv448/0xCDF1B9FE6D3E4C12 2025-11-20 [E]
      CDF1B9FE6D3E4C120F5DC81493E5EDEFE8CACE3F335561FE22388BC96852F633

root@b02bef684bbe:~#

Related Objects

Event Timeline

William created this task.Thu, Nov 20, 7:36 AM
• werner triaged this task as Low priority.Thu, Nov 20, 8:58 AM
• werner added projects: gnupg, workaround.
• werner added a subscriber: • werner.

Interesting. What SQlite version are you using? To see the exact reason and you have a copy of the old pubring.db, please add

log-file /foo/bar/keyboxd.log
verbose

to keyboxd.conf and try again after stopping all daemon and use a backup of your last pubring.db. I guess that there was some problem with the migration to the new database schema.

Workaround is obvious.

William added a comment.EditedThu, Nov 20, 9:38 AM

keyboxd (GnuPG) 2.5.13

gpg -K
cat keyboxd.log

listening on socket '/root/.gnupg/S.keyboxd'
keyboxd (GnuPG) 2.5.13 started
handler 0x7f2eba314640 for fd 9 started
connection from process 4361 (0:0)
(SQL: PRAGMA foreign_keys = ON)
(SQL: CREATE TABLE IF NOT EXISTS config (name  TEXT NOT NULL UNIQUE,value TEXT NOT NULL ))
database version: 1
database created: 2025-11-20 06:11:12
(SQL: CREATE TABLE IF NOT EXISTS pubkey (ubid     BLOB NOT NULL PRIMARY KEY,type  INTEGER NOT NULL,ephemeral INTEGER NOT NULL DEFAULT 0,revoked INTEGER NOT NULL DEFAULT 0,keyblob BLOB NOT NULL))
(SQL: CREATE TABLE IF NOT EXISTS fingerprint (fpr  BLOB NOT NULL PRIMARY KEY,kid  BLOB NOT NULL,keygrip BLOB NOT NULL,subkey INTEGER NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey))
(SQL: CREATE INDEX IF NOT EXISTS fingerprintidx0 on fingerprint (ubid))
(SQL: CREATE INDEX IF NOT EXISTS fingerprintidx1 on fingerprint (fpr))
(SQL: CREATE INDEX IF NOT EXISTS fingerprintidx2 on fingerprint (keygrip))
(SQL: CREATE TABLE IF NOT EXISTS userid (uid  TEXT NOT NULL,addrspec TEXT,type  INTEGER NOT NULL,uidno INTEGER NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey))
(SQL: CREATE INDEX IF NOT EXISTS userididx0 on userid (ubid))
(SQL: CREATE INDEX IF NOT EXISTS userididx1 on userid (uid))
(SQL: CREATE INDEX IF NOT EXISTS userididx3 on userid (addrspec))
(SQL: CREATE TABLE IF NOT EXISTS issuer (sn TEXT NOT NULL,dn TEXT NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey))
(SQL: CREATE INDEX IF NOT EXISTS issueridx1 on issuer (dn))
database '/root/.gnupg/public-keys.d/pubring.db' created
(SQL: SELECT ubid, type, ephemeral, revoked, keyblob FROM pubkey as p WHERE p.type = 1 ORDER by ubid)
(SQL: SELECT ubid, type, ephemeral, revoked, keyblob FROM pubkey as p WHERE p.type = 1 ORDER by ubid)
command 'NEXT' failed: Not found
handler 0x7f2eba314640 for fd 9 terminated

keyboxd (GnuPG) 2.5.14

gpg -K
cat keyboxd.log

listening on socket '/root/.gnupg/S.keyboxd'
keyboxd (GnuPG) 2.5.14 started
handler 0x7f0beb7d2640 for fd 9 started
connection from process 4255 (0:0)
(SQL: PRAGMA foreign_keys = ON)
(SQL: CREATE TABLE IF NOT EXISTS config (name  TEXT NOT NULL UNIQUE,value TEXT NOT NULL ))
database version 1 is not valid
database version: 1
database created: 2025-11-20 06:11:12
(SQL: CREATE TABLE IF NOT EXISTS pubkey (ubid     BLOB NOT NULL PRIMARY KEY,type  INTEGER NOT NULL,ephemeral INTEGER NOT NULL DEFAULT 0,revoked INTEGER NOT NULL DEFAULT 0,keyblob BLOB NOT NULL))
(SQL: CREATE TABLE IF NOT EXISTS fingerprint (fpr  BLOB NOT NULL,kid  BLOB NOT NULL,keygrip BLOB NOT NULL,subkey INTEGER NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey,flags INTEGER NOT NULL DEFAULT 0))
(SQL: CREATE INDEX IF NOT EXISTS fingerprintidx0 on fingerprint (ubid))
(SQL: CREATE INDEX IF NOT EXISTS fingerprintidx1 on fingerprint (fpr))
(SQL: CREATE INDEX IF NOT EXISTS fingerprintidx2 on fingerprint (keygrip))
(SQL: CREATE TABLE IF NOT EXISTS userid (uid  TEXT NOT NULL,addrspec TEXT,type  INTEGER NOT NULL,uidno INTEGER NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey))
(SQL: CREATE INDEX IF NOT EXISTS userididx0 on userid (ubid))
(SQL: CREATE INDEX IF NOT EXISTS userididx1 on userid (uid))
(SQL: CREATE INDEX IF NOT EXISTS userididx3 on userid (addrspec))
(SQL: CREATE TABLE IF NOT EXISTS issuer (sn TEXT NOT NULL,dn TEXT NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey))
(SQL: CREATE INDEX IF NOT EXISTS issueridx1 on issuer (dn))
database '/root/.gnupg/public-keys.d/pubring.db' opened
migrating database from version 1 to version 2
(SQL: begin transaction)
(SQL: CREATE TABLE IF NOT EXISTS fingerprint_new (fpr  BLOB NOT NULL,kid  BLOB NOT NULL,keygrip BLOB NOT NULL,subkey INTEGER NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey,flags INTEGER NOT NULL DEFAULT 0))
(SQL: INSERT INTO fingerprint_new SELECT * FROM fingerprint)
error preparing SQL statement: SQL logic error
(SQL: rollback)
error creating database '/root/.gnupg/public-keys.d/pubring.db': SQL error
error during the initial search reset: SQL error
command 'SEARCH' failed: SQL error
handler 0x7f0beb7d2640 for fd 9 terminated

I think I've found the cause， it's because of this commit: "kbx: Fix schema of the fingerprint table." Commit 0cc7759ed5a3890b4e28563a6b5e97f3aa551530, where (DATABASE_VERSION) is set to 2 and migrate ...

• werner added a comment.Thu, Nov 20, 11:25 AM

Can you please schek which Sqlite version you have installed? I have not seen this on my system.

Also please check whether there is already a fingerprint_new table on your system. SQLlite requires that one creates a new table with the changes (dropping of PRIMARY KEY), copying the content and then renaming the tables. If that sucseeds the database version will be bumped up to 2 to indicate that the migration has been done. To check

sqlite3 ~/.gnupg/public-keys.d/pubring.db
> .schema

which should not list a fingerprint_new table. I have no other explanation for the SQL Logic error right now.

William added a comment.Thu, Nov 20, 12:29 PM

gpg was compiled with the latest version of SQLite3 at the time as its own private library, without linking against the system's libsqlite3.so.
The sqlite3 CLI is the version that comes pre-installed with Ubuntu 22.04.5.

GnuPG 2.5.13

root@b02bef684bbe:~# ldd /usr/bin/gpg | grep -i sqlite
	libsqlite3.so => /usr/bin/../lib/x86_64-linux-gnu/gnupg/private/libsqlite3.so (0x00007f866a2d5000)
root@b02bef684bbe:~# ll /usr/bin/../lib/x86_64-linux-gnu/gnupg/private/libsqlite3*   
lrwxrwxrwx 1 root root      20 Nov  4 04:58 /usr/bin/../lib/x86_64-linux-gnu/gnupg/private/libsqlite3.so -> libsqlite3.so.3.50.4*
lrwxrwxrwx 1 root root      20 Nov  4 04:58 /usr/bin/../lib/x86_64-linux-gnu/gnupg/private/libsqlite3.so.0 -> libsqlite3.so.3.50.4*
-rwxr-xr-x 1 root root 1529448 Nov  4 04:58 /usr/bin/../lib/x86_64-linux-gnu/gnupg/private/libsqlite3.so.3.50.4*
root@b02bef684bbe:~# sqlite3 ~/.gnupg/public-keys.d/pubring.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE config (name  TEXT NOT NULL UNIQUE,value TEXT NOT NULL );
CREATE TABLE pubkey (ubid     BLOB NOT NULL PRIMARY KEY,type  INTEGER NOT NULL,ephemeral INTEGER NOT NULL DEFAULT 0,revoked INTEGER NOT NULL DEFAULT 0,keyblob BLOB NOT NULL);
CREATE TABLE fingerprint (fpr  BLOB NOT NULL PRIMARY KEY,kid  BLOB NOT NULL,keygrip BLOB NOT NULL,subkey INTEGER NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey);
CREATE INDEX fingerprintidx0 on fingerprint (ubid);
CREATE INDEX fingerprintidx1 on fingerprint (fpr);
CREATE INDEX fingerprintidx2 on fingerprint (keygrip);
CREATE TABLE userid (uid  TEXT NOT NULL,addrspec TEXT,type  INTEGER NOT NULL,uidno INTEGER NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey);
CREATE INDEX userididx0 on userid (ubid);
CREATE INDEX userididx1 on userid (uid);
CREATE INDEX userididx3 on userid (addrspec);
CREATE TABLE issuer (sn TEXT NOT NULL,dn TEXT NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey);
CREATE INDEX issueridx1 on issuer (dn);
sqlite> 

root@b02bef684bbe:~# sha256sum ~/.gnupg/public-keys.d/pubring.db
54dfa5624410cf7b9e623330f546b4449f9bc76be364ec72ae13e927a4d08bb7  /root/.gnupg/public-keys.d/pubring.db

GnuPG 2.5.14

root@b02bef684bbe:~# ldd /usr/bin/gpg | grep -i sqlite
	libsqlite3.so => /usr/bin/../lib/x86_64-linux-gnu/gnupg/private/libsqlite3.so (0x00007f1a9a011000)
root@b02bef684bbe:~# ll /usr/bin/../lib/x86_64-linux-gnu/gnupg/private/libsqlite3* 
lrwxrwxrwx 1 root root      20 Nov 20 03:55 /usr/bin/../lib/x86_64-linux-gnu/gnupg/private/libsqlite3.so -> libsqlite3.so.3.51.0*
lrwxrwxrwx 1 root root      20 Nov 20 03:55 /usr/bin/../lib/x86_64-linux-gnu/gnupg/private/libsqlite3.so.0 -> libsqlite3.so.3.51.0*
-rwxr-xr-x 1 root root 1554760 Nov 20 03:55 /usr/bin/../lib/x86_64-linux-gnu/gnupg/private/libsqlite3.so.3.51.0*
root@b02bef684bbe:~# gpg -K
gpg: keydb_search_first failed: SQL error
root@b02bef684bbe:~# gpg -K
[keyboxd]
---------
sec   ed448/0x8C094B1A1E6725BB 2025-11-20 [SC]
      8C094B1A1E6725BB3416B0AA87841D84246C8BF825BAE6C01849D3CA782188E6
uid                   [ultimate] test01
ssb   ky1024_cv448/0xD24C39250E7638B3 2025-11-20 [E]
      D24C39250E7638B3C560C6B91AD8BC96C53691A6D2AACB9A3C53052D7DCB8C03

root@b02bef684bbe:~# sqlite3 ~/.gnupg/public-keys.d/pubring.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE config (name  TEXT NOT NULL UNIQUE,value TEXT NOT NULL );
CREATE TABLE pubkey (ubid     BLOB NOT NULL PRIMARY KEY,type  INTEGER NOT NULL,ephemeral INTEGER NOT NULL DEFAULT 0,revoked INTEGER NOT NULL DEFAULT 0,keyblob BLOB NOT NULL);
CREATE TABLE fingerprint (fpr  BLOB NOT NULL PRIMARY KEY,kid  BLOB NOT NULL,keygrip BLOB NOT NULL,subkey INTEGER NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey);
CREATE INDEX fingerprintidx0 on fingerprint (ubid);
CREATE INDEX fingerprintidx1 on fingerprint (fpr);
CREATE INDEX fingerprintidx2 on fingerprint (keygrip);
CREATE TABLE userid (uid  TEXT NOT NULL,addrspec TEXT,type  INTEGER NOT NULL,uidno INTEGER NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey);
CREATE INDEX userididx0 on userid (ubid);
CREATE INDEX userididx1 on userid (uid);
CREATE INDEX userididx3 on userid (addrspec);
CREATE TABLE issuer (sn TEXT NOT NULL,dn TEXT NOT NULL,ubid BLOB NOT NULL REFERENCES pubkey);
CREATE INDEX issueridx1 on issuer (dn);
sqlite> 

root@b02bef684bbe:~# sha256sum ~/.gnupg/public-keys.d/pubring.db
54dfa5624410cf7b9e623330f546b4449f9bc76be364ec72ae13e927a4d08bb7  /root/.gnupg/public-keys.d/pubring.db