Re: Browsing by tag sometimes shows items with alternative tag

[Mario Frank]

Hey Swati,

that's okay. I anyway assume that there was some incomplete porting from dbconfig to in-code queries.
The function takes the parentid as parameters but the parameter is commented out in the function definition in the cpp file.
Also, two queries need the parentid as input but do not get it and the syntax for the parameters is :parentid, so it looks
as dbconfig syntax to me.

And in fact, I was able to fix the problem partially by making the two queries which cannot be used in a prepared statement (LOCK and UNLOCK)
direct SQL queries. Giving the parentid as parameter for the two queries that need it also reduced the database corruption after I moved a corrupted tag
forth and back. But it was not really stable as other (parent) tags were also corrupted and I could not see why.

I think we will have two possibilities:
1) A DB update for MySQL which contains only a functionality to correct the lft and rgt values according to the parentid property
2) A DB update switching to another approach of handling tag trees for MySQL. There is an open discussion on dev list already.
3) I didn't mention this possibility as a normal user would not like it. The users could create a new database. But here, there is a probability of loss of information as MySQL support is not yet stable.

It would be interesting, for what functionality the lft and rgt values are needed. It is possible, that there is a much better approach.
If I have the time today, I will take a look at it.

Cheers,
Mario

Am 10.08.2017 um 10:58 schrieb Swati Lodha:

Hi 

I'm currently at home for 2 days due to some family reasons and I don't have my laptop for work. I'll definitely take a look at the Bugzilla entry and get back to you.

Apologies for inconvenience. 

Regards

ᐧ

On Thu, Aug 10, 2017 at 1:33 AM, Mario Frank <[hidden email]> wrote:

Hey Gilles,

I tried to fix it but I do not see a fast way.

Also, I do not any sense in the structure of the table. I have the
following problems:

Why do both the entries in Tags and TagsTree have a pid?
Why are the columns lft and rgt needed? Isn't the parent of a tag
completely sufficient? As I know, there is also an ON DELETE CASCADE
for MySQL and it is possible to reference the parent as foreign key to
the same
table.

Swati,
Can you take a look at the following bug? I think you are more fit in
MySQL than I am.
https://bugs.kde.org/show_bug.cgi?id=383326

Cheers,
Mario



Am 09.08.2017 um 20:17 schrieb Gilles Caulier:
> Mario,
>
> You can ask to Swati. She improve the Mysql dtatabase backend/schema
> while summer 2016, and i'm sure that she review this kind of problem
> already. She has also certainly a Mysql DB reday to test.
>
> Gilles
>
> 2017-08-09 20:07 GMT+02:00 Mario Frank <[hidden email]>:
>> Hey Meku,
>>
>> I think this is a bug. But I
>>
>> 1) do not have a MySQL (and currently not the possibility to create one) to
>> test and confirm
>>
>> 2) Thus also cannot fix the problem shorthand.
>>
>> I opened a bugzilla entry with your and mine findings here:
>>
>> https://bugs.kde.org/show_bug.cgi?id=383326
>>
>> I hope one of the other devs can fix it faster than I.
>>
>> Best,
>>
>> Mario
>>
>>
>> "Am 09.08.2017 um 14:36 schrieb meku:
>> Studying the database there appears to be some inconsistent data in the Tags
>> table causing this.
>>
>> Tag B probably used to be a child of Tag A, but some time ago Tag B was
>> moved to the root.
>>
>> Tag B's 'pid' is '0' because it a child of the root node, but the 'lft' and
>> 'rgt' fields have not been updated so still fall within the range of Tag A's
>> 'lft' and 'rgt'.
>>
>> If browsing using Tags with "Include Tag Sub-Tree" enabled relies on the
>> 'lft' and 'rgt' fields then it would explain the inconsistent behaviour I'm
>> seeing - and it appears it only happens to Tags that have been moved in the
>> hierarchy.
>>
>> Is this a bug, and is there a mysql query to repair it?"
>>
>> Am 08.08.2017 um 15:45 schrieb Mario Frank:
>>
>> Hi Meku,
>>
>> this is odd, as when the disabling solves the problem, the unexpected items
>> must have
>>
>> a tag that is a child tag of A. At least for sanity reasons.
>>
>> The source code itself looks good. Gilles, can you take a look at the
>> definition of
>>
>> getItemIDsInTagRecursive in the dbconfig? For me, it looks correct, too.
>>
>> Can you send a screenshot or something like this?
>>
>> Cheers,
>> Mario
>>
>> Am 08.08.2017 um 15:10 schrieb meku:
>>
>> Hi Mario,
>>
>> Yes, disabling "Include Tag Sub-Tree" hides the unexpected items.
>>
>> Tag B is not a child of Tag A and the unexpected items do not have Tag A or
>> any of A's child tags.
>>
>> I can work with mysql queries.
>>
>> On 8 August 2017 at 22:55, Mario Frank <[hidden email]> wrote:
>>> Hi Meku,
>>>
>>> can you check the following?
>>>
>>> In menu View: is the option "include tag tree" (I do not know how it is
>>> called exactly in the English version, I have the German one)
>>>
>>> checked? If yes, images with subtags are also shown in the browser and
>>> may have tag B.
>>>
>>> Otherwise, do you have experience with MySQL queries?
>>>
>>> Cheers,
>>>
>>> Mario
>>>
>>>
>>> Am 08.08.2017 um 14:46 schrieb meku:
>>>> I am seeing some inconsistent behaviour when browsing items by Tags.
>>>>
>>>> Selecting a single tag (A) in the tree shows all items with tag (A)
>>>> but also unrelated items with tag (B).  So far I have only discovered
>>>> this happening for a few specific tags, but it continues happening
>>>> after restarting Digikam.
>>>>
>>>> If I use the Search for tag (A) then it shows the expected results,
>>>> it's only within the Tag browser that this issue is occurring.
>>>>
>>>> Using Digikam 5.7 appbundle with mysql.
>>
>>

--