SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression

[ku8az]

After brand new installation of Memories I can't select Timeline folder, nothing happens and dialog reappears. In JS console is an error: An exception occurred while executing a query: SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression
After setting of both folders manually via occ as advised in issue #111, I'm getting the same error in UI as well.

Raspberry Pi 4 (4GB)
NextCloud 25.0.2
PostgreSQL 13.8
Memories 4.9.3

Stacktrace:
OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression

/var/www/.../lib/private/DB/ConnectionAdapter.php - line 69:
OC\DB\Exceptions\DbalException::wrap()
/var/www/.../apps/memories/lib/Db/TimelineQueryDays.php - line 328:
OC\DB\ConnectionAdapter->executeQuery()
/var/www/.../apps/memories/lib/Db/TimelineQueryPeopleRecognize.php - line 75:
OCA\Memories\Db\TimelineQuery->executeQueryWithCTEs()
/var/www/.../apps/memories/lib/Controller/PeopleController.php - line 59:
OCA\Memories\Db\TimelineQuery->getPeopleRecognize()
/var/www/.../lib/private/AppFramework/Http/Dispatcher.php - line 225:
OCA\Memories\Controller\PeopleController->recognizePeople()
/var/www/.../lib/private/AppFramework/Http/Dispatcher.php - line 133:
OC\AppFramework\Http\Dispatcher->executeController()

Edit:
After DB schema regeneration and running all migrates, the folder selection dialog reacted as expected. However, UI error remains and I'm not able to use the application at all. After selection of almost anything in left menu the error is thrown.

Originally posted by @ku8az in #111 (comment)

[pulsejet]

@ku8az what UI error are you referring to? Do you no longer get the SQL error?

[ku8az]

Sorry, I mean this particular error in UI. I wrote it because at first, I wasn't even able to get into app UI. After folder settings I got into but always receive this error.

[pulsejet]

Hmm I've no idea why it's calling recognize functions for folder selection. What URL are you on?

[ku8az]

Maybe it was some my misconfiguration before, after renewing a whole memories app DB schema via migrations I was able to select a folder on welcome screen.

However, when I go into app I immediately see the error.
I see it on: timeline, folders, videos, archive, thisday.
On tags, recognize I see an error: Request failed with status code 500 while People in Photos works.

URL is just: apps/memories/ or apps/memories/XXX

[ku8az]

After short investigation in nextcloud logs, it seems the error is thrown only for tags and recognize parts, no other error messages for other parts are logged. However, errors in app are shown little bit odd as this error shows in all mentioned parts while parts which the error is logged for show that 500 status code error.
I'll try to reindex all data and will see what happens.

[idefy]

I have a brand new installation of NC and installed memories. I have the exact same error.
The Photos folder can be opened with no errors. But any other options (timeline, favorites, etc...) all results in this error:<
An exception occurred while executing a query: SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression

[sowoi]

I also had this error and had to do a lot of reverse research until I realized what the problem was. I use PostgreSQL but the error can affect basically any database. In my case the cause was a restore from a dump where a file ID was no longer unique. I suspect that everyone here has previously performed a DB restore. The database query that had caused this for me was:

WITH RECURSIVE oc_cte_folders_all(fileid, name) AS (
    SELECT f.fileid, f.name 
    FROM oc_filecache f 
    WHERE NOT EXISTS (
        SELECT 1   
        FROM oc_filecache f2    
        WHERE f2.parent = f.fileid  
        AND (f2.name = '.nomedia' OR f2.name = '.nomemories')
    )
)
SELECT f.fileid, f.name 
FROM oc_filecache f 
INNER JOIN oc_cte_folders_all c ON f.parent = c.fileid 
WHERE f.mimetype = (SELECT "id" FROM "oc_mimetypes" WHERE "mimetype" = 'httpd/unix-directory') 
AND f.name <> '.archive' 
AND NOT EXISTS (
    SELECT 1    
    FROM oc_filecache f2    
    WHERE f2.parent = f.fileid     
    AND (f2.name = '.nomedia' OR f2.name = '.nomemories')
);

An id was assigned the same mimetype twice. This should not happen, but it did.

@pulsejet

SELECT "id", "mimetype" 
FROM "oc_mimetypes" 
WHERE "mimetype" = 'httpd/unix-directory';
 id |       mimetype       
----+----------------------
  2 | httpd/unix-directory
  2 | httpd/unix-directory
(2 rows)

The solution was simple. First create a backup and then delete the redundant entry:

DELETE FROM "oc_mimetypes"
WHERE "id" = 2
AND ctid NOT IN (
    SELECT MIN(ctid)
    FROM "oc_mimetypes"
    WHERE "mimetype" = 'httpd/unix-directory'
    GROUP BY "id", "mimetype"
);
DELETE 1

After that, the error disappeared for me.

@pulsejet

Thank you for developing memories. My family and I really use the app every day. Without it, I would have deleted Nextcloud a long time ago because the in-house photo app sucks.

[pulsejet]

@sowoi wow this is an amazing catch! Putting a MAX in the subquery should likely fix this; of course that won't make the table "correct" but at least it won't crash anymore. Thanks for finding this!