Bug 387364

Summary:	makeUpdates 8 to 9 Failure executing query
Product:	[Applications] digikam	Reporter:	pascal lacroix <kikoucalou>
Component:	Database-Mysql	Assignee:	Digikam Developers <digikam-bugs-null>
Status:	RESOLVED FIXED
Severity:	normal	CC:	metzpinguin
Priority:	NOR
Version First Reported In:	5.8.0
Target Milestone:	---
Platform:	Other
OS:	Linux
Latest Commit:	https://commits.kde.org/digikam/7a99e10fa55caa928646d8ad76a8a60e41b8ac7a	Version Fixed/Implemented In:	5.8.0
Sentry Crash Report:

Description pascal lacroix 2017-11-27 17:19:13 UTC

Schema update to v9 failled

Version du serveur : 10.2.10-MariaDB-10.2.10


./digikam-5.8.0-20171126T055526-x86-64.appimage 

...

digikam.dbengine: Loading SQL code from config file "/tmp/.mount_yEHrA3/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 delete rows from table which is parent in a foreign key constraint 'ImageTagProperties_Tags' of table 'ImageTagProperties'" 1834 2 
Bound values:  ()
digikam.dbengine: Error while executing DBAction [ "UpdateSchemaFromV8ToV9" ] Statement [ "ALTER TABLE Tags MODIFY COLUMN name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;" ]
digikam.coredb: Core database: schema update to V 9 failed!
digikam.coredb: Core database: cannot process schema initialization

Comment 1 Maik Qualmann 2017-11-27 17:30:20 UTC

The AppImage is too old. There is already another syntax for changing the tables. Please wait a bit in the next few days with the pre-release AppImages if MySQL is used.

Maik

Comment 2 Maik Qualmann 2017-12-08 06:56:50 UTC

Can you please repeat the DB update with the current pre-release AppImage?

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

Maik

Comment 3 pascal lacroix 2017-12-08 11:54:40 UTC

I will proceed this DB update soon as possible this WE and revert

Pascal

Comment 4 pascal lacroix 2017-12-11 04:50:12 UTC

DB update error almost fixed with pre-release : ./digikam-5.8.0-20171207T053823-x86-64.appimage 

...

digikam.database: Database Parameters:
   Type:                     "QMYSQL"
   DB Core Name:             "digikamcore"
   DB Thumbs Name:           "digikamthumbs"
   DB Face Name:             "digikamface"
   Connect Options:          ""
   Host Name:                "127.0.0.1"
   Host port:                3306
   Internal Server:          false
   Internal Server Path:     ""
   Internal Server Serv Cmd: ""
   Internal Server Init Cmd: ""
   Username:                 "pascalou"
   Password:                 "XXXXXXXX"

digikam.dbengine: Failure executing query:
 "" 
Error messages: "QMYSQL: Unable to execute query" "Table 'digikamcore.Albums' doesn't exist" 1146 2 
Bound values:  ()
digikam.dbengine: Error while executing DBAction [ "Migrate_Cleanup_Prepare" ] Statement [ "\n                ALTER TABLE Albums DROP FOREIGN KEY Albums_Images;\n            " ]
digikam.coredb: Core database: running schema update
digikam.coredb: Core database: no database file available
digikam.coredb: Core database: trying to copy contents from DB with ActionName: [ "Migrate_Read_AlbumRoots" ] to DB with ActionName [ "Migrate_Write_AlbumRoots" ]
digikam.coredb: Core database: driver doesn't support query size. We try to go to the last row and back to the current.
digikam.coredb: Core database: result size: [ 0 ]
digikam.coredb: Core database: query isOnValidRow [ true ] isActive [ true ] result size: [ -1 ]
digikam.coredb: Core database: column: [ "id" ] value [ QVariant(qlonglong, 1) ]
digikam.coredb: Core database: column: [ "label" ] value [ QVariant(QString, "Images") ]
digikam.coredb: Core database: column: [ "status" ] value [ QVariant(qlonglong, 0) ]
digikam.coredb: Core database: column: [ "type" ] value [ QVariant(qlonglong, 1) ]
digikam.coredb: Core database: column: [ "identifier" ] value [ QVariant(QString, "volumeid:?uuid=b077f6de-7757-4f4d-8869-0dbb54691b2c") ]
digikam.coredb: Core database: column: [ "specificPath" ] value [ QVariant(QString, "/home/pascalou/Images") ]
digikam.coredb: Core database: trying to copy contents from DB with ActionName: [ "Migrate_Read_Albums" ] to DB with ActionName [ "Migrate_Write_Albums" ]
digikam.coredb: Core database: driver doesn't support query size. We try to go to the last row and back to the current.
digikam.coredb: Core database: result size: [ 674 ]
digikam.coredb: Core database: query isOnValidRow [ true ] isActive [ true ] result size: [ -1 ]
digikam.coredb: Core database: column: [ "id" ] value [ QVariant(qlonglong, 1) ]
digikam.coredb: Core database: column: [ "albumRoot" ] value [ QVariant(qlonglong, 1) ]
digikam.coredb: Core database: column: [ "relativePath" ] value [ QVariant(QString, "/") ]
digikam.coredb: Core database: column: [ "date" ] value [ QVariant(QString, "2017-12-03") ]
digikam.coredb: Core database: column: [ "caption" ] value [ QVariant(QString, "") ]
digikam.coredb: Core database: column: [ "collection" ] value [ QVariant(QString, "") ]
digikam.coredb: Core database: query isOnValidRow [ true ] isActive [ true ] result size: [ -1 ]
digikam.coredb: Core database: column: [ "id" ] value [ QVariant(qlonglong, 2) ]
.....

After restart face database error :

-- digiKam AppImage Bundle
-- Use 'help' as CLI argument to know all available options
QApplication: invalid style override passed, ignoring it.
digikam.widgets: Breeze icons ressource file found
digikam.general: AlbumWatch use QFileSystemWatcher
digikam.general: Database Parameters:
   Type:                     "QMYSQL"
   DB Core Name:             "digikamcore"
   DB Thumbs Name:           "digikamthumbs"
   DB Face Name:             "digikamface"
   Connect Options:          ""
   Host Name:                "127.0.0.1"
   Host port:                3306
   Internal Server:          false
   Internal Server Path:     ""
   Internal Server Serv Cmd: ""
   Internal Server Init Cmd: ""
   Username:                 "pascalou"
   Password:                 "XXXXXXXX"

digikam.dbengine: Loading SQL code from config file "/tmp/.mount_sZUGaK/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  9
digikam.coredb: Core database: makeUpdates  9  to  9
digikam.database: Creating new Location  "/home/pascalou/Images"  uuid  "volumeid:?uuid=b077f6de-7757-4f4d-8869-0dbb54691b2c"
digikam.database: location for  "/home/pascalou/Images"  is available  true
KMemoryInfo: Platform identified :  "LINUX"
KMemoryInfo: TotalRam:  3690930176
digikam.general: Allowing a cache size of 175 MB
digikam.thumbsdb: ThumbDB SelectThumbnailSetting val ret =  0
digikam.thumbsdb: ThumbDB SelectThumbnailSetting val ret =  0
digikam.thumbsdb: Thumbs database: have a structure version  "3"
digikam.general: Thumbnails database ready for use
digikam.dimg: ("/usr/share/color/icc", "/home/pascalou/.local/share/icc", "/home/pascalou/Documents/Profiles_icc/RGB")
digikam.dimg: The original Adobe RGB (1998) profile has been found at "/home/pascalou/Documents/Profiles_icc/RGB/AdobeRGB1998.icc"
digikam.dimg: Found X.org XICC monitor profile  "PL2473HD #1 2017-04-02 09-14 2.2 F-S XYZLUT+MTX"
digikam.general: Camera XML data:  "/home/pascalou/.local/share/digikam/cameras.xml"
digikam.facedb: Face database: no database file available
digikam.dbengine: Failure executing query:
 "" 
Error messages: "QMYSQL: Unable to execute query" "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows INTEGER,\n                            cols INTEGER,\n                        ' at line 7" 1064 2 
Bound values:  ()
digikam.dbengine: Error while executing DBAction [ "CreateFaceDBOpenCVLBPH" ] Statement [ "CREATE TABLE IF NOT EXISTS OpenCVLBPHistograms\n                            (id INTEGER PRIMARY KEY AUTO_INCREMENT,\n                            recognizerid INTEGER,\n                            identity INTEGER,\n                            context TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,\n                            type INTEGER,\n                            rows INTEGER,\n                            cols INTEGER,\n                            data BLOB) ENGINE InnoDB;\n                " ]
digikam.facedb: Face database: cannot process schema initialization
digikam.facesengine: Failed to initialize face database
digikam.general: Face PipeLine: add database writer
digikam.general: Face PipeLine: add faces trainer
digikam.facedb: FaceDB SelectFaceSetting val ret =  0
digikam.facedb: FaceDB SelectFaceSetting val ret =  0
digikam.facedb: Face database: have a structure version  ""
digikam.facedb: DBFaceVersion not available! Giving up schema upgrading.
digikam.facedb: Face database: cannot process schema initialization
digikam.facesengine: Failed to initialize face database
digikam.general: Face PipeLine: add database writer
digikam.general: Face PipeLine: add faces trainer
digikam.geoiface: "setting backend marble"
QtAV 1.12.0(Jul 20 2017, 17:39:32)

...

digikam.general: scan mode: ScanDeferredFiles
digikam.general: total scan value :  104895
digikam.database: items to tag ()
digikam.database: Complete scan took: 85928 msecs.
digikam.general: Adjusting environment variables for AppImage bundle
digikam.general: Event is dispatched to desktop notifier through DBUS

Pascal

Comment 5 Maik Qualmann 2017-12-11 18:07:15 UTC

Git commit 7a99e10fa55caa928646d8ad76a8a60e41b8ac7a by Maik Qualmann.
Committed on 11/12/2017 at 18:06.
Pushed by mqualmann into branch 'master'.

use backticks for reserved MySQL words in the face database
FIXED-IN: 5.8.0

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

https://commits.kde.org/digikam/7a99e10fa55caa928646d8ad76a8a60e41b8ac7a

Comment 6 pascal lacroix 2017-12-13 22:15:53 UTC

Face DB update error fixed on ./digikam-5.8.0-20171212T132305-x86-64.appimage 

...
digikam.general: Camera XML data:  "/home/pascalou/.local/share/digikam/cameras.xml"
digikam.facedb: FaceDB SelectFaceSetting val ret =  0
digikam.facedb: FaceDB SelectFaceSetting val ret =  0
digikam.facedb: Face database: have a structure version  "2"
digikam.facesengine: Face database ready for use
digikam.general: Face PipeLine: add database writer
digikam.general: Face PipeLine: add faces trainer
digikam.facesengine: Face database ready for use
digikam.general: Face PipeLine: add database writer
digikam.general: Face PipeLine: add faces trainer
digikam.geoiface: "setting backend marble"
QtAV 1.12.0(Jul 20 2017, 17:39:32)
Multimedia framework base on Qt and FFmpeg.
...

Bravo  !!!

Pascal