port sql database error

[Richard L. Hamilton]

Just reading the error messages, I would NOT assume that there's a duplicate with the same id. Rather, some other process is adding to the database while you're querying it, or at least it looks to the querying program as if that were the case.

Sqlite3 is AFAIK NOT truly multi-user. It allows any number of concurrent readers, only one writer at a time. Reading while another process is writing might see an inconsistent view. I think that readers and a single simultaneous writer can only happen in "WAL mode" - extra files present alongside the database file, like this:

sh-3.2$ ls /opt/local/var/macports/registry/registry.db*
/opt/local/var/macports/registry/registry.db
/opt/local/var/macports/registry/registry.db-shm
/opt/local/var/macports/registry/registry.db-wal

(no, do NOT delete the -shm or -wal files unless someone who knows much better than me tells you to)

I have not yet read enough to determine whether inconsistent reads with an active writer is really a bug or a known (mis-)feature. I'm certainly NOT an sqlite3 expert, I've run a few sql commands to look at databases, but never looked at its code nor written a program that uses it, nor read more than the least documentation necessary to do whatever I was trying to do.

If iti were me, I'd try

pgrep -lf /opt/local/

and see if there was some other likely process out there (although I don't swear that would find them all). Or maybe

lsof /opt/local/var/macports/registry/registry.db*

In general, I would NOT attempt to change the database in any way (not even running a vacuum on it) until someone that knows more about sqlite3 than I do can tell me what to try; the situation might be salvageable now, but the wrong action might make it unsalvageable.


> On Jan 22, 2025, at 19:02, Riccardo Mottola via macports-users <macports-users at lists.macports.org> wrote:
> 
> Hi,
> 
> during/after upgrade I got this:
> $ port outdated
> Error: process_cmd failed: sqlite error: another row available (100) while executing query: SELECT cxx_stdlib FROM registry.ports WHERE id=1184
> 
> Compared to the past, this time no reboot or other trick fixed the problem by by itself...
> 
> I do:
> 
> $ sqlite3 /opt/local/var/macports/registry/registry.db "select * from ports where id=1184"
> 1184|xmlcatmgr|435d94eb7431bfb474662c034bffac8cbde64759424e188613ed8a7f8287ba96-1298|/opt/local/var/macports/software/xmlcatmgr/xmlcatmgr-2.2_1.darwin_10.x86_64.tbz2|0|2.2|1|||installed|1737559896|image|x86_64|0|darwin|10||0
> 
> This means only one!
> 
> what can I do?
> 
> port installed works
> 
> I tried "port reclaim". It appeared to work and complete, did two passes. Now... I get the error on another id!
> 
> $ port outdated
> Error: process_cmd failed: sqlite error: another row available (100) while executing query: SELECT cxx_stdlib FROM registry.ports WHERE id=1218
> 
> Which corresponds to another port:
> 1218|pkgconfig|677ba022ffa40ea6642b68951fc2422ebf9a966224178524a7b3f0c5e7269d32-3295|/opt/local/var/macports/software/pkgconfig/pkgconfig-0.29.2_0.darwin_10.x86_64.tbz2|0|0.29.2|0|||installed|1737587718|image|x86_64|0|darwin|10||0
> 
> 
> now I am confused and worried.
> 
> Riccardo
>