mail
mail copied to clipboard
nextcloud
missing indices after activating Mail in NC-AIO
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
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
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.
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.
I can confirm this behavior with a Nextcloud 30.0.6 installation and latest mail app (4.2.0-rc.3) - and with a PostgreSQL database. I currently switched from MariaDB to PostgreSQL - the warning didnt show up with MariaDB before.
Also, neither occ maintenance:repair --include-expensive nor occ db:add-missing-indices are helping.
I found the culprit. We are trying to replace an optional index. The previous index didn't exist, so nothing was replaced.
The fix can be found at https://github.com/nextcloud/server/pull/51439.
If you create a backup first you may try it out with the help of https://docs.nextcloud.com/server/latest/admin_manual/issues/applying_patch.html#patching-server.
