[Digikam-devel] [digikam] [Bug 355831] MySQL Schema Improvements

[Richard Mortimer via KDE Bugzilla]

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

--- Comment #28 from Richard Mortimer <richm+kde at oldelvet.org.uk> ---
(In reply to caulier.gilles from comment #27)
> Richard,
> 
> I start to test step by step all pending patches.
> 
> > For patch : Remove trigger dependency for MySQL.
> ==> the patch do not touch the dbsettingswidget.cpp code where SQL
> requirements is given to previously create the databases in remote Mysql
> server by the system admin.
OK. I haven't looked at internal MySQL yet. I've got quite a few MySQL database
servers hanging around so it is easier for me to test and debug there in the
early stages.

> ==> This give a lots of error on the console when i use Mysql internal
> server (i don't yet tested Mysql remote server yet). Note that Mysql
> Internal server use "digikam" database name for all database (Core, Thumbs,
> Face). Look errors below :

Did those errors cause the migration to fail? Or did it complete with just the
complaints on the console?

> 
> digikam.dbengine: Failure executing query:
>  "UPDATE Tags SET icon=? WHERE id=?;" 
> Error messages: "QMYSQL3: Unable to execute statement" "Cannot add or update
> a child row: a foreign key constraint fails (`digikam`.`Tags`, CONSTRAINT
> `Tags_Images` FOREIGN KEY (`icon`) REFERENCES `Images` (`id`) ON DELETE SET
> NULL ON UPDATE CASCADE)" 1452 2 
> Bound values:  (QVariant(qlonglong, 0), QVariant(int, 1))

Those errors look like there are some Tags that have icons setup to point at
non-existant images. The examples you gave all have the first bound value as
zero which is typically a non-existant icon (instead of null). I suspect
most/all are set to zero instead of null. We should be able to filter out those
definitely bad ones.

What are your thoughts on filtering out data that is broken because the Album,
Image, Tag it refers to does not exist in the database? I tend to think that
the data was not accessible to users in the original database so it should be
safe to not transfer it.

> > For patch : Defer migration of Albums.icon until after the Images table has been migrated
> 
> ==> sound like it need the previous patch to be applied. the patch is
> rejected. Did you confirm ?
Ah sorry. I rebased my patches based on what you had already committed to the
public git and squashed updates on a single topic into a single patch. I
thought it best to make the patch in bugzilla be clear about that whole changes
for a particular reason.

> 
> > For patch : Move Thumbnail database Settings to ThumbSettings for MySQL
> 
> ==> We need the same solution for Face database which also create a Settings
> table.
Yes. I did not get around to that yet. Am I correct in that we can assume no
need to provide an upgrade here because there are no existing MySQL users with
face settings yet.

> ==> sound like it need the previous patch to be applied. the patch is
> rejected. Did you confirm ?
Yes. It does require the previous patch. 

> ==> After renaming Thumb and Face DB Settings tables, this will fix bug
> #331628 ? At least, as you specify InnoDB database engine, TokuDB engine
> will be not possible to use instead. Right ?
Hmmm. A difficult problem. Really we just want to NOT use MyISAM because it
does not support referential integrity.

TokuDB should work and it would be possible to manually change all the tables
after upgrade.
If automatic support for a particular backend was required then I think it
would have to be specified on the setup/migration dialog and then manually
specified during the migration.

Richard

-- 
You are receiving this mail because:
You are the assignee for the bug.