Emails appear twice In the preview section as if they were in a thread on Nextcloud mail 1.12.0 with ProtonMail (with local bridge)

[S0ulf3re]

Steps to reproduce

1. Install proton bridge via website instructions
2. Connect local account to bridge
3. Error appears immediately, with each email opening to a preview below one above it

Expected behavior

Emails should only appear once.

Actual behavior

Emails appear twice as if they were in a threaded conversations. Note that this doesn’t seem to happen with emails in the trash, with the exception of some drafts. This also happens with threaded conversations, with each email appearing twice per sender.

Mail app version

1.12.0

Mailserver or service

ProtonMail with Proton Bridge

Operating system

Ubuntu 22.04

PHP engine version

PHP 8.0

Web server

Apache (supported)

Database

MySQL

Additional info

Running with Nextcloud Snap

[keto]

I have the same issue with Gmail account over IMAP, so it's probably not related to the Proton mail or bridge.

[mbootsman]

Same issue here. Any news on this?

[ChristophWurst]

My guess: Gmail and Proton have an special mailbox with the attribute \\all. It is a virtual mailbox that contains all messages of your account.

Our app doesn't exclude that mailbox from threading, so a message will always exist twice. Once in the real mailbox, a second time in the virtual all messages mailbox.

To dive into that, please open the browser console and go to the network tab. Open another message/thread. Then open the one with the duplicate. There is a request that ends with /thread. It returns JSON. Inspect the data. Let me know the databaseId, messageId, UID and mailboxId of both messages.

[AAA3A-AAA3A]

Hello,

I have the same problem with my Gmail account, recently added to my NextCloud instance. Here's the JSON response to a thread containing the same message twice:

[
    {
        "databaseId": 7861,
        "uid": 6462,
        "subject": "...",
        "dateInt": 1692713254,
        "flags": {
            "seen": true,
            "flagged": false,
            "answered": false,
            "deleted": false,
            "draft": false,
            "forwarded": false,
            "hasAttachments": false,
            "important": false,
            "$junk": false,
            "$notjunk": false,
            "mdnsent": false
        },
        "tags": [],
        "from": [
            {
                "label": "...",
                "email": "..."
            }
        ],
        "to": [
            {
                "label": "...",
                "email": "..."
            }
        ],
        "cc": [],
        "bcc": [],
        "mailboxId": 57,
        "messageId": "\u003CCzijBVX6DkTSD5-npS6n0w@notifications.google.com\u003E",
        "inReplyTo": null,
        "references": [],
        "threadRootId": "...",
        "imipMessage": false,
        "previewText": "...",
        "encrypted": false
    },
    {
        "databaseId": 9629,
        "uid": 11176,
        "subject": "...",
        "dateInt": 1692713254,
        "flags": {
            "seen": true,
            "flagged": false,
            "answered": false,
            "deleted": false,
            "draft": false,
            "forwarded": false,
            "hasAttachments": false,
            "important": false,
            "$junk": false,
            "$notjunk": false,
            "mdnsent": false
        },
        "tags": [],
        "from": [
            {
                "label": "...",
                "email": "..."
            }
        ],
        "to": [
            {
                "label": "...",
                "email": "..."
            }
        ],
        "cc": [],
        "bcc": [],
        "mailboxId": 65,
        "messageId": "\u003CCzijBVX6DkTSD5-npS6n0w@notifications.google.com\u003E",
        "inReplyTo": null,
        "references": [],
        "threadRootId": "...",
        "imipMessage": false,
        "previewText": "...",
        "encrypted": false
    }
]

Thank you in advance.

[AAA3A-AAA3A]

@ChristophWurst Any update about that issue?

[ChristophWurst]

SELECT `name` FROM oc_mail_mailboxes WHERE id IN (57, 65)

[amertahir]

There's a simple solution to this problem, in the "/var/www/nextcloud/apps/mail/lib/Db/MessageMapper.php" file, add the following groupBy clause to findThread method:

->groupBy('messages.message_id')

so, this makes the function look like this:

public function findThread(Account $account, string $threadRootId): array {
                $qb = $this->db->getQueryBuilder();
                $qb->select('messages.*')
                        ->from($this->getTableName(), 'messages')
                        ->join('messages', 'mail_mailboxes', 'mailboxes', $qb->expr()->eq('messages.mailbox_id', 'mailboxes.id', IQueryBuilder::PARAM_INT))
                        ->where(
                                $qb->expr()->eq('mailboxes.account_id', $qb->createNamedParameter($account->getId(), IQueryBuilder::PARAM_INT)),
                                $qb->expr()->eq('messages.thread_root_id', $qb->createNamedParameter($threadRootId, IQueryBuilder::PARAM_STR), IQueryBuilder::PARAM_STR)
                        )
                        ->groupBy('messages.message_id')
                        ->orderBy('messages.sent_at', 'desc');

                return $this->findRelatedData($this->findEntities($qb), $account->getUserId());
        }

I'm sure there's a better way to do this, but it works for me so I'm happy.

[ChristophWurst]

@amertahir does that mean you have multiple copies of the same message?

[amertahir]

Yes. I believe this issue is relevant to email servers like Gmail and Proton that organize messages by labels. In this case, a message is labeled with "Inbox" and "All Mail" and so it appears twice in the results of the original SQL query. It's the same message with the same messageId but represented twice in the database, hence, the group by clause works.

[amertahir]

If the IMAP server is giving duplicate messages if a message is tagged with multiple labels (different folders have the same message with the same messageId), then maybe at the time of fetching the Mail app can organize it in the DB in such a way that there's a many-to-many relationship with labels (Folders). I'm not sure if that's already the case or if that approach will cause scalability issues. I do know that a design change for a corner case like this is kind of unwarranted.