(mysql)Database migration difficulty when migrating from digikam 4.* to 5.*

Question

I have ! a decade worth of tagged photographs managed with digikam and my debian stable/testing mix recently upgraded to digikam 5.*.

I now am facing the issue that the (mysql)database holding that labor of love appears to be giving issues when trying to upgrade to the newer data base scheme (v.8).

When starting digikam v5.* pointed at the 4.*-used database backend I get the following:

digikam.dbengine: Loading SQL code from config file "/usr/share/digikam/database/dbconfig.xml"
digikam.dbengine: Checking XML version ID => expected:  3  found:  3
digikam.coredb: Core database: running schema update
digikam.coredb: Core database: have a structure version  7
digikam.coredb: Core database: makeUpdates  7  to  8
digikam.dbengine: Failure executing query:
 ""
Error messages: "QMYSQL: Unable to execute query" "Can't DROP 'Albums_AlbumRoots'; check that column/key exists" 1091 2
Bound values:  ()
digikam.dbengine: Error while executing DBAction [ "UpdateSchemaFromV7ToV8" ] Statement [ "ALTER TABLE Albums\n                                        DROP FOREIGN KEY Albums_AlbumRoots;" ]
digikam.coredb: Core database: schema update to V 8 failed!
digikam.coredb: Core database: cannot process schema initialization

When explicitly trying to use the db migration tool included into 5.* I end up with the error represented in the following image:

Can anyone help with decyphering what's going on here, respectively how to fix it?

Joh

Answer 1

I had the same problem due to some inconsistencies in my old db. After fixing those (mainly deleting some dead references) an upgrade went through. I still seem to have another issue, but probably not related to the db.

This will show your the problems (should all be empty except the root tag):

taken from https://bugs.kde.org/show_bug.cgi?id=355831#c73

select * from Images where album not in (select Albums.id from Albums);
select id, icon from Albums where icon != 0 and icon not in (select id from Images);
select * from Albums where Albums.albumRoot not in (select AlbumRoots.id from AlbumRoots);
select * from ImageHaarMatrix where imageid not in (select id from Images);
select * from ImageInformation where imageid not in (select id from Images);
select * from ImageMetadata where imageid not in (select id from Images);
select * from VideoMetadata where imageid not in (select id from Images);
select * from ImagePositions where imageid not in (select id from Images);
select * from ImageComments where imageid not in (select id from Images);
select * from ImageCopyright where imageid not in (select id from Images);
select * from Tags where pid != 0 and pid not in (select id from Tags);
select id, icon from Tags where icon != 0 and icon not in (select id from Images);
select * from ImageTags where tagid not in (select id from Tags);
select * from ImageTags where imageid not in (select id from Images);
select * from ImageProperties where imageid not in (select id from Images);
select * from ImageHistory where imageid not in (select id from Images);
select * from ImageRelations where subject not in (select id from Images);
select * from ImageRelations where object not in (select id from Images);
select * from ImageTagProperties where imageid not in (select id from Images);
select * from TagProperties where tagid not in (select id from Tags);
select * from ImageTagProperties where tagid not in (select id from Tags);

There is also a repair script to be found at the source.

And I just learned, that 5.2.0 has been released two days ago!

Answer 2

(Having a hard time migrating from Debian Wheezy to Debian Stretch, hope this helps someone...)

This worked for me:

• make sure digiKam is not running

• copy your old configuration file from ~/.kde/share/config/digikamrc to ~/.config/digikamrc

• open your digikam4.db database file with some sqlite editor (e.g. sqlitebrowser)

• edit table AlbumRoots and fix slashes in identifier column (i.e. replace all instances of %2F with /)

  UPDATE AlbumRoots
  SET identifier = replace(identifier, '%2F', '/')
  WHERE identifier LIKE '%%%2F%';
  

• save the database

• cross your fingers (optional, but recommended)

• start digiKam

Good luck!