Issue when migrating sqlite to mysql

[Christoph Huckle]

Hello Gents,

I've looked in the bug reports and also in the mail-list but couldn't solve this issue:

I try to migrate my existing sqlite database into mysql and as soon as it starts copying over the imageinformation this error happens:

digikam.dbengine: Failure executing query:
"INSERT INTO ImageInformation (imageid, rating, creationDate, digitizationDate, orientation, width, height, format, colorDepth, colorModel) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"  
Error messages: "QMYSQL3: Der Befehl konnte nicht ausgeführt werden" "Cannot add or update a child row: a foreign key constraint fails (`digikam`.`ImageInformation`, CONSTRAINT `ImageInformation_Images` FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)" 1452 2  
Bound values:  (QVariant(qlonglong, 0), QVariant(qlonglong, 0), QVariant(QString, "2013-07-21T11:49:57"), QVariant(QString, "2013-07-21T11:49:57"), QVariant(qlonglong, 1), QVariant(qlonglong, 3024), QVariant(qlonglong, 2016), QVariant(QString, "JPG"), QVariant(qlonglong, 8), QVariant(qlonglong, 5))
digikam.dbengine: Error while executing DBAction [ "Migrate_Write_ImageInformation" ] Statement [ "INSERT INTO ImageInformation (imageid, rating, creationDate, digitizationDate, orientation, width, height, format, colorDepth, colorModel) VALUES (:imageid, :rating, :creationDate, :digitizationDate, :orientation, :width, :height, :format, :colorDepth, :colorModel);" ]
digikam.coredb: Core database: error while converting table data. Details:  QSqlError("1452", "QMYSQL3: Der Befehl konnte nicht ausgeführt werden", "Cannot add or update a child row: a foreign key constraint fails(`digikam`.`ImageInformation`, CONSTRAINT `ImageInformation_Images` FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)")

Tried it a few times, always the same error.

Let me know if you need more information or if I shall open a bug report.

Cheers

Christoph

[Christoph Huckle]

Okay, I found the issue: imageid = 0, there is no image existing in the table "Images" but there is an entry with that ID in the table "ImageInformation".

Would be good to have this exception catched and handled, in case that happens for others.

BR
Christoph

Christoph Huckle <[hidden email]> schrieb am Fr., 19. Aug. 2016 um 14:06 Uhr:

Hello Gents,

I've looked in the bug reports and also in the mail-list but couldn't solve this issue:

I try to migrate my existing sqlite database into mysql and as soon as it starts copying over the imageinformation this error happens:

digikam.dbengine: Failure executing query:
"INSERT INTO ImageInformation (imageid, rating, creationDate, digitizationDate, orientation, width, height, format, colorDepth, colorModel) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"  
Error messages: "QMYSQL3: Der Befehl konnte nicht ausgeführt werden" "Cannot add or update a child row: a foreign key constraint fails (`digikam`.`ImageInformation`, CONSTRAINT `ImageInformation_Images` FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)" 1452 2  
Bound values:  (QVariant(qlonglong, 0), QVariant(qlonglong, 0), QVariant(QString, "2013-07-21T11:49:57"), QVariant(QString, "2013-07-21T11:49:57"), QVariant(qlonglong, 1), QVariant(qlonglong, 3024), QVariant(qlonglong, 2016), QVariant(QString, "JPG"), QVariant(qlonglong, 8), QVariant(qlonglong, 5))
digikam.dbengine: Error while executing DBAction [ "Migrate_Write_ImageInformation" ] Statement [ "INSERT INTO ImageInformation (imageid, rating, creationDate, digitizationDate, orientation, width, height, format, colorDepth, colorModel) VALUES (:imageid, :rating, :creationDate, :digitizationDate, :orientation, :width, :height, :format, :colorDepth, :colorModel);" ]
digikam.coredb: Core database: error while converting table data. Details:  QSqlError("1452", "QMYSQL3: Der Befehl konnte nicht ausgeführt werden", "Cannot add or update a child row: a foreign key constraint fails(`digikam`.`ImageInformation`, CONSTRAINT `ImageInformation_Images` FOREIGN KEY (`imageid`) REFERENCES `Images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)")

Tried it a few times, always the same error.

Let me know if you need more information or if I shall open a bug report.

Cheers

Christoph

[thommym]

Hi Christoph,

I get the exact same error so I wonder if you found any work around?




Cheers,

/Thommy M.

[Christoph Huckle]

Hey Thommy,

Well the workaround was to edit the MySQL table and delete all entries in the table "Images" with the property "imageid = 0".

Did not find any easier way.

Regards

Christoph

thommym <[hidden email]> schrieb am Do., 1. Dez. 2016 um 18:56 Uhr:

Hi Christoph,

I get the exact same error so I wonder if you found any work around?




Cheers,

/Thommy M.



--
View this message in context: http://digikam.1695700.n4.nabble.com/Issue-when-migrating-sqlite-to-mysql-tp4688952p4690301.html
Sent from the digikam-users mailing list archive at Nabble.com.

--

[Stuart T Rogers]

I am wondering how many people use digikam for printing their photos? My
reason for asking is that until V5 I had always used digikam for
printing photos when needed. However since V5 and the use of QT5 it has
become impossible as the dialog will not allow the correct selection of
paper size or landscape/portrait mode. I have tested this on both
openSUSE Tumbleweed Leap42.2, and on Fedora 25 and all three exhibit the
same issue. I have raised bugs with both distros but they just get left
and nothing is happening. On searching for QT printing issues it does
seem that there are a multitude of issues and in some cases no one seems
to know how to resolve them. The printing issue is not restricted to
digikam as the same issue happens with any application which uses QT5
printing support. QT5 printing is simply not fit for purpose.

Is it possible for digikam to bring back the old printing capability
which worked perfectly in all releases prior to V5.

Stuart

On 02/12/16 07:43, Christoph Huckle wrote:

> Hey Thommy,
>
> Well the workaround was to edit the MySQL table and delete all entries
> in the table "Images" with the property "imageid = 0".
>
> Did not find any easier way.
>
> Regards
> Christoph
>
> thommym <[hidden email]
> <mailto:[hidden email]>> schrieb am Do., 1. Dez. 2016 um
> 18:56 Uhr:
>
>     Hi Christoph,
>
>     I get the exact same error so I wonder if you found any work around?
>
>
>
>
>     Cheers,
>
>     /Thommy M.
>
>
>
>     --
>     View this message in context:
>     http://digikam.1695700.n4.nabble.com/Issue-when-migrating-sqlite-to-mysql-tp4688952p4690301.html
>     Sent from the digikam-users mailing list archive at Nabble.com.
>
> --
> Christoph Huckle
>
> Unterm Aspalter 22
> 5106 Veltheim
>
> 076 419 62 61

--
Website: http://www.stella-maris.org.uk
or:      http://www.broadstairs.org