Bug 373605 - Failed to update database schema from version 7 to version 8
Summary: Failed to update database schema from version 7 to version 8
Status: RESOLVED FIXED
Alias: None
Product: digikam
Classification: Applications
Component: Database-Mysql (show other bugs)
Version: 5.3.0
Platform: Kubuntu Linux
: NOR critical
Target Milestone: ---
Assignee: Digikam Developers
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-12-13 10:06 UTC by anttia
Modified: 2017-11-26 06:19 UTC (History)
3 users (show)

See Also:
Latest Commit:
Version Fixed In: 5.8.0


Attachments
sql script to detect FK violations (2.44 KB, application/sql)
2016-12-19 00:19 UTC, Kusi
Details

Note You need to log in before you can comment on or make changes to this bug.
Description anttia 2016-12-13 10:06:00 UTC
Hi. I tried to install Digikam 5.3. I started it,configured to use the mysql database I have on my network drive and after some time it gave me this error:

Error messages: "QMYSQL: Unable to execute query" "Cannot add or update a child row: a foreign key constraint fails (`digikamfi/#sql-12a1_bff8`, 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 [ "UpdateSchemaFromV7ToV8" ] Statement [ "ALTER 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 8 failed!
digikam.coredb: Core database: cannot process schema initialization
Comment 1 anttia 2016-12-14 06:38:22 UTC
Second time I tried to start the program it gave me the same error, but started the program. Problem was that it does show any albums or thumbbnails. When I clos it gives me this error message:

digikam.dbengine: WARNING !!! Transaction count is -1 when destroying database!!!
Comment 2 Maik Qualmann 2016-12-14 17:15:32 UTC
I think this problem will be fixed in digiKam-5.4.0. See Bug 372312

Try with Linux universal AppImage bundle (5.4.0) available here :

https://drive.google.com/drive/folders/0BzeiVr-byqt5Y0tIRWVWelRJenM

Maik
Comment 3 anttia 2016-12-18 12:38:51 UTC
And it also did brake the support for scandinavian charecters, so when i open digikam 4.14 all tags with scandinavian charecters are messed up.
Comment 4 Kusi 2016-12-19 00:18:07 UTC
Since I reported https://bugs.kde.org/show_bug.cgi?id=372312, I can give you some more comments:

Over the years, my database started containing invalid entries, like yours. Your Albums table has entries pointing to an image which doesn't exist. DK4 and earlier did not prevent these illegal combinations, so the error is not immediately noticed.

Since DK5, these violations are prohibited by foreign key constraints, which is a good thing. Whenever you change to a more strict scheme, you need to clean up the mess before. I had to manually delete all illegal rows (and there were many) in my db before upgrading to DK5.

If you hit the same issue as I had, then you need to get your hands dirty by fixing your db either with the "mysql" or "dbeaver" tool. You can detect FK violations with the attached sql script:

1) edit the first line and change to your digikam db name
2) run mysql -u<your user> -p<your pass> < check-fk-violations.sql

The script returns illegal entries in your db. Then you need to investigate and figure out if it's ok for you to delete the violating rows. 

best, Kusi

PS: Maik, seems like I have FK violations again even with your added constraints. My db was clean a month ago. At a first glance, the violating rows involve deleted images (in DK5). Seems like something is still wrong. I need to investigate more.
Comment 5 Kusi 2016-12-19 00:19:16 UTC
Created attachment 102868 [details]
sql script to detect FK violations
Comment 6 caulier.gilles 2017-04-16 20:23:54 UTC
new 5.6.0 pre-release as bundle is available here :

https://drive.google.com/drive/folders/0BzeiVr-byqt5Y0tIRWVWelRJenM

Please check if this problem still reproducible with these versions.

Thanks in advance

Gilles Caulier
Comment 7 caulier.gilles 2017-06-22 21:43:21 UTC
digiKam 5.6.0 is now released and available as bundle for Linux, MacOS and Windows.

https://www.digikam.org/news/2017-06-21-5.6.0-release-announcement/

Can you check if problem still exists with this version ?

Thanks in advance

Gilles Caulier
Comment 8 caulier.gilles 2017-07-23 18:27:37 UTC
New digiKam 5.7.0 are built with current implementation as pre-release bundles:

https://drive.google.com/drive/folders/0BzeiVr-byqt5Y0tIRWVWelRJenM

Problem still reproducible ?
Comment 9 Maik Qualmann 2017-11-26 06:19:31 UTC
Git commit 9a17c743ea9a41c1ef050b08517343813ef6a9f4 by Maik Qualmann.
Committed on 25/11/2017 at 22:02.
Pushed by mqualmann into branch 'master'.

update DB to v9 and fix migration from SQLite to MySQL
Related: bug 378838, bug 369083, bug 350574
FIXED-IN: 5.8.0

M  +5    -1    NEWS
M  +129  -97   data/database/dbconfig.xml.cmake.in
M  +1    -1    data/database/mysql-global.conf
M  +1    -20   libs/database/coredb/coredbcopymanager.cpp
M  +5    -2    libs/database/coredb/coredbschemaupdater.cpp

https://commits.kde.org/digikam/9a17c743ea9a41c1ef050b08517343813ef6a9f4