Bug 345045 - MYSQL : images not displayed in album
Summary: MYSQL : images not displayed in album
Status: RESOLVED WORKSFORME
Alias: None
Product: digikam
Classification: Applications
Component: Database-Mysql (show other bugs)
Version: 4.4.0
Platform: Debian unstable Linux
: NOR normal
Target Milestone: ---
Assignee: Digikam Developers
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-03-11 11:50 UTC by Vincent Danjean
Modified: 2016-07-23 08:58 UTC (History)
5 users (show)

See Also:
Latest Commit:
Version Fixed In: 5.1.0


Attachments
info extracted from the corrupted database (6.64 KB, text/plain)
2015-06-17 08:37 UTC, Vincent Danjean
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Vincent Danjean 2015-03-11 11:50:09 UTC
Hi,

  Using digikam for a long time, I'm experiencing database corruption from time to time (this is still going on with 4.4.0, the last Debian packaged version). I initially reported the problem and my analysis to the Debian Bug Tracker ( https://bugs.debian.org/756512 ) and maintainers just asked me to report it here. So, here is the copy-paste of my initial bug report (but I confirm the bug is still present in 4.4.0).

===============================

  I'm using digikam for several years to handle all my photos. Regularly,
some images are present in repository on disk, they are counted into the album
list (for example, I see '8 (221)' that tells me there is 221 images in the
album/directory named '8', and it is indeed what there is on filesystem).
However, they are not displayed in the central part of digikam.
  I looked for lots of thing on Internet about this bug. I read and test
lots of things.
  Eventually, I found the java program DigikamDBValidator. I know it is not
maintain anymore and it is not necessarly fully working with recent version of
digikam, but it allows me to start to look into the digikam database.

  And I found several problems.

  And theses problems come back (with other images) after I first corrected
them. So the bug was present into the 4.0.0 digikam version. Upgrading to the
4.1.0 does not fix the database problems (but I do not know (yet) if 4.1.0 will
corrupt the database again)

  For information, I'm using a mysql database for a long time (nearly when it
has been available in the Debian package)


  First, I found several image related tables that refer to non existing
images. For example, I get several rows with the following request :
SELECT ImageComments.id FROM ImageComments LEFT OUTER JOIN Images ON Images.id=ImageComments.imageid WHERE Images.name IS NULL
  I correct them with :
DELETE i FROM ImageComments i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;

  And I had this problem in ImageComments, ImageCopyright, ImageInformation,
ImageMetadata, and ImageTags tables (I not sure I cheked all required tables,
there can be more).


  That said, fixing this does not fix my problem with images not displayed.
I found these image with this request :
SELECT id FROM Images WHERE Images.id NOT IN (SELECT ImageInformation.imageid FROM ImageInformation);
  This are images into the 'Images' table but not into the 'ImageInformation'
table.
  I get the path to these images with the following request :
SELECT Images.id, concat(albumRoot, ":", relativePath, "/" , Images.name) FROM Images JOIN Albums on Images.album=Albums.id WHERE Images.id NOT IN (SELECT ImageInformation.imageid FROM ImageInformation);

  And indeed, the listed images where the ones counted into the album list but
not displayed by digikam.

  I fix this by creating empty lines in the ImageInformation table (but this image ID):
insert into ImageInformation (imageid) SELECT id FROM Images WHERE Images.id NOT IN (SELECT ImageInformation.imageid FROM ImageInformation);

  Starting digikam just after this 'fix' makes all my missing images displayed!
Looking at the database, I needed to execute 'read metadata from images again'
in the album menu (I'm not sure of the exact english text: my digikam is in
french) in the album where I had missing images in order to got the
ImageInformation fully filled.
  Needless to say that executing this entry menu before creating the lines in
the ImageInformation table did not have any effect. It is something I tried
lots of times.

  So, I'm not sure what leads to this database corruption but it is something I
observe several times. And it happens with the 4.x series (ie, I'm sure I fixed
the database while I was already using digikam 4.x and today I needed to fix it
again).

===============================

  Regards,
    Vincent
Comment 1 Scott Kitterman 2015-03-16 17:36:29 UTC
This does look like solid is likely reporting the mounts as bad in this case.
Comment 2 Christoph Feck 2015-05-16 21:30:08 UTC
Scott, you seem to better understand the report than me. Could you please explain where you see badly reported mount points?
Comment 3 Vincent Danjean 2015-06-17 08:35:45 UTC
I just got the problem again with the last digikam version in Debian (4:4.4.0-1.1+b2, ie upstream version 4.4.0)

I just imported photo from a smartphone with the digikam importer in the zz_import/6 subalbum.
Before the operation, digikam correctly shows the 9 images that were already present in zz_import/6.

Then, I did a mass rename (with 'F2' and the rule '[date:yyyyMMdd_hh:mm]_[file]{range:1,}{lower}.[ext]{lower}') of all new photos.
Then, I move all new photos to zz_import/4.
At this point, in digikam, I only see the 9 old images that I did not touch. Correct. But, in fact, digikam tells me there is more image in the album (in the state bar at the bottom) and, indeed, on disk, there is still lots of files. It was between 100 and 200 images (sorry, I did not remember the exact number).

So, I get again my notes about how to fix the databse. I closed digikam and started mysql and type:
mysql> use digikamdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE i FROM ImageComments i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;
Query OK, 189 rows affected (0.02 sec)

mysql> DELETE i FROM ImageCopyright i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;
Query OK, 0 rows affected (0.12 sec)

mysql> DELETE i FROM ImageInformation i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;
Query OK, 142 rows affected (0.11 sec)

mysql> DELETE i FROM ImageMetadata i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;
Query OK, 152 rows affected (0.10 sec)

mysql> DELETE i FROM ImageTags i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;
Query OK, 464 rows affected (0.30 sec)

mysql> select CONCAT(relativePath, '/', name) from Images LEFT OUTER JOIN ImageInformation on Images.id=ImageInformation.imageid LEFT OUTER JOIN Albums on Images.album=Albums.id where ImageInformation.imageid is NULL INTO OUTFILE '/tmp/digikam.log' ;
Query OK, 146 rows affected (0.06 sec)

I will add the /tmp/digikam.log to this bug. You will see that it contains file from /zz_import/6 and /zz_import/4. Files from /zz_import/4 are probably due to a previous database corruption that I did not notice (because there is too much photos in this album from me to notice the difference between the displayed photos and number of photo stated in the status bar). However, photos listed in /zz_import/6 are all photos I just imported/renamed. You can see that some (most) of them have their old name (IMG*) and a few have the new name (*_img_*) after the rename. So, for me, the corruption occurs during the mass rename operation.

Just for the record, for other, I fixed all of this by :
1) touching all these files ($ touch zz_import/6/* )
2) start digikam
3) touching againt all these files
4) restart digikam
(perhaps, it is possible to be more efficient here)
At this point, images where visible again in Digikam.
I then needed to force the reload of all metadata in the zz_import/6 album
I succeed but for the film (no exif metadata...) By chance, my smartphone include the date in the filename, so I've been able to manually fix the date for this file.

I you need more specific information when I see this bug, please just tell me.

  Regards,
    Vincent
Comment 4 Vincent Danjean 2015-06-17 08:37:31 UTC
Created attachment 93207 [details]
info extracted from the corrupted database

See my bug comment to know how this file has been generated
Comment 5 Christoph Feck 2015-06-17 10:01:01 UTC
I got no answer to comment #2, reassigning back to digikam developers.
Comment 6 caulier.gilles 2015-06-17 10:19:55 UTC
Vincent,

If the database is corrupted, did you tried to rebuild it with a new recent digiKam release. Last on is 4.11.0 commit with 90 bugs closed since 4.10.0.

Gilles Caulier
Comment 7 Vincent Danjean 2015-06-17 11:10:43 UTC
  Hi Gilles,

  I'm not sure to understand the version number you tell me about (4.11.0, 4.10.0). It is digikam version itself ? It is an internal version ?
  More over, do you have a document telling me how to do such a rebuild?
  If the rebuild comes only from on-disk files, I'm relunctant. Indeed, my current database contains lots of information that is not duplicated into files. The more obvious is the date of all my films that is only stored into the current database, not in the films themselves.

  That said, I hope you see that, in my last report, my problem comes from files I just imported. Perhaps there is another inconsistency in the database that triggered this behavior but it seems a bit strange to me. If you want that I look into the current database, just tell me. I know how to explore the mysql database. I can verify what you want.

For now, my database seems clean wrt the small consistency checks I do :
mysql> use digikamdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE i FROM ImageComments i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;
Query OK, 0 rows affected (0.02 sec)

mysql> DELETE i FROM ImageCopyright i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;
Query OK, 0 rows affected (0.11 sec)

mysql> DELETE i FROM ImageInformation i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;
Query OK, 0 rows affected (0.10 sec)

mysql> DELETE i FROM ImageMetadata i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;
Query OK, 0 rows affected (0.09 sec)

mysql> DELETE i FROM ImageTags i LEFT OUTER JOIN Images ON Images.id=i.imageid WHERE Images.name IS NULL;
Query OK, 0 rows affected (0.29 sec)

mysql> select CONCAT(relativePath, '/', name) from Images LEFT OUTER JOIN ImageInformation on Images.id=ImageInformation.imageid LEFT OUTER JOIN Albums on Images.album=Albums.id where ImageInformation.imageid is NULL;
Empty set (0.05 sec)


  Regards,
    Vincent
Comment 8 caulier.gilles 2015-06-17 11:28:08 UTC
4.10.0 or 4.11.0 is digiKam version release.

Gilles Caulier
Comment 9 Vincent Danjean 2015-06-17 11:58:35 UTC
Ok. Digikam is a software with lots of dependencies. I will wait for its packaging in Debian (even in experimental) and not try to recompile it myself. I will report back here if I observe the bug again (such as today) in a more recent version. That said, it is a bug I observe since a long time and through several releases.

As a side note (it should probably not be in this bug report), I would very welcome any information telling me how to regenerate thumbnails for films. The 'Refresh' entry in the 'Albums' menu seems to do it for image but not for films.

Regards,
   Vincent
Comment 10 swatilodha27 2016-06-26 22:29:38 UTC
Are you still able to reproduce the problem with digiKam5.0.0-Beta7 ?
Comment 11 caulier.gilles 2016-07-06 20:34:21 UTC
This file still valid using last digiKam 5.0.0 ?

Gilles Caulier
Comment 12 Vincent Danjean 2016-07-06 23:15:50 UTC
  Hi,

  I install digikam from Debian packages but the 5.0.0 is not packaged yet (even in experimental). So I will test and report when it will be packaged (there are too many dependencies for digikam to recompile it locally without messing my system)

  Regards
    Vincent
Comment 13 swatilodha27 2016-07-09 11:42:49 UTC
Vincent,

DigiKam 5.0.0 is out. Please see if you could still reproduce the problem and provide necessary updates, if any.

Thanks
Comment 14 caulier.gilles 2016-07-16 13:28:30 UTC
Vincent,

DK 5.0.0 is packaged for Debian in Philips Johnson repository:

https://launchpad.net/~philip5/+archive/ubuntu/extra

Gilles Caulier
Comment 15 caulier.gilles 2016-07-23 08:38:38 UTC
Swati,

This problem cannot be reproduced with current implementation. I recommend to close it with 5.1.0 release. What do you think ?

Gilles Caulier
Comment 16 swatilodha27 2016-07-23 08:56:36 UTC
Yes. I think this could be closed.  I'll do it.