Bug 280678

Please update to 2.0.0 where this problem have been probably fixed...

Gilles Caulier

I installed digikam successfully to ubuntu 11.04 64 bit. The database migration worked. This bug therefore appears to be a Fedora issue.
Installing 2.0.0 does not appear to be an option with Fedora 15. A Fedora 16 package is available, however it will not install.
The 2.0.0 tarball requires so many dependencies that installation is not practical. I gave up after spending time installing cmake only to find that it needs a ton of its own dependencies to work.
I suspect that the bug relates to the dialogue that launches the database migration process. There is probably only the need to make and install a couple of files.
Thanks for your help.

Ed,

This file still valid using digiKam 2.4 ?

Gilles Caulier

The same situation here.
Digikam 2.5, 
Linux arch 3.1.7-1-ARCH #1 SMP PREEMPT Wed Jan 4 08:11:16 CET 2012 x86_64

I have created 2 databases: digikam and digikamthumbs and granted permissions for my user to manage them.

Happens in both cases when I try to migrate from sqlite → mysql, or when just trying to configure digikam to use mysql. I also found that after performing migration it creates 20 tables in the digikam db, but digikamthumbs remains empty.

I have the same issue on openSuse 11.4/KDE4.6.5/Digikam 2.3.0 - clicking on "Test DB Connection" returns "Database connection test successful". I have granted ALL priveleges on *.* to the digikam user (otherwise I get an error about CREATE TRIGGER priveleges not being granted if I only grant database level privleges), but when clicking "Migrate" on the database migration dialog I see the error "Error while creating database schema".

Running digikam from command line with debugging turned on (via kdebugdialog) shows the following:

digikam(13542)/kdeui (KNotification) KNotificationManager::close: 204
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.
digikam(13542)/digikam (core) Digikam::SchemaUpdater::update: SchemaUpdater update
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackend::prepareQuery: Prepare failed!
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query:
 "SELECT value FROM Settings WHERE keyword=?;" 
Error messages: "QMYSQL3: Unable to prepare statement" "Table 'digikam.Settings' doesn't exist" 1146 2 
Bound values:  ()
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query:
 "SELECT value FROM Settings WHERE keyword='DBVersion';" 
Error messages: "QMYSQL: Unable to execute query" "Table 'digikam.Settings' doesn't exist" 1146 2 
Bound values:  (QVariant(QString, "DBVersion") )
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackend::prepareQuery: Prepare failed!
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query:
 "SELECT value FROM Settings WHERE keyword=?;" 
Error messages: "QMYSQL3: Unable to prepare statement" "Table 'digikam.Settings' doesn't exist" 1146 2 
Bound values:  ()
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query:
 "SELECT value FROM Settings WHERE keyword='DBVersionRequired';" 
Error messages: "QMYSQL: Unable to execute query" "Table 'digikam.Settings' doesn't exist" 1146 2 
Bound values:  (QVariant(QString, "DBVersionRequired") )
digikam(13542)/digikam (core) Digikam::SchemaUpdater::startUpdates: Have a database structure version  0
digikam(13542)/digikam (core): DBVersion not available! Giving up schema upgrading. 
digikam(13542)/kdeui (KNotification) KNotificationManager::notificationClosed: 209
digikam(13542)/digikam (core) Digikam::SchemaUpdater::update: SchemaUpdater update
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackend::prepareQuery: Prepare failed!
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query:
 "SELECT value FROM Settings WHERE keyword=?;" 
Error messages: "QMYSQL3: Unable to prepare statement" "Table 'digikam.Settings' doesn't exist" 1146 2 
Bound values:  ()
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query:
 "SELECT value FROM Settings WHERE keyword='DBVersion';" 
Error messages: "QMYSQL: Unable to execute query" "Table 'digikam.Settings' doesn't exist" 1146 2 
Bound values:  (QVariant(QString, "DBVersion") )
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackend::prepareQuery: Prepare failed!
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query:
 "SELECT value FROM Settings WHERE keyword=?;" 
Error messages: "QMYSQL3: Unable to prepare statement" "Table 'digikam.Settings' doesn't exist" 1146 2 
Bound values:  ()
digikam(13542)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query:
 "SELECT value FROM Settings WHERE keyword='DBVersionRequired';" 
Error messages: "QMYSQL: Unable to execute query" "Table 'digikam.Settings' doesn't exist" 1146 2 
Bound values:  (QVariant(QString, "DBVersionRequired") )
digikam(13542)/digikam (core) Digikam::SchemaUpdater::startUpdates: Have a database structure version  0
digikam(13542)/digikam (core): DBVersion not available! Giving up schema upgrading. 
digikam(13542)/kdeui (KNotification) KNotificationManager::notificationClosed: 218
digikam(13542)/kio (Slave) KIO::Slave::kill: killing slave pid 13562 ( "digikamdates://" )

FYI, I have just downloaded and built from the latest git sources. The exact same symptoms occur with version 2.6.0!

Also noticed it tries ti run such sql "CREATE TABLE IF NOT EXISTS AlbumRoots(
id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT...", my user hasn't such permissions.

FYI, I was hitting this exact same problem. Eventually I found that it is *not* sufficient to just do "GRANT ALL PRIVILEGES" for the user/database in question. I had to explicitly give the user the 'SUPER' privilege too. This is what I eventually granted which allowed migration to work

mysql> show grants for 'digikam'@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for digikam@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO 'digikam'@'%' IDENTIFIED BY PASSWORD '*XXXXX' | 
| GRANT ALL PRIVILEGES ON `digikam`.* TO 'digikam'@'%' WITH GRANT OPTION                                 | 
| GRANT ALL PRIVILEGES ON `thumbnails-digikam`.* TO 'digikam'@'%' WITH GRANT OPTION                      | 
+--------------------------------------------------------------------------------------------------------+

Confirmed that granting the "SUPER" privilege for the digikam user also fixed (or worked around) this for me on openSuSE 11.4 as well. I also discovered that I had 2 digikam users - one digikam@localhost and one digikam@% - the latter I granted SUPER to but the former was used in preference - it started working after I dropped the digikam@localhost user (it may have also worked if I granted SUPER to that user instead). 

Is this a security risk, having the digikam user granted SUPER privileges? I haven't yet tested if revoking SUPER after migrating the database will still allow everything to work as expected. In any case, unless this can be changed it needs to be added to the documentation.

I think this is bad idea to grant such privileges. For example amarok works fine without such things... All it needs — username, password and permissions to manage single database.

From memory the SUPER privilege is only required during the migration of the database. Once migrated it can be revoked.Newer versions of MySQL don't need SUPER privs unless you have binary logging enabled.
http://stackoverflow.com/questions/7520593/mysql-trigger-delimiter-without-super

I think this is a duplicate of https://bugs.kde.org/show_bug.cgi?id=262321

(In reply to comment #11)
> I think this is bad idea to grant such privileges. For example amarok works
> fine without such things... All it needs — username, password and
> permissions to manage single database.

for the record; amarok run mysql with skip-grant-tables [1] option, so everybody is root.

If we need to modify the schema, table or indexes we need alter privileges, but we can still make things better separating update of the database from normal operations so you need to provide a user with "alter" provileges only for a limited period of time, when doing the changes.

[1] http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_skip-grant-tables

I just wanted to confirm I'm having the same issue, but digikam is running on a 64-bit slackware system and MySQL 5.0 is running a ReadyNAS Duo (sparc version of Debian). Grant SUPER to my digikam user didn't work!!!

This is the output of my SHOW GRANTS:

mysql> show grants for 'digikam';
+--------------------------------------------------------------------------------------------------------+
| Grants for digikam@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO 'digikam'@'%' IDENTIFIED BY PASSWORD '*xxx' |
| GRANT ALL PRIVILEGES ON `digikamthumbs`.* TO 'digikam'@'%' WITH GRANT OPTION                           |
| GRANT ALL PRIVILEGES ON `digikam`.* TO 'digikam'@'%' WITH GRANT OPTION                                 |
+--------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

and this is the output of my STATUS:
mysql> STATUS;
--------------
mysql  Ver 14.14 Distrib 5.1.56, for slackware-linux-gnu (x86_64) using readline 5.1

Connection id:          37
Current database:       digikam
Current user:           digikam@workstation1
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.24a-Debian_3.infrant1 Infrant ReadyNAS distribution
Protocol version:       10
Connection:             nas1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 2 days 2 min 1 sec

Threads: 1  Questions: 1092  Slow queries: 0  Opens: 416  Flush tables: 1  Open tables: 26  Queries per second avg: 0.006
--------------

There has been a regression from Digikam 2.5.0 to 2.6.0. Under 2.5.0 (Fedora 16) the database connection to MySql worked. Under Digikam 2.6.0 (Fedora 17), it is not possible to connect to MySQL database under any circumstance. 'Database connection test was not successful. Error was: Driver not loaded Driver not loaded.
I have loaded all mysql-connectors available from the Fedora repository. I have granted all possible privileges possible under MySQL, a connection still cannot be made.

I got the schema error at 7% progress on digikam 2.5 with all permissions including super.  Then I figured some people get it to work so... must be mysql settings.  I was using my-small.cnf, slightly modified.  I commented
thread_stack = 128K

..and now it works.  Maybe the schema error is a pretty general failure, but it's worth checking this and other limits.

I got the error message "Error while creating the database schema", because I had accidentally installed mysql 4.1 instead of mysql 5 on my NAS. It would be nice to detect this, or at least give more information in the error message. Proposed error message:

Error while creating the database schema. The following SQL command returned an error: CREATE TRIGGER privcheck_trigger AFTER DELETE ON PrivCheck FOR EACH ROW BEGIN END. Possible reasons:
-Your mysql server is too old. Triggers are supported only from mysql version 5.0 onwards.
-Your mysql account does not have the required privileges. On mysql version 5.0, the account needs the SUPER privilege during migrations. On mysql version 5.1 and above, the mysql account needs the CREATE TRIGGER right on the digikam database.

I think such an error message would greatly ease the search for the cause of this error.

*** This bug has been marked as a duplicate of bug 262321 ***

Not reproducible with 7.0.0 beta 1.