-
I recently got the error below on my Nextcloud Administration Overview. I'm not sure what to do about it because the recommended occ action failed. I'd be open to any ideas or further questioning. 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_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". I sshed into my machine and ran: sudo docker exec --user www-data -it nextcloud-aio-nextcloud php occ db:add-missing-indices I then got: Adding additional mail_messages_mb_id_uid_uidx index to the oc_mail_messages table, this can take some time...
In ExceptionConverter.php line 53:
An exception occurred while executing a query: SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index "mail_messages_mb_id_uid_uidx"
DETAIL: Key (mailbox_id, uid)=(23, 4810) is duplicated.
In Exception.php line 28:
SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index "mail_messages_mb_id_uid_uidx"
DETAIL: Key (mailbox_id, uid)=(23, 4810) is duplicated.
In Connection.php line 71:
SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index "mail_messages_mb_id_uid_uidx"
DETAIL: Key (mailbox_id, uid)=(23, 4810) is duplicated.
db:add-missing-indices [--dry-run] If I run: sudo docker exec --user www-data -it nextcloud-aio-nextcloud php occ db:add-missing-indices --dry-run I get: Adding additional mail_messages_mb_id_uid_uidx index to the oc_mail_messages table, this can take some time...
Removing mail_messages_mb_id_uid index from the oc_mail_messages table
oc_mail_messages table updated successfully.
Adding additional mail_smime_certs_uid_email_idx index to the oc_mail_smime_certificates table, this can take some time...
Removing mail_smime_certs_uid_idx index from the oc_mail_smime_certificates table
oc_mail_smime_certificates table updated successfully.
Adding additional mail_trusted_senders_idx index to the oc_mail_trusted_senders table, this can take some time...
Removing mail_trusted_senders_type index from the oc_mail_trusted_senders table
oc_mail_trusted_senders table updated successfully.
Adding additional mail_coll_idx index to the oc_mail_coll_addresses table, this can take some time...
Removing mail_coll_addr_userid_index index from the oc_mail_coll_addresses table
Removing mail_coll_addr_email_index index from the oc_mail_coll_addresses table
oc_mail_coll_addresses table updated successfully.
DROP INDEX mail_trusted_senders_type;
CREATE INDEX mail_trusted_senders_idx ON oc_mail_trusted_senders (user_id, email, type);
DROP INDEX mail_messages_mb_id_uid;
CREATE UNIQUE INDEX mail_messages_mb_id_uid_uidx ON oc_mail_messages (mailbox_id, uid);
DROP INDEX mail_coll_addr_userid_index;
DROP INDEX mail_coll_addr_email_index;
CREATE INDEX mail_coll_idx ON oc_mail_coll_addresses (user_id, email, display_name);
DROP INDEX mail_smime_certs_uid_idx;
CREATE INDEX mail_smime_certs_uid_email_idx ON oc_mail_smime_certificates (user_id, email_address); |
Beta Was this translation helpful? Give feedback.
Replies: 2 comments
-
Duplicate of nextcloud/mail#9810 |
Beta Was this translation helpful? Give feedback.
-
hey everyone! Ran into those pesky "missing index" warnings in Nextcloud running in Docker? Here’s a quick fix: 1. Find Your Container: First up, let's figure out which container is running Nextcloud: docker ps 2. Run the Command: Replace docker exec -u 33 -it nextcloud-container-name php occ db:add-missing-indices 3. Check if It Worked: Confirm everything's sorted by running: docker exec -u 33 -it nextcloud-container-name php occ status Pro Tip: This might take a while for large databases, so maybe run it when your server's not too busy. Oh, and always back up your data before you poke around! If you're setting up for the first time or need more detailed steps on getting Nextcloud up and running with Docker Compose, check out my guide: How to Install Nextcloud Using Docker Compose. Happy tweaking! |
Beta Was this translation helpful? Give feedback.
Duplicate of nextcloud/mail#9810