Bug 386606

The modified preorder tree traversal fields Tags.lft & Tags.rgt become invalid when you move a tag in the tags hierarchy.

Example: assume the 4 tags Monkey, Lifesaver, blue, red with the following hierarchy

- Monkey
- Lifesaver -> blue
- Lifesaver -> red

When you select the tag "Lifesaver", all pictures with the tags blue and red show up. Now you move tag "blue" to the tag root, such that you have the following hierarchy

- Monkey
- blue
- Lifesaver -> red

When you select the tag "Lifesaver", also the picture with tag "blue" is selected, which is a bug

The example above is used in the script digikam_with_sample_db from https://github.com/githubkusi/digikam_controlled_environment. The repo contains some handy tools for database investigations in a controlled, repeatable environment

The reason for the bug are invalid columns Tags.lft/rgt. These columns are not set correctly when moving tags (maybe there are other problematic situations).

looking at current master, I see that there is a move_tagstree for sqlite, but not for mysql in the file dbconfig.xml.cmake.in. This is probably the issue. 

Anyways, adding mysql support for moving tags wouldn't heal an already broken mysql db. I assume that basically everybody using a mysql backend for a few years has a broken tags search functionality by now, which can only be solved by recalculating the lft/rgt fields from scratch. I've written a script which recalculates proper traversal tree columns and works with the example above

https://github.com/githubkusi/digikam_rebuild_mptt

The code is not yet bullet-proof (no db locking during the fix, no sanity checks), but something like this should go into the db maintenance tools.

The issue was already reported here: https://bugs.kde.org/show_bug.cgi?id=338050
which was fixed in 5.1. However, the issue is still present in 5.6 for me.