| Summary: | UpdateSchemaFromV7ToV9 fails due to foreign key contraint in 5.8.0-01 | ||
|---|---|---|---|
| Product: | [Applications] digikam | Reporter: | Jyrki Soini <Jyrki.Soini> |
| Component: | Database-Schema | Assignee: | Digikam Developers <digikam-bugs-null> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | metzpinguin, nico.kruber |
| Priority: | NOR | ||
| Version: | 5.8.0 | ||
| Target Milestone: | --- | ||
| Platform: | Appimage | ||
| OS: | Linux | ||
| Latest Commit: | https://commits.kde.org/digikam/443ce0e848a0da4fa7c80dd5b077fb6b8a545126 | Version Fixed In: | 5.9.0 |
| Sentry Crash Report: | |||
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 USE DIGIKAM_CORE_DATABASE UPDATE Settings SET value=9 WHERE keyword='DBVersion'; Maik 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 https://commits.kde.org/digikam/5893fde69518d70904d2b96e13800c7432b4e4c4 Fix for digiKam-5.8.0: See Bug 388977 Maik 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.
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 https://commits.kde.org/digikam/443ce0e848a0da4fa7c80dd5b077fb6b8a545126 |
> 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