Getting a lot of Duplicate Entry messages since Roundcube 5.1 (EXECUTE mdb2_statement_mysql_)

[spriebe]

Hi,

i've recently upgraded from 4.1 to 5.1. Now our error.log is full of these Error messages. Sadly i wasn't able to find any solution.

---

[07-Mar-2011 08:14:27] MDB2 Error: constraint violation (-3): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_mysql_5324cc58f9bcb5f77a41951d3104cbca4f90059d0b3 USING @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10]
[Native code: 1062]
[Native message: Duplicate entry '619-INBOX.msg-20671' for key 2]

[07-Mar-2011 08:14:27] MDB2 Error: constraint violation (-3): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_mysql_53255c5114d9df17e28ed0051ac12a654f66e0dd030 USING @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10]
[Native code: 1062]
[Native message: Duplicate entry '619-INBOX.msg-20672' for key 2]
...

---

Stefan

[alec]

TRUNCATE TABLE messages;

[spriebe]

Thanks but i've already done a TRUNCATE TABLE messages. But i get the errors after some time again.

Truncate done 40 minutes ago.

Now again:

[07-Mar-2011 14:11:27] MDB2 Error: constraint violation (-3): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_mysql_41708894fa8aa68844ac3409f5fc8ea7fd6530199b7 USING @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10]
[Native code: 1062]
[Native message: Duplicate entry '619-INBOX.msg-18980' for key 2]

[alec]

Are you running two instances of Roundcube at the same time or sth like that? What IMAP server?

[spriebe]

No just just running one roundcube installation - but for sure there could be multiple users online.

IMAP Server:
/usr/local/dovecot/sbin/dovecot --version
1.0.15

[alec]

I'm writing about the same user logged in to many Roundcube instances on client side (e.g. using two browsers or two PCs). You should upgrade dovecot. 1.0 is really old. You can disable caching too. Finally enable imap_debug and provide the log.

[spriebe]

1.0.15 is the default lenny version - so we'll stay for at least 12 month on it until we upgrade to squeeze.

I cannot check if there is a user with two browsers. So if you say it doesn't have any nagitive influence i'll just disable the logging ;-)

[unicorn]

Faster RC without duplicate INSERT errors

[unicorn]

Replying to spriebe:

Thanks but i've already done a TRUNCATE TABLE messages. But i get the errors after some time again.

Truncate done 40 minutes ago.

Now again:

[07-Mar-2011 14:11:27] MDB2 Error: constraint violation (-3): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_mysql_41708894fa8aa68844ac3409f5fc8ea7fd6530199b7 USING @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10]
[Native code: 1062]
[Native message: Duplicate entry '619-INBOX.msg-18980' for key 2]

We have the same problem. The best, but dirty, solution for us is in the attached file Faster RC without duplicate INSERT errors. May be developers think about refactoring. It's solution when more users work with the same mailbox together on diffrent computers.

[spriebe]

@unicorn
thanks for the patch

Can a dev please look at it and tell us it would make sense to integrate it?

[spriebe]

@unicorn
sadly i still get the same message after your patch.

[08-Mar-2011 09:37:46] MDB2 Error: constraint violation (-3): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_mysql_5179845da4077be8e25c3aa743ecd4263a6b2ce4a91 USING @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10]
[Native code: 1062]
[Native message: Duplicate entry '619-INBOX.msg-19241' for key 2]

[unicorn]

Replying to spriebe:

@unicorn
sadly i still get the same message after your patch.

[08-Mar-2011 09:37:46] MDB2 Error: constraint violation (-3): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_mysql_5179845da4077be8e25c3aa743ecd4263a6b2ce4a91 USING @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10]
[Native code: 1062]
[Native message: Duplicate entry '619-INBOX.msg-19241' for key 2]

After our patch you get this message only one time, when you have clear messages cache and more users open the same mailbox together, because RC reads messages to memory and than inserts to messages table. RC inserts same data from memory to the table, when more users open mailbox and you have clear messages table. Our patch is solution for next using of mailbox from the messages cache table, it's important for us. I recommend to set the message_cache_lifetime param in the main config for example to 100d.

[unicorn]

For dev: main mistake is that you using message number instead message uid when you compare messages list contrary to messages cache table. Users can't use the same mailbox together with this version of RC, because recursion and deleting by message number is the wrong way. Try delete the oldest message when you have in a mailbox thousands messages in one browser and open this mailbox in other browser.

[alec]

The patch is wrong. Makes that message UID is inserted to idx column. Which has no sense for me.

[alec]

I see two solutions for this issue which is caused by user sessions race.

1. Use sql transactions and lock table for the whole sync_header_index procedure.
2. Ignore such errors and don't send them to the log.

[unicorn]

Replying to alec:

I see two solutions for this issue which is caused by user sessions race.

1. Use sql transactions and lock table for the whole sync_header_index procedure.

This isn't good idea, because if javascript failed and process locks table, what process does unlock table?

2. Ignore such errors and don't send them to the log.

This is very, very bad, you have big bug with inserting rows, lets we say and you want hide this errors? RC needs to create new way to synchronize messages. Our solution with replacing idx by uid is only fallback solution. Think about synchronize messages by UID, because this ID is unique for every folders and compare e.g. with message hash, message-ID or other way to check if message is still same. Actual version of RC is very slow when you have thousands messages in the mailbox, because your process deletes every rows from db table and again reads messages from IMAP and inserts to DB, when I delete the oldest message from messages list. If you don't do it, then RC is not useful for massive usage. Don't throw away this chance.

[alec]

The messages caching is being rewritten in devel-mcache branch and almost ready for testing. I've minimized the possibility to encounter this issue. I still don't think it is a big issue. Inserting a row which already exists shouldn't be considered as a bug and for performance reasons we don't want to check if it exists before the insert.

[alec]

The branch has been merged in [80152b33].