[Bug 279580] New: Unable to get MySQL to work after update to 2.0

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

           Summary: Unable to get MySQL to work after update to 2.0
           Product: digikam
           Version: 2.0.0
          Platform: Archlinux Packages
        OS/Version: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: Database
        AssignedTo: [hidden email]
        ReportedBy: [hidden email]


Version:           2.0.0 (using KDE 4.7.0)
OS:                Linux

Hi,

after the update to Digikam 2.0 the MySQL support does not work anymore and I
can't get it to work from scratch.



Reproducible: Didn't try

Steps to Reproduce:
Steps I have taken:

Upgraded to Digikam 2.0, after the next start I got the CREATE TRIGGER
privilege error. I tried granting TRIGGER to all tables of my digikam database
( main and thumbnails ) and digikam started again, but it failed to migrate the
database. Maybe digikam should output some more information.

Then I tried to create a new database and user, granted all permissions and
gave that to digikam. Upon starting, I have the TRIGGER problem again, grant
that permission and afterwards digikam complains, that it cannot find the
DBVersion key ( Of course not! Its a new database! ).

Then I tried starting with a SQLite database and migrating to MySQL, but it
fails with "could not create database scheme".


Actual Results:  
The database does not get populated and digikam outputs no useful information
what went wrong.

Expected Results:  
MySQL should work again.

Now I'm out of ideas, could digikam PLEASE provide some more information apart
from the usual texts and without going too deep into debugging?

I'm running Archlinux with

digikam 2.0.0-2
kdebase-workspace 4.7.0-2

Thanks.

--
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=279580


Dan Ohnesorg <[hidden email]> changed:

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




--- Comment #1 from Dan Ohnesorg <dan ohnesorg cz>  2011-08-08 16:14:02 ---
same problem here:
digikam(25633)/digikam (core): DBVersion not available! Giving up schema
upgrading.

while creating new database.

Also using latin1 for file names is a little bit old fashioned.

--
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

[Francesco Riosa-2]

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


Francesco Riosa <[hidden email]> changed:

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




--- Comment #2 from Francesco Riosa <francesco+kde pnpitalia it>  2011-08-08 21:10:36 ---
Hi, I'm on a guest pc so cannot test before write but a know to work procedure
is:

1) create a sqlite database
2) create an empty mysql schema say "digikam"
3) give all privileges to a user, on the schema "digikam" and flush privileges
4) migrate from sqlite to mysql

It's not possible at the moment to use two separate databases for mysql, it's
an open bug which is actively worked on.

In any case it's important for me to know what don't go as expected,adding the
following to your my.cnf, in the section [mysqld] allow for logging mysql
queryes:
[mysqld]
general_log_file = /var/log/mysql/general_log.sql
/var/log/mysql/ must be existant and with owner mysql

and something like the following to $HOME/.kde4/share/config/kdebugrc, replace
the path with a suitable one, the real interesting one is 50003

[50003]
InfoFilename=/home/vivo/digikam-devel/log/digikam-core-50003.log
InfoOutput=0

[50004]
InfoFilename=/home/vivo/digikam-devel/log/digikam-kio-50004.log
InfoOutput=0

[50005]
InfoFilename=/home/vivo/digikam-devel/log/digikam-showfoto-50005.log
InfoOutput=0

[50006]
InfoFilename=/home/vivo/digikam-devel/log/digikam-plugins-50006.log
InfoOutput=0

[50007]
InfoFilename=/home/vivo/digikam-devel/log/digikam-dbserver-50007.log
InfoOutput=0

please, if possible attach the logs for a failed attempt to this bug
I'll be slow to answer until after aug 15

regards, Francesco

--
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=279580





--- Comment #3 from tomas benedykt <tomas benedykt centrumholdings com>  2011-08-08 22:23:23 ---
mysql schema - missing procedure "create_index_if_not_exists"

as a "dirty fix" you may use the following syntax (fixed from
/usr/share/apps/digikam/database/dbconfig.xml by adding delimiter tags)

thanks for 50003.log, it was very helpful

<sql>
delimiter //

                CREATE PROCEDURE create_index_if_not_exists(table_name_vc
varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
                SQL SECURITY INVOKER
                BEGIN

                set @Index_cnt = (
                    SELECT COUNT(1) cnt
                    FROM INFORMATION_SCHEMA.STATISTICS
                    WHERE CONVERT(DATABASE() USING latin1) =
CONVERT(TABLE_SCHEMA USING latin1)
                      AND CONVERT(table_name USING latin1) =
CONVERT(table_name_vc USING latin1)
                      AND CONVERT(index_name USING latin1) =
CONVERT(index_name_vc USING latin1)
                );

                IF IFNULL(@Index_cnt, 0) = 0 THEN
                    set @index_sql = CONCAT(
                        CONVERT( 'ALTER TABLE ' USING latin1),
                        CONVERT( table_name_vc USING latin1),
                        CONVERT( ' ADD INDEX ' USING latin1),
                        CONVERT( index_name_vc USING latin1),
                        CONVERT( '(' USING latin1),
                        CONVERT( field_list_vc USING latin1),
                        CONVERT( ');' USING latin1)
                    );
                    PREPARE stmt FROM @index_sql;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                END IF;


                END //

DELIMITER ;
</sql>

--
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

[Francesco Riosa-2]

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





--- Comment #4 from Francesco Riosa <francesco+kde pnpitalia it>  2011-08-09 10:08:32 ---
(In reply to comment #3)
> mysql schema - missing procedure "create_index_if_not_exists"
>
This should happen only when using two different schema for images metadata and
thumbnail, could you confirm?

--
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=279580


tomas benedykt <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |tomas.benedykt@centrumholdi
                   |                            |ngs.com




--- Comment #5 from tomas benedykt <tomas benedykt centrumholdings com>  2011-08-09 12:23:22 ---
(In reply to comment #4)
> This should happen only when using two different schema for images metadata and
> thumbnail, could you confirm?
confirmed, i had 2 separate schemas for digikam & thumbs since support of the
mysql engine was added to digikam

--
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=279580


Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]
            Summary|Unable to get MySQL to work |MYSQL : unable to get
                   |after update to 2.0         |database to work after
                   |                            |update to 2.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=279580

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|MYSQL : unable to get       |MySQL : unable to get
                   |database to work after      |database to work after
                   |update to 2.0               |update to 2.0

--
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=279580

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|MySQL : unable to get       |MYSQL : unable to get
                   |database to work after      |database to work after
                   |update to 2.0               |update to 2.0

--
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=279580

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Database                    |Database-Mysql

--
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

[bugzilla_noreply]

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

--- Comment #6 from [hidden email] ---
@Thomas, from comment #3

Why we need to use "DELIMITER //" with create_index_if_not_exists method ?

I tried to patch dbconfig.xml file following your tip without any improvement
to fix this entry.

Gilles Caulier

--
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

[bugzilla_noreply]

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

--- Comment #7 from [hidden email] ---
Patch against current implementation of XML config file :

--------------------------------------------------------------------------------------
diff --git a/data/database/dbconfig.xml.cmake.in
b/data/database/dbconfig.xml.cmake.in
index f4b3b05..e6adcb4 100644
--- a/data/database/dbconfig.xml.cmake.in
+++ b/data/database/dbconfig.xml.cmake.in
@@ -1054,6 +1054,7 @@
                     DROP PROCEDURE IF EXISTS create_index_if_not_exists;
                 </statement>
                 <statement mode="plain">
+                    DELIMITER //
                     CREATE PROCEDURE create_index_if_not_exists(table_name_vc
varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
                     SQL SECURITY INVOKER
                     BEGIN
@@ -1080,7 +1081,8 @@
                         EXECUTE stmt;
                         DEALLOCATE PREPARE stmt;
                     END IF;
-                    END;
+                    END //
+                    DELIMITER ;
                 </statement>
                 <statement mode="plain">CALL
create_index_if_not_exists('Images','dir_index','album');</statement>
                 <statement mode="plain">CALL
create_index_if_not_exists('Images','hash_index','uniqueHash');</statement>
@@ -1387,6 +1389,7 @@ ORDER BY inf.rating DESC, img.name ASC
                     DROP PROCEDURE IF EXISTS create_index_if_not_exists;
                 </statement>
                 <statement mode="plain">
+                    DELIMITER //
                     CREATE PROCEDURE create_index_if_not_exists(table_name_vc
varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
                     SQL SECURITY INVOKER
                     BEGIN
@@ -1413,7 +1416,8 @@ ORDER BY inf.rating DESC, img.name ASC
                         EXECUTE stmt;
                         DEALLOCATE PREPARE stmt;
                     END IF;
-                    END;
+                    END //
+                    DELIMITER ;
                 </statement>
                 <statement mode="plain">CALL
create_index_if_not_exists('UniqueHashes','id_uniqueHashes','thumbId');</statement>
                 <statement mode="plain">CALL
create_index_if_not_exists('FilePaths','id_filePaths','thumbId');</statement>

--------------------------------------------------------------------------------------

--
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

[bugzilla_noreply]

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

--- Comment #8 from [hidden email] ---
The delimiter here is a joke solution and did not work.

--
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

[bugzilla_noreply]

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

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
      Latest Commit|                            |http://commits.kde.org/digi
                   |                            |kam/74adf4f5dcdacc4b6574e61
                   |                            |f55dac956fa4c7611
   Version Fixed In|                            |5.0.0
         Resolution|---                         |FIXED
             Status|UNCONFIRMED                 |RESOLVED

--- Comment #9 from [hidden email] ---
Git commit 74adf4f5dcdacc4b6574e61f55dac956fa4c7611 by Gilles Caulier.
Committed on 19/11/2015 at 14:38.
Pushed by cgilles into branch 'master'.

First stage to fix Mysql support:

- For Internal server:
  Use the current system user to run mysql_installdb program to init local
database and server tables.
  The database is always installed in user account (~/.local/share/digikam/).
The goal is to set this install path configurable as with SQlite.
  Check the value returned to start init and sever program with QProcess. If
something is wrong abort the sequence and do not try to populate/use the
database.
  This will fix a crash if DB backend is null at digiKam shutdown.
  Fix the default embeded mysql server configuration to not need grant
privilege with index creation.

- For remote server:
  Fix the index creation procedure SQL statements to not check security rules.
No more grant privilege is required,
  excepted for the DB tables and the standard DB user dedicated for digiKam
use.
  Important : for a remote server, the database creation need to be instanced
by administrator, using following SQL statements (which will be put in DB setup
page later as helper):

  * For a common Mysql database storage :

  CREATE DATABASE digikamdb; GRANT ALL PRIVILEGES ON digikamdb.* TO
'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;

  with:

  digikamdb       : the common database name (storing Core, Thumbnails, and
Face databases).
  digikam/digikam : the name/password of user account used by digiKam to access
on mysql server.

  * For separated Mysql database storages :

  CREATE DATABASE digikamcoredb; GRANT ALL PRIVILEGES ON digikamcoredb.* TO
'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;
  CREATE DATABASE digikamthumbsdb; GRANT ALL PRIVILEGES ON digikamthumbsdb.* TO
'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;
  CREATE DATABASE digikamfacedb; GRANT ALL PRIVILEGES ON digikamfacedb.* TO
'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;

  with:

  digikamcoredb   : the Core database name.
  digikamthumbsdb : the Thumbnails database name.
  digikamfacedb   : the Face database name.
  digikam/digikam : the name/password of user account used by digiKam to access
on mysql server.

- The Face recognition database is now fully integrated in Mysql support. A new
settings have been add to DB setup page.
Related: bug 316747, bug 311041, bug 327062, bug 283502
FIXED-IN: 5.0.0

M  +29   -15   databaseserver/databaseserver.cpp
M  +4    -0    databaseserver/databaseserver.h

http://commits.kde.org/digikam/74adf4f5dcdacc4b6574e61f55dac956fa4c7611

--
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