I had some recently added pictures to my collections which were not showing correctly in the album view of Digikam, while they were showing correctly in the file system. In particular I had three folders: A - with 88 CR2 (Canon RAW) files, showing only 81 images B - with 471 JPG, showing only 452 images C - with 3 AVI files, showing 0 files I am not sure what caused this problem, maybe some hang or crash while searching for new pictures. This may also be related to these bugs: https://bugs.kde.org/show_bug.cgi?id=320466 https://bugs.kde.org/show_bug.cgi?id=324134 I tried to move the folders, to rebuild image fingerprints, to rebuild thumbnails, but nothing helped. I then looked into the database (I'm using MySQL) and noticed that missing images did not have a corresponding entry in the ImageInformation table. So probably they are excluded because of a JOIN with this table. To fix it, I simply: 1) added empty rows in ImageInformation with "imageid" corresponding to missing images 2) images then were shown in Digikam 3) I instructed Digikam to re-read data from those images I would expect Digikam to re-create the row in case it does not exist, at least when doing rebuild fingerprint/rebuild thumbnails tasks. I consdier this a major bug, since someone that does not know/want to deal with the database will not be able to make images reappear easily, and may also not notice it, loosing the ability to access some images from Digikam. Reproducible: Always Steps to Reproduce: 1. Find a target image, and find its 'id' from the database 2. Delete the row in ImageInformation having the corresponding 'imageid' 3. Open digikam and look at the album containing the image, image will not show Actual Results: The image is not showing in the album view Expected Results: Image should show in the album view, since it exists in the filesystem. Occurs both on 4.1 (current Debian testing), and 4.3 (current Debian unstable). Actually I tried to upgrade to 4.3 to see if it would solve the poblem, but no luck. Workaround: - In the database, identify "Images" entries corresponding to the missing images - Identify "Images.Id" - For each missing image, add an empty row in ImageInformation table, having "imageid" corresponding to those of missing images, other field may be NULL - Make Digikam re-read the image information of the involved images from the GUI
New digiKam 4.11.0 is available with official PKG installer for OSX. https://www.digikam.org/node/740 Can you reproduce the problem with this release ?
(Used digikam version 5.0.0-beta6) I'm able to reproduce this error using MySQL Server. image is view-able using SQLite.
Using "LEFT JOIN" instead of "INNER JOIN" in the SQL queries involving Images and ImageInformation tables, would be useful here?
Good question. Why it must be different between Mysql ans SQlite for a common statement in database ? Richard, your expertise here will be welcome. Thanks in advance. Gilles Caulier
Richard, Do you see my previous comment ? Thanks in advance Gilles Caulier
Sorry. Had missed this one. I cannot see an obvious reason why there would be a difference between SQLite and MySQL. It should be easy to test the queries from the commandline in both database types to see if they really do return different results. The only possible theory that springs to mind would be related to the fact that in getItemURLsInAlbumByItemDate and getItemURLsInAlbumByItemRating the ImageInformation.rating order by field is not included in the selected columns. I know that some databases (PostgresQL for instance) are very strict about that but MySQL and SQLite tend to be much looser in enforcing that type of SQL behaviour. Whatever of the cause I would certainly be in favour of Swati's suggestion of using a LEFT JOIN instead of an INNER JOIN in the 5 queries that are referencing ImageInformation within them. Of course the calling code would need auditing to ensure that it could cope with those fields having null values if the ImageInformation row is not present.
MySQL and SQLite return the same results. Both do not show images if the entry for that image id is removed from Image Information table.
Created attachment 100320 [details] Patch to fix this issue. I've changed INNER JOIN to LEFT JOIN at several places in the source code to ensure that images are visible in UI, even if entry is removed from Image Information table. Please test and provide feedback.
Swati, In your patch, did you touch the sqlite schema or only mysql one ? The patch is a little bit intrusive. Do you test this patch to see if side effects are introduced in general digiKam use cases ? Gilles Caulier
Richard, This patch from Swati sound acceptable for you ? Gilles Caulier
(In reply to caulier.gilles from comment #9) > Swati, > > In your patch, did you touch the sqlite schema or only mysql one ? I've changed the queries for both MySQL and SQLite in XML schema file, and of course the common queries for both in source code. > The patch is a little bit intrusive. Could you please tell me which part? Do you test this patch to see if side > effects are introduced in general digiKam use cases ? As far as I did, it worked fine for me!
By intrusive, i want mean it touch code in different place. As sqlite code in XML file is touched, the sqlite schema ID need to be updated ? No schema updater need to be written ? Gilles Caulier
(In reply to caulier.gilles from comment #12) > By intrusive, i want mean it touch code in different place. > > As sqlite code in XML file is touched, the sqlite schema ID need to be > updated ? No schema updater need to be written ? Do you think it should be updated? Since it is only a fix for a issue?
Well, if the schema of tables are untouched no, and it sound the case. Only the way to handle data from table with SQL action are touched. But the revision of XML file need to be increased of course. Gilles
Created attachment 100335 [details] Updated patch Okay, I did realize after some testing that I made some unwanted changes in the XML schema file which weren't required. Please take a look at the updated patch, and let me know if it's good enough! Thank you.
In your new patch xml file is untouched. It's the w
In your new patch xml file is untouched, only cpp files. It's wanted ? Gilles Caulier
(In reply to caulier.gilles from comment #17) > In your new patch xml file is untouched, only cpp files. It's wanted ? Yes, 'cause that cpp file decides the images to be displayed in the UI. So, JOIN must be changed here. Does it works well for you too?
I cannot test for the moment as i'm not at home. Ask to Maik if he can test. Gilles Caulier
Swati, I look to this problem and the patch tomorrow. Maik
Yes, the patch works. With this patch, an image is again visible in the album when the image information is missing. After rereading metadata from the image are the image information also available again. I have so far found no negative side effects. We have before release from DigiKam-5.1.0 time to test. Swati, apply the patch. Maik
Thanks a lot Maik for testing. I'll apply the patch and close this entry.