ImportanceClassifier: More than 1000 expressions in a list are not allowed on Oracle.

[kesselb]

Steps to reproduce

1. Have more than 1000 mailboxes
2. Run classifier
3. :disappointed:

Expected behavior

No warning ;)

Actual behavior

From a customer log:

{
  "reqId": "",
  "level": 3,
  "time": "October 31, 2023 23:22:55",
  "remoteAddr": "",
  "user": "--",
  "app": "core",
  "method": "",
  "url": "--",
  "message": "More than 1000 expressions in a list are not allowed on Oracle.",
  "userAgent": "--",
  "version": "27.1.2.2",
  "exception": {
    "Exception": "Doctrine\\DBAL\\Query\\QueryException",
    "Message": "More than 1000 expressions in a list are not allowed on Oracle.",
    "Code": 0,
    "Trace": [
      {
        "file": "/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php",
        "line": 293,
        "function": "execute",
        "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
        "type": "->"
      },
      {
        "file": "/var/www/html/lib/public/AppFramework/Db/QBMapper.php",
        "line": 335,
        "function": "executeQuery",
        "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
        "type": "->"
      },
      {
        "file": "/var/www/html/apps/mail/lib/Db/MessageMapper.php",
        "line": 1289,
        "function": "findEntities",
        "class": "OCP\\AppFramework\\Db\\QBMapper",
        "type": "->"
      },
      {
        "file": "/var/www/html/apps/mail/lib/Service/Classification/ImportanceClassifier.php",
        "line": 171,
        "function": "findLatestMessages",
        "class": "OCA\\Mail\\Db\\MessageMapper",
        "type": "->"
      },
      {
        "file": "/var/www/html/apps/mail/lib/BackgroundJob/TrainImportanceClassifierJob.php",
        "line": 90,
        "function": "train",
        "class": "OCA\\Mail\\Service\\Classification\\ImportanceClassifier",
        "type": "->"
      },
      {
        "file": "/var/www/html/lib/public/BackgroundJob/Job.php",
        "line": 81,
        "function": "run",
        "class": "OCA\\Mail\\BackgroundJob\\TrainImportanceClassifierJob",
        "type": "->"
      },
      {
        "file": "/var/www/html/lib/public/BackgroundJob/TimedJob.php",
        "line": 103,
        "function": "start",
        "class": "OCP\\BackgroundJob\\Job",
        "type": "->"
      },
      {
        "file": "/var/www/html/lib/public/BackgroundJob/TimedJob.php",
        "line": 93,
        "function": "start",
        "class": "OCP\\BackgroundJob\\TimedJob",
        "type": "->"
      },
      {
        "file": "/var/www/html/cron.php",
        "line": 152,
        "function": "execute",
        "class": "OCP\\BackgroundJob\\TimedJob",
        "type": "->"
      }
    ],
    "File": "/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php",
    "Line": 263,
    "message": "More than 1000 expressions in a list are not allowed on Oracle.",
    "query": "SELECT `m`.* FROM `*PREFIX*mail_messages` `m` INNER JOIN `*PREFIX*mail_recipients` `r` ON `m`.`id` = `r`.`message_id` WHERE (`r`.`type` = :dcValue1) AND (`m`.`mailbox_id` IN (:dcValue2)) ORDER BY `sent_at` desc LIMIT 1000",
    "exception": {},
    "CustomMessage": "More than 1000 expressions in a list are not allowed on Oracle."
  }
}

We need chunking for the mailboxIds here as well for oracle

https://github.com/nextcloud/mail/blob/7eababf801ac6d7bdcc7aff522e525d417f7e1f4/lib/Service/Classification/ImportanceClassifier.php#L167-L173

Mail app version

They are using Nextcloud 27. Unsure about the mail app version.

Mailserver or service

No response

Operating system

PHP engine version

PHP 8.1

Web server

Apache (supported)

Database

MySQL

Additional info

No response

[ChristophWurst]

Have more than 1000 mailboxes

of course

[pongraczi]

Recent All-in-One: Nextcloud Hub 6 (27.1.5)
Mail: 3.5.2

I have definitely huge number of:

• own folders
• shared folders from other users
• emails per folder where huge means several thousands.

I tried to access to my folder, I hit this bug, too. I am not sure, but seems similar/same reason of my problems.

Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle

``` Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php - line 293: OC\DB\QueryBuilder\QueryBuilder->execute() /var/www/html/custom_apps/mail/lib/Db/StatisticsDao.php - line 201: OC\DB\QueryBuilder\QueryBuilder->executeQuery() /var/www/html/custom_apps/mail/lib/Service/Classification/FeatureExtraction/RepliedMessagesExtractor.php - line 62: OCA\Mail\Db\StatisticsDao->getNumberOfMessagesWithFlagGrouped([ [ "OCA\\Ma ... "], "answered", [ "cyrus36@logmaster.hu"]) /var/www/html/custom_apps/mail/lib/Service/Classification/ImportanceRulesClassifier.php - line 75: OCA\Mail\Service\Classification\FeatureExtraction\RepliedMessagesExtractor->prepare("*** sensiti ... *", [ [ "OCA\\Ma ... "], [ [ "OCA\\Ma ... ]], [ [ "OCA\\Ma ... ]]) /var/www/html/custom_apps/mail/lib/Service/Classification/ImportanceClassifier.php - line 315: OCA\Mail\Service\Classification\ImportanceRulesClassifier->classifyImportance("*** sensiti ... *", [ [ "OCA\\Ma ... "], [ [ "OCA\\Ma ... ]], [ [ "OCA\\Ma ... ]]) /var/www/html/custom_apps/mail/lib/Listener/NewMessageClassificationListener.php - line 121: OCA\Mail\Service\Classification\ImportanceClassifier->classifyImportance("*** sensiti ... *", [ [ "OCA\\Ma ... ]]) /var/www/html/lib/private/EventDispatcher/ServiceEventListener.php - line 86: OCA\Mail\Listener\NewMessageClassificationListener->handle([ "OCA\\Mail ... "]) /var/www/html/3rdparty/symfony/event-dispatcher/EventDispatcher.php - line 251: OC\EventDispatcher\ServiceEventListener->__invoke([ "OCA\\Mail ... "], "OCA\\Mail\\ ... d", [ "Symfony\\ ... "]) /var/www/html/3rdparty/symfony/event-dispatcher/EventDispatcher.php - line 73: Symfony\Component\EventDispatcher\EventDispatcher->callListeners([ [ "Closure"],[ "Closure"]], "OCA\\Mail\\ ... d", [ "OCA\\Mail ... "]) /var/www/html/lib/private/EventDispatcher/EventDispatcher.php - line 94: Symfony\Component\EventDispatcher\EventDispatcher->dispatch([ "OCA\\Mail ... "], "OCA\\Mail\\ ... d") /var/www/html/custom_apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 432: OC\EventDispatcher\EventDispatcher->dispatch("OCA\\Mail\\ ... d", [ "OCA\\Mail ... "]) /var/www/html/custom_apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 261: OCA\Mail\Service\Sync\ImapToDbSynchronizer->runPartialSync([ "OCA\\Mail ... "], "*** sensiti ... *", "*** sensiti ... *", "*** sensiti ... *", "*** sensiti ... *", "*** sensiti ... *") /var/www/html/custom_apps/mail/lib/Service/Sync/SyncService.php - line 129: OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync("*** sensiti ... *") /var/www/html/custom_apps/mail/lib/Controller/MailboxesController.php - line 156: OCA\Mail\Service\Sync\SyncService->syncMailbox("*** sensiti ... *") /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 230: OCA\Mail\Controller\MailboxesController->sync("*** sensiti ... *") /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 137: OC\AppFramework\Http\Dispatcher->executeController([ "OCA\\Mail ... "], "sync") /var/www/html/lib/private/AppFramework/App.php - line 183: OC\AppFramework\Http\Dispatcher->dispatch([ "OCA\\Mail ... "], "sync") /var/www/html/lib/private/Route/Router.php - line 315: OC\AppFramework\App::main("OCA\\Mail\\ ... r", "sync", [ "OC\\AppFr ... "], [ "28","mail.mailboxes.sync"]) /var/www/html/lib/base.php - line 1068: OC\Route\Router->match("/apps/mail/ ... c") /var/www/html/index.php - line 38: OC::handleRequest() ```

Horde_Imap_Client_Exception: Too many auth attempts - Background job

``` Horde_Imap_Client_Exception: Too many auth attempts /var/www/html/custom_apps/mail/vendor/bytestream/horde-imap-client/lib/Horde/Imap/Client/Base.php - line 853: OCA\Mail\IMAP\ImapClientRateLimitingDecorator->_login("*** sensiti ... *") /var/www/html/custom_apps/mail/vendor/bytestream/horde-imap-client/lib/Horde/Imap/Client/Base.php - line 2510: Horde_Imap_Client_Base->login("*** sensiti ... *") /var/www/html/custom_apps/mail/vendor/bytestream/horde-imap-client/lib/Horde/Imap/Client/Base.php - line 2493: Horde_Imap_Client_Base->_fetchWrapper("INBOX", [ "Horde_Ima ... "], [ [ "Horde_I ... ]]) /var/www/html/custom_apps/mail/lib/IMAP/MessageMapper.php - line 874: Horde_Imap_Client_Base->fetch("INBOX", [ "Horde_Ima ... "], [ [ "Horde_I ... ]]) /var/www/html/custom_apps/mail/lib/IMAP/PreviewEnhancer.php - line 87: OCA\Mail\IMAP\MessageMapper->getBodyStructureData([ "OCA\\Mail ... e], "INBOX", [ 85871]) /var/www/html/custom_apps/mail/lib/Service/PreprocessingService.php - line 83: OCA\Mail\IMAP\PreviewEnhancer->process([ "OCA\\Mail\\Account"], [ "OCA\\Mail\\Db\\Mailbox",15], [ [ "OCA\\Ma ... ]]) /var/www/html/custom_apps/mail/lib/BackgroundJob/PreviewEnhancementProcessingJob.php - line 94: OCA\Mail\Service\PreprocessingService->process(1704093392, [ "OCA\\Mail\\Account"]) /var/www/html/lib/public/BackgroundJob/Job.php - line 81: OCA\Mail\BackgroundJob\PreviewEnhancementProcessingJob->run([ 1]) /var/www/html/lib/public/BackgroundJob/TimedJob.php - line 103: OCP\BackgroundJob\Job->start([ "OC\\BackgroundJob\\JobList"]) /var/www/html/lib/public/BackgroundJob/TimedJob.php - line 93: OCP\BackgroundJob\TimedJob->start([ "OC\\BackgroundJob\\JobList"]) /var/www/html/cron.php - line 152: OCP\BackgroundJob\TimedJob->execute([ "OC\\BackgroundJob\\JobList"], [ "OC\\Log"]) ```

[pongraczi]

Anyway, mail seems can access some emails during cron sync, because I can see several new emails are marked as important.
I use Betterbird to use that email account, so, I can see the big red letters. :)

Otherwise mail technically never appears on the webui.

[pongraczi]

Hmmm, I just run a diagnose, some progress:
Account has 1535628 messages in 14316 mailboxes

IMAP capabilities

``` - ACL - ANNOTATE-EXPERIMENT-1 - APPENDLIMIT - BINARY - CATENATE - CHILDREN - COMPRESS - CONDSTORE - CREATE-SPECIAL-USE - DIGEST - ENABLE - ESEARCH - ESORT - ID - IDLE - IMAP4REV1 - LIST-EXTENDED - LIST-METADATA - LIST-MYRIGHTS - LIST-STATUS - LITERAL+ - LOGINDISABLED - MAILBOX-REFERRALS - METADATA - MOVE - MULTIAPPEND - NAMESPACE - NO_ATOMIC_RENAME - OBJECTID - PREVIEW - QRESYNC - QUOTA - RIGHTS - SAVEDATE - SCAN - SEARCH - SORT - SPECIAL-USE - STATUS - THREAD - UIDPLUS - UNSELECT - URLAUTH - WITHIN - X-CREATEDMODSEQ - X-QUOTA - X-REPLICATION - X-SIEVE-MAILBOX - XCONVERSATIONS - XLIST - XMOVE ```

UPDATE: just put the result into the details to save space on screen.

[pongraczi]

Version: 3.5.6
Nextcloud-All-in-one 27.1.5

Still an issue. Not possible to use mail app at all, even when it works with small number of mailboxes it is a nice app.
Instead, management decided to use zimbra, because its web interface just works with the same amount of mailboxes.

[pongraczi]

Is that possible to turn off classification with command occ (using nextcloud-all-in-one) ? thnx

Of course, the mail app does not appear anymore (40+ minutes, I am patient) and I have no chance to turn off this settings (hoping that, it could "solve" this issue in a magic way).

[pongraczi]

Account has 1610179 messages in 14321 mailboxes.

This is too much?

[ChristophWurst]

It's a lot. The largest I have tested is 600k messages.

[pongraczi]

Hmmmm, let me know, how to test, as you can see, here we have a lot of material to test ;)

Anyway, it is a corporate-alike email server with lot of shared folders and tons of messages, on cyrus imap.
I think it is not an uncommon situation in companies and would be nice to get it work with your email app. I really like it, I follow it from the beginning and the improvement is huge.

Maybe you can confirm my intuiton: turning off smart email classification could help something? At least get rid of this error message or something.

Seriously, I try to help you to test any theories you have. Except deleting 14000 mailboxes and 1M messages :)))))

[ChristophWurst]

Maybe you can confirm my intuiton: turning off smart email classification could help something? At least get rid of this error message or something.

Yes, that should help

[pongraczi]

This is my mail app since I added my monster-size-mailbox.
A little hint would be useful, how to turn off that on the mail account (No. 5) using DB sql or something :)

[pongraczi]

Well well well. I got progress.
Using direct database manipulation, I checked that, Show only subscribed mailboxes and now I got a working email app. At least, the mail app is visible and I can navigate as expected.

As the app was not working with defaults, I had to change it in the database directly:

Even with this trick, loading mail app (start or reload the page) takes several minutes.
When I click (enter) on new folders (it was not opened with the mail app yet, so, new) it takes some minutes until it shows up something. Combined with Page not responding (this one will work after some more minutes).

I recommend to enable the show only subscribed folders settings to give a chance to fine tuning the settings using mail app.