nextcloud
ImportanceClassifier: More than 1000 expressions in a list are not allowed on Oracle.
Steps to reproduce
- Have more than 1000 mailboxes
- Run classifier
- :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
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", [ "[email protected]"])
/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"])
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.
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.
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.
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).
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 :)))))
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
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 :)
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.