Bug 435455

Summary: Migration cannot drop or create FaceMatrices table [patch]
Product: [Applications] digikam Reporter: meku <kde.bugs>
Component: Database-MysqlAssignee: Digikam Developers <digikam-bugs-null>
Status: RESOLVED FIXED    
Severity: normal CC: caulier.gilles, metzpinguin
Priority: NOR    
Version First Reported In: 7.2.0   
Target Milestone: ---   
Platform: Appimage   
OS: Linux   
Latest Commit: Version Fixed In: 7.3.0
Sentry Crash Report:

Description meku 2021-04-07 08:57:44 UTC 
SUMMARY

Running Digikam with external mysql (using Mariadb) there are failures in the database migration.

Debugging output from Digikam:

Digikam::FaceDb::setting: FaceDB SelectFaceSetting val ret =  0                                                       Digikam::FaceDb::setting: FaceDB SelectFaceSetting val ret =  0                                                       
Digikam::FaceDbSchemaUpdater::startUpdates: Face database: have a structure version  "3"                              
Digikam::BdEngineBackendPrivate::debugOutputFailedQuery: Failure executing query:                                     
 ""                                                                                                                   
Error messages: "QMYSQL: Unable to execute query" "Cannot delete or update a parent row: a foreign key constraint fail
s" "1451" 2                                                                                                           
Bound values:  ()                                                                                                     
Digikam::BdEngineBackend::execDBAction: Error while executing DBAction [ "CreateFaceDBFaceMatrices" ] Statement [ "DRO
P TABLE IF EXISTS FaceMatrices;\n                " ]                                                                  
Digikam::FaceDbSchemaUpdater::updateV3ToV4: fail to recreate FaceMatrices table
Comment 1 meku 2021-04-07 09:03:03 UTC 
I suspect the DROP TABLE is failing due to a foreign key linking to the FaceMatrices table.

Additionally the CREATE TABLE FaceMatrices statement will not run due to missing field escaping:

This line:
CONSTRAINT FaceEmbedding_Identities FOREIGN KEY (identity) REFERENCES Identities (id) ON DELETE CASCADE ON UPDATE CASCADE)

Needs to escape the `identity` field:
CONSTRAINT FaceEmbedding_Identities FOREIGN KEY (`identity`) REFERENCES Identities (id) ON DELETE CASCADE ON UPDATE CASCADE)
Comment 2 Maik Qualmann 2021-04-07 10:32:37 UTC 
That cannot be the cause, because "identity" is not a reserved keyword in MySQL. I'm taking a closer look tonight.

Maik
Comment 3 meku 2021-04-07 10:53:46 UTC 
(In reply to Maik Qualmann from comment #2)
> That cannot be the cause, because "identity" is not a reserved keyword in
> MySQL. I'm taking a closer look tonight.
> 
> Maik

Yeah I think you are right, create statement should work. I must have been looking at the wrong SQL engine list of keywords.
Comment 4 Maik Qualmann 2021-04-07 11:21:18 UTC 
I think the cause is clear. We can deactivate the constraint check when deleting the old table. You have elements in your table whose id is not in the identities table. I'm adding it tonight.

Maik
Comment 5 Maik Qualmann 2021-04-07 16:55:00 UTC 
Git commit 087024f261ed66279b7016f26e4f606f6b97f01b by Maik Qualmann.
Committed on 07/04/2021 at 16:54.
Pushed by mqualmann into branch 'master'.

disable foreign key checks when drop the table

M  +3    -2    core/data/database/dbconfig.xml.cmake.in

https://invent.kde.org/graphics/digikam/commit/087024f261ed66279b7016f26e4f606f6b97f01b
Comment 6 Maik Qualmann 2021-04-09 20:58:52 UTC 
New AppImage for testing is available. Has the problem been resolved?

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

Maik
Comment 7 meku 2021-04-11 23:55:59 UTC 
(In reply to Maik Qualmann from comment #6)
> New AppImage for testing is available. Has the problem been resolved?

Appears to be resolved in digiKam-7.3.0-20210411T170959-x86-64.appimage

Thanks.