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
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
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
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
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
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