Bug 383326

Summary:	Moving a tag does not update the parent tag correctly
Product:	[Applications] digikam	Reporter:	Mario Frank <mario.frank>
Component:	Database-Mysql	Assignee:	Digikam Developers <digikam-bugs-null>
Status:	RESOLVED FIXED
Severity:	major	CC:	elle, kde.bugs, metzpinguin
Priority:	NOR
Version:	5.7.0
Target Milestone:	---
Platform:	Appimage
OS:	Linux
Latest Commit:	https://commits.kde.org/digikam/74ea34d9fa0247a91c341ee99e6f001caa8020c3	Version Fixed In:	5.8.0
Sentry Crash Report:

Description Mario Frank 2017-08-09 17:59:18 UTC

From Nabble:
http://digikam.1695700.n4.nabble.com/Browsing-by-tag-sometimes-shows-items-with-alternative-tag-td4703595.html

The user experienced a tag in tag view containing an image that has neither this tag nor a child tag assigned. The image was only shown if images from the subtags are configured to be shown, too.

After analysing the database values for the tags that were assigned to the image, he found out, that the lft and rgt values were in the range of the tag in which he was seeing the image. He assumes that he has moved the really assigned tag some time ago.

After looking at the code, I assume there is a bug in the case of MySQL databases.

In TagDragDropHandler::dropEvent, the call CoreDbAccess().db()->moveTag(newParentTag) is emitted if a MySQL database is configured. This is done in order to update the lft and rgt values as this is not done by triggers, opposed to QSLite.

But in CoreDB::moveTag the parameter is never used, as it is commented out.

I can not test it as I do not have a MySQL database. Also, I am not sure how to fix it without the possibility for testing.

I rated the severity as major as the database is corrupted.

Comment 1 Mario Frank 2017-08-09 19:04:35 UTC

I got a small MySQL database to work right now and was able to reproduce

the corruption. I even got an error message in the trace:

"

digikam.dbengine: Prepare failed!
digikam.dbengine: Failure executing query:
 "LOCK TABLE Tags WRITE;"
Error messages: "QMYSQL3: Der Befehl konnte nicht initialisiert werden" "This command is not supported in the prepared statement protocol yet" 1295 2

"

So there definitely is a problem and as I see it, this will have not worked for a longer time. Looks like an incomplete migration from dbconfig to source code.

I will try to fix it.

Comment 2 meku 2017-08-10 00:18:54 UTC

Looking at the trace there are two consecutive MySQL errors:

"
digikam.dbengine: Prepare failed!
digikam.dbengine: Failure executing query:
 "LOCK TABLE Tags WRITE;"
Error messages: "QMYSQL3: Unable to prepare statement" "This command is not supported in the prepared statement protocol yet" 1295 2
Bound values:  ()
digikam.dbengine: Prepare failed!
digikam.dbengine: Failure executing query:
 "UNLOCK TABLES;"
Error messages: "QMYSQL3: Unable to prepare statement" "This command is not supported in the prepared statement protocol yet" 1295 2
Bound values:  ()
digikam.general: Delete Shortcut assigned to tag  1272
"

Comment 3 Mario Frank 2017-08-10 07:39:03 UTC

Hey meku,

yes, I saw these errors yesterday, too. Getting rid of the errors is easy but does not solve the main problem that lft and rgt are not updated properly.

If noone does solve the problem before I do, I will try again to solve it tomorrow.

Comment 4 Maik Qualmann 2017-11-10 18:37:22 UTC

Git commit 74ea34d9fa0247a91c341ee99e6f001caa8020c3 by Maik Qualmann.
Committed on 10/11/2017 at 18:35.
Pushed by mqualmann into branch 'master'.

fix updating lft/rgt columns in the Tags table with MySQL
Related: bug 386606
FIXED-IN: 5.8.0

M  +0    -11   app/dragdrop/tagdragdrop.cpp
M  +26   -10   data/database/dbconfig.xml.cmake.in
M  +10   -12   libs/database/coredb/coredb.cpp
M  +0    -7    libs/database/coredb/coredb.h

https://commits.kde.org/digikam/74ea34d9fa0247a91c341ee99e6f001caa8020c3

Comment 5 meku 2017-11-21 10:43:12 UTC

How can I repair this corrupt lft/rgt data in the Tags table without clearing the database?

Re-importing all the metadata would cause loss of details for all the videos in the database due to Bug #375570 (XMP sidecar files are not read for videos when importing metadata).