Bug 258409 - Tags and TagsTree table is empty after DB migration from sqlite to mysql
Summary: Tags and TagsTree table is empty after DB migration from sqlite to mysql
Status: RESOLVED FIXED
Alias: None
Product: digikam
Classification: Applications
Component: Database-Migration (show other bugs)
Version: 1.4.0
Platform: Ubuntu Linux
: NOR major
Target Milestone: ---
Assignee: Digikam Developers
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-11-30 18:48 UTC by Volker
Modified: 2017-07-25 10:36 UTC (History)
3 users (show)

See Also:
Latest Commit:
Version Fixed In: 2.0.0


Attachments
screenshot showing db migration was successfull (84.09 KB, image/png)
2010-11-30 19:00 UTC, Volker
Details
digikam output in console during migration bzip2 compressed (2.92 KB, application/octet-stream)
2010-11-30 19:03 UTC, Volker
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Volker 2010-11-30 18:48:08 UTC
Version:           1.4.0 (using KDE 4.5.1) 
OS:                Linux

I executed the database migration tool (sqlite to internal mysql) in digikam. The tool runs without errors (process indicator runs till 100%).

After the migration I can connect to the mysql database. Unfortunately the Tags and the TagsTree table are empty. So after the migration all Tags are lost.

I have tried this twice - both times the tables were empty.



Reproducible: Always

Steps to Reproduce:
run the db migration tool in digikam from sqlite to mysql

Actual Results:  
the Tags and TagsTree tables are empty after db migration.

Expected Results:  
the Tags and TagsTree tables should be filled in mysql by the db migration tool
Comment 1 Volker 2010-11-30 19:00:27 UTC
Created attachment 53909 [details]
screenshot showing db migration was successfull
Comment 2 Volker 2010-11-30 19:03:36 UTC
Created attachment 53910 [details]
digikam output in console during migration bzip2 compressed
Comment 3 Volker 2010-11-30 19:04:10 UTC
Here is what the mysql looks like after migration:

mysql> show tables;
+-------------------+
| Tables_in_digikam |
+-------------------+
| AlbumRoots        |
| Albums            |
| DownloadHistory   |
| ImageComments     |
| ImageCopyright    |
| ImageHaarMatrix   |
| ImageInformation  |
| ImageMetadata     |
| ImagePositions    |
| ImageProperties   |
| ImageTags         |
| Images            |
| Searches          |
| Settings          |
| Tags              |
| TagsTree          |
+-------------------+
16 rows in set (0.00 sec)

mysql> select * from Tags;
Empty set (0.00 sec)

mysql> select * from TagsTree;
Empty set (0.00 sec)
Comment 4 caulier.gilles 2011-03-06 18:20:11 UTC
*** Bug 267800 has been marked as a duplicate of this bug. ***
Comment 5 Volker 2011-05-01 11:20:33 UTC
I have now reproduced the same issue with digikam 1.9.0 under KDE 4.6.2. (Kubuntu 11.04)

I have used the database migration tool to migrate from sqlite to mysql. The migration shows no error.

The Tags and TagsTree tables are empty. The other tables e.g. Albums contain entries.

mysql> select * from TagsTree;
Empty set (0.00 sec)

mysql> select * from Tags;
Empty set (0.00 sec)

mysql> select * from Albums;
+-----+-----------+-----------------------------------------------------------------------------------------+------------+---------+---------------------+------+
| id  | albumRoot | relativePath                                                                            | date       | caption | collection          | icon |
+-----+-----------+-----------------------------------------------------------------------------------------+------------+---------+---------------------+------+
|   1 |         1 | /albums                                                                                 | 2008-09-07 | NULL    | NULL                | NULL |
|   2 |         1 | /albums/sonstige                                                                        | 2008-09-07 | NULL    | NULL                | NULL |
|   3 |         1 | /albums/tiere                                                                           | 2008-09-07 | NULL    | NULL                | NULL |
|   4 |         1 | /albums/Birding                                                                         | 2008-09-07 |         | Natur               | NULL |
|   5 |         1 | /albums/Personen                                                                        | 2008-09-07 | NULL    | NULL                | NULL |
Comment 6 Francesco Riosa 2011-05-06 01:42:48 UTC
I've tested this with digikam 2.0 compiled from git sources, and the patch from bug #271924 .
I'm unable to reproduce the bug, the `tags` table is populated with default tags and user defined ones.

The Table `TagTree` is populated, one tag with `id` 25 and `pid` 0 does not exists in mysql table

The Table `TagProperties` is populated, however some tags which exist in the sqlite version are not present in the mysql one, namely:
4;person
5;unknowPerson
10;tagKeyboardShortcut
25;tagKeyboardShortcut
only those with `property` "internalTag" are present in the mysql version

The differences DON'T AFFECT tags I've applied to photos, they are all there.

Volker do you care to retest with git version and the mentioned patch?
Comment 7 Volker 2011-05-06 21:03:30 UTC
Hi Francesco, 

I'm a bit confused. Whe I look in my sqlite database with a sqlite browser, I don't see a table TagProperties. Is this maybe the problem?

volker@kiste:~/Fotos$ sqlite3 digikam4.db 
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT name FROM sqlite_master
   ...> WHERE type='table'
   ...> ORDER BY name;
AlbumRoots
Albums
DownloadHistory
ImageComments
ImageCopyright
ImageHaarMatrix
ImageInformation
ImageMetadata
ImagePositions
ImageProperties
ImageTags
Images
Searches
Settings
Tags
TagsTree
sqlite> 


I'd be willing to test with the patched git version. I there a howto for this?
Will this have impact for the rest of kde?

regards, 

Volker

(In reply to comment #6)
> I've tested this with digikam 2.0 compiled from git sources, and the patch from
> bug #271924 .
> I'm unable to reproduce the bug, the `tags` table is populated with default
> tags and user defined ones.
> 
> The Table `TagTree` is populated, one tag with `id` 25 and `pid` 0 does not
> exists in mysql table
> 
> The Table `TagProperties` is populated, however some tags which exist in the
> sqlite version are not present in the mysql one, namely:
> 4;person
> 5;unknowPerson
> 10;tagKeyboardShortcut
> 25;tagKeyboardShortcut
> only those with `property` "internalTag" are present in the mysql version
> 
> The differences DON'T AFFECT tags I've applied to photos, they are all there.
> 
> Volker do you care to retest with git version and the mentioned patch?
Comment 8 Francesco Riosa 2011-05-06 22:04:40 UTC
(In reply to comment #7)
> Hi Francesco, 
> 
> I'm a bit confused. Whe I look in my sqlite database with a sqlite browser, I
> don't see a table TagProperties. Is this maybe the problem?

Hi, possibly not present in previous version, and I'll look at that in future but for now if you want to test the git version that would be the best thing.

[snip]

> I'd be willing to test with the patched git version. I there a howto for this?
> Will this have impact for the rest of kde?

Should have no impact, just be sure to backup your $HOME/.kde*/ , expecially .kde/share/{apps/digikam,config/digikamrc} (or better setup a new test user).
Also backup the digikam database

there is a tutorial at http://www.digikam.org/drupal/download?q=download/GIT
but if you want to install as user (which is less invasive) follow these steps:

8< 8< 8< 8< 8< 8< 8<
cd # go @ home
git clone http://anongit.kde.org/digikam-software-compilation digikam-sc
cd digikam-sc
# the following a bunch of things namely:
# digikam libkface libkmap kipi-plugins libkexiv2 libkdcraw
# libkipi libksane libmediawiki digikam-doc kipi-plugins-doc
# it's ~ 300MB download take some coffe
./download-repos
# if you already have all dependancies installed you're ready to build it:
mkdir build
cd build
cmake \
    -DCMAKE_INSTALL_PREFIX=${HOME}/usr \
    -DSYSCONF_INSTALL_DIR=${HOME}/etc \
    -DCMAKE_BUILD_TYPE=Debugfull \
    -DCMAKE_INSTALL_DO_STRIP=OFF \
    .. \
&& make -j2 \
&& make install
# time for another coffe
8< 8< 8< 8< 8< 8< 8<

To be able to run digikam or showfoto from ${HOME}/usr you need to set some environment variables and to rebuild some caches, I've a script that does it for me:

8< 8< 8< 8< 8< 8< 8<
#! /bin/bash

export DIGIKAM_DIR=~/usr
export PATH=$DIGIKAM_DIR/bin:$PATH
export LD_LIBRARY_PATH=$DIGIKAM_DIR/lib:$LD_LIBRARY_PATH
export KDEDIR=$DIGIKAM_DIR
export KDEDIRS=$KDEDIR
export XDG_DATA_DIRS=$XDG_DATA_DIRS:$DIGIKAM_DIR/share
# update KDE's system configuration cache
kbuildsycoca4
# start app
digikam $@
8< 8< 8< 8< 8< 8< 8<

kbuildsycoca4 is long and not really needed if system isn't changed from latest kbuildsycoca4 run, but better safe than sorry. The same script apply for other programs, replacing the last line "digikam $@" as appropriate.

time for enjoing the new splashscreens committed some hours ago ;)
Comment 9 Francesco Riosa 2011-05-06 22:12:40 UTC
missed an important step: 
after `./download-repos`

8< 8< 8< 8< 8< 8< 8<
wget "http://bugsfiles.kde.org/attachment.cgi?id=59678" -O dbconfig.patch
patch -p0 < dbconfig.patch # may be wise to use --dry-run to test
8< 8< 8< 8< 8< 8< 8<

the patch or a variant of it will be soon committed and this step will not be needed any more.
Comment 10 Volker 2011-05-08 13:29:36 UTC
Hi Francesco, 

thanks for your detailed howto! 
After some struggle with the dependencies, I managed to install the patched digikam 2.0 b6 under my home directory.

Digikam starts up fine. I started the db migration tool immediately after startup. The first connection error (to mysql) I got was due to apparmor (I have ~/.kde linked to ~/Private/.kde). I disabled the mysqld apparmor profiles and still got a connection error when clicking on "Check DB connection". 
I then deleted the existing mysql db under ~/.kde/share/apps/digikam.

After clicking "Check DB connection" the mysql database was recreated again under ~/.kde/share/apps/digikam and the database connection test was successful.

Unfortunately there is an error when I start the db migration. The progressbar shows 7% and a message pops up "Error while creating the database schema".

Console output shows the following: 
volker@kiste:~$ digikam_git.sh 
kbuildsycoca4 running...
kbuildsycoca4(2825) KConfigGroup::readXdgListEntry: List entry Categories in "/usr/share/applications/im-switch.desktop" is not compliant with XDG standard (missing trailing semicolon). 
kbuildsycoca4(2825) KConfigGroup::readXdgListEntry: List entry Categories in "/usr/share/applications/gpsdrive.desktop" is not compliant with XDG standard (missing trailing semicolon). 
kbuildsycoca4(2825) KConfigGroup::readXdgListEntry: List entry Categories in "/usr/share/applications/cryptkeeper.desktop" is not compliant with XDG standard (missing trailing semicolon). 
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.
QInotifyFileSystemWatcherEngine::addPaths: inotify_add_watch failed: Datei oder Verzeichnis nicht gefunden
QFileSystemWatcher: failed to add paths: /home/volker/.config/ibus/bus

(digikam:2828): GStreamer-CRITICAL **: gst_debug_add_log_function: assertion `func != NULL' failed                                                                                  
digikam(2828)/digikam (core) Digikam::ImageInfoJob::slotResult: Failed to list url:  ""                                                                                             
digikam(2828)/digikam (core) Digikam::ImageInfoJob::slotResult: Failed to list url:  ""                                                                                             
QSqlDatabasePrivate::removeDatabase: connection 'MigrationToDatabase175311056' is still in use, all queries will cease to work.                                                     
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.                                                                   
QSqlDatabasePrivate::removeDatabase: connection 'MigrationToDatabase175311056' is still in use, all queries will cease to work.                                                     
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.                                                                   
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.                                                                   
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.                                                                   
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.                                                                   
QSqlDatabasePrivate::addDatabase: duplicate connection name 'PrivilegesCheckDatabase172208384', old connection removed.                                                             
QSqlDatabasePrivate::addDatabase: duplicate connection name 'PrivilegesCheckDatabase172208384', old connection removed.                   

Any clue? 

regards, 

Volker
Comment 11 Volker 2011-05-08 16:57:14 UTC
Hi, 
I investigated a bit more...

I turned on mysql full logging. The last statements are the following:

------
[...]
                    2 Query     CREATE TABLE IF NOT EXISTS TagProperties
                            (tagid INTEGER,
                             property TEXT CHARACTER SET utf8,
                             value LONGTEXT CHARACTER SET utf8)
                    2 Query     CREATE TABLE IF NOT EXISTS ImageTagProperties
                            (imageid INTEGER,
                             tagid INTEGER,
                             property TEXT CHARACTER SET utf8,
                             value LONGTEXT CHARACTER SET utf8)
                    2 Query     COMMIT
                    2 Query     BEGIN WORK
                    2 Query     CALL create_index_if_not_exists('Images','dir_index','album')
                    2 Query     COMMIT
                    2 Quit
-------

I tried to execute the "create index" manually to see what happens. When I do this I get a collation error:
-------
volker@kiste:~$ mysql --socket=/home/volker/Private/.kde/share/apps/digikam/db_misc/mysql.socket
Your MySQL connection id is 5
Server version: 5.1.54-1ubuntu4-log (Ubuntu)
mysql> use digikam;
mysql> SET NAMES Utf8; 
Query OK, 0 rows affected (0.00 sec)
mysql> CALL create_index_if_not_exists('Images','dir_index','album');
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
------- 

I'm not sure if this is the cause for the error in digikam.

I will look into it a bit more...

regards, 

Volker
Comment 12 Volker 2011-05-08 18:41:00 UTC
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@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.
Comment 13 Francesco Riosa 2011-05-08 22:56:57 UTC
(In reply to comment #12)
great work, I already encountered problems with collations and fixed part of them, your tests show that create_index_if_not_exists still need to be made more robust or collations forced.

"DELETE FROM TagsTree" as is delete all elements of TagsTree, not very nice, need to find where it is and debug it.

Even more important, where things need to be case sensitive another collation should be used possibly utf8_bin, but still need to investigate it deeply.
But this must be checked also with digikam for windows :(

-----------
If you use external database remember to add 
lower_case_table_names = 1
to the [mysqld] section, I don't know if it's really needed but may help (I would remove that need in the future if possible).
Comment 14 Francesco Riosa 2011-05-09 00:57:03 UTC
note to self:
ALTER TABLE albumroots DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE albums DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE customidentifiers DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE downloadhistory DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE filepaths DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imagecomments DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imagecopyright DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imagehaarmatrix DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imagehistory DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imageinformation DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imagemetadata DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imagepositions DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imageproperties DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imagerelations DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE images DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imagetagproperties DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE imagetags DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE searches DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE settings DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE tagproperties DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE tags DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE tagstree DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE thumbnails DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE uniquehashes DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
or similar
Comment 15 Francesco Riosa 2011-05-09 17:00:27 UTC
@bug_id = 258409
@bug_status = RESOLVED
@resolution = FIXED

Git commit ec7ceeea0d2587abb9f1bdd17109d4903998e943 by Francesco Riosa.
Committed on 09/05/2011 at 14:20.
Pushed by riosa into branch 'master'.

more resilient database upgrade and startup

All statements are made resilient to a partially upgraded database.
- CREATE TABLE are done with IF NOT EXISTS
- TRIGGERs are dropped and re-created
- a new stored procedure is added "create_index_if_not_exists"
  the procedure check in information_schema if the index exists,
  if not does create it
- all INDEXes are created using "create_index_if_not_exists"

BUG: 271924
BUG: 258409
BUG: 257183
BUG: 267733
BUG: 262231
BUG: 233770

M  +90   -56   data/database/dbconfig.xml.cmake     

http://commits.kde.org/digikam/ec7ceeea0d2587abb9f1bdd17109d4903998e943