| Summary: | Tags and TagsTree table is empty after DB migration from sqlite to mysql | ||
|---|---|---|---|
| Product: | [Applications] digikam | Reporter: | Volker <neuntoeter> |
| Component: | Database-Migration | Assignee: | Digikam Developers <digikam-bugs-null> |
| Status: | RESOLVED FIXED | ||
| Severity: | major | CC: | marco.tedaldi, neuntoeter, vivo75+kde |
| Priority: | NOR | ||
| Version: | 1.4.0 | ||
| Target Milestone: | --- | ||
| Platform: | Ubuntu | ||
| OS: | Linux | ||
| Latest Commit: | Version Fixed In: | 2.0.0 | |
| Sentry Crash Report: | |||
| Attachments: |
screenshot showing db migration was successfull
digikam output in console during migration bzip2 compressed |
||
|
Description
Volker
2010-11-30 18:48:08 UTC
Created attachment 53909 [details]
screenshot showing db migration was successfull
Created attachment 53910 [details]
digikam output in console during migration bzip2 compressed
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) *** Bug 267800 has been marked as a duplicate of this bug. *** 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 | 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? 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? (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 ;) 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. 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 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
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. (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). 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 @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 |