[Digikam-users] Mysql/MariaDb database expert needs...
Gilles Caulier
caulier.gilles at gmail.com
Wed Nov 18 15:10:05 GMT 2015
I think i find the problem with database creation in case of internal Mysql
server is used...
There are 2 instance in code where CREATE DATABASE sql statement are used :
https://projects.kde.org/projects/extragear/graphics/digikam/repository/revisions/master/entry/databaseserver/databaseserver.cpp#L401
https://projects.kde.org/projects/extragear/graphics/digikam/repository/revisions/master/entry/databaseserver/databaseserver.cpp#L451
I think it miss extra arguments in these statement to grant privilege to
user (digikam in this case)
Gilles Caulier
2015-11-18 15:33 GMT+01:00 Gilles Caulier <caulier.gilles at gmail.com>:
> The proof about common mysql server database init :
>
> [root at localhost lib]# mysql
> Welcome to the MariaDB monitor. Commands end with ; or \g.
> Your MariaDB connection id is 15
> Server version: 10.0.22-MariaDB Mageia MariaDB Server
>
> Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
>
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
>
> MariaDB [(none)]> show databases;
> +--------------------+
> | Database |
> +--------------------+
> | digikamdb |
> | information_schema |
> | mysql |
> | performance_schema |
> | test |
> +--------------------+
> 5 rows in set (0.01 sec)
>
> MariaDB [(none)]> use digikamdb;
> Database changed
> MariaDB [digikamdb]> show tables;
> +----------------------+
> | Tables_in_digikamdb |
> +----------------------+
> | AlbumRoots |
> | Albums |
> | CustomIdentifiers |
> | DownloadHistory |
> | FilePaths |
> | Identities |
> | IdentityAttributes |
> | ImageComments |
> | ImageCopyright |
> | ImageHaarMatrix |
> | ImageHistory |
> | ImageInformation |
> | ImageMetadata |
> | ImagePositions |
> | ImageProperties |
> | ImageRelations |
> | ImageTagProperties |
> | ImageTags |
> | Images |
> | OpenCVLBPHRecognizer |
> | OpenCVLBPHistograms |
> | Searches |
> | Settings |
> | TagProperties |
> | Tags |
> | TagsTree |
> | Thumbnails |
> | UniqueHashes |
> | VideoMetadata |
> +----------------------+
> 29 rows in set (0.01 sec)
>
> Gilles Caulier
>
> 2015-11-18 15:32 GMT+01:00 Gilles Caulier <caulier.gilles at gmail.com>:
>
>> For the moment no. It's always easy to drop code against to write code.
>>
>> As i explore and fix code, i can expect to find a solution for internal
>> server. I will see later to drop or not.
>>
>> Note : with this commit :
>>
>> http://commits.kde.org/digikam/74742b93f227ff4413e10cf3c8f895d452c10858
>>
>> We are able to setup a mysql server to host Core, Thumbs, and Face
>> database in both case :
>>
>> - Separated DB
>> - Common DB
>>
>> The case of internal server will inherit of this fix. Without it, i'm
>> sure that it cannot work properly.
>>
>> So for internal server, we need to found why index procedure is
>> problematic. As i can see the server is initialized by a mysql-global.conf
>> config text file. I'm sure that some settings need to be adjusted here, but
>> which one exactly, i don't know....
>>
>> Gilles Caulier
>>
>> 2015-11-18 15:23 GMT+01:00 Henrique Santos Fernandes <sf.rique at gmail.com>
>> :
>>
>>> I thought support for interval database had been dropped!
>>> My bad
>>>
>>> Em qua, 18 de nov de 2015 12:19, Gilles Caulier <
>>> caulier.gilles at gmail.com> escreveu:
>>>
>>>> Certainly, but this will not solve the problem with internal server
>>>> solution, because in this case there is no way to tune table creation from
>>>> mysql prompt... All is done in background by digiKam database server (i
>>>> don't yet investigate in this code).
>>>>
>>>> Gilles
>>>>
>>>> 2015-11-18 14:56 GMT+01:00 Henrique Santos Fernandes <
>>>> sf.rique at gmail.com>:
>>>>
>>>>>
>>>>> Em qua, 18 de nov de 2015 às 11:46, Gilles Caulier <
>>>>> caulier.gilles at gmail.com> escreveu:
>>>>>
>>>>>> 2015-11-18 14:33 GMT+01:00 Henrique Santos Fernandes <
>>>>>> sf.rique at gmail.com>:
>>>>>>
>>>>>>> I dont understando that much but i have 2 questions.
>>>>>>>
>>>>>>> Why dont create the index normaly instead of using this if exist
>>>>>>> procedure?
>>>>>>>
>>>>>>
>>>>>> I don't know. I don't write the SQL code for MySQL. This have been
>>>>>> done by an old contributor, few years ago...
>>>>>>
>>>>>> If you look into this procedure code, it's sound complicated. I don't
>>>>>> know why ?
>>>>>>
>>>>>> CREATE PROCEDURE create_index_if_not_exists(table_name_vc
>>>>>> varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
>>>>>> 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;
>>>>>>
>>>>>> Calls of this procedure is done like this (for thumbnails DB for ex) :
>>>>>>
>>>>>> CALL
>>>>>> create_index_if_not_exists('UniqueHashes','id_uniqueHashes','thumbId');
>>>>>> CALL create_index_if_not_exists('FilePaths','id_filePaths','thumbId');
>>>>>> CALL
>>>>>> create_index_if_not_exists('CustomIdentifiers','id_customIdentifiers','thumbId');
>>>>>>
>>>>>> Why this complexity with this procedure. No idea...
>>>>>>
>>>>>>
>>>>>>> If the procedure is really needed, you need to create it with
>>>>>>> digikam user?
>>>>>>>
>>>>>>
>>>>>> it's already the case with current code.
>>>>>>
>>>>>>
>>>>>>> You should be able to create the procedure when seting up the
>>>>>>> databases, grant privilges and stuff. right?
>>>>>>>
>>>>>>>
>>>>>> So user digikam dont need to create the procedure right? the
>>>>>>> root/admin could do this..
>>>>>>>
>>>>>>>
>>>>>> That i can see on Internet, procedure and function need Mysql grant
>>>>>> priviledges to be executed, not created... But i'm not an expert...
>>>>>>
>>>>>
>>>>> Can you try the explained ins thsi link and see if you have
>>>>> permissiosn?
>>>>>
>>>>> http://stackoverflow.com/questions/10089308/mysql-stored-procedure-permissions
>>>>>
>>>>> I guess it would be somethng like this:
>>>>>
>>>>> GRANT EXECUTE ON PROCEDURE digikamthumbsdb.create_index_if_not_exists TO 'digikam'@'localhost';
>>>>>
>>>>> Read the comments, because the user said it needed to add something in
>>>>> this query!
>>>>>
>>>>> You may add to all procedures as well..
>>>>>
>>>>> http://dba.stackexchange.com/questions/20221/mysql-user-permission-on-stored-procedure
>>>>>
>>>>> Hope this help!
>>>>>
>>>>>
>>>>>>
>>>>>> Gilles Caulier
>>>>>> _______________________________________________
>>>>>> Digikam-users mailing list
>>>>>> Digikam-users at kde.org
>>>>>> https://mail.kde.org/mailman/listinfo/digikam-users
>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Digikam-users mailing list
>>>>> Digikam-users at kde.org
>>>>> https://mail.kde.org/mailman/listinfo/digikam-users
>>>>>
>>>>>
>>>> _______________________________________________
>>>> Digikam-users mailing list
>>>> Digikam-users at kde.org
>>>> https://mail.kde.org/mailman/listinfo/digikam-users
>>>>
>>>
>>> _______________________________________________
>>> Digikam-users mailing list
>>> Digikam-users at kde.org
>>> https://mail.kde.org/mailman/listinfo/digikam-users
>>>
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-users/attachments/20151118/41212f75/attachment.html>
More information about the Digikam-users
mailing list