Bug 383326 - Moving a tag does not update the parent tag correctly
Summary: Moving a tag does not update the parent tag correctly
Status: RESOLVED FIXED
Alias: None
Product: digikam
Classification: Applications
Component: Database-Mysql (show other bugs)
Version: 5.7.0
Platform: Appimage Linux
: NOR major
Target Milestone: ---
Assignee: Digikam Developers
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-08-09 17:59 UTC by Mario Frank
Modified: 2017-11-21 10:43 UTC (History)
3 users (show)

See Also:
Latest Commit:
Version Fixed In: 5.8.0
Sentry Crash Report:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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).