Sync failed for INBOX: Did not expect more than one result when executing: query

[UteHaus]

Steps to reproduce

1. Open Mails
2. Sync Mails

Expected behavior

No Sync fail!

Actual behavior

[mail] Fehler: OCA\Mail\Exception\ServiceException: Sync failed for 4:INBOX: Did not expect more than one result when executing: query "SELECT * FROM `*PREFIX*mail_tags` WHERE (`imap_label` = :dcValue1) AND (`user_id` = :dcValue2)";  at <<closure>>

0. /var/www/html/custom_apps/mail/lib/Service/Sync/SyncService.php line 123
   OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync("*** sensitive parameters replaced ***")
1. /var/www/html/custom_apps/mail/lib/Controller/MailboxesController.php line 155
   OCA\Mail\Service\Sync\SyncService->syncMailbox("*** sensitive parameters replaced ***")
2. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 230
   OCA\Mail\Controller\MailboxesController->sync("*** sensitive parameters replaced ***")
3. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 137
   OC\AppFramework\Http\Dispatcher->executeController(["OCA\\Mail\\Con ... "], "sync")
4. /var/www/html/lib/private/AppFramework/App.php line 183
   OC\AppFramework\Http\Dispatcher->dispatch(["OCA\\Mail\\Con ... "], "sync")
5. /var/www/html/lib/private/Route/Router.php line 315
   OC\AppFramework\App::main("OCA\\Mail\\Cont ... r", "sync", ["OC\\AppFramewo ... "], ["11","mail.mailboxes.sync"])
6. /var/www/html/lib/base.php line 1068
   OC\Route\Router->match("/apps/mail/api/mailboxes/11/sync")
7. /var/www/html/index.php line 38
   OC::handleRequest()

Caused by:

OCP\AppFramework\Db\MultipleObjectsReturnedException: Did not expect more than one result when executing: query "SELECT * FROM `*PREFIX*mail_tags` WHERE (`imap_label` = :dcValue1) AND (`user_id` = :dcValue2)";  at <<closure>>

 0. /var/www/html/lib/public/AppFramework/Db/QBMapper.php line 361
    OCP\AppFramework\Db\QBMapper->findOneQuery(["OC\\DB\\QueryBuilder\\QueryBuilder"])
 1. /var/www/html/custom_apps/mail/lib/Db/TagMapper.php line 60
    OCP\AppFramework\Db\QBMapper->findEntity(["OC\\DB\\QueryBuilder\\QueryBuilder"])
 2. /var/www/html/custom_apps/mail/lib/Db/TagMapper.php line 97
    OCA\Mail\Db\TagMapper->getTagByImapLabel("junk", "flo")
 3. /var/www/html/custom_apps/mail/lib/Db/MessageMapper.php line 350
    OCA\Mail\Db\TagMapper->tagMessage(["OCA\\Mail\\Db\ ... "], "<20231221080103 ... >", "flo")
 4. /var/www/html/custom_apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php line 428
    OCA\Mail\Db\MessageMapper->insertBulk("*** sensitive parameters replaced ***", ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "], ["OCA\\Mail\\Db\ ... "])
 5. /var/www/html/custom_apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php line 261
    OCA\Mail\Service\Sync\ImapToDbSynchronizer->runPartialSync(["OCA\\Mail\\IMA ... "], "*** sensitive parameters replaced ***", "*** sensitive parameters replaced ***", "*** sensitive parameters replaced ***", "*** sensitive parameters replaced ***", "*** sensitive parameters replaced ***")
 6. /var/www/html/custom_apps/mail/lib/Service/Sync/SyncService.php line 123
    OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync("*** sensitive parameters replaced ***")
 7. /var/www/html/custom_apps/mail/lib/Controller/MailboxesController.php line 155
    OCA\Mail\Service\Sync\SyncService->syncMailbox("*** sensitive parameters replaced ***")
 8. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 230
    OCA\Mail\Controller\MailboxesController->sync("*** sensitive parameters replaced ***")
 9. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 137
    OC\AppFramework\Http\Dispatcher->executeController(["OCA\\Mail\\Con ... "], "sync")
10. /var/www/html/lib/private/AppFramework/App.php line 183
    OC\AppFramework\Http\Dispatcher->dispatch(["OCA\\Mail\\Con ... "], "sync")
11. /var/www/html/lib/private/Route/Router.php line 315
    OC\AppFramework\App::main("OCA\\Mail\\Cont ... r", "sync", ["OC\\AppFramewo ... "], ["11","mail.mailboxes.sync"])
12. /var/www/html/lib/base.php line 1068
    OC\Route\Router->match("/apps/mail/api/mailboxes/11/sync")
13. /var/www/html/index.php line 38
    OC::handleRequest()

POST /apps/mail/api/mailboxes/11/sync
from 87.161.42.125 by flo at 2024-01-02T21:47:47+00:00

Mail app version

3.5.0

Mailserver or service

Posteo and hosteurope

Operating system

Docker 27.1.5-apache

PHP engine version

PHP 8.0

Web server

Nginx

Database

PostgreSQL

Additional info

Postgres version: 14.10-1.pgdg110+1 Nexcloud 27.1.5

The exception cam maybe with the update to 3.5.0 . My last synced mail is from 19.12 and the app update was on the 18.12 .

[ChristophWurst]

The unique index mail_msg_tags_usr_lbl_idx on mail_tags should not allow duplicates.

Could you please inspect your database schema and the existence of this index? Thanks

[UteHaus]

The mail_msg_tags_usr_lbl_idx index is not defined. My currend indices on the mail_tag table.

To ensure they are all indices set, I executed occ db:add-missing-indices but all indices already created.

[UteHaus]

Exist for this problem a workaround?

[UteHaus]

I fix the issue in 2 steps:

1. add the missing unique constraint over user_id and imap_label`.
2. also removed the duplicate entries in the table oc_mail_tags

You can close the issue.