[digikam] [Bug 325655] New: after database migration, random tags assigned to new images

[Thomas Bleher]

https://bugs.kde.org/show_bug.cgi?id=325655

            Bug ID: 325655
           Summary: after database migration, random tags assigned to new
                    images
    Classification: Unclassified
           Product: digikam
           Version: 3.3.0
          Platform: unspecified
                OS: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: Database
          Assignee: [hidden email]
          Reporter: [hidden email]

I recently converted my digikam database from MySQL to SQLite. Today I imported
some new pictures (by copying them into the appropriate folder and then
starting digikam) and was quite surprised that some images already contained
tags, even though they came directly from my camera, which doesn't assign any
tags.
It seems that my database contains lots of tag entries for non-existing images:
A "select count(*) from ImageTags Left Join Images on imageid=id where id is
Null;" yields "29689" as a result. My guess is that the tags stayed in the
database when the images were deleted. I didn't notice this issue before,
probably because the auto-increment of the database caused the ids not to be
re-used. Apparently this auto-increment status was not transported during
migration, and so old tags were applied to new images.
(I have verified that the entries in the ImageTags table used for the new image
was already present in the old MySQL database, but there was no image with the
corresponding id).

Shouldn't there be a foreign key constraint in the database, so that the
database cannot move to an inconsistent state? At the very least, the database
migration tool should clean the database up, so no random tags are assigned to
new images.

Reproducible: Always

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Thomas Bleher]

https://bugs.kde.org/show_bug.cgi?id=325655

--- Comment #1 from Thomas Bleher <[hidden email]> ---
To clean up the leftover tags, use the following command: "delete from
ImageTags where imageid not in (select id from Images);"

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Thomas Bleher]

https://bugs.kde.org/show_bug.cgi?id=325655

--- Comment #2 from Thomas Bleher <[hidden email]> ---
The problem is worse than described in the original bug report: the ImageTags
table is not the only one affected. I also noticed that ImageMetadata and
ImageInformation contained lots of stray rows (probably other tables too). It
also seems like images get a new id whenever they are moved to a new folder.

The symptoms are as follows: When moving a whole folder to a new parent folder,
the images in the folder suddenly have wrong metadata (like date taken,
thumbnail rotation). This can be corrected by rereading the metadata from disk,
but this is of course very annoying.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=325655

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]
            Summary|after database migration,   |MIGRATION : after MySQL to
                   |random tags assigned to new |Sqlite database conversion,
                   |images                      |random tags are assigned to
                   |                            |new images

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=325655

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Database                    |Database-Migration

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=325655

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Database-Migration          |Database-Sqlite

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=325655

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #3 from [hidden email] ---
Swati,

This problem is certainly due to the famous bug about lft and rgt ID assigned
of tags in Mysql DB.

As this bug is fixed now, a fresh Mysql DB converted to SQlite will not have
this problem.

For older Mysql DB, where ID confusion still exists, i think nothing can be
changed/fixed.

So, the first stage for this bug is to try to convert a large Tags hierarchy
from fresh Mysql DB to Sqlite to see if problem remain.

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=325655

Richard Mortimer <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #4 from Richard Mortimer <[hidden email]> ---
For anyone who has this problem prior to migration to full referential
integrity the SQL in the following attachments (as part of
https://bugs.kde.org/show_bug.cgi?id=355831 ) will help to identify and remove
any bad data in the database.

https://bugs.kde.org/attachment.cgi?id=99905
https://bugs.kde.org/attachment.cgi?id=99906

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=325655

--- Comment #5 from [hidden email] ---
Thomas,

Foreign key constraint is now in DB schema (Current version 5.0.0), as a result
DB can't contain tags for non existing images.So,  I was unable to reproduce
this issue.

Also, I used the SQL query to verify the same:
//
MariaDB [digikam]> select count(*) from ImageTags Left Join Images on
imageid=id where id is Null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)
//

The result was 0 even for ImageMetadata and ImageInformation tables.

Please try with the recent version again, and report updates if any.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=325655

--- Comment #6 from Thomas Bleher <[hidden email]> ---
So I have long ago switched to sqlite and have no intention of setting up MySQL
just to recheck that bug. If you think the bug should no longer happen, it is
OK for me if you just close it.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=325655

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
   Version Fixed In|                            |5.1.0
             Status|UNCONFIRMED                 |RESOLVED
         Resolution|---                         |FIXED

--- Comment #7 from [hidden email] ---
Ok,

Thanks Thomas for your feedback.

I close this file now. Don't hesitate to re-open if necessary.

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel