[digikam] [Bug 350574] MIGRATION: MySQL to SQLite fails about Tags tree [patch]

[Maik Qualmann]

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

Maik Qualmann  changed:

   What|Removed |Added

 CC||metzping...@gmail.com

--- Comment #16 from Maik Qualmann  ---
Hello Richard,

the reason is because some tables, even though the data was correct, were not
copied at all. These included ImageComments, ImageCopyright and ImageRelations.
But I will test the filters step by step and then bring them back. The Settings
table was not copied at all before because it was commented out in
coredbcopymanager.cpp. DigiKam recreates the entries, at the moment I only
consider the Locale as important.

Maik

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 350574] MIGRATION: MySQL to SQLite fails about Tags tree [patch]

[Richard Mortimer]

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

--- Comment #15 from Richard Mortimer  ---
(In reply to Maik Qualmann from comment #14)
> Git commit 9a17c743ea9a41c1ef050b08517343813ef6a9f4 by Maik Qualmann.
> Committed on 25/11/2017 at 22:02.
> Pushed by mqualmann into branch 'master'.
> 
> update DB to v9 and fix migration from SQLite to MySQL
> Related: bug 378838, bug 369083, bug 325654
> FIXED-IN: 5.8.0
> 
> M  +5-1NEWS
> M  +129  -97   data/database/dbconfig.xml.cmake.in
> M  +1-1data/database/mysql-global.conf
> M  +1-20   libs/database/coredb/coredbcopymanager.cpp
> M  +5-2libs/database/coredb/coredbschemaupdater.cpp
> 
> https://commits.kde.org/digikam/9a17c743ea9a41c1ef050b08517343813ef6a9f4

Is there a reason why the committed patch removes all of the careful filtering
of orphan records from old broken databases that were being migrated from
pre-referential integrity schema?

There was lots of careful filtering via LEFT JOIN and IS NOT NULL to ensure
that everything that was migrated was connected to an active ALBUM root.

I was actually thinking that the same needed doing when using a SQLite database
as source and haven't seen anything to suggest that it was a bad idea. Indeed
it fixed a lot of migration complaints.

I'm also wondering why the migration has been changed to only migrate "Locale"
from the settings? There is no reference to a bug/reason for that. I cannot
tell if that slipped in by mistake or not.

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 350574] MIGRATION: MySQL to SQLite fails about Tags tree [patch]

[Maik Qualmann]

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

Maik Qualmann  changed:

   What|Removed |Added

   Version Fixed In||5.8.0
 Resolution|--- |FIXED
  Latest Commit||https://commits.kde.org/dig
   ||ikam/9a17c743ea9a41c1ef050b
   ||08517343813ef6a9f4
 Status|UNCONFIRMED |RESOLVED

--- Comment #14 from Maik Qualmann  ---
Git commit 9a17c743ea9a41c1ef050b08517343813ef6a9f4 by Maik Qualmann.
Committed on 25/11/2017 at 22:02.
Pushed by mqualmann into branch 'master'.

update DB to v9 and fix migration from SQLite to MySQL
Related: bug 378838, bug 369083, bug 325654
FIXED-IN: 5.8.0

M  +5-1NEWS
M  +129  -97   data/database/dbconfig.xml.cmake.in
M  +1-1data/database/mysql-global.conf
M  +1-20   libs/database/coredb/coredbcopymanager.cpp
M  +5-2libs/database/coredb/coredbschemaupdater.cpp

https://commits.kde.org/digikam/9a17c743ea9a41c1ef050b08517343813ef6a9f4

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 350574] MIGRATION: MySQL to SQLite fails about Tags tree [patch]

[via KDE Bugzilla]

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

--- Comment #13 from swatilodh...@gmail.com ---
(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 watching all bug changes.

[digikam] [Bug 350574] MIGRATION: MySQL to SQLite fails about Tags tree [patch]

[via KDE Bugzilla]

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

caulier.gil...@gmail.com 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 watching all bug changes.

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

[via KDE Bugzilla]

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

--- Comment #12 from swatilodh...@gmail.com ---
(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 watching all bug changes.

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

[Richard Mortimer via KDE Bugzilla]

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

--- Comment #11 from Richard Mortimer  ---
(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 watching all bug changes.

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

[via KDE Bugzilla]

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

caulier.gil...@gmail.com changed:

   What|Removed |Added

 CC||richm+...@oldelvet.org.uk
Summary|MIGRATION: MySQL to SQLite  |MIGRATION: MySQL to SQLite
   |fails   |fails [patch]

--- Comment #10 from caulier.gil...@gmail.com ---
Richard,

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

Gilles Caulier

-- 
You are receiving this mail because:
You are watching all bug changes.

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

[via KDE Bugzilla]

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

--- Comment #9 from caulier.gil...@gmail.com ---
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 watching all bug changes.

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

[via KDE Bugzilla]

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

--- Comment #8 from caulier.gil...@gmail.com ---
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 watching all bug changes.

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

[via KDE Bugzilla]

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

--- Comment #7 from swatilodh...@gmail.com ---
Created attachment 100317
  --> https://bugs.kde.org/attachment.cgi?id=100317=edit
To migrate from MySQL to SQLite

-- 
You are receiving this mail because:
You are watching all bug changes.

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

[Peter Bauer via KDE Bugzilla]

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

--- Comment #6 from Peter Bauer  ---
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 watching all bug changes.

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

[via KDE Bugzilla]

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

--- Comment #5 from caulier.gil...@gmail.com ---
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 watching all bug changes.

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

[Peter Bauer via KDE Bugzilla]

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

Peter Bauer  changed:

   What|Removed |Added

 CC||peter.m.ba...@gmail.com

--- Comment #4 from Peter Bauer  ---
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 watching all bug changes.

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

[via KDE Bugzilla]

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

--- Comment #3 from caulier.gil...@gmail.com ---
This file still valid using last digiKam 5.0.0 ?

Gilles Caulier

-- 
You are receiving this mail because:
You are watching all bug changes.

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

[via KDE Bugzilla]

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

caulier.gil...@gmail.com changed:

   What|Removed |Added

 CC||swatilodh...@gmail.com

--- Comment #2 from caulier.gil...@gmail.com ---
*** Bug 361809 has been marked as a duplicate of this bug. ***

-- 
You are receiving this mail because:
You are watching all bug changes.