[Digikam-devel] [Bug 258409] Tags and TagsTree table is empty after DB migration from sqlite to mysql

[Volker]

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





--- Comment #12 from Volker <neuntoeter arcor de>  2011-05-08 18:41:00 ---
I changed the following in
"/home/volker/Private/.kde/share/apps/digikam/mysql.conf"
---
#character_set_server=latin1
#collation_server=latin1_general_ci
collation_server=utf8_unicode_ci
character_set_server=utf8
log=mysql.full
---

Btw. when I delete the /home/volker/Private/.kde/share/apps/digikam directory,
then the mysql.conf file is created automatically. The head of the file looks
as follows - is this ok?:
---
#
# Global Akonadi MySQL server settings,
# These settings can be adjusted using $HOME/.config/akonadi/mysql-local.conf
#
# Based on advice by Kris Köhntopp <kris at mysql.com>
#
---

After changing the collation and character set of the server the schema is
created and the migration of the database starts. 
Unfortunately I then got a "duplicate key error". In mysql the concerning
images had an entry in ImageInformation but not in Images. I deleted the Image
from digikam by moving it to trash. 

Then there was another duplicate key error. It turned out that there were two
albums with the same name - one was written with captial letters the other not.
The problem is probably the column relativePath of type longtext in table
Albums. Longtext is not case sensitive in mysql. It could also be that this
error is a result of my collation change in mysql.conf.

The Step "Copy Tags" ran more than half an hour. The mysql log shows me
following statements (again and again).
[...]
            4 Query  DELETE FROM TagsTree
            4 Query  REPLACE INTO TagsTree
            SELECT node.id, parent.pid
            FROM Tags AS node, Tags AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            ORDER BY parent.lft
            4 Query  DELETE FROM TagsTree
            4 Query  REPLACE INTO TagsTree
            SELECT node.id, parent.pid
            FROM Tags AS node, Tags AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            ORDER BY parent.lft
            4 Query  DELETE FROM TagsTree
            4 Query  REPLACE INTO TagsTree
            SELECT node.id, parent.pid
            FROM Tags AS node, Tags AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            ORDER BY parent.lft
            4 Query  DELETE FROM TagsTree
            4 Query  REPLACE INTO TagsTree
            SELECT node.id, parent.pid
            FROM Tags AS node, Tags AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            ORDER BY parent.lft
            4 Query  DELETE FROM TagsTree
            4 Query  REPLACE INTO TagsTree
            SELECT node.id, parent.pid
            FROM Tags AS node, Tags AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            ORDER BY parent.lft
            4 Query  DELETE FROM TagsTree
            4 Query  REPLACE INTO TagsTree
            SELECT node.id, parent.pid
            FROM Tags AS node, Tags AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            ORDER BY parent.lft
[...]
I'm not sure if I interpred correct, but it seems that "DELETE FROM TagsTree"
always deletes the previous "Replace into".

After the Tags are copied I get the "Database copied successfully".

The table Tags and TagsTree now contain entries.
mysql> select count(*) from TagsTree;
+----------+
| count(*) |
+----------+
|      863 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from Tags;
+----------+
| count(*) |
+----------+
|      241 |
+----------+
1 row in set (0.00 sec)

The number of rows for the table Tags are the same in mysql and sqlite.
In mysql I have more entries in TagsTree (863) than in sqlite (680)

I don't know if this is a problem.

After the migration I changed the database to mysql. From the first view this
looks ok. Tags are shown and e.g. found by searches.

-- 
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.