| Summary: | Failed to update the database schema from version 10 to version 11. | ||
|---|---|---|---|
| Product: | [Applications] digikam | Reporter: | Vincent Danjean <vdanjean.ml> |
| Component: | Database-Mysql | Assignee: | Digikam Developers <digikam-bugs-null> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | metzpinguin, vdanjean.ml |
| Priority: | NOR | ||
| Version: | 7.5.0 | ||
| Target Milestone: | --- | ||
| Platform: | Debian unstable | ||
| OS: | Linux | ||
| Latest Commit: | Version Fixed In: | 7.7.0 | |
| Sentry Crash Report: | |||
Activate Qt Debug Log: export QT_LOGGING_RULES="digikam.*=true" Post the resulting log in the terminal. Maik The main problem on Ubuntu and Debian with this update from 10 to 11 was lack of permissions to create triggers. Diehe Bug 440458 for solution. Maik ``` $ QT_LOGGING_RULES="digikam.*=true" digikam digikam.widgets: Use installed icons digikam.general: Switch to widget style: "" digikam.general: AlbumWatch is disabled digikam.general: Database Parameters: Type: "QMYSQL" DB Core Name: "digikamdb" DB Thumbs Name: "digikamthumbdb" DB Face Name: "digikamfaces" DB Similarity Name: "" Connect Options: "" Host Name: "localhost" Host port: 3306 Internal Server: false Internal Server Path: "" Internal Server Admin Cmd: "mysqladmin" Internal Server Serv Cmd: "" Internal Server Init Cmd: "" Username: "digikamuser" 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 10 digikam.coredb: Core database: makeUpdates 10 to 15 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 [ "UpdateSchemaFromV10ToV11" ] Statement [ "CREATE TRIGGER temp_insert_tagstree AFTER INSERT ON temp_Tags\n FOR EACH ROW INSERT INTO TagsTree SELECT NEW.id, NEW.pid\n UNION SELECT NEW.id, pid FROM TagsTree WHERE id = NEW.pid;\n " ] digikam.coredb: Core database: schema update to V 11 failed! digikam.coredb: Core database: cannot process schema initialization ========== digikam.general: KMemoryInfo: Platform identified : "LINUX" digikam.general: KMemoryInfo: TotalRam: 33504178176 digikam.general: Allowing a cache size of 400 MB QThreadStorage: Thread 0x7ffff7fc2b00 exited after QThreadStorage 12 destroyed ``` Popups appears where I wrote "========" (not present in the terminal) And I checked with phpmyadmin: on the three databases (digikamdb, digikamfaces, digikamthumbdb), the "digikamuser" has all permissions but the "GRANT" one (and no table specific permission). In particular, the "TRIGGER" permission is set (phpmyadmin popup help: "allow creating and dropping triggers.") It's the problem, run this in the MySQL: set global log_bin_trust_function_creators=1; Maik Read also this Bug 435065 Maik By executing the following mysql commands:
```
update user set Super_priv='Y' where User='digikamuser';
FLUSH PRIVILEGES;
```
I've been able to go to the next mysql command. But this new one also fails (with the same global behavior).
In the terminal, I can see this:
```
digikam.widgets: Use installed icons
digikam.general: Switch to widget style: ""
digikam.general: AlbumWatch is disabled
digikam.general: Database Parameters:
Type: "QMYSQL"
DB Core Name: "digikamdb"
DB Thumbs Name: "digikamthumbdb"
DB Face Name: "digikamfaces"
DB Similarity Name: ""
Connect Options: ""
Host Name: "localhost"
Host port: 3306
Internal Server: false
Internal Server Path: ""
Internal Server Admin Cmd: "mysqladmin"
Internal Server Serv Cmd: ""
Internal Server Init Cmd: ""
Username: "digikamuser"
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 10
digikam.coredb: Core database: makeUpdates 10 to 15
digikam.dbengine: Failure executing query:
""
Error messages: "QMYSQL: Unable to execute query" "Column count doesn't match value count at row 1" "1136" 2
Bound values: ()
digikam.dbengine: Error while executing DBAction [ "UpdateSchemaFromV10ToV11" ] Statement [ "REPLACE INTO temp_Tags SELECT * FROM Tags WHERE id != 0;" ]
digikam.coredb: Core database: schema update to V 11 failed!
digikam.coredb: Core database: cannot process schema initialization
digikam.general: KMemoryInfo: Platform identified : "LINUX"
digikam.general: KMemoryInfo: TotalRam: 33504178176
digikam.general: Allowing a cache size of 400 MB
QThreadStorage: Thread 0x7ffff7fc2b00 exited after QThreadStorage 12 destroyed
```
In mariadb logs, I can see this:
```
2022-03-13T19:07:51.776807Z 246 Connect digikamuser@localhost on digikamdb using Socket
2022-03-13T19:07:51.776943Z 246 Init DB digikamdb
2022-03-13T19:07:51.777044Z 246 Query SET NAMES utf8mb3
2022-03-13T19:07:51.777179Z 246 Prepare SELECT ? + ?
2022-03-13T19:07:51.777237Z 246 Close stmt
2022-03-13T19:07:51.777261Z 246 Quit
2022-03-13T19:07:52.621166Z 247 Connect digikamuser@localhost on digikamdb using Socket
2022-03-13T19:07:52.621331Z 247 Init DB digikamdb
2022-03-13T19:07:52.621415Z 247 Query SET NAMES utf8mb3
2022-03-13T19:07:52.621514Z 247 Prepare SELECT ? + ?
2022-03-13T19:07:52.621574Z 247 Close stmt
2022-03-13T19:07:52.621831Z 248 Connect digikamuser@localhost on digikamdb using Socket
2022-03-13T19:07:52.621933Z 248 Init DB digikamdb
2022-03-13T19:07:52.622048Z 248 Query SET NAMES utf8mb3
2022-03-13T19:07:52.622176Z 248 Prepare SELECT ? + ?
2022-03-13T19:07:52.622191Z 248 Close stmt
2022-03-13T19:07:52.622342Z 248 Query DROP TABLE IF EXISTS PrivCheck
2022-03-13T19:07:53.639186Z 248 Query CREATE TABLE IF NOT EXISTS PrivCheck
(id INT,
name VARCHAR(35))
ENGINE InnoDB
2022-03-13T19:07:57.806128Z 248 Query ALTER TABLE PrivCheck DROP COLUMN name
2022-03-13T19:07:57.826204Z 248 Query DROP TABLE PrivCheck
2022-03-13T19:07:57.832284Z 248 Query DROP TABLE IF EXISTS PrivCheck
2022-03-13T19:07:57.833875Z 248 Quit
2022-03-13T19:07:57.833977Z 247 Query select table_name from information_schema.tables where table_schema = 'digikamdb' and table_type = 'BASE TABLE'
2022-03-13T19:07:57.834987Z 247 Prepare SELECT value FROM Settings WHERE keyword=?
2022-03-13T19:07:57.835067Z 247 Reset stmt
2022-03-13T19:07:57.835163Z 247 Execute SELECT value FROM Settings WHERE keyword='DBVersion'
2022-03-13T19:07:57.835381Z 247 Close stmt
2022-03-13T19:07:57.835431Z 247 Prepare SELECT value FROM Settings WHERE keyword=?
2022-03-13T19:07:57.835444Z 247 Reset stmt
2022-03-13T19:07:57.835482Z 247 Execute SELECT value FROM Settings WHERE keyword='DBVersionRequired'
2022-03-13T19:07:57.835585Z 247 Close stmt
2022-03-13T19:07:57.835677Z 247 Query BEGIN WORK
2022-03-13T19:07:57.835819Z 247 Query DROP TRIGGER IF EXISTS temp_insert_tagstree
2022-03-13T19:07:57.837309Z 247 Query DROP TRIGGER IF EXISTS insert_tagstree
2022-03-13T19:07:57.838890Z 247 Query DROP TRIGGER IF EXISTS delete_tagstree
2022-03-13T19:07:57.840331Z 247 Query DROP TRIGGER IF EXISTS move_tagstree
2022-03-13T19:07:57.843124Z 247 Query DROP TABLE IF EXISTS temp_Tags
2022-03-13T19:07:57.851436Z 247 Query DROP TABLE IF EXISTS TagsTree
2022-03-13T19:07:57.858627Z 247 Query DROP VIEW IF EXISTS TagsTree
2022-03-13T19:07:57.860223Z 247 Query CREATE TABLE IF NOT EXISTS temp_Tags
(id INTEGER,
pid INTEGER,
name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
icon BIGINT,
iconkde LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
lft INTEGER,
rgt INTEGER)
ENGINE InnoDB
2022-03-13T19:07:57.873927Z 247 Query CREATE TABLE IF NOT EXISTS TagsTree
(id INTEGER NOT NULL,
pid INTEGER NOT NULL,
UNIQUE (id, pid))
ENGINE InnoDB
2022-03-13T19:07:57.887722Z 247 Query CREATE TRIGGER temp_insert_tagstree AFTER INSERT ON temp_Tags
FOR EACH ROW INSERT INTO TagsTree SELECT NEW.id, NEW.pid
UNION SELECT NEW.id, pid FROM TagsTree WHERE id = NEW.pid
2022-03-13T19:07:57.891701Z 247 Query REPLACE INTO temp_Tags SELECT * FROM Tags WHERE id != 0
2022-03-13T19:07:57.893000Z 247 Query ROLLBACK
2022-03-13T19:07:57.893129Z 247 Prepare REPLACE INTO Settings VALUES (?,?)
2022-03-13T19:07:57.893173Z 247 Reset stmt
2022-03-13T19:07:57.893210Z 247 Execute REPLACE INTO Settings VALUES ('DBVersion','10')
2022-03-13T19:07:57.894267Z 247 Close stmt
2022-03-13T19:07:57.894334Z 247 Prepare REPLACE INTO Settings VALUES (?,?)
2022-03-13T19:07:57.894350Z 247 Reset stmt
2022-03-13T19:07:57.894407Z 247 Execute REPLACE INTO Settings VALUES ('DBVersionRequired','5')
2022-03-13T19:07:57.895533Z 247 Close stmt
```
Regards,
Vincent
Hmm, are the "lft" and "rgt" columns still there in the "Tags" table? It is possible that you have started too many attempts and these columns have already been deleted. Maik Yes, it was my fault. Before opening this bug, I tried to execute the upgrade commands from /usr/share/digikam/database/dbconfig.xml As I was connected as root (and not digikamuser), I did not see the permission problems. But I executed all the command (the last two ones removed the columns). Adding them back allows digikam to upgrade the database until v15. Many thanks for your help. I will open a bug report on Debian so that the permission problem be documented more visibly. You might also want to improve the error message and to suggest to re-run digikam with 'QT_LOGGING_RULES="digikam.*=true"' in the error popup. The popup says "Please read the error messages printed on the console", but there was no messages without QT_LOGGING_RULES set. Regards Vincent Well, we had to restrict logging because it caused crashes on Ubuntu and Debian. ((:-)) There is a note in the AppImage in the terminal and on our home page under "Contribute". Of course we can add as info in the message or print in the terminal. Thank you for your feedback, I'm closing the bug now. Maik |
SUMMARY Last version of digikam in debian unstable fails to start due to a problem in db upgrade. STEPS TO REPRODUCE 1. I run "digikam" in a terminal (it has been several months I did not run it) OBSERVED RESULT In the terminal : digikam.coredb: Core database: cannot process schema initialization In a small window (popup), the classical "Scanning collections, please wait..." but stuck at 0% with the "Error" message. In another popup windows, not classical, "Failed to update the database schema from version 10 to version 11. Please read the error messages printed on the console and report this error as a bug at bugs.kde.org." Clicking to the (only) "OK" button in the second popup makes both popups to disappear and the "digikam" command to end. Note: 1) I'm using an external DB (mysql when I started a long time ago, and mariadb now) 2) in mysql log, I see this : 2022-03-13T17:33:11.477700Z 121 Connect digikamuser@localhost on digikamdb using Socket 2022-03-13T17:33:11.478373Z 121 Init DB digikamdb 2022-03-13T17:33:11.479252Z 121 Query SET NAMES utf8mb3 2022-03-13T17:33:11.479590Z 121 Prepare SELECT ? + ? 2022-03-13T17:33:11.479629Z 121 Close stmt 2022-03-13T17:33:11.479842Z 121 Quit 2022-03-13T17:33:12.339278Z 122 Connect digikamuser@localhost on digikamdb using Socket 2022-03-13T17:33:12.339978Z 122 Init DB digikamdb 2022-03-13T17:33:12.342229Z 122 Query SET NAMES utf8mb3 2022-03-13T17:33:12.342731Z 122 Prepare SELECT ? + ? 2022-03-13T17:33:12.342815Z 122 Close stmt 2022-03-13T17:33:12.343555Z 123 Connect digikamuser@localhost on digikamdb using Socket 2022-03-13T17:33:12.343891Z 123 Init DB digikamdb 2022-03-13T17:33:12.344999Z 123 Query SET NAMES utf8mb3 2022-03-13T17:33:12.345523Z 123 Prepare SELECT ? + ? 2022-03-13T17:33:12.345585Z 123 Close stmt 2022-03-13T17:33:12.345841Z 123 Query DROP TABLE IF EXISTS PrivCheck 2022-03-13T17:33:12.347942Z 123 Query CREATE TABLE IF NOT EXISTS PrivCheck (id INT, name VARCHAR(35)) ENGINE InnoDB 2022-03-13T17:33:12.358940Z 123 Query ALTER TABLE PrivCheck DROP COLUMN name 2022-03-13T17:33:12.380712Z 123 Query DROP TABLE PrivCheck 2022-03-13T17:33:12.387938Z 123 Query DROP TABLE IF EXISTS PrivCheck 2022-03-13T17:33:12.390167Z 122 Query select table_name from information_schema.tables where table_schema = 'digikamdb' and table_type = 'BASE TABLE' 2022-03-13T17:33:12.390796Z 123 Quit 2022-03-13T17:33:12.391412Z 122 Prepare SELECT value FROM Settings WHERE keyword=? 2022-03-13T17:33:12.391509Z 122 Reset stmt 2022-03-13T17:33:12.391574Z 122 Execute SELECT value FROM Settings WHERE keyword='DBVersion' 2022-03-13T17:33:12.391793Z 122 Close stmt 2022-03-13T17:33:12.391855Z 122 Prepare SELECT value FROM Settings WHERE keyword=? 2022-03-13T17:33:12.391867Z 122 Reset stmt 2022-03-13T17:33:12.391904Z 122 Execute SELECT value FROM Settings WHERE keyword='DBVersionRequired' 2022-03-13T17:33:12.392007Z 122 Close stmt 2022-03-13T17:33:12.392061Z 122 Query BEGIN WORK 2022-03-13T17:33:12.392183Z 122 Query DROP TRIGGER IF EXISTS temp_insert_tagstree 2022-03-13T17:33:12.393283Z 122 Query DROP TRIGGER IF EXISTS insert_tagstree 2022-03-13T17:33:12.394384Z 122 Query DROP TRIGGER IF EXISTS delete_tagstree 2022-03-13T17:33:12.395444Z 122 Query DROP TRIGGER IF EXISTS move_tagstree 2022-03-13T17:33:12.396444Z 122 Query DROP TABLE IF EXISTS temp_Tags 2022-03-13T17:33:12.404139Z 122 Query DROP TABLE IF EXISTS TagsTree 2022-03-13T17:33:12.413945Z 122 Query DROP VIEW IF EXISTS TagsTree 2022-03-13T17:33:12.415528Z 122 Query CREATE TABLE IF NOT EXISTS temp_Tags (id INTEGER, pid INTEGER, name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci, icon BIGINT, iconkde LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci, lft INTEGER, rgt INTEGER) ENGINE InnoDB 2022-03-13T17:33:12.431519Z 122 Query CREATE TABLE IF NOT EXISTS TagsTree (id INTEGER NOT NULL, pid INTEGER NOT NULL, UNIQUE (id, pid)) ENGINE InnoDB 2022-03-13T17:33:12.445760Z 122 Query CREATE TRIGGER temp_insert_tagstree AFTER INSERT ON temp_Tags FOR EACH ROW INSERT INTO TagsTree SELECT NEW.id, NEW.pid UNION SELECT NEW.id, pid FROM TagsTree WHERE id = NEW.pid 2022-03-13T17:33:12.446035Z 122 Query ROLLBACK 2022-03-13T17:33:12.446240Z 122 Prepare REPLACE INTO Settings VALUES (?,?) 2022-03-13T17:33:12.446315Z 122 Reset stmt 2022-03-13T17:33:12.446372Z 122 Execute REPLACE INTO Settings VALUES ('DBVersion','10') 2022-03-13T17:33:12.447019Z 122 Close stmt 2022-03-13T17:33:12.447064Z 122 Prepare REPLACE INTO Settings VALUES (?,?) 2022-03-13T17:33:12.447109Z 122 Reset stmt 2022-03-13T17:33:12.447163Z 122 Execute REPLACE INTO Settings VALUES ('DBVersionRequired','5') 2022-03-13T17:33:12.447849Z 122 Close stmt I do not know how to go futher to debug the problem. EXPECTED RESULT digikam does it db upgrade and start SOFTWARE/OS VERSIONS Windows: macOS: Linux/KDE Plasma: (available in About System) KDE Plasma Version: KDE Frameworks Version: Qt Version: ADDITIONAL INFORMATION