missing indices after activating Mail in NC-AIO

ferdiga commented 4 months ago

Steps to reproduce

install NC AIO
activate Mail app
got to system admin page message displayed: The database is missing some indexes. Due to the fact that adding indexes on big tables could take some time they were not added automatically. By running "occ db:add-missing-indices" those missing indexes could be added manually while the instance keeps running. Once the indexes are added queries to those tables are usually much faster. Missing optional index "mail_messages_strucanalyz_idx" in table "mail_messages". Missing optional index "mail_class_creat_idx" in table "mail_classifiers". Missing optional index "mail_acc_prov_idx" in table "mail_accounts". Missing optional index "mail_alias_accid_idx" in table "mail_aliases". Missing optional index "mail_messages_mb_id_uid_uidx" in table "mail_messages". Missing optional index "mail_smime_certs_uid_email_idx" in table "mail_smime_certificates". Missing optional index "mail_trusted_senders_idx" in table "mail_trusted_senders". Missing optional index "mail_coll_idx" in table "mail_coll_addresses".

Expected behavior

all indices should be created

Actual behavior

missing indices

Mail app version

3.7.2

Mailserver or service

No response

Operating system

AWS ubuntu

PHP engine version

PHP 8.2

Web server

Apache (supported)

Database

PostgreSQL

Additional info

No response

ChristophWurst commented 4 months ago

Is this a new or upgraded Installation?

ferdiga commented 4 months ago

new AIO - it comes without enabled mail - and I enabled it

acetousk commented 4 months ago

I have the same problem

ChristophWurst commented 4 months ago

Just add your +1

Lxeon commented 4 months ago

the same problem, but i delete table oc_mail_messages all index(except primary key); then try occ db:add-missing-indices,

no error but still get the info "Missing optional index "mail_messages_mb_id_uid_uidx" in table "mail_messages"."

and i try to find out added index, but nothing

scebbers commented 4 months ago

I have the same issue on a bare metal install of Nextcloud on a TrueNAS Core/FreeBSD jail using version 29.0.4. Nextcloud shows the message 'Missing optional index "mail_messages_mb_id_uid_uidx" in table "mail_messages"', which persists even after executing occ db:add-missing-indices.

katerasrael commented 4 months ago

+1

joshtrichards commented 2 months ago

nextcloud/server#48148 looks similar.

GinoBadouri commented 1 month ago

It's still happening after upgrading to 30.0.1

Detected some missing optional indices. Occasionally new indices are added (by Nextcloud or installed applications) to improve database performance. Adding indices can sometimes take awhile and temporarily hurt performance so this is not done automatically during upgrades. Once the indices are added, queries to those tables should be faster. Use the command `occ db:add-missing-indices` to add them. Missing indices: "mail_messages_mb_id_uid_uidx" in table "mail_messages", "mail_smime_certs_uid_email_idx" in table "mail_smime_certificates", "mail_trusted_senders_idx" in table "mail_trusted_senders", "mail_coll_idx" in table "mail_coll_addresses"

All plugins are up to date and I've tried to run sudo -u www-data php -d memory_limit=512M occ db:add-missing-indices multiple times, but the warning doesn't go away.

I've checked the mail plugin and I'm still able to send and receive email, so it doesn't seem to hurt basic functionality there.

nextcloud / mail