Bug 388867 - problem migrating databaseschema from 8 to 9
Summary: problem migrating databaseschema from 8 to 9
Status: RESOLVED FIXED
Alias: None
Product: digikam
Classification: Applications
Component: Database-Schema (show other bugs)
Version: 5.9.0
Platform: Fedora RPMs Linux
: NOR critical (vote)
Target Milestone: ---
Assignee: Digikam Developers
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-01-12 14:55 UTC by mlrvtw
Modified: 2018-07-22 07:33 UTC (History)
6 users (show)

See Also:
Latest Commit:
Version Fixed In: 5.9.0


Attachments
screendump error (59.90 KB, image/png)
2018-01-12 14:55 UTC, mlrvtw
Details
dbconfig.xml (130.06 KB, application/xml)
2018-01-12 20:22 UTC, Maik Qualmann
Details
dbconfig.xml (144.45 KB, application/xml)
2018-03-27 17:16 UTC, Maik Qualmann
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mlrvtw 2018-01-12 14:55:00 UTC
Created attachment 109818 [details]
screendump error

After migrating digikam from 5.7.0 to 5.8.0 I got an error directly after starting 5.8.0. The problem is that the migration of the databasescheme from version 8 to 9 has failed. See screendump in the attachement.

Digikam 5.8.0 doesn't start.
Comment 1 caulier.gilles 2018-01-12 16:23:42 UTC
Please install DebugView program from Microsoft and run it while digiKam starting.

https://docs.microsoft.com/en-us/sysinternals/downloads/debugview

You will see all debug traces generated internally by digiKam. This will be interesting to investigate.

Gilles Caulier
Comment 2 Maik Qualmann 2018-01-12 16:34:44 UTC
Do you use MySQL or MariaDB? An internal or external MySQL server?

Maik
Comment 3 mlrvtw 2018-01-12 20:16:39 UTC
(In reply to Maik Qualmann from comment #2)
> Do you use MySQL or MariaDB? An internal or external MySQL server?
> 
> Maik

mySQL intern - MariaDB vs 10.2

Roeland
Comment 4 mlrvtw 2018-01-12 20:18:37 UTC
(In reply to caulier.gilles from comment #1)
> Please install DebugView program from Microsoft and run it while digiKam
> starting.
> 
> https://docs.microsoft.com/en-us/sysinternals/downloads/debugview
> 
> You will see all debug traces generated internally by digiKam. This will be
> interesting to investigate.
> 
> Gilles Caulier

The logfile during migrating 5.7.0 to 5.8.0:
00000001	0.00000000	[6072] InitSideBySide failed create an activation context. Error: 1814	
00000002	11.33147907	[10604] digikam.widgets: Breeze icons ressource file found	
00000003	11.33652115	[10604] digikam.general: AlbumWatch use QFileSystemWatcher	
00000004	11.36268330	[10604] digikam.general: Database Parameters:	
00000005	11.36268330	[10604]    Type:                     "QMYSQL"	
00000006	11.36268330	[10604]    DB Core Name:             "digikam"	
00000007	11.36268330	[10604]    DB Thumbs Name:           "digikam"	
00000008	11.36268330	[10604]    DB Face Name:             "digikam"	
00000009	11.36268330	[10604]    Connect Options:          ""	
00000010	11.36268330	[10604]    Host Name:                "localhost"	
00000011	11.36268330	[10604]    Host port:                3307	
00000012	11.36268330	[10604]    Internal Server:          true	
00000013	11.36268330	[10604]    Internal Server Path:     "C:/Users/mlrvt/digikam"	
00000014	11.36268330	[10604]    Internal Server Serv Cmd: "C:/Program Files/MariaDB 10.2/bin//mysqld.exe"	
00000015	11.36268330	[10604]    Internal Server Init Cmd: "C:/Program Files/MariaDB 10.2/bin//mysql_install_db.exe"	
00000016	11.36268330	[10604]    Username:                 "root"	
00000017	11.36268330	[10604]    Password:                 ""	
00000018	11.36268330	[10604] 	
00000019	11.36393261	[10604] digikam.databaseserver: Database Parameters:	
00000020	11.36393261	[10604]    Type:                     "QMYSQL"	
00000021	11.36393261	[10604]    DB Core Name:             "digikam"	
00000022	11.36393261	[10604]    DB Thumbs Name:           "digikam"	
00000023	11.36393261	[10604]    DB Face Name:             "digikam"	
00000024	11.36393261	[10604]    Connect Options:          ""	
00000025	11.36393261	[10604]    Host Name:                "localhost"	
00000026	11.36393261	[10604]    Host port:                3307	
00000027	11.36393261	[10604]    Internal Server:          true	
00000028	11.36393261	[10604]    Internal Server Path:     "C:/Users/mlrvt/digikam"	
00000029	11.36393261	[10604]    Internal Server Serv Cmd: "C:/Program Files/MariaDB 10.2/bin//mysqld.exe"	
00000030	11.36393261	[10604]    Internal Server Init Cmd: "C:/Program Files/MariaDB 10.2/bin//mysql_install_db.exe"	
00000031	11.36393261	[10604]    Username:                 "root"	
00000032	11.36393261	[10604]    Password:                 ""	
00000033	11.36393261	[10604] 	
00000034	11.36401176	[10604] digikam.databaseserver: Internal Server data path: "C:/Users/mlrvt/digikam/.mysql.digikam/db_data"	
00000035	11.36451340	[10604] digikam.databaseserver: The mysql configuration was already up-to-date: "C:/Users/mlrvt/AppData/Local/digikam/mysql.conf"	
00000036	11.36466312	[10604] digikam.databaseserver: Failed to open MySQL error log.	
00000037	11.77182961	[10604] digikam.databaseserver: Database server: "C:/Program Files/MariaDB 10.2/bin//mysqld.exe" ("--defaults-file=C:\\Users\\mlrvt\\AppData\\Local\\digikam\\mysql.conf", "--datadir=C:\\Users\\mlrvt\\digikam\\.mysql.digikam\\db_data", "--skip-networking=0", "--port=3307")	
00000038	11.78020382	[10604] digikam.databaseserver: Internal database server started	
00000039	11.78075218	[10604] digikam.databaseserver: Running 0 seconds...	
00000040	12.11741829	[10604] digikam.dbengine: Loading SQL code from config file "C:/Program Files/digiKam/data/digikam/database/dbconfig.xml"	
00000041	12.12179470	[10604] digikam.dbengine: Checking XML version ID => expected:  3  found:  3	
00000042	12.12433529	[10604] digikam.coredb: Core database: running schema update	
00000043	12.21508312	[10604] digikam.coredb: Core database: have a structure version  8	
00000044	12.21511459	[10604] digikam.coredb: Core database: makeUpdates  8  to  9	
00000045	26.46192360	[10604] digikam.dbengine: Failure executing query:	
00000046	26.46192360	[10604]  "" 	
00000047	26.46192360	[10604] Error messages: "QMYSQL: Unable to execute query" "Can't create table `digikam`.`#sql-28dc_50` (errno: 121 \"Duplicate key on write or update\")" 1005 2 	
00000048	26.46192360	[10604] Bound values:  ()	
00000049	26.46204376	[10604] digikam.dbengine: Error while executing DBAction [ "UpdateSchemaFromV7ToV9" ] Statement [ "ALTER IGNORE TABLE Albums\n                                        ADD CONSTRAINT Albums_AlbumRoots FOREIGN KEY (albumRoot) REFERENCES AlbumRoots (id) ON DELETE CASCADE ON UPDATE CASCADE,\n                                        ADD UNIQUE (albumRoot, relativePath(255)),\n                                        ENGINE InnoDB;" ]	
00000050	26.46210861	[10604] digikam.coredb: Core database: schema update to V 9 failed!	
00000051	26.46483231	[10604] digikam.coredb: Core database: cannot process schema initialization	
00000052	40.35442734	[10604] KMemoryInfo: Platform identified :  "WINDOWS"	
00000053	40.35445404	[10604] KMemoryInfo: TotalRam:  12713754624	
00000054	40.35453415	[10604] digikam.general: Allowing a cache size of 200 MB	
00000055	40.36982346	[10604] QThreadStorage: Thread 0x230dbaf0 exited after QThreadStorage 7 destroyed	
00000056	40.37002563	[10604] QWaitCondition: Destroyed while threads are still waiting	
00000057	40.37097931	[10604] QThread: Destroyed while thread is still running
Comment 5 Maik Qualmann 2018-01-12 20:22:56 UTC
Created attachment 109824 [details]
dbconfig.xml

It fails because of duplicate data in the AlbumRoots table. Normally, ALTER IGNORE TABLE should prevent this. I can not say why this does not work with some MySQL servers. We do the following:

Install digiKam-5.8.0 again. Replace the dbconfig.xml in C:\Program Files\digiKam\data\digikam\database with this one. Start digiKam, it should not fail anymore, but do not use the current database. You use the internal server. Copy the database to SQLite with the migration tool and import it back to your internal database. Now you can use digiKam-5.8.0.

Maik
Comment 6 mlrvtw 2018-01-12 20:51:19 UTC
(In reply to Maik Qualmann from comment #5)
> Created attachment 109824 [details]
> dbconfig.xml
> 
> It fails because of duplicate data in the AlbumRoots table. Normally, ALTER
> IGNORE TABLE should prevent this. I can not say why this does not work with
> some MySQL servers. We do the following:
> 
> Install digiKam-5.8.0 again. Replace the dbconfig.xml in C:\Program
> Files\digiKam\data\digikam\database with this one. Start digiKam, it should
> not fail anymore, but do not use the current database. You use the internal
> server. Copy the database to SQLite with the migration tool and import it
> back to your internal database. Now you can use digiKam-5.8.0.
> 
> Maik

Thanks Maik, the problem is solved.

Roeland
Comment 7 caulier.gilles 2018-01-13 13:50:44 UTC
Maik,

The new dbconfig.xml file is not yet applied to git master to be included in 5.9.0. Right ?

Gilles
Comment 8 Maik Qualmann 2018-01-13 15:03:24 UTC
Right, and it will not applied. It does not add CONSTRAINT and UNIQUE so the conversion will not fail. Therefore, the database must also be copied using the migration tool. I find it interesting that MySQL does not seem to offer a clean way to convert a table type without failing. As I said, it works for me, probably depending on the used MySQL / MariaDB version. If further problems are reported, I will probably have to program the only remaining option via temp tables.

Maik
Comment 9 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 388824
FIXED-IN: 5.9.0

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

https://commits.kde.org/digikam/5893fde69518d70904d2b96e13800c7432b4e4c4
Comment 10 Maik Qualmann 2018-01-15 19:34:14 UTC
Fix for digiKam-5.8.0: See Bug 388977

Maik
Comment 11 dajomu 2018-03-26 22:08:05 UTC
This bug is not fixed in digikam 5.9. "Failed to update the database schema from version 8 to version 9." Also running MariaDB 10.2.13
Comment 12 dajomu 2018-03-26 22:12:44 UTC
(In reply to dajomu from comment #11)
> This bug is not fixed in digikam 5.9. "Failed to update the database schema
> from version 8 to version 9." Also running MariaDB 10.2.13

This is the output from DebugView;


[1788] digikam.widgets: Breeze icons ressource file found
[1788] digikam.general: AlbumWatch use QFileSystemWatcher
[1788] Failed to query "\\\\?\\acpi#pnp0c0a#1#{72631e54-78a4-11d0-bcf7-00aa00b7b32a}" reason: "The system cannot find the file specified. 2"
[1788] digikam.general: Database Parameters:
[1788]    Type:                     "QMYSQL"
[1788]    DB Core Name:             "digikam"
[1788]    DB Thumbs Name:           "digikam"
[1788]    DB Face Name:             "digikam"
[1788]    Connect Options:          ""
[1788]    Host Name:                "localhost"
[1788]    Host port:                3307
[1788]    Internal Server:          true
[1788]    Internal Server Path:     "C:/Users/beda/Pictures/Digikam"
[1788]    Internal Server Serv Cmd: "C:/Program Files/MariaDB 10.2/bin/mysqld.exe"
[1788]    Internal Server Init Cmd: "C:/Program Files/MariaDB 10.2/bin/mysql_install_db.exe"
[1788]    Username:                 "root"
[1788]    Password:                 ""
[1788] 
[1788] digikam.databaseserver: Database Parameters:
[1788]    Type:                     "QMYSQL"
[1788]    DB Core Name:             "digikam"
[1788]    DB Thumbs Name:           "digikam"
[1788]    DB Face Name:             "digikam"
[1788]    Connect Options:          ""
[1788]    Host Name:                "localhost"
[1788]    Host port:                3307
[1788]    Internal Server:          true
[1788]    Internal Server Path:     "C:/Users/beda/Pictures/Digikam"
[1788]    Internal Server Serv Cmd: "C:/Program Files/MariaDB 10.2/bin/mysqld.exe"
[1788]    Internal Server Init Cmd: "C:/Program Files/MariaDB 10.2/bin/mysql_install_db.exe"
[1788]    Username:                 "root"
[1788]    Password:                 ""
[1788] 
[1788] digikam.databaseserver: Internal Server data path: "C:/Users/beda/Pictures/Digikam/.mysql.digikam/db_data"
[1788] digikam.databaseserver: The mysql configuration was already up-to-date: "C:/Users/beda/AppData/Local/digikam/mysql.conf"
[7396] Suspending
[1788] digikam.databaseserver: Database server: "C:/Program Files/MariaDB 10.2/bin/mysqld.exe" ("--defaults-file=C:\\Users\\beda\\AppData\\Local\\digikam\\mysql.conf", "--datadir=C:\\Users\\beda\\Pictures\\Digikam\\.mysql.digikam\\db_data", "--skip-networking=0", "--port=3307")
[1788] digikam.databaseserver: Internal database server started
[1788] digikam.databaseserver: Running 0 seconds...
[1788] digikam.dbengine: Loading SQL code from config file "C:/Program Files/digiKam/data/digikam/database/dbconfig.xml"
[1788] digikam.dbengine: Checking XML version ID => expected:  3  found:  3
[1788] digikam.coredb: Core database: running schema update
[1788] digikam.coredb: Core database: have a structure version  8
[1788] digikam.coredb: Core database: makeUpdates  8  to  9
[1788] digikam.dbengine: Failure executing query:
[1788]  "" 
[1788] Error messages: "QMYSQL: Unable to execute query" "Cannot delete rows from table which is parent in a foreign key constraint 'Images_Albums' of table 'images'" 1834 2 
[1788] Bound values:  ()
[1788] digikam.dbengine: Error while executing DBAction [ "UpdateSchemaFromV7ToV9" ] Statement [ "ALTER TABLE Albums MODIFY COLUMN relativePath LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;" ]
[1788] digikam.coredb: Core database: schema update to V 9 failed!
[1788] digikam.coredb: Core database: cannot process schema initialization
[1788] KMemoryInfo: Platform identified :  "WINDOWS"
[1788] KMemoryInfo: TotalRam:  8465080320
[1788] digikam.general: Allowing a cache size of 200 MB
[1788] QThreadStorage: Thread 0x23ebb750 exited after QThreadStorage 7 destroyed
[1788] QWaitCondition: Destroyed while threads are still waiting
[1788] QThread: Destroyed while thread is still running
[6900] [kpdf2wordshellext]DllCanUnloadNow, module=C:\Users\beda\AppData\Local\Kingsoft\WPS Office\10.2.0.5996\office6\addons\kpdf2wordshellext\kpdf2wordshellext64.dll
[6900] [kpdf2wordshellext]DllCanUnloadNow, module=C:\Users\beda\AppData\Local\Kingsoft\WPS Office\10.2.0.5996\office6\addons\kpdf2wordshellext\kpdf2wordshellext64.dll
[6900] [kpdf2wordshellext]DllCanUnloadNow, module=C:\Users\beda\AppData\Local\Kingsoft\WPS Office\10.2.0.5996\office6\addons\kpdf2wordshellext\kpdf2wordshellext64.dll
Comment 13 Maik Qualmann 2018-03-27 06:12:25 UTC
There should be no mistake here. A table with the lowercase name "images" should not exist. Presumably their database is corrupted by previous update attempts. I'm going to create a custom dbconfig.xml for testing this evening. If that does not work, you'll need to create a new database.

Maik
Comment 14 Maik Qualmann 2018-03-27 17:16:45 UTC
Created attachment 111685 [details]
dbconfig.xml

As I said, an 'image' table should not exist and was never created by digiKam. This is an individual dbconfig.xml and an attempt to solve the problem without a new database. Please test it.

Replace the dbconfig.xml with this one.

C:\Program Files\digiKam\data\digikam\database

Maik
Comment 15 Ian Robertson 2018-04-26 11:22:27 UTC
also on fedora26 (mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64)) after rpm update from 5.5
Comment 16 Maik Qualmann 2018-04-26 12:02:06 UTC
On which digiKam version updated? Please post the output of the console.

Maik
Comment 17 Ian Robertson 2018-04-26 12:12:48 UTC
Mistaken upgrade was from 5.7

[ianrob01@nova ~]$ rpm -qa | grep digikam
digikam-libs-5.9.0-1.fc26.x86_64
digikam-5.9.0-1.fc26.x86_64
digikam-doc-5.9.0-1.fc26.noarch

from non updated machine
root@lenovo ~]# rpm -qa | grep digikam
digikam-5.7.0-4.fc26.x86_64
digikam-libs-5.7.0-4.fc26.x86_64
digikam-doc-5.7.0-4.fc26.noarch

confused as downgrade takes me to 5.5
Comment 18 Maik Qualmann 2018-04-26 16:29:56 UTC
Please start digiKam in a console and post the messages to see what happens when the database is updated.

Maik
Comment 19 Ian Robertson 2018-04-26 17:28:59 UTC
[ianrob01@nova ~]$ digikam
digikam.coredb: Core database: cannot process schema initialization

DB is available and working with 5.7 appimage or docker
Comment 20 Maik Qualmann 2018-04-27 06:10:15 UTC
I want to see more messages from the console. You may need to enable the debug messages in your distribution first. Maybe look in kdebugdialog, if the output for digiKam is activated.

Maik
Comment 21 Ian Robertson 2018-04-27 09:26:21 UTC
Hi Maik,

Would very much like to give you more debug info am not familiar with
kdedebugdialog (   fired it up checked only digikam items , but dont see
any additional output ) have installed
digikam-debuginfo-5.9.0-1.fc26.x86_64 as suggested by gdb .  Can you
point me at some help.


Prior to raising the bug I saw message about the digikam db user not
having sufficient priv  ( strace ? ) but am able to reproduce this . (
db is on a remote centos box )

Is there any way or running the schema upgrade standalone as a workaround ?
Comment 22 Maik Qualmann 2018-04-27 10:38:58 UTC
Please try in the console before executing digiKam to delete this variable. This has nothing to do with the debug packages and does not need to be installed.

export QT_LOGGING_RULES=""
digikam


Maik
Comment 23 Ian Robertson 2018-04-27 11:06:22 UTC
QT_LOGGING_RULES was not set
[ianrob01@nova ~]$ env | grep QT_LOGGING_RULES
[ianrob01@nova ~]$ echo $QT_LOGGING_RULES

explicitly setting null made no difference

[ianrob01@nova ~]$ export QT_LOGGING_RULES=""
[ianrob01@nova ~]$ digikam
digikam.coredb: Core database: cannot process schema initialization
Comment 24 Maik Qualmann 2018-04-27 11:14:10 UTC
Does this file exist? If yes, rename.

~/.config/QtProject/qtlogging.ini

Maik
Comment 25 Simon 2018-04-27 11:19:52 UTC
The following might help:
  export QT_LOGGING_RULES="digikam.*=true"

Or also check for qtlogging.ini in /etc/xdg/QtProject/. If there is a "*.debug=false" line in there, that's the problem. This is shipped by default in newer fedora and debian (and thus probably everywhere else too). I asked about it here https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=873369
Comment 26 Maik Qualmann 2018-04-27 11:21:35 UTC
There may also be a global setting in Fedora, then set the content:

[Rules]
*.debug=true
qt.*.debug=false

Maik
Comment 27 Ian Robertson 2018-04-27 11:28:58 UTC
Nice one Simon. 
[ianrob01@nova ~]$ export QT_LOGGING_RULES="digikam.*=true"
[ianrob01@nova ~]$ digikam
digikam.general: AlbumWatch use QFileSystemWatcher
digikam.general: Database Parameters:
   Type:                     "QMYSQL"
   DB Core Name:             "digikam5"
   DB Thumbs Name:           "digikam5"
   DB Face Name:             "digikam5"
   Connect Options:          ""
   Host Name:                "pro.barony.loc"
   Host port:                3306
   Internal Server:          false
   Internal Server Path:     ""
   Internal Server Serv Cmd: ""
   Internal Server Init Cmd: ""
   Username:                 "digikam5"
   Password:                 "XXXXXXXX"

digikam.dbengine: Loading SQL code from config file "/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" "You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)" 1419 2 
Bound values:  ()
digikam.dbengine: Error while executing DBAction [ "UpdateSchemaFromV7ToV9" ] Statement [ "DROP TRIGGER IF EXISTS delete_image;" ]
digikam.coredb: Core database: schema update to V 9 failed!
digikam.coredb: Core database: cannot process schema initialization

---
Odd as have granted ALL on digikam5.* to 'digikam5'@'%' - disable binary logging  ( mariadb )

Ian
Comment 28 Ian Robertson 2018-04-27 11:38:36 UTC
confirm grants look OK to me 
[root@pro ~]# mysql -udigikam5 -pdigikam5 digikam5
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 114682
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [digikam5]> show grants;
+---------------------------------------------------------------------------------------------------------+
| Grants for digikam5@%                                                                                   |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'digikam5'@'%' IDENTIFIED BY PASSWORD '*88AD091BFF5C660C6DB35CEAE6E3A036F01F5C55' |
| GRANT ALL PRIVILEGES ON `digikam5`.* TO 'digikam5'@'%'                                                  |
+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Comment 29 Maik Qualmann 2018-04-27 11:51:22 UTC
I can not say why you are not a super user. It could help not to use the wildcard character %, but "localhost". Or you can try to set the following variable in mysql:

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

Maik
Comment 30 Ian Robertson 2018-04-27 12:17:00 UTC
Thanks, did the trick. ( db user cannot be @localhost,  db runs on centos nas box, digikam runs on various fedora elsewheres [ can only run digikam 4.x on centos ] , suppose '%.barony.loc' would really be best )

Ian
Comment 31 Maik Qualmann 2018-07-22 07:33:28 UTC
*** Bug 396734 has been marked as a duplicate of this bug. ***