Bug 189080

digikam is running under openSuSE 11.1 with KDE 4.2.2 from the openSuSE Build Service repository KDE:/KDE4:/Factory:/Desktop/openSUSE_11.1/, architecture is x86_64.

Hum, not reproducible here. Mandriva 2009, x86-32

Gilles Caulier

I have checked again. I have found,that I could save comments to some pictures and not to some other pictures. The reason was, that there have been duplicate entries for the same imageid in database table "imagecomments". The newer entries had lower ID values (column ID is different from column IMAGEID). Obviously the comment shown is that with the highest ID value. After I have removed the wrong comments (they had all the same text from my camera), I could see the comments, I have entered before. I was now also able to change the comments.

Might this be a consequence of converting the database from digikam3.db? The affected pictures have been in the database before the conversion.

Yes, certainly a bug.

Marcel ?

Gilles

May it be that the invalid comment texts can be found in the exif/iptc metadata?
Do you still have an example of the multiple entries in your ImageComments table (with all fields in that table)?

Created attachment 32761 [details]
gzipped compressed xml export of table imagecomments with duplicate imageid's

You're totally right. The duplicate comments were coming from the pictures' EXIF comment fields. Please get the attached table export. Examples for this failure are e.g. starting at imageid 11370. These were the pictures which I wanted to comment when my comments were not accepted.

Ok the cause of this problem is a SQL subtlety that I was not aware of:
"The UNIQUE constraint causes an unique index to be created on the specified columns. All NULL values are considered different from each other and from all other values for the purpose of determining uniqueness, hence a UNIQUE column may contain multiple entries with the value of NULL."
There is a UNIQUE constraint in ImageComments for (imageid, type, language, author). Now you have identical values for imageid, type and language and NULL for author. Would author be '' (the empty string) there would not be duplicate entries.
Now I have to find out how to solve that.

This may be SQL to fix an affected database:

DELETE FROM ImageComments WHERE id IN (SELECT C2.id FROM ImageComments AS C1, ImageComments AS C2 WHERE C1.imageid=C2.imageid AND C1.type=C2.type AND C1.language=C2.language AND C1.author IS NULL AND C2.author IS NULL AND C1.id < C2.id);

(removes all second entries where the UNIQUE enforcement fails because author is null)

Thanks for the query. I have used it to identify the duplicate entries. As sometimes the first entry had the right comment and sometimes the second, I have delete the duplicate entries interactively with knoda/KDE3.

*** Bug 190411 has been marked as a duplicate of this bug. ***

SVN commit 991767 by mwiesweg:

Take into account that a NULL value will prevent a UNIQUE restriction taking effect.
1) In ImageComments, treat an empty author like a null author
2) When upgrading - there will still be users upgrading from 0.9, work around it
   by deleting all affected entries before upgrading the image comments from the old db

Users already affected by this bug need to solve it manually as outlined in the bug report.

BUG: 189080

 M  +3 -1      NEWS  
 M  +9 -4      libs/database/imagecomments.cpp  
 M  +8 -0      libs/database/schemaupdater.cpp  


WebSVN link: http://websvn.kde.org/?view=rev&revision=991767

*** Bug 199482 has been marked as a duplicate of this bug. ***

*** Bug 220903 has been marked as a duplicate of this bug. ***