Bug 401811

Summary:	DB migration (SQLite -> MySQL) fails if images are in trash
Product:	[Applications] digikam	Reporter:	Simon Wüllhorst <mail>
Component:	Database-Migration	Assignee:	Digikam Developers <digikam-bugs-null>
Status:	RESOLVED FIXED
Severity:	normal	CC:	metzpinguin
Priority:	NOR
Version First Reported In:	5.9.0
Target Milestone:	---
Platform:	Other
OS:	Linux
Latest Commit:	https://commits.kde.org/digikam/cc40fc3f5ee0c52c1b3c1003377e5a13e61f8856	Version Fixed In:	6.0.0
Sentry Crash Report:

Description Simon Wüllhorst 2018-12-06 00:34:39 UTC

Hi folks,

I tried to migrate my digiKam database from SQLite to MySQL. Unfortunately the migration process every time failed during migrating the table "ImageInformation" by displaying the following error message:

"Fehler beim Konvertieren der Datenbank.
Details: Cannot add or update a child row: a foreign key constraint fails (`digikam core`.`image information`, CONSTRAINT `ImageInformation_Images` FOREIGN KEY (`imageid`) REFERENCES `images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)"

Since the table "Images" is migrated before this error occures I assumed that all images are imported properly to this table. However this assumption was faulty: There are a lot of entries in the SQLite table "images" that are not shown in the migrated MySQL table.

By taking a closer look onto the missing entries it turns out all missing Images-entries have been in trash before.

After reviewing the involved parts of the source code I was able to determine the reason for this behavior:

- When a image is moved to trash the value of album is set to NULL (see: https://cgit.kde.org/digikam.git/tree/libs/database/coredb/coredb.cpp?id=2eaa680e12ab9c033767a8cd47355c4337523a88#n4570 )
- During the migration only images assigned to an existing album are migrated (see: https://cgit.kde.org/digikam.git/tree/data/database/dbconfig.xml.cmake.in?id=2eaa680e12ab9c033767a8cd47355c4337523a88#n622 and https://cgit.kde.org/digikam.git/tree/data/database/dbconfig.xml.cmake.in?id=2eaa680e12ab9c033767a8cd47355c4337523a88#n1684 )
- Since there is no album associated to the id NULL all images located in trash aren't imported.

I don't now whether there is any particular reason for filtering out images that are not associated to an album during migration. However reverting the respective commit (see: https://cgit.kde.org/digikam.git/commit/?id=2eaa680e12ab9c033767a8cd47355c4337523a88 ) is fixing the issue.

STEPS TO REPRODUCE
1. Move an image to trash.
2. Start DB-migration (e. g. SQLite-> MySQL)
3. Wait until the error message occurs.

Greetings,
Simon

Comment 1 Maik Qualmann 2018-12-06 06:49:25 UTC

I can not reproduce the problem here with digiKam-6.0.0-beta3. But even with 5.9.0 the problem should not occur. We use "INSERT IGNORE" to insert the record, if the image id does not exist, the record is ignored. That should never fail.

The fact that the images are "cleaning up" is an important part of the migration, so as not to take on any legacies.

Which database do you use? MySQL or MariaDB? Which version?

Maik

Comment 2 Maik Qualmann 2018-12-06 07:56:57 UTC

I just see that the database migration is broken by a change (image => item) at the moment in the git/master version, i fix this today.

Maik

Comment 3 Simon Wüllhorst 2018-12-06 09:45:43 UTC

Hi Maik,
you're totally right. Sorry for filing this issue too hasty. I'm using a MySQL 5.6 Server instance. However the patchlevel was before 5.6.30. So it was affected to this issue: https://bugs.mysql.com/bug.php?id=78853

This fully explains the unexpected behavior so this bug report can be closed. Sorry for that.

Greetings,
Simon

Comment 4 Maik Qualmann 2018-12-06 11:22:08 UTC

Git commit cc40fc3f5ee0c52c1b3c1003377e5a13e61f8856 by Maik Qualmann.
Committed on 06/12/2018 at 11:21.
Pushed by mqualmann into branch 'master'.

fix broken database migration in the git/master version
FIXED-IN: 6.0.0

M  +2    -1    NEWS
M  +4    -4    core/libs/database/coredb/coredbcopymanager.cpp

https://commits.kde.org/digikam/cc40fc3f5ee0c52c1b3c1003377e5a13e61f8856

Comment 5 Maik Qualmann 2018-12-06 11:27:36 UTC

Thanks for the feedback. Good to know that this bug existed in MySQL. Just as a note, it's not official, but we only recommend and test with MariaDB.

Maik