Bug 388824 - UpdateSchemaFromV7ToV9 fails due to foreign key contraint in 5.8.0-01
Summary: UpdateSchemaFromV7ToV9 fails due to foreign key contraint in 5.8.0-01
Alias: None
Product: digikam
Classification: Applications
Component: Database-Schema (show other bugs)
Version: 5.8.0
Platform: Appimage Linux
: NOR normal (vote)
Target Milestone: ---
Assignee: Digikam Developers
Depends on:
Reported: 2018-01-11 20:05 UTC by Jyrki Soini
Modified: 2018-01-19 06:08 UTC (History)
2 users (show)

See Also:
Latest Commit:
Version Fixed In: 5.9.0


Note You need to log in before you can comment on or make changes to this bug.
Description Jyrki Soini 2018-01-11 20:05:06 UTC
> digikam-5.8.0-01-x86-64.appimage 
-- digiKam AppImage Bundle
-- Use 'help' as CLI argument to know all available options
digikam.widgets: Breeze icons ressource file found
digikam.general: AlbumWatch use QFileSystemWatcher
digikam.general: Database Parameters:
   Type:                     "QMYSQL"
   DB Core Name:             "digikam"
   DB Thumbs Name:           "digikam"
   DB Face Name:             "digikam"
   Connect Options:          "UNIX_SOCKET=/run/mysql/mysql.sock"
   Host Name:                "localhost"
   Host port:                3306
   Internal Server:          false
   Internal Server Path:     ""
   Internal Server Serv Cmd: ""
   Internal Server Init Cmd: ""
   Username:                 "digikam"
   Password:                 "XXXXXXXXXXXXXXXXXXXX"

digikam.dbengine: Loading SQL code from config file "/tmp/.mount_digika5P5naf/usr/share/digikam/database/dbconfig.xml"
digikam.dbengine: Checking XML version ID => expected:  3  found:  3
digikam.coredb: Core database: running schema update
digikam.coredb: Core database: have a structure version  8
digikam.coredb: Core database: makeUpdates  8  to  9
digikam.dbengine: Failure executing query:
Error messages: "QMYSQL: Unable to execute query" "Cannot add or update a child row: a foreign key constraint fails (`digikam`.`#sql-875_d`, CONSTRAINT `Images_Albums` FOREIGN KEY (`album`) REFERENCES `Albums` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)" 1452 2 
Bound values:  ()
digikam.dbengine: Error while executing DBAction [ "UpdateSchemaFromV7ToV9" ] Statement [ "ALTER IGNORE TABLE Images\n                                        ADD CONSTRAINT Images_Albums FOREIGN KEY (album) REFERENCES Albums (id) ON DELETE CASCADE ON UPDATE CASCADE,\n                                        ADD UNIQUE (album, name(255)),\n                                        ENGINE InnoDB;" ]
digikam.coredb: Core database: schema update to V 9 failed!
digikam.coredb: Core database: cannot process schema initialization
KMemoryInfo: Platform identified :  "LINUX"
KMemoryInfo: TotalRam:  16762318848
digikam.general: Allowing a cache size of 200 MB
QThreadStorage: Thread 0x7fa94574dc80 exited after QThreadStorage 11 destroyed
Comment 1 Maik Qualmann 2018-01-11 20:16:58 UTC
Why it does not work for you, I can not say at the moment. My test database with wrong foreign keys can be ported without problems. My recommendation, increase the database version manually and use the migration tool to copy the database into a new one.

mysql -u USERNAME -p


UPDATE Settings SET value=9 WHERE keyword='DBVersion';

Comment 2 Maik Qualmann 2018-01-15 19:26:27 UTC
Git commit 5893fde69518d70904d2b96e13800c7432b4e4c4 by Maik Qualmann.
Committed on 15/01/2018 at 19:24.
Pushed by mqualmann into branch 'master'.

fix schema update from V7/8 to V9 with temporary tables
Related: bug 388977, bug 388867
FIXED-IN: 5.9.0

M  +4    -2    NEWS
M  +124  -30   data/database/dbconfig.xml.cmake.in

Comment 3 Maik Qualmann 2018-01-15 19:35:05 UTC
Fix for digiKam-5.8.0: See Bug 388977

Comment 4 Nico Kruber 2018-01-19 00:43:44 UTC
actually, this is not really enough without adding something like

<statement mode="plain">SET FOREIGN_KEY_CHECKS=0;</statement>

before trying to migrate the quirky tables. My problem was with the Tags table because of this in the (new) dbconfig.xml:

                <statement mode="plain">RENAME TABLE Tags TO Tags_old;</statement>
                <statement mode="plain">CREATE TABLE Tags LIKE Tags_old;</statement>
                <statement mode="plain">ALTER TABLE Tags
                                        ADD CONSTRAINT Tags_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE,
                                        ADD UNIQUE(pid, name(100)),
                                        ENGINE InnoDB;</statement>
                <statement mode="plain">REPLACE INTO Tags SELECT * FROM Tags_old;</statement>
                <statement mode="plain">SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';</statement>
                <statement mode="plain">REPLACE INTO Tags (id, pid, name, icon, iconkde, lft, rgt) VALUES (0, -1, '_Digikam_root_tag_', NULL, NULL,
                                        (SELECT MIN(tl.lft) FROM Tags AS tl),
                                        (SELECT MAX(tr.rgt) FROM Tags AS tr));</statement>
                <statement mode="plain">SET SQL_MODE=@OLD_SQL_MODE;</statement>
                <statement mode="plain">UPDATE Tags SET icon = NULL WHERE icon = 0;</statement>

Only the last statement fixes the invalid references to an image with ID 0 but the foreign key constraint was added before that already and this is why the REPLACE INTO failed.
Comment 5 Maik Qualmann 2018-01-19 06:08:22 UTC
Git commit 443ce0e848a0da4fa7c80dd5b077fb6b8a545126 by Maik Qualmann.
Committed on 19/01/2018 at 06:07.
Pushed by mqualmann into branch 'master'.

disable foreign key checks temporarily for the Tags table

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