Bug 447416

Summary: Unable to use mariadb 10 remote database with digikam 7.3
Product: [Applications] digikam Reporter: Mattia <mattia.verga>
Component: Database-MysqlAssignee: Digikam Developers <digikam-bugs-null>
Status: RESOLVED FIXED    
Severity: normal CC: metzpinguin
Priority: NOR    
Version First Reported In: 7.3.0   
Target Milestone: ---   
Platform: Other   
OS: Linux   
Latest Commit: Version Fixed/Implemented In: 7.5.0
Sentry Crash Report:

Description Mattia 2021-12-23 10:03:49 UTC 
I had a working digikam 7.2 installation (from Fedora Linux 35 RPMs) with a remote MariaDB 5.x database on a Qnap NAS. Things still worked after I migrated the database to MariaDB 10.x on the same NAS.

After the upgrade to digikam 7.3 (always Fedora Linux RPMs) at startup I got:

digikam.coredb: Core database: running schema update
digikam.coredb: Core database: have a structure version  12
digikam.coredb: Core database: makeUpdates  12  to  13
digikam.dbengine: Failure executing query:
 ""
Error messages: "QMYSQL: Unable to execute query" "Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50544, now running 100508. Please use mariadb-upgrade to fix this error" "1558" 2
Bound values:  ()
digikam.dbengine: Error while executing DBAction [ "UpdateSchemaFromV12ToV13" ] Statement [ "\n                    DROP PROCEDURE IF EXISTS create_index_if_not_exists;\n                " ]
digikam.coredb: Core database: schema update to V 13 failed!
digikam.coredb: Core database: cannot process schema initialization

I made several attempts to export/import the database and to drop the db and let digikam 7.3 recreate it, but it always fails.
So, I started from scratch: downgraded Digikam to 7.2, start Digikam from clean and let it create a local SQLite database. After that I've upgraded Digikam to 7.3, started with the local SQLite database and tried to run the migration tool to the newly created MariaDB on NAS, but I get:

digikam.coredb: Core database: running schema update
digikam.coredb: Core database: no database file available
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 [ "CreateTriggers" ] Statement [ "CREATE TRIGGER insert_tagstree AFTER INSERT ON Tags FOR EACH ROW\n                    INSERT INTO TagsTree SELECT NEW.id, NEW.pid\n                     UNION SELECT NEW.id, pid FROM TagsTree WHERE id = NEW.pid;\n                " ]

Downgrading Digikam to 7.2 and running the migration tool succeeds, but then I return to the initial "schema update failed" error...

Version of components:
digikam-7.2.0-5.fc35
digikam-7.3.0-4.fc35
MariaDB 10.5.8
Comment 1 Maik Qualmann 2021-12-23 12:01:20 UTC 
In the MySQL error message you can already see what you have to do, namely execute "mariadb-upgrade". If you upgrade to a new major version, the database structure must be adapted to the new server version. With an external database, you have to manage that. Otherwise, if you try to change the database, it will fail. digiKam cannot execute the "mariadb-upgrade" command itself for an external database, as extended rights are required. For the internal MySQL database, we have built in the detection and digiKam carries out "mariadb-upgrade" itself.


Maik
Comment 2 Maik Qualmann 2021-12-23 12:48:41 UTC 
Your other problem with Super Privilege: digiKam uses now triggers. In this bug report is what you have to do: Bug 435065.

On MySQL Server PC:

mysql -u USERNAME -h HOSTNAME -p

set global log_bin_trust_function_creators=1;

Maik