Bug 241464 - Error with the postgres backend: "The value of a duplicated key breaks the unique constraint collectiontable_parentandnameindex"
Summary: Error with the postgres backend: "The value of a duplicated key breaks the un...
Status: RESOLVED FIXED
Alias: None
Product: Akonadi
Classification: Frameworks and Libraries
Component: server (show other bugs)
Version: unspecified
Platform: Unlisted Binaries Linux
: NOR normal
Target Milestone: ---
Assignee: Volker Krause
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-06-11 20:28 UTC by Christophe Marin
Modified: 2012-03-11 15:19 UTC (History)
2 users (show)

See Also:
Latest Commit:
Version Fixed In:

Attachments
Add an attachment

Note You need to log in before you can comment on or make changes to this bug.
Description Christophe Marin 2010-06-11 20:28:51 UTC 
Using trunk (akonadiserver rev. 1136839) + Qt 4.6.3 and postgresql 8.4.3

The message is displayed after each interval check:

[/d/kde/inst/kdepim-runtime/bin/akonadi_imap_resource] akonadi_imap_resource_0(8312)/kdepimlibs (kimap) ImapResource::onSelectDone: All fine, asking for all message flags looking for changes
[akonadiserver] interval checking  collection  8 ( "accnt1 - gmail" )
[akonadiserver] interval checking  collection  9 ( "INBOX" )
[akonadiserver] interval checking  collection  12 ( "folder1" )
[akonadiserver] interval checking  collection  13 ( "folder2" )
[akonadiserver] interval checking  collection  20 ( "folder3" )
[akonadiserver] interval checking  collection  10 ( "[Gmail]" )
[akonadiserver] interval checking  collection  14 ( "Brouillons" )
[akonadiserver] interval checking  collection  15 ( "Corbeille" )
[akonadiserver] interval checking  collection  16 ( "Messages envoyés" )
[akonadiserver] interval checking  collection  17 ( "Spam" )
[akonadiserver] interval checking  collection  18 ( "Suivis" )
[akonadiserver] interval checking  collection  11 ( "folder4" )
[/d/kde/inst/kdepim-runtime/bin/akonadi_imap_resource] akonadi_imap_resource_0(8312)/kdecore (KConfigSkeleton) KCoreConfigSkeleton::writeConfig:
[akonadiserver] Error during executing query "INSERT INTO CollectionTable (remoteId, remoteRevision, name, parentId, resourceId, cachePolicyInherit) VALUES (:0, :1, :2, :3, :4, :5) RETURNING id" :  "ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « collectiontable_parentandnameindex »
[akonadiserver] QPSQL: Unable to create query"
[akonadiserver] Error during insertion into table "CollectionTable" "ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « collectiontable_parentandnameindex »
[akonadiserver] QPSQL: Unable to create query"
[akonadiserver] Unable to free statement: ERREUR:  la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc
[akonadiserver] de la transaction
[akonadiserver] 
[/d/kde/inst/kdepim-runtime/bin/akonadi_imap_resource] akonadi_imap_resource_0(8312)/kdepimlibs (kimap) ImapResource::retrieveItems: "/INBOX"
[/d/kde/inst/kdepim-runtime/bin/akonadi_imap_resource] akonadi_imap_resource_0(8312)/kdepimlibs (kimap) ImapResource::triggerExpunge: "INBOX"
[/d/kde/inst/kdepim-runtime/bin/akonadi_imap_resource] akonadi_imap_resource_0(8312)/kdepimlibs (kimap) ImapResource::onSelectDone: integrity:  "INBOX"  should be:  485  current:  485


Translations (I guess we should change the backend settings to avoid having localized messages): 

"ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « collectiontable_parentandnameindex »
-> The value of a duplicated key breaks the unique constraint « collectiontable_parentandnameindex »

ERREUR:  la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc
-> The transaction is canceled, commands will be ignored until the end of the transaction bloc
Comment 1 Christophe Marin 2010-06-17 19:10:36 UTC 
Looks like the message is also displayed when trying to manually download messages in kmail2 (check mail / account)
Comment 2 Christophe Marin 2010-06-17 22:48:17 UTC 
Better translation for the error:

[akonadiserver] Error during executing query "INSERT INTO CollectionTable (remoteId, remoteRevision, name, parentId, resourceId, cachePolicyInherit) VALUES (:0, :1, :2, :3, :4, :5) RETURNING id" :  "ERROR:  duplicate key value violates unique constraint "collectiontable_parentandnameindex"
[akonadiserver] QPSQL: Unable to create query"
[akonadiserver] Error during insertion into table "CollectionTable" "ERROR:  duplicate key value violates unique constraint "collectiontable_parentandnameindex"
[akonadiserver] QPSQL: Unable to create query"
[akonadiserver] Unable to free statement: ERROR:  current transaction is aborted, commands ignored until end of transaction block
[akonadiserver]


which may be what is described there: http://railspikes.com/2009/3/6/duplicate-key-violates-unique-constraint
Comment 3 Christophe Marin 2010-06-20 23:48:46 UTC 
Here's the full query that the server is running: 

[akonadiserver] Executing query "INSERT INTO CollectionTable (remoteId, remoteRevision, name, parentId, resourceId, cachePolicyInherit) VALUES (:0, :1, :2, :3, :4, :5) RETURNING id"
[akonadiserver] ":0" QVariant(QString, "/Junk")
[akonadiserver] ":1" QVariant(QString, "")
[akonadiserver] ":2" QVariant(QString, "Junk")
[akonadiserver] ":3" QVariant(qlonglong, 5)
[akonadiserver] ":4" QVariant(qlonglong, 3)
[akonadiserver] ":5" QVariant(bool, true)
[akonadiserver] Error during executing query "INSERT INTO CollectionTable (remoteId, remoteRevision, name, parentId, resourceId, cachePolicyInherit) VALUES (:0, :1, :2, :3, :4, :5) RETURNING id" :  "ERROR:  duplicate key value violates unique constraint "collectiontable_parentandnameindex"
[akonadiserver] QPSQL: Unable to create query"
[akonadiserver] Error during insertion into table "CollectionTable" "ERROR:  duplicate key value violates unique constraint "collectiontable_parentandnameindex"
[akonadiserver] QPSQL: Unable to create query"
[akonadiserver] Unable to free statement: ERROR:  current transaction is aborted, commands ignored until end of transaction block
[akonadiserver]
Comment 4 Christophe Marin 2010-06-25 00:05:42 UTC 
Looks like it's not postgres specific:
from bug 242698:

[/space/kde/installs/trunk/bin/akonadi_imap_resource]
akonadi_imap_resource_0(7738) setCurrentAuthMode: selected auth mode: "Clear
text"
[/space/kde/installs/trunk/bin/akonadi_imap_resource]
akonadi_imap_resource_0(7738) SetupServer::slotSafetyChanged: serverTest null
[akonadiserver] Database "akonadi" opened using driver "QMYSQL"
[/space/kde/installs/trunk/bin/akonadi_imap_resource]
akonadi_imap_resource_0(7738)/kdepimlibs (kimap) ImapResource::retrieveItems:
".maxtorhof"
[/space/kde/installs/trunk/bin/akonadi_imap_resource]
akonadi_imap_resource_0(7738)/kdepimlibs (kimap) ImapResource::triggerExpunge:
"INBOX.lists.maxtorhof"
[akonadiserver] Error during executing query "INSERT INTO CollectionTable
(remoteId, remoteRevision, name, parentId, resourceId, cachePolicyInherit)
VALUES (:0, :1, :2, :3, :4, :5)" :  "Duplicate entry '112-trash' for key
'CollectionTable_parentAndNameIndex' QMYSQL3: Unable to execute statement"
[akonadiserver] Error during insertion into table "CollectionTable" "Duplicate
entry '112-trash' for key 'CollectionTable_parentAndNameIndex' QMYSQL3: Unable
to execute statement"
[/space/kde/installs/trunk/bin/akonadi_imap_resource]
akonadi_imap_resource_0(7738)/libakonadi
Akonadi::SpecialCollectionsRequestJobPrivate::collectionCreateResult: Failed
CollectionCreateJob. "Unknown error. (Could not create collection)"
[/space/kde/installs/trunk/bin/akonadi_imap_resource]
akonadi_imap_resource_0(7738)/kdepimlibs (kimap)
KIMAP::ExpungeJob::handleResponse: Unhandled response:  * 2815 EXISTS
[/space/kde/installs/trunk/bin/akonadi_imap_resource]
akonadi_imap_resource_0(7738)/kdepimlibs (kimap)
Comment 5 Tobias Koenig 2010-08-02 21:24:06 UTC 
Hej Christophe,

can you still reproduce this bug with a current version?

Ciao,
Tobias
Comment 6 Christophe Marin 2010-08-28 12:01:37 UTC 
Hi Tobias,

Well, I see it when creating the DB at least, from the creation log file:

2010-08-27 15:53:41 CEST akonadi krop ERROR:  duplicate key value violates unique constraint "mimetypetable_name_key"
2010-08-27 15:53:41 CEST akonadi krop STATEMENT:  EXECUTE qpsqlpstmt_10 ('application/x-vnd.akonadi.calendar.journal')

(the error and the statement are probably unrelated). I need to check if this still happens after manually fetching messages.
Comment 7 Christophe Marin 2011-09-13 09:33:53 UTC 
Bah, after switching again to postgres, I see a similar error again

Error during executing query "INSERT INTO CollectionTable (remoteId, remoteRevision, name, parentId, resourceId, cachePolicyInherit) VALUES (:0, :1, :2, :3, :4, :5) RETURNING id" :  "ERROR:  duplicate key value violates unique constraint "collectiontable_parentandnameindex"
DÉTAIL : Key (parentid, name)=(2, \x7472617368) already exists.
QPSQL: Unable to create query"
Error during insertion into table "CollectionTable" "ERROR:  duplicate key value violates unique constraint "collectiontable_parentandnameindex"
DÉTAIL : Key (parentid, name)=(2, \x7472617368) already exists.
QPSQL: Unable to create query"
Comment 8 Christophe Marin 2011-09-13 09:54:24 UTC 
and the query causing that is :

Executing query "INSERT INTO CollectionTable (remoteId, remoteRevision, name, parentId, resourceId, cachePolicyInherit) VALUES (:0, :1, :2, :3, :4, :5) RETURNING id"
":0" QVariant(QString, "trash")
":1" QVariant(QString, "")
":2" QVariant(QString, "trash")
":3" QVariant(qlonglong, 2)
":4" QVariant(qlonglong, 2)
":5" QVariant(bool, true)


the resource with id=2 is the maildir one and

SELECT * FROM collectiontable WHERE name='trash' AND resourceId=2 already 
returns a row :
id=7
remoteid=""
remoterevision=""
name=trash
parentid=2
resourceid=2
[...]
cachepolicyinherit=true
Comment 9 Christophe Marin 2011-09-13 11:29:48 UTC 
and the kmail error I didn't notice before:

kmail2(29009) KMKernel::instanceError:  instance : "akonadi_maildir_resource_0"  was got an error : "Maildir '' for collection '' is invalid."
Comment 10 Christophe Marin 2011-09-14 10:07:05 UTC 
(In reply to comment #9)
> and the kmail error I didn't notice before:
> 
> kmail2(29009) KMKernel::instanceError:  instance : "akonadi_maildir_resource_0"
>  was got an error : "Maildir '' for collection '' is invalid."

from the akonadi side:
AkonadiAgentServer(18886) MaildirResource::maildirForCollection: Got incomplete ancestor chain: Collection ID: 7    remote ID: ""
   name: "trash"
   url: KUrl("akonadi:?collection=7")
   parent: 2 "/home/krop/.local/share/local-mail"
   resource: "akonadi_maildir_resource_0"
   rights: QFlags(0x1|0x2|0x4|0x8|0x10|0x20)
   contents mime type: ("message/rfc822", "inode/directory")
    CachePolicy:
   inherit: true
   interval: -1
   timeout: 1
   sync on demand: true
   local parts: ("ENVELOPE")
    CollectionStatistics:
   count: -1
   unread count: -1
   size: -1
Comment 11 Volker Krause 2012-03-11 15:19:09 UTC 
Fixed in kdepimlibs commit 662bfe163c486146c9e408d0add6fd5fcc415b83.