MDB2 Error: constraint violation

[Laze]

Actual 0.3.1

I quite often get these errors:

[10-Dec-2009 08:10:46] MDB2 Error: constraint violation (-3): _doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_mysql_43f1d87d2ce79a0dd5349aa9dea0a7063c855cf83 USING @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10]
[Native code: 1062]
[Native message: Duplicate entry '120-INBOX.msg-3852' for key 2]

[10-Dec-2009 08:10:46 +0100]: DB Error: MDB2 Error: constraint violation Query: _doQuery: [Error message: Could not execute statement] [Last executed query: EXECUTE mdb2_statement_mysql_43f1d87d2ce79a0dd5349aa9dea0a7063c855cf83 USING @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10] [Native code: 1062] [Native message: Duplicate entry '120-INBOX.msg-3852' for key 2]  in /var/www/htdocs/program/include/rcube_mdb2.php on line 627 (GET /?_task=mail&_action=get&_uid=3852&_mbox=INBOX&_part=3)


I have tried the fowlloing:
Truncate both cache and messages tables.
Everything is updated

Could anybody please, please help with this and point me which is causing this ? Its driving me mad!

[Laze]

A bit more data

Code Select Expand

INSERT INTO messages
         (user_id, del, cache_key, created, idx, uid, subject, `from`, `to`, cc, date, size, headers, structure)
         VALUES (?, 0, ?, now(), ?, ?, ?, ?, ?, ?, FROM_UNIXTIME(1260435211), ?, ?, ?)

Values being inserted:

Array
(
    [0] => 104
    [1] => INBOX.msg
    [2] => 4
    [3] => 1808
    [4] => xxxxxxxxxxxxxxx
    [5] => xxxxxxxxxx <[email protected]>
    [6] => xxxxx <[email protected]>
    [7] =>
    [8] => 34777
    [9] => O:14:&quot;iilBasicHeader&quot;:32:{s:2:&quot;id&quot;;s:1:&quot;4&quot;;s:3:&quot;uid&quot;;s:4:&quot;1808&quot;;s:7:&quot;subject&quot;;s:38:&quot;xxxxxxxxxxxxxxxxxxxx&quot;;s:4:&quot;from&quot;;s:30:&quot;&quot;xxxxxxxx&quot; <[email protected]>&quot;;s:2:&quot;to&quot;;s:47:&quot;&quot;xxxxxx&quot; <[email protected]>&quot;;s:2:&quot;cc&quot;;s:0:&quot;&quot;;s:7:&quot;replyto&quot;;s:0:&quot;&quot;;s:11:&quot;in_reply_to&quot;;s:0:&quot;&quot;;s:4:&quot;date&quot;;s:31:&quot;Thu, 10 Dec 2009 09:53:31 +0100&quot;;s:9:&quot;messageID&quot;;s:61:&quot;<[email protected]>&quot;;s:4:&quot;size&quot;;s:5:&quot;34777&quot;;s:8:&quot;encoding&quot;;s:0:&quot;&quot;;s:7:&quot;charset&quot;;s:0:&quot;&quot;;s:5:&quot;ctype&quot;;s:17:&quot;multipart/related&quot;;s:5:&quot;flags&quot;;a:1:{i:0;s:4:&quot;Seen&quot;;}s:9:&quot;timestamp&quot;;s:10:&quot;1260435211&quot;;s:1:&quot;f&quot;;s:0:&quot;&quot;;s:14:&quot;body_structure&quot;;s:637:&quot;(((&quot;text&quot; &quot;plain&quot; (&quot;charset&quot; &quot;iso-8859-1&quot;) NIL NIL &quot;quoted-printable&quot; 3266 113 NIL NIL NIL NIL)(&quot;text&quot; &quot;html&quot; (&quot;charset&quot; &quot;iso-8859-1&quot;) NIL NIL &quot;quoted-printable&quot; 18130 483 NIL NIL NIL NIL) &quot;alternative&quot; (&quot;boundary&quot; &quot;----_=_NextPart_002_01CA7976.3FF2F363&quot;) NIL NIL NIL)(&quot;image&quot; &quot;jpeg&quot; (&quot;name&quot; &quot;image007.jpg&quot;) &quot;<[email protected]>&quot; &quot;image007.jpg&quot; &quot;base64&quot; 2974 NIL NIL NIL &quot;image007.jpg&quot;)(&quot;image&quot; &quot;jpeg&quot; (&quot;name&quot; &quot;image003.jpg&quot;) &quot;<[email protected]>&quot; &quot;image003.jpg&quot; &quot;base64&quot; 6714 NIL NIL NIL &quot;image003.jpg&quot;) &quot;related&quot; (&quot;boundary&quot; &quot;----_=_NextPart_001_01CA7976.3FF2F363&quot; &quot;type&quot; &quot;multipart/alternative&quot;) NIL NIL NIL)&quot;;s:12:&quot;internaldate&quot;;s:26:&quot;10-Dec-2009 09:53:38 +0100&quot;;s:10:&quot;references&quot;;s:0:&quot;&quot;;s:8:&quot;priority&quot;;s:0:&quot;&quot;;s:6:&quot;mdn_to&quot;;s:0:&quot;&quot;;s:8:&quot;mdn_sent&quot;;s:0:&quot;&quot;;s:8:&quot;is_draft&quot;;s:0:&quot;&quot;;s:4:&quot;seen&quot;;s:1:&quot;1&quot;;s:7:&quot;deleted&quot;;s:0:&quot;&quot;;s:6:&quot;recent&quot;;s:0:&quot;&quot;;s:8:&quot;answered&quot;;s:0:&quot;&quot;;s:9:&quot;forwarded&quot;;s:0:&quot;&quot;;s:4:&quot;junk&quot;;s:0:&quot;&quot;;s:7:&quot;flagged&quot;;s:0:&quot;&quot;;s:6:&quot;others&quot;;a:0:{}}
    [10] =>
)


[SKaero]

Does this happen with a fresh install?

[Laze]

Its a freshly upgraded to 0.3.1 - we are unable to do a complete new install - this would remove existing users.

It seems like its trying to insert the same record again.

[SKaero]

Can you test it with a different database?

[Laze]

We have fixed it with a quick and dirty patch - this doesn't work with other databases than MySQL - should probally be  MDB2 - InstallationWiki or a delete before the insert.

The add_message_cache function seems to be bad due to the fact that you can force it to insert into the table without it checking if the data is already present.

But there ie

Code Select Expand

--- program/include/rcube_imap.php      (revision 66)
+++ program/include/rcube_imap.php      (working copy)
@@ -2514,7 +2514,7 @@
     else  // insert new record
       {
       $this->db->query(
-        "INSERT INTO ".get_table_name('messages')."
+        "REPLACE INTO ".get_table_name('messages')."
          (user_id, del, cache_key, created, idx, uid, subject, ".$this->db->quoteIdentifier('from').", ".$this->db->quoteIdentifier('to').", cc, date, size, headers, structure)
          VALUES (?, 0, ?, ".$this->db->now().", ?, ?, ?, ?, ?, ?, ".$this->db->fromunixtime($headers->timestamp).", ?, ?, ?)",
         $_SESSION['user_id'],