[Bug 283323] New: a new label _Digikam_root_tag_ is created every time

[Leo-2]

https://bugs.kde.org/show_bug.cgi?id=283323

           Summary: a new label _Digikam_root_tag_ is created every time
           Product: digikam
           Version: 2.1.1
          Platform: Ubuntu Packages
        OS/Version: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: Face Detection
        AssignedTo: [hidden email]
        ReportedBy: [hidden email]


Created an attachment (id=64205)
 --> (http://bugs.kde.org/attachment.cgi?id=64205)
what happends

Version:           2.1.1 (using KDE 4.7.1)
OS:                Linux

Every time I start to tag some new face a new label "_Digikam_root_tag_" is
created.


Reproducible: Always

Steps to Reproduce:
just start to tac some face. Should be in the tag "Persons" or the localized
version.
If I rename it, a new "_Digikam_root_tag_" is created and the new label with
the name is set in it


Expected Results:  
the name of the tagged person should be in "persons" root label

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Marcel Wiesweg]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #1 from Marcel Wiesweg <marcel wiesweg gmx de>  2011-10-04 19:35:46 ---
Francesco, I believe this is a MySQL specific tag?

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa-2]

https://bugs.kde.org/show_bug.cgi?id=283323


Francesco Riosa <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #2 from Francesco Riosa <francesco+kde pnpitalia it>  2011-10-04 20:07:39 ---
(In reply to comment #0)
Leo if you're running on mysql database could you provide the output of the
following query?

SELECT * FROM Tags ORDER BY id LIMIT 10;



(In reply to comment #1)
> Francesco, I believe this is a MySQL specific tag?

yes it is:
file: data/database/dbconfig.xml.cmake
Revision 24ee26d9

the tree algorithm used by the mysql schema need one and only one root, thus
I'm inserting/replacing the tag with id = 0 in the tags table.

it should be executed _only_ by the "CreateTriggers" DBACtion.

SELECT
  @minLeft := IF(ISNULL(MIN(lft)), 1, MIN(lft)-1),
  @maxRight := IF(ISNULL(MAX(rgt)), 2, MAX(rgt)+1)
FROM Tags
WHERE id >= 0 AND pid>=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

REPLACE INTO Tags
  (id, pid, name, icon, iconkde, lft, rgt)
VALUES
(0, -1, '_Digikam_root_tag_', 0, NULL, @minLeft, @maxRight )

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Leo-2]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #3 from Leo <sir_kalot yahoo it>  2011-10-05 07:35:09 ---
(In reply to comment #2)
> (In reply to comment #0)
> Leo if you're running on mysql database could you provide the output of the
> following query?
>
> SELECT * FROM Tags ORDER BY id LIMIT 10;

Here is it

mysql> SELECT * FROM Tags ORDER BY id LIMIT 10;
+----+------+----------------------------+------+---------+-----+-----+
| id | pid  | name                       | icon | iconkde | lft | rgt |
+----+------+----------------------------+------+---------+-----+-----+
|  0 |   -1 | _Digikam_root_tag_         |    0 | NULL    |   1 | 178 |
|  1 |    0 | Cham                       |    0 | NULL    | 176 | 177 |
|  2 |    0 | _Digikam_Internal_Tags_    |    0 | NULL    | 132 | 175 |
|  3 |    2 | Need Resolving History     |    0 | NULL    | 173 | 174 |
|  4 |    2 | Need Tagging History Graph |    0 | NULL    | 171 | 172 |
|  5 |    2 | Original Version           |    0 | NULL    | 169 | 170 |
|  6 |    2 | Current Version            |    0 | NULL    | 167 | 168 |
|  7 |    2 | Intermediate Version       |    0 | NULL    | 165 | 166 |
|  8 |    2 | Version Always Visible     |    0 | NULL    | 163 | 164 |
|  9 |    2 | Scanned for Faces          |    0 | NULL    | 161 | 162 |
+----+------+----------------------------+------+---------+-----+-----+
10 rows in set (0.00 sec)

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=283323


Dávid Andor <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #4 from Dávid Andor <david-andor kozpontiagy hu>  2011-10-10 17:15:53 ---
I have same problem.
Here is my output
mysql [localhost] {andor} (digikam) > SELECT * FROM Tags ORDER BY id LIMIT 10;
+----+------+-------------------------+------+---------+-----+-----+
| id | pid  | name                    | icon | iconkde | lft | rgt |
+----+------+-------------------------+------+---------+-----+-----+
|  0 |   -1 | _Digikam_root_tag_      |    0 | NULL    |   1 |  94 |
|  1 |    0 | _Digikam_root_tag_      |    0 | NULL    |  44 |  93 |
|  2 |    1 | _Digikam_root_tag_      |    0 | NULL    |  45 |  92 |
|  3 |    2 | Zsani                   |    0 | NULL    |  90 |  91 |
|  4 |    0 | _Digikam_Internal_Tags_ |    0 | NULL    |   2 |  43 |
|  5 |    4 | Color Label None        |    0 | NULL    |  41 |  42 |
|  6 |    4 | Color Label Red         |    0 | NULL    |  39 |  40 |
|  7 |    4 | Color Label Orange      |    0 | NULL    |  37 |  38 |
|  8 |    4 | Color Label Yellow      |    0 | NULL    |  35 |  36 |
|  9 |    4 | Color Label Green       |    0 | NULL    |  33 |  34 |
+----+------+-------------------------+------+---------+-----+-----+
10 rows in set (0.00 sec)

mysql [localhost] {andor} (digikam) > SELECT * FROM TagsTree ORDER BY id LIMIT
10;
+----+------+
| id | pid  |
+----+------+
|  0 |   -1 |
|  1 |    0 |
|  2 |    1 |
|  3 |    2 |
|  4 |    0 |
|  5 |    4 |
|  6 |    4 |
|  7 |    4 |
|  8 |    4 |
|  9 |    4 |
+----+------+

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa-2]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #5 from Francesco Riosa <francesco+kde pnpitalia it>  2011-10-10 17:42:52 ---
(In reply to comment #4)

> I have same problem.
> Here is my output
> mysql [localhost] {andor} (digikam) > SELECT * FROM Tags ORDER BY id LIMIT 10;
> +----+------+-------------------------+------+---------+-----+-----+
> | id | pid  | name                    | icon | iconkde | lft | rgt |
> +----+------+-------------------------+------+---------+-----+-----+
> |  0 |   -1 | _Digikam_root_tag_      |    0 | NULL    |   1 |  94 |
> |  1 |    0 | _Digikam_root_tag_      |    0 | NULL    |  44 |  93 |
> |  2 |    1 | _Digikam_root_tag_      |    0 | NULL    |  45 |  92 |
> |  3 |    2 | Zsani                   |    0 | NULL    |  90 |  91 |

Please could you provide also the output of:
show create table Tags;

if there is an unique index on the `id` coulumn the 3 "_Digikam_root_tag_"
should not be there, that, or the "no auto value on zero" statement failed

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #6 from Dávid Andor <david-andor kozpontiagy hu>  2011-10-10 18:05:12 ---
CREATE TABLE `Tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) DEFAULT NULL,
  `name` longtext CHARACTER SET utf8 NOT NULL,
  `icon` int(11) DEFAULT NULL,
  `iconkde` longtext CHARACTER SET utf8,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=47 DEFAULT CHARSET=latin1


show create table TagsTree;
CREATE ALGORITHM=UNDEFINED DEFINER=`andor`@`localhost` SQL SECURITY DEFINER
VIEW `TagsTree` AS select `Tags`.`id` AS `id`,`Tags`.`pid` AS `pid` from `Tags`

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa-2]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #7 from Francesco Riosa <francesco+kde pnpitalia it>  2011-10-10 18:31:11 ---
(In reply to comment #6)

> CREATE TABLE `Tags` (
>   `id` int(11) NOT NULL AUTO_INCREMENT,
>   `pid` int(11) DEFAULT NULL,
>   `name` longtext CHARACTER SET utf8 NOT NULL,
>   `icon` int(11) DEFAULT NULL,
>   `iconkde` longtext CHARACTER SET utf8,
>   `lft` int(11) NOT NULL,
>   `rgt` int(11) NOT NULL,
>   PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=47 DEFAULT CHARSET=latin1
>
>
> show create table TagsTree;
> CREATE ALGORITHM=UNDEFINED DEFINER=`andor`@`localhost` SQL SECURITY DEFINER
> VIEW `TagsTree` AS select `Tags`.`id` AS `id`,`Tags`.`pid` AS `pid` from `Tags`

bad, the record is created by the following statements:

-- 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8<

SELECT
    @minLeft := IF(ISNULL(MIN(lft)), 1, MIN(lft)-1),
    @maxRight := IF(ISNULL(MAX(rgt)), 2, MAX(rgt)+1)
FROM Tags
WHERE id >= 0 AND pid>=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

REPLACE INTO Tags
    (id, pid, name, icon, iconkde, lft, rgt)
    VALUES
    (0, -1, '_Digikam_root_tag_', 0, NULL, @minLeft, @maxRight )
SET SQL_MODE=@OLD_SQL_MODE;

-- 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8< 8<

The first select store in two variables the bounds of our tags tree.

The set stmt tell mysql not to assign a value to the id field even if it's
zero, by default it increment a primary key column if the value inserted is
zero or NULL

the replace stmt then should only alter (or insert) the record 0, never
inserting a duplicate one.

Could I abuse of your kindeness and ask to manually run the statements between
the scissors manually from a console client (phpmyadmin is not an option, it
sometimes mangle @vars)

Does it add a new record?
If yes could you try replacing the replace with the following two?

INSERT IGNORE INTO Tags
    (id, pid, name, icon, iconkde, lft, rgt)
    VALUES
    (0, -1, '_Digikam_root_tag_', 0, NULL, @minLeft, @maxRight );

UPDATE Tags
SET
  lft = @minLeft,
  rgt = @maxRight
WHERE id = 0
;

Also the mysql version could be interesting.

just a closing note:
do _not_ delete the records from the table, it would broke the tree structure,
do it from digikam if possible, if not we need to work on it too

thanks,
Francesco

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Leo-2]

https://bugs.kde.org/show_bug.cgi?id=283323


Leo <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #8 from Leo <sir_kalot yahoo it>  2011-10-10 18:41:01 ---
Sorry, but at the moment I cannot help you anymore, since I blocked by this bug
:(
https://bugs.launchpad.net/ubuntu/+source/digikam/+bug/871597

Hope to help you as soon as possible

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=283323


qwexy <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #9 from qwexy <qwq121qwq gmx net>  2011-10-14 12:28:47 ---
KDE 4.7.2 Digikam 2.2.0 Opensuse, mysql

Every tag hierarchy written to xmp into the photos starts with
_Digikam_root_tag_/

That will lead to a doubled tag list in xmp, if the tag list allready exists
without _Digikam_root_tag_/ at the beginning.

Can someone confirm this?

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Dmitri Popov]

https://bugs.kde.org/show_bug.cgi?id=283323


Dmitri Popov <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #10 from Dmitri Popov <dmpop9mm yahoo com>  2011-10-14 17:19:25 ---
I can confirm that using MySQL as digiKam's back end results in a parallel
_Digikam_root_tag_/ tagging structure. _Digikam_root_tag_ tags can be removed
using the following command:

exiftool -xmp:LastKeywordXMP= -xmp:TagsList= -xmp:hierarchicalSubject= *.jpg

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Martin Burnicki-2]

https://bugs.kde.org/show_bug.cgi?id=283323


Martin Burnicki <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]
                   |                            |t




--- Comment #11 from Martin Burnicki <martin burnicki burnicki net>  2011-10-17 11:15:21 ---
I'm also observing this with the DK 2.2.0 rpm package for openSUSE 11.4. I've
been using mysql as database with DK 1.1.9 and have just upgraded to DK 2.2.0.

BTW, automatically upgrading the database from schema 5 to schema 6 still
fails. I had to drop all tables in the DK database and let them be created from
scratch to get DK working with mysql. :-(

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Marcel Wiesweg]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #12 from Marcel Wiesweg <marcel wiesweg gmx de>  2011-10-29 15:23:21 ---
I would like to direct commenters' focus to Francesco's comment #7, it seems he
needs active help to debug this problem.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Michael Holtermann-3]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #13 from Michael Holtermann <linux michael-holtermann de>  2011-10-30 11:33:21 ---
Created an attachment (id=65041)
 --> (http://bugs.kde.org/attachment.cgi?id=65041)
Console output of mysql-statements from comment #7

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Michael Holtermann-3]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #14 from Michael Holtermann <linux michael-holtermann de>  2011-10-30 11:35:07 ---
(sorry for double post, this comments comment #13...)

Hi Francesco,

I've executed the statements, please see the attached console output.

Do you missed a semicolon after the 3rd statement (REPLACE INTO)? Executing at
is produces an error (line 33 ff.)

As far as I see the REPLACE does not produce a new node, so I did not execute
the INSERT IGNORE statement.

Please let me know, if any further information is needed.

My Mysql is 5.1.58-1ubuntu1.

Kind regards,
Michael

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa-2]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #15 from Francesco Riosa <francesco+kde pnpitalia it>  2011-11-01 21:50:03 ---
I've reproduced this with mysql-5.1.59, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' is not
working, or it's not executed in the same connection as the insert.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa-2]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #16 from Francesco Riosa <francesco+kde pnpitalia it>  2011-11-01 21:56:23 ---
(In reply to comment #15)
> I've reproduced this with mysql-5.1.59, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' is not
> working, or it's not executed in the same connection as the insert.

P.S. if consecutive query inside a <dbaction> tag are not executed in the same
connection problem will arise in all query containing @variables, because these
are local to the connection.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa-2]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #17 from Francesco Riosa <francesco+kde pnpitalia it>  2011-11-01 22:42:30 ---
Question to someone experiencing the problem:

show create table Tags ;

look like this?

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) DEFAULT NULL,
  `name` longtext NOT NULL,
  `icon` int(11) DEFAULT NULL,
  `iconkde` longtext,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8

I'm interested expecially in the "AUTO_INCREMENT=2147483648" part

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa-2]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #18 from Francesco Riosa <francesco+kde pnpitalia it>  2011-11-02 00:53:57 ---
http://commits.kde.org/digikam/33dc55daee96bc68d4e7d9a286fcf7e78efd523e
first part of the fix

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Leo-2]

https://bugs.kde.org/show_bug.cgi?id=283323





--- Comment #19 from Leo <sir_kalot yahoo it>  2011-11-02 15:19:20 ---
Since the last update I'm running now the ubuntu version 2:2.1.1-0ubuntu1+kde
4.7.2 (whic does not have this problem anymore)
But if it could help
CREATE TABLE `Tags` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `pid` int(11) DEFAULT NULL,
 `name` longtext CHARACTER SET utf8 NOT NULL,
 `icon` int(11) DEFAULT NULL,
 `iconkde` longtext CHARACTER SET utf8,
 `lft` int(11) NOT NULL,
 `rgt` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=latin1

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel