Error 1062 Duplicate entry 'mail_messages_mb_id_uid_uidx'

ℹ️ Support
, ,
1

Today, I’ve upgraded my NC instance to version 29.0.2.
Upon performing the occ db: add-missing-indices step, I’ve got the following error message:

In ExceptionConverter.php line 62:

An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘14-65501’ for key ‘mail_messages_mb_id_uid_uidx’

In Exception.php line 28:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘14-65501’ for key ‘mail_messages_mb_id_uid_uidx’

In Connection.php line 71:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘14-65501’ for key ‘mail_messages_mb_id_uid_uidx’

db:add-missing-indices [–dry-run]

What could I do to solve this issue? The mail app seems to be working fine. FYI, I use this instance only for me personally so if it could be solved by wiping a db table this would be fine for me.

1 Like
2

Same Problem. Could you solve the issue?

3

Hi,

we’ve added a repair routine for mail to prepare the database for the new index: fix: add repair job to deleted duplicated cached messages by st3iny · Pull Request #9765 · nextcloud/mail · GitHub

Update your mail app to 3.7.2 and try again.

4

Hi, I have the same problem since the migration to Nextcloud 29. How can it be solved ?

sudo -u nextcloud php /var/www/nextcloud/occ db:add-missing-indices
Adding additional mail_messages_mb_id_uid_uidx index to the oc_mail_messages table, this can take some time...

In ExceptionConverter.php line 62:
An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '16-87' for key 'mail_messages_mb_id_uid_uidx'

In Exception.php line 28:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '16-87' for key 'mail_messages_mb_id_uid_uidx'

In Connection.php line 71:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '16-87' for key 'mail_messages_mb_id_uid_uidx'

Nextcloud 29.0.4
Mail app 3.7.5

5

I also have had this same exception come up on the key "mail_messages_mb_id_uid_uidx’. I just upgraded from 28 to 29. I have mail app 3.7.5 also.

In ExceptionConverter.php line 62:

An exception occurred while executing a query: SQLSTATE[23000]: Integrity c
onstraint violation: 1062 Duplicate entry ‘3-69551’ for key ‘mail_messages_
mb_id_uid_uidx’

In Exception.php line 28:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘3-69
551’ for key ‘mail_messages_mb_id_uid_uidx’

In Connection.php line 71:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘3-69
551’ for key ‘mail_messages_mb_id_uid_uidx’

6

Just upgraded to Mail 3.7.6 on NC 29.0.4 and getting this same 3 error messages when running ‘occ db:add-missing-indices’.

The “fix” listed in this link is already included in 3.7.6 “apps/mail/lib/Listener/OptionalIndicesListener.php”:

Looking at the SQL table structure for table mail_messages, the current index is:
mail_messages_mb_id_uid | BTREE | No | No
using
mailbox_id | 5 | A | No |
and
uid | 49003 | A | No |

mail_messages_mb_id_uid_uidx is not a current index.

I’m not sure how to track down what “3-69551” is, but that’s the source of the duplicate entry issue.

7

Just updated to 3.7.7 and the same issue exists. Kinda expected that because the changelog did not show any changes related to this.

Trying to find that fileid and full path in the DB produces zero results:

SELECT CONCAT(oc_storages.id, oc_filecache.path) AS concatenated_path FROM oc_storages INNER JOIN oc_filecache ON oc_storages.numeric_id = oc_filecache.storage WHERE oc_filecache.fileid = 15;

And since it’s such a low fileid of 15, a simple viewing of the table directly and sorting by that column confirms fileid 15 does not exist in oc_filecache table.

I’m at a loss for how to find what this file is, and how to alter it or delete it to allow the indices to be added for the mail app.

I’m not convinced it’s a Mail app issue directly, but I can’t find the source of the duplicate entry. Running “occ db:add-missing-indices -vvv” isn’t helpful in tracking down what “15-6762” is either.

Hopefully someone is able to help with resolving this issue soon.

8

SUCCESS!!!

Out of frustration, I gave up on the precision scalpel approach and went with the brute-force hammer approach instead.

I decided to manually rename the index in oc_mail_messages from “mail_messages_msgid_id” to “mail_messages_msgid_idx”:

ALTER TABLE oc_mail_messages RENAME INDEX mail_messages_msgid_id TO mail_messages_msgid_idx

… and re-run the “occ db:add-missing-indices -vvv” command again.

That gave partial success! All other indexes were added:

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.

But the NC Admin still showed that the mail_messages_msgid_idx index was still missing:

* The database is missing some indexes. ... Missing optional index "mail_messages_msgid_idx" in table "mail_messages".

So I renamed the index from mail_messages_msgid_idx back to mail_messages_msgid_id:

ALTER TABLE oc_mail_messages RENAME INDEX mail_messages_msgid_idx TO mail_messages_msgid_id

… and re-ran the “occ db:add-missing-indices -vvv” command again.

Adding additional mail_messages_msgid_idx index to the oc_mail_messages table, this can take some time...
oc_mail_messages table updated successfully.

SUCCESS!!! Kinda-sorta

The NC admin was STILL showing a missing index for mail_messages_mb_id_uid_uidx"

* The database is missing some indexes. ... Missing optional index "mail_messages_mb_id_uid_uidx" in table "mail_messages".

Running “occ db:add-missing-indices -vvv” did not add the index. Nothing was done.

So inspecting the DB showed that:
mail_messages_msgid_id
mail_messages_msgid_idx
and
mail_messages_mb_id_uid_uidx indexes did all exist.
But mail_messages_mb_id_uid_uid did not exist.

So I went through the same procedure of renaming it from mail_messages_mb_id_uid_uidx to mail_messages_mb_id_uid_uid and re-ran the “occ db:add-missing-indices -vvv” command:

ALTER TABLE oc_mail_messages RENAME INDEX mail_messages_mb_id_uid_uidx TO mail_messages_mb_id_uid_uid

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.

100% SUCCESS!!!

NC admin now shows all indexes added, zero issues, and no entries in the error log since these changes.

Mail is working properly, and I do not see any issues at this point.

I’m sure there’s a better way to handle this, but this worked for me, and I thought I’d share for those of you still encountering this problem and just want it resolved even if the solution isn’t elegant.

9

I did some searching on the database to figure out where the error was coming from. In my database the oc_mail_messages table has an id and a uid. It seems that the id column is unique to each entry, however the uid seems to have muliple duplicates, which are in the same mailbox

MariaDB [nextcloud]> select oc_mail_messages.id,
                         oc_mail_messages.uid,
                         oc_mail_messages.mailbox_id
                         from oc_mail_messages inner join (
                             select uid from oc_mail_messages 
                             group by uid 
                             having count(uid) > 1 
                                  order by uid
                         ) 
                         temp on oc_mail_messages.uid= temp.uid limit 20;
+-------+-------+------------+
| id    | uid   | mailbox_id |
+-------+-------+------------+
| 22426 |     1 |          2 |
| 22427 |     2 |          2 |
| 22428 |     3 |          2 |
| 22429 |     4 |          2 |
| 24432 | 69552 |          3 |
| 24932 | 69552 |          3 |
| 24433 | 69553 |          3 |
| 24933 | 69553 |          3 |
| 24434 | 69554 |          3 |
| 24934 | 69554 |          3 |
| 24435 | 69555 |          3 |
| 24935 | 69555 |          3 |
| 24436 | 69556 |          3 |
| 24936 | 69556 |          3 |
| 24437 | 69557 |          3 |
| 24937 | 69557 |          3 |
| 24438 | 69558 |          3 |
| 24938 | 69558 |          3 |
| 24439 | 69559 |          3 |
| 24939 | 69559 |          3 |
+-------+-------+------------+
20 rows in set (0.026 sec)

Think I could switch the id and the uid without breaking everything?

I realized that all the duplicate uid’s were duplicate emails that must have been added during a race condition. I was able to delete all the duplicates

MariaDB [nextcloud]> delete oc_mail_messages 
    ->   from oc_mail_messages 
    ->     inner join (
    ->       select
    ->         p.id,
    ->         p.uid,
    ->         p.mailbox_id,
    ->         p.message_id,
    ->         ROW_NUMBER()
    ->           over ( partition by p.uid order by p.id) as dup_num 
    ->       from oc_mail_messages p 
    ->         group by p.uid, p.id
    ->     ) dup 
    ->     on oc_mail_messages.id = dup.id and oc_mail_messages.message_id = dup.message_id
    ->     where dup.dup_num >1;
Query OK, 11363 rows affected (1.104 sec)

now, running “occ db:add-missing-indices” succeeds.