findNewIds() chunking can return massive duplicate/already-known ID sets and cause memory exhaustion

[helioaiolis]

Steps to reproduce

1. Use a mailbox with more than 1000 locally known messages. In our case the mailbox had ~41,000 known messages.
2. Open the mailbox in Nextcloud Mail and perform a filtered search.
3. Let the client call the mailbox sync endpoint while the filter is active.
4. Instrument MessageMapper::findNewIds() and compare the number of known IDs with the returned new IDs.

In our production case:

knownIds = 40810

but findNewIds() returned approximately:

newIds = 820000

In another request the filtered result reached approximately 982697 IDs.

The issue is caused by chunking the known ID list into groups of 1000 while NOT IN (:ids) only excludes the current chunk.

Expected behavior

findNewIds() should return only IDs that are not present in the complete known-ID set.

Each message ID should be returned at most once, regardless of how the known IDs are chunked internally.

Actual behavior

When the known ID list contains more than 1000 IDs, each SQL query excludes only the current 1000-ID chunk.

Known IDs belonging to the other chunks can therefore be returned again and again.

The results of all chunks are then concatenated, producing a very large result set.

In our case:

knownIds = 40810
newIds = ~820000

This caused the mailbox sync request to approach the 1 GB PHP memory limit and eventually fail with memory exhaustion.

We tested the following workaround successfully:

$knownIds = array_fill_keys($ids, true);
$results = [];

foreach (array_chunk($ids, 1000) as $chunk) {
    $select->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY);

    foreach ($this->findIds($select) as $id) {
        if (!isset($knownIds[$id])) {
            $results[$id] = $id;
        }
    }
}

return array_values($results);

Mail app version

5.12.0

Nextcloud version

34.0.2

Mailserver or service

IMAP + Gmail

Operating system

Debian GNU/Linux 13 (trixie)

PHP engine version

Other

Nextcloud memory caching

memcache.local: APCu memcache.distributed: Redis memcache.locking: Redis

Web server

Apache (supported)

Database

PostgreSQL

Additional info

PHP: 8.5.10
Web server: Apache 2.4.68
OS: Debian GNU/Linux 13 (trixie)

Nextcloud memory caching:

• memcache.local: \OC\Memcache\APCu
• memcache.distributed: \OC\Memcache\Redis
• memcache.locking: \OC\Memcache\Redis

PHP memory_limit: 1024M

The issue was reproduced on a mailbox with approximately 41,000 locally known messages.

[karandeepsingh7070]

Hi @helioaiolis, please assign this to my name. I will look into this.

[helioaiolis]

@karandeepsingh7070 Thanks for looking into this! I don't have permission to assign issues in this repository, so please feel free to assign yourself or ask a maintainer to assign it to you.