[digikam] [Bug 350574] New: MIGRATION: MySQL to SQLite fails

[mhoppstaedter]

https://bugs.kde.org/show_bug.cgi?id=350574

            Bug ID: 350574
           Summary: MIGRATION: MySQL to SQLite fails
           Product: digikam
           Version: unspecified
          Platform: Other
                OS: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: Database
          Assignee: [hidden email]
          Reporter: [hidden email]

Created attachment 93722
  --> https://bugs.kde.org/attachment.cgi?id=93722&action=edit
screenshot from error message

I want to convert my MySQL database to SQLite. But after a long time of
conversion the migration breaks up with error 'Fehler beim Konvertieren der
Datenbank. Details: UNIQUE constraint failed: Tags.name, Tags.pid'.

I've started DK from command line but there is no hint what went wrong.

snake@snakebox:~$ digikam
digikam(10688)/digikam (core): Could not find parent with url:
"/00_Fotos/00_Inc2/ABI 91/Lehrer"  for:  "/00_Fotos/00_Inc2/ABI 91/Lehrer/web"
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2011/0102/Filme/00006.MTS: The file contains data
of an unknown image type
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2011/0806 Abitreffen, Naturfreundehaus
Spiesen/Filme/00019.MTS: The file contains data of an unknown image type
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2011/1224-30/Filme/00022.MTS: The file contains
data of an unknown image type
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2011/1224-30/Filme/00037.MTS: The file contains
data of an unknown image type
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2012/0609 Geburtstagsfeier Lennox, EM
Eroeffnungsspiel im Fanblock D/Filme/00112.MTS: The file contains data of an
unknown image type
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2012/Sandra/2012-12-05/mts/00019.MTS: The file
contains data of an unknown image type
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2013/2013-05-30/mts/00055.MTS: The file contains
data of an unknown image type
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2013/2013-12-05/mts/00161.MTS: The file contains
data of an unknown image type
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2013/2013-12-24/mts/00003.MTS: The file contains
data of an unknown image type
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2014/2014-06-06/mts/00000.MTS: The file contains
data of an unknown image type
digikam(10688)/KEXIV2: Cannot load metadata from file   (Error # 11 :
/home/snake/Bilder/00_Fotos/2014/2014-12-24/mts/00043.MTS: The file contains
data of an unknown image type
digikam(10688)/digikam (core) Digikam::DNotificationWrapper: parent is null
digikam(10688)/digikam (core) Digikam::ImageInfo::fromLocalFile: No location
could be retrieved for ""
QFSFileEngine::open: No file name specified
QFSFileEngine::open: No file name specified

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=350574

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #1 from Gilles Caulier <[hidden email]> ---
*** Bug 329849 has been marked as a duplicate of this bug. ***

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[mhoppstaedter]

https://bugs.kde.org/show_bug.cgi?id=350574

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Platform|Other                       |Kubuntu Packages
            Version|unspecified                 |4.11.0

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=350574

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Database                    |Database-Migration

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=350574

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Database-Migration          |Database-Sqlite

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Database-Sqlite             |Database-Migration
                 CC|                            |[hidden email]

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #2 from [hidden email] ---
*** Bug 361809 has been marked as a duplicate of this bug. ***

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

--- Comment #3 from [hidden email] ---
This file still valid using last digiKam 5.0.0 ?

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

Peter Bauer <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #4 from Peter Bauer <[hidden email]> ---
digiKam 5.0.0 is per default compiled without the experimental MySQL support so
the migration is not possible anyway. It would be very helpful to have a
working migration to SQLite in version 4 so people can use Digikam with SQLite
until the rewritten MySQL support is ready.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

--- Comment #5 from [hidden email] ---
Mysql support is just an option to turn on at compilation time through cmake...
It's explained in README.

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

--- Comment #6 from Peter Bauer <[hidden email]> ---
Of course, but for e.g. Fedora 24 it is not turned on so the people currently
using MySQL have to migrate to SQLite for 5.0.0.
Is the "experimental" MySQL support supposed to be stable enough for performing
the migration to SQLite or should the migration be done using version 4.x?

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

--- Comment #7 from [hidden email] ---
Created attachment 100317
  --> https://bugs.kde.org/attachment.cgi?id=100317&action=edit
To migrate from MySQL to SQLite

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

--- Comment #8 from [hidden email] ---
Peter,

Do not use 4.x to migrate. Swati currently to fix code relevant in 5.x

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

--- Comment #9 from [hidden email] ---
Swati,

With your patch, are you able to migrate code without error with Tags table ?

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]
            Summary|MIGRATION: MySQL to SQLite  |MIGRATION: MySQL to SQLite
                   |fails                       |fails [patch]

--- Comment #10 from [hidden email] ---
Richard,

Swati has proposed a simple patch for this file. Any viewpoint about ?

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

--- Comment #11 from Richard Mortimer <[hidden email]> ---
(In reply to caulier.gilles from comment #10)
> Richard,
>
> Swati has proposed a simple patch for this file. Any viewpoint about ?
>
> Gilles Caulier

I've been discussing the issue with Swati via email today. I don't think that
the patch is a fix for the problem.

The TagsTree is getting migrated wrongly. The attempt to remove the triggers
was just to show that the trigger was definitely causing the problem. I can see
a number of issues:

First off the Tags migration is potentially broken because to work properly
with referential integrity the entries need to be migrated from the root of the
tree outwards. Without that the parent references may not be present when a
child is added. In most cases things will work out fine because the parent will
likely have a lower numbered primary key than the child and hence sorting by id
will make things better. But it is easy to see how things might go wrong if
tags are moved around in the tree. It is easy to detect by looking for entries
where pid > id. But at present things may be broken.

Secondly it seems that either duplicate TagsTree entries are getting added or
maybe somehow not getting added correctly. This needs investigation. It should
be fairly simple to do because it seems that things can be reproduced easily so
it will be possible to use the sqlite3 commandline to investigate the state of
the database when things go wrong.

I'm actually wondering if the add_tag (and other) trigger is getting added to
the database twice. I think I remember that SQLite can have multiple triggers
on one table so maybe there are two triggers trying to add an entry into the
TagsTree table.

Looking at the coreschemaupdater.cpp file there are two places where
createTriggers() is called so that may be relevant. Note I only did a very
quick look at the source so it might be a misleading idea. But I think there is
more investigation that needs doing to find the cause of the problem.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

--- Comment #12 from [hidden email] ---
(In reply to caulier.gilles from comment #9)
> Swati,
>
> With your patch, are you able to migrate code without error with Tags table ?

Yes, code is migrated without error but there's issue with the TagsTree table
since trigger is removed. So as Richard said, this is definitely a wrong way to
remove insert_tagstree trigger.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|MIGRATION: MySQL to SQLite  |MIGRATION: MySQL to SQLite
                   |fails [patch]               |fails about Tags tree
                   |                            |[patch]

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=350574

--- Comment #13 from [hidden email] ---
(In reply to Richard Mortimer from comment #11)
...(skip)

>
> Secondly it seems that either duplicate TagsTree entries are getting added
> or maybe somehow not getting added correctly. This needs investigation. It
> should be fairly simple to do because it seems that things can be reproduced
> easily so it will be possible to use the sqlite3 commandline to investigate
> the state of the database when things go wrong.
>
> I'm actually wondering if the add_tag (and other) trigger is getting added
> to the database twice. I think I remember that SQLite can have multiple
> triggers on one table so maybe there are two triggers trying to add an entry
> into the TagsTree table.

Using sqlite browser, I looked at the database tables for SQLite and found that
in TagsTree table, each tag is actually added TWICE. I guess this is the reason
why UNIQUE constraint fails while migrating to SQLite...

> Looking at the coreschemaupdater.cpp file there are two places where
> createTriggers() is called so that may be relevant. Note I only did a very
> quick look at the source so it might be a misleading idea. But I think there
> is more investigation that needs doing to find the cause of the problem.

Yes, the first one while creating database, and second in a version update. But
in the update step, trigger is first deleted and then again created, so I guess
this is fine?

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel