dK 5.7.0 Error converting database (haar matrix)

[J Albrecht]

I’ve tried to convert my database from SQLite to MySQL twice. Both attempts appeared to be going well until approximately 75% into the process. At those times, they failed with the following dialogue:

Error while converting the database. Details: Cannot add or update a child row: a foreign key constraint fails (`digikam`.`ImageHaarMatrix`, CONSTRAINT `ImageHaarMatrix_Images` FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Any idea how I might be able to overcome this critical issue?

[Gilles Caulier-4]

Hi,

A lots of work have be done to next 5.8.0 about Mysql support and migration tool. The current pre-release AppImage bundle can be tested here :

https://files.kde.org/digikam/

Gilles Caulier

2017-12-04 14:33 GMT+01:00 J Albrecht <[hidden email]>:

I’ve tried to convert my database from SQLite to MySQL twice. Both attempts appeared to be going well until approximately 75% into the process. At those times, they failed with the following dialogue:

Error while converting the database. Details: Cannot add or update a child row: a foreign key constraint fails (`digikam`.`ImageHaarMatrix`, CONSTRAINT `ImageHaarMatrix_Images` FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Any idea how I might be able to overcome this critical issue?

[Richard Mortimer]

Hi,

I haven't had chance to check yet but suspect a number of the original
failure scenarios that we had with MySQL to MySQL migration have been
broken by the recent changes.

It now seems to be only checking for the presence of associated images
in the old database. We have see issues involving images that no longer
had an associated Album or even images in Albums where the Album Root
had been deleted. The query logic that protected against that should
have been directly transferable to SQLite (indeed I was planning on
testing that when my laptop died a few months back). I got my laptop
mended a short while ago and will try to see how the latest changes cope
with my old digikam 4 databases.

Regards

Richard

On 04/12/2017 14:19, Gilles Caulier wrote:

> Hi,
>
> A lots of work have be done to next 5.8.0 about Mysql support and
> migration tool. The current pre-release AppImage bundle can be tested here :
>
> https://files.kde.org/digikam/
>
> Gilles Caulier
>
> 2017-12-04 14:33 GMT+01:00 J Albrecht <[hidden email]
> <mailto:[hidden email]>>:
>
>     I’ve tried to convert my database from SQLite to MySQL twice. Both
>     attempts appeared to be going well until approximately 75% into the
>     process. At those times, they failed with the following dialogue:
>
>     Error while converting the database. Details: Cannot add or update a
>     child row: a foreign key constraint fails
>     (`digikam`.`ImageHaarMatrix`, CONSTRAINT `ImageHaarMatrix_Images`
>     FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE
>     ON UPDATE CASCADE)
>
>     Any idea how I might be able to overcome this critical issue?
>
>

[J Albrecht]

Thanks for your comments Giles.

Perhaps I don’t even need the MySQL database?  

I’m trying to establish a shared database on my home server so that other users on other boxes will have the same access on their machines. Is there a “good” way of doing this other than relying on MYSQL?  Or, should I simply dump all remnants of the existing dK installation (including configs etc) and reinstall using MySQL from the beginning?

Additional insight/direction is welcome and appreciated!

On 04 Dec 2017, at 09:19, Gilles Caulier <[hidden email]> wrote:

Hi,

A lots of work have be done to next 5.8.0 about Mysql support and migration tool. The current pre-release AppImage bundle can be tested here :

https://files.kde.org/digikam/

Gilles Caulier

2017-12-04 14:33 GMT+01:00 J Albrecht <[hidden email]>:

I’ve tried to convert my database from SQLite to MySQL twice. Both attempts appeared to be going well until approximately 75% into the process. At those times, they failed with the following dialogue:

Error while converting the database. Details: Cannot add or update a child row: a foreign key constraint fails (`digikam`.`ImageHaarMatrix`, CONSTRAINT `ImageHaarMatrix_Images` FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Any idea how I might be able to overcome this critical issue?

[maderios]

On 12/04/2017 02:33 PM, J Albrecht wrote:
> I’ve tried to convert my database from SQLite to MySQL twice. Both attempts appeared to be going well until approximately 75% into the process. At those times, they failed with the following dialogue:
>
> Error while converting the database. Details: Cannot add or update a child row: a foreign key constraint fails (`digikam`.`ImageHaarMatrix`, CONSTRAINT `ImageHaarMatrix_Images` FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
>
> Any idea how I might be able to overcome this critical issue?
>
DK 5.8 conversion tool sqlite → mariadb external server works fine.
For me, now, Mariadb internal server support in Digikam 5.8 is OK.
Thanks Gilles, Maik and all developers. :)

--
Maderios

[Maik Qualmann]

I'll try with my English. I think that the current implementation solves all
problems. I can now migrate my old SQLite database in circle without losing
data, and it also deletes invalid entries. If we filter albums and images, we
would have to check all of the following tables against the same filtering,
otherwise they would fail when inserted into the target database. I bypass the
problem of using INSERT IGNORE to automatically drop invalid entries.

Maik

Am Montag, 4. Dezember 2017, 16:33:09 CET schrieb Richard Mortimer:

> Hi,
>
> I haven't had chance to check yet but suspect a number of the original
> failure scenarios that we had with MySQL to MySQL migration have been
> broken by the recent changes.
>
> It now seems to be only checking for the presence of associated images
> in the old database. We have see issues involving images that no longer
> had an associated Album or even images in Albums where the Album Root
> had been deleted. The query logic that protected against that should
> have been directly transferable to SQLite (indeed I was planning on
> testing that when my laptop died a few months back). I got my laptop
> mended a short while ago and will try to see how the latest changes cope
> with my old digikam 4 databases.
>
> Regards
>
> Richard
>
> On 04/12/2017 14:19, Gilles Caulier wrote:
> > Hi,
> >
> > A lots of work have be done to next 5.8.0 about Mysql support and
> > migration tool. The current pre-release AppImage bundle can be tested here
> > :
> >
> > https://files.kde.org/digikam/
> >
> > Gilles Caulier
> >
> > 2017-12-04 14:33 GMT+01:00 J Albrecht <[hidden email]
> >
> > <mailto:[hidden email]>>:
> >     I’ve tried to convert my database from SQLite to MySQL twice. Both
> >     attempts appeared to be going well until approximately 75% into the
> >     process. At those times, they failed with the following dialogue:
> >    
> >     Error while converting the database. Details: Cannot add or update a
> >     child row: a foreign key constraint fails
> >     (`digikam`.`ImageHaarMatrix`, CONSTRAINT `ImageHaarMatrix_Images`
> >     FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE
> >     ON UPDATE CASCADE)
> >    
> >     Any idea how I might be able to overcome this critical issue?

[Gilles Caulier-4]

This evening i will restart the bundle 5.8.0 pre release process to be able to test with current implementation.

Best

Gilles Caulier

2017-12-05 12:31 GMT+01:00 Maik Qualmann <[hidden email]>:

I'll try with my English. I think that the current implementation solves all
problems. I can now migrate my old SQLite database in circle without losing
data, and it also deletes invalid entries. If we filter albums and images, we
would have to check all of the following tables against the same filtering,
otherwise they would fail when inserted into the target database. I bypass the
problem of using INSERT IGNORE to automatically drop invalid entries.

Maik

Am Montag, 4. Dezember 2017, 16:33:09 CET schrieb Richard Mortimer:
> Hi,
>
> I haven't had chance to check yet but suspect a number of the original
> failure scenarios that we had with MySQL to MySQL migration have been
> broken by the recent changes.
>
> It now seems to be only checking for the presence of associated images
> in the old database. We have see issues involving images that no longer
> had an associated Album or even images in Albums where the Album Root
> had been deleted. The query logic that protected against that should
> have been directly transferable to SQLite (indeed I was planning on
> testing that when my laptop died a few months back). I got my laptop
> mended a short while ago and will try to see how the latest changes cope
> with my old digikam 4 databases.
>
> Regards
>
> Richard
>
> On 04/12/2017 14:19, Gilles Caulier wrote:
> > Hi,
> >
> > A lots of work have be done to next 5.8.0 about Mysql support and
> > migration tool. The current pre-release AppImage bundle can be tested here
> > :
> >
> > https://files.kde.org/digikam/
> >
> > Gilles Caulier
> >
> > 2017-12-04 14:33 GMT+01:00 J Albrecht <[hidden email]
> >
> > <mailto:[hidden email]>>:
> >     I’ve tried to convert my database from SQLite to MySQL twice. Both
> >     attempts appeared to be going well until approximately 75% into the
> >     process. At those times, they failed with the following dialogue:
> >
> >     Error while converting the database. Details: Cannot add or update a
> >     child row: a foreign key constraint fails
> >     (`digikam`.`ImageHaarMatrix`, CONSTRAINT `ImageHaarMatrix_Images`
> >     FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE
> >     ON UPDATE CASCADE)
> >
> >     Any idea how I might be able to overcome this critical issue?

[Gilles Caulier-4]

All the bundles are now updated online at usual place :

https://files.kde.org/digikam/

Gilles Caulier

2017-12-05 12:40 GMT+01:00 Gilles Caulier <[hidden email]>:

This evening i will restart the bundle 5.8.0 pre release process to be able to test with current implementation.

Best

Gilles Caulier

2017-12-05 12:31 GMT+01:00 Maik Qualmann <[hidden email]>:

I'll try with my English. I think that the current implementation solves all
problems. I can now migrate my old SQLite database in circle without losing
data, and it also deletes invalid entries. If we filter albums and images, we
would have to check all of the following tables against the same filtering,
otherwise they would fail when inserted into the target database. I bypass the
problem of using INSERT IGNORE to automatically drop invalid entries.

Maik

Am Montag, 4. Dezember 2017, 16:33:09 CET schrieb Richard Mortimer:
> Hi,
>
> I haven't had chance to check yet but suspect a number of the original
> failure scenarios that we had with MySQL to MySQL migration have been
> broken by the recent changes.
>
> It now seems to be only checking for the presence of associated images
> in the old database. We have see issues involving images that no longer
> had an associated Album or even images in Albums where the Album Root
> had been deleted. The query logic that protected against that should
> have been directly transferable to SQLite (indeed I was planning on
> testing that when my laptop died a few months back). I got my laptop
> mended a short while ago and will try to see how the latest changes cope
> with my old digikam 4 databases.
>
> Regards
>
> Richard
>
> On 04/12/2017 14:19, Gilles Caulier wrote:
> > Hi,
> >
> > A lots of work have be done to next 5.8.0 about Mysql support and
> > migration tool. The current pre-release AppImage bundle can be tested here
> > :
> >
> > https://files.kde.org/digikam/
> >
> > Gilles Caulier
> >
> > 2017-12-04 14:33 GMT+01:00 J Albrecht <[hidden email]
> >
> > <mailto:[hidden email]>>:
> >     I’ve tried to convert my database from SQLite to MySQL twice. Both
> >     attempts appeared to be going well until approximately 75% into the
> >     process. At those times, they failed with the following dialogue:
> >
> >     Error while converting the database. Details: Cannot add or update a
> >     child row: a foreign key constraint fails
> >     (`digikam`.`ImageHaarMatrix`, CONSTRAINT `ImageHaarMatrix_Images`
> >     FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE
> >     ON UPDATE CASCADE)
> >
> >     Any idea how I might be able to overcome this critical issue?

[Richard Mortimer-2]

Thanks. I have set a migration off. Last time I tried it took a few
hours so I will report back in the morning.

Regards

Richard

On 05/12/2017 21:00, Gilles Caulier wrote:

> All the bundles are now updated online at usual place :
>
> https://files.kde.org/digikam/
>
> Gilles Caulier
>
> 2017-12-05 12:40 GMT+01:00 Gilles Caulier <[hidden email]
> <mailto:[hidden email]>>:
>
>     This evening i will restart the bundle 5.8.0 pre release process to
>     be able to test with current implementation.
>
>     Best
>
>     Gilles Caulier
>
>     2017-12-05 12:31 GMT+01:00 Maik Qualmann <[hidden email]
>     <mailto:[hidden email]>>:
>
>         I'll try with my English. I think that the current
>         implementation solves all
>         problems. I can now migrate my old SQLite database in circle
>         without losing
>         data, and it also deletes invalid entries. If we filter albums
>         and images, we
>         would have to check all of the following tables against the same
>         filtering,
>         otherwise they would fail when inserted into the target
>         database. I bypass the
>         problem of using INSERT IGNORE to automatically drop invalid
>         entries.
>
>         Maik
>
>         Am Montag, 4. Dezember 2017, 16:33:09 CET schrieb Richard Mortimer:
>         > Hi,
>         >
>         > I haven't had chance to check yet but suspect a number of the
>         original
>         > failure scenarios that we had with MySQL to MySQL migration
>         have been
>         > broken by the recent changes.
>         >
>         > It now seems to be only checking for the presence of
>         associated images
>         > in the old database. We have see issues involving images that
>         no longer
>         > had an associated Album or even images in Albums where the
>         Album Root
>         > had been deleted. The query logic that protected against that
>         should
>         > have been directly transferable to SQLite (indeed I was
>         planning on
>         > testing that when my laptop died a few months back). I got my
>         laptop
>         > mended a short while ago and will try to see how the latest
>         changes cope
>         > with my old digikam 4 databases.
>         >
>         > Regards
>         >
>         > Richard
>         >
>         > On 04/12/2017 14:19, Gilles Caulier wrote:
>         > > Hi,
>         > >
>         > > A lots of work have be done to next 5.8.0 about Mysql
>         support and
>         > > migration tool. The current pre-release AppImage bundle can
>         be tested here
>         > > :
>         > >
>         > > https://files.kde.org/digikam/
>         > >
>         > > Gilles Caulier
>         > >
>         > > 2017-12-04 14:33 GMT+01:00 J Albrecht <[hidden email]
>         <mailto:[hidden email]>
>         > >
>         > > <mailto:[hidden email] <mailto:[hidden email]>>>:
>         > >     I’ve tried to convert my database from SQLite to MySQL
>         twice. Both
>         > >     attempts appeared to be going well until approximately
>         75% into the
>         > >     process. At those times, they failed with the following
>         dialogue:
>         > >
>         > >     Error while converting the database. Details: Cannot add
>         or update a
>         > >     child row: a foreign key constraint fails
>         > >     (`digikam`.`ImageHaarMatrix`, CONSTRAINT
>         `ImageHaarMatrix_Images`
>         > >     FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON
>         DELETE CASCADE
>         > >     ON UPDATE CASCADE)
>         > >
>         > >     Any idea how I might be able to overcome this critical
>         issue?
>
>
>

[Richard Mortimer-2]

Hi all,

On 05/12/2017 22:30, Richard Mortimer wrote:
> Thanks. I have set a migration off. Last time I tried it took a few
> hours so I will report back in the morning.
>

It did complete the migration and a cursor check says that things are there.

A few more comments below.

>>     2017-12-05 12:31 GMT+01:00 Maik Qualmann <[hidden email]
>>     <mailto:[hidden email]>>:
>>
>>         I'll try with my English. I think that the current
>>         implementation solves all
>>         problems. I can now migrate my old SQLite database in circle
>>         without losing
>>         data, and it also deletes invalid entries. If we filter albums
>>         and images, we
>>         would have to check all of the following tables against the same
>>         filtering,

The old MySQL queries did filter out against albums, images album roots etc.

From what I've read the only thing it didn't filter out was duplicate
records. I don't think it would have been too hard to do that in the SQL
queries but that isn't really needed now and erroneous records are ignored.

>>         otherwise they would fail when inserted into the target
>>         database. I bypass the
>>         problem of using INSERT IGNORE to automatically drop invalid
>>         entries.
I must admit to being very wary of doing this because it pretty much
hides any other problems by just throwing data away with no fatal error
reported.

Anyway. Thank you for taking on the job of getting rid of the last few
issues in migration. It isn't easy fixing up the consequences of old
bugs where the database was not enforcing referential integrity.

Regards

Richard





>>
>>         Maik