[Bug 280678] New: Cannot migrate from sqlite to mysql

https://bugs.kde.org/show_bug.cgi?id=280678

           Summary: Cannot migrate from sqlite to mysql
           Product: digikam
           Version: 1.9.0
          Platform: Fedora RPMs
        OS/Version: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: Database
        AssignedTo: [hidden email]
        ReportedBy: [hidden email]


Version:           1.9.0 (using KDE 4.6.5)
OS:                Linux

'tools' - 'database migration' - 'migrate' results in error: 'Error while
creating the database schema'
'Check DB Connection' shows connection is being made okay.
The database migration creates a single unpopulated table 'AlbumRoots' in
target database. The progress information shows overall progress at 7\% and
step progress at 0\% when error comes up.

Reproducible: Always

Steps to Reproduce:
Create mysql database. Grant all privileges on the database to digikam user.
Test database for creating, editing, dropping \&c. through various mysql
clients.
Go to digikam and elect 'tools' 'database migration'. Migrate from sqlite to
mysql. Enter proper login info for mysql. The error will occur.

Actual Results:  
'Error while creating the database schema'.

Expected Results:  
digikam would create appropriate tables in mysql and then upload data through
series of mysql insert statements or create a temporary file and 'load data
infile'

This is on a Fedora-15 64-bit system.
mysql runs very well on this system.
Appears similar to bug 276171

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=280678


Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #1 from Gilles Caulier <caulier gilles gmail com>  2011-08-24 07:27:27 ---
Please update to 2.0.0 where this problem have been probably fixed...

Gilles Caulier

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678





--- Comment #2 from E. Lewis <ed lewis enlewis com>  2011-08-24 18:39:47 ---
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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=280678





--- Comment #3 from Gilles Caulier <caulier gilles gmail com>  2011-12-15 09:08:57 ---
Ed,

This file still valid using digiKam 2.4 ?

Gilles Caulier

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=280678


Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|Cannot migrate from sqlite  |MYSQL : cannot migrate from
                   |to mysql                    |sqlite database




--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678


Alexander <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #4 from Alexander <vo zaeb gmail com>  2012-01-08 08:09:19 ---
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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678


Rodney Baker <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #5 from Rodney Baker <rodney baker iinet net au>  2012-01-17 14:20:40 ---
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".

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678





--- Comment #6 from Rodney Baker <rodney baker iinet net au>  2012-01-21 13:52:58 ---
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://" )

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678





--- Comment #7 from Rodney Baker <rodney baker iinet net au>  2012-01-21 16:31:24 ---
FYI, I have just downloaded and built from the latest git sources. The exact
same symptoms occur with version 2.6.0!

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=280678


Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Version|1.9.0                       |2.6.0




--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678





--- Comment #8 from Alexander <vo zaeb gmail com>  2012-01-29 21:05:18 ---
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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678

Daniel Berrange <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #9 from Daniel Berrange <[hidden email]> ---
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                      |
+--------------------------------------------------------------------------------------------------------+

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678

--- Comment #10 from Rodney Baker <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678

--- Comment #11 from Alexander <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678

Richard Mortimer <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #12 from Richard Mortimer <[hidden email]> ---
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

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa-2]

https://bugs.kde.org/show_bug.cgi?id=280678

Francesco Riosa <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #13 from Francesco Riosa <[hidden email]> ---
(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

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678

schultzter <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #14 from schultzter <[hidden email]> ---
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
--------------

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=280678

--- Comment #15 from E. Lewis <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Douglas Leonard]

https://bugs.kde.org/show_bug.cgi?id=280678

Douglas Leonard <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]
                   |                            |m

--- Comment #16 from Douglas Leonard <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Rob]

 <E.Lewis> writes:

>
> https://bugs.kde.org/show_bug.cgi?id=280678
>
> --- Comment #15 from E. Lewis <ed.lewis@...> ---
> 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.
>

Ed,

You need to install the qt mysql package. It doesn't get installed when
installing mysql-connector*.:

$ yum install qt4-mysql

Best,
Rob

_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel