Discussion:
[digikam] [Bug 401811] New: DB migration (SQLite -> MySQL) fails if images are in trash
Add Reply
Simon Wüllhorst
2018-12-06 00:34:39 UTC
Reply
Permalink
https://bugs.kde.org/show_bug.cgi?id=401811

Bug ID: 401811
Summary: DB migration (SQLite -> MySQL) fails if images are in
trash
Product: digikam
Version: 5.9.0
Platform: Other
OS: Linux
Status: REPORTED
Severity: normal
Priority: NOR
Component: Database-Migration
Assignee: digikam-bugs-***@kde.org
Reporter: ***@simon-wuellhorst.de
Target Milestone: ---

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
--
You are receiving this mail because:
You are watching all bug changes.
Maik Qualmann
2018-12-06 06:49:25 UTC
Reply
Permalink
https://bugs.kde.org/show_bug.cgi?id=401811

Maik Qualmann <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmail.com

--- Comment #1 from Maik Qualmann <***@gmail.com> ---
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
--
You are receiving this mail because:
You are watching all bug changes.
Maik Qualmann
2018-12-06 07:56:57 UTC
Reply
Permalink
https://bugs.kde.org/show_bug.cgi?id=401811

--- Comment #2 from Maik Qualmann <***@gmail.com> ---
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
--
You are receiving this mail because:
You are watching all bug changes.
Simon Wüllhorst
2018-12-06 09:45:43 UTC
Reply
Permalink
https://bugs.kde.org/show_bug.cgi?id=401811

--- Comment #3 from Simon Wüllhorst <***@simon-wuellhorst.de> ---
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
--
You are receiving this mail because:
You are watching all bug changes.
Maik Qualmann
2018-12-06 11:22:08 UTC
Reply
Permalink
https://bugs.kde.org/show_bug.cgi?id=401811

Maik Qualmann <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|REPORTED |RESOLVED
Resolution|--- |FIXED
Version Fixed In| |6.0.0
Latest Commit| |https://commits.kde.org/dig
| |ikam/cc40fc3f5ee0c52c1b3c10
| |03377e5a13e61f8856

--- Comment #4 from Maik Qualmann <***@gmail.com> ---
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
--
You are receiving this mail because:
You are watching all bug changes.
Maik Qualmann
2018-12-06 11:27:36 UTC
Reply
Permalink
https://bugs.kde.org/show_bug.cgi?id=401811

--- Comment #5 from Maik Qualmann <***@gmail.com> ---
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
--
You are receiving this mail because:
You are watching all bug changes.
Loading...