Bug 325655

Summary:	MIGRATION : after MySQL to Sqlite database conversion, random tags are assigned to new images
Product:	[Applications] digikam	Reporter:	Thomas Bleher <ThomasBleher>
Component:	Database-Sqlite	Assignee:	Digikam Developers <digikam-bugs-null>
Status:	RESOLVED FIXED
Severity:	normal	CC:	caulier.gilles, richm+kde, swatilodha27
Priority:	NOR
Version:	3.3.0
Target Milestone:	---
Platform:	unspecified
OS:	Linux
Latest Commit:		Version Fixed In:	5.1.0
Sentry Crash Report:

Description Thomas Bleher 2013-10-05 08:49:01 UTC

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

Comment 1 Thomas Bleher 2013-12-07 21:05:34 UTC

To clean up the leftover tags, use the following command: "delete from ImageTags where imageid not in (select id from Images);"

Comment 2 Thomas Bleher 2013-12-07 21:19:25 UTC

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.

Comment 3 caulier.gilles 2016-07-19 06:52:38 UTC

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

Comment 4 Richard Mortimer 2016-07-19 12:31:03 UTC

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

Comment 5 swatilodha27 2016-07-21 17:31:37 UTC

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.

Comment 6 Thomas Bleher 2016-07-26 20:38:36 UTC

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.

Comment 7 caulier.gilles 2016-07-27 06:56:48 UTC

Ok, 

Thanks Thomas for your feedback.

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

Gilles Caulier