[Digikam-users] Mysql/MariaDb database expert needs...
Gilles Caulier
caulier.gilles at gmail.com
Fri Nov 20 15:19:55 GMT 2015
New database setup config panel :
https://www.flickr.com/photos/digikam/23121578776
https://www.flickr.com/photos/digikam/23147681465
https://www.flickr.com/photos/digikam/22851871180
It's now yet fully done. It still some buttons and tunes to add, especially
to be able to config Mysql internal place to store database files (as
SQlite), but at least it's more better than before.
Gilles Caulier
2015-11-19 16:02 GMT+01:00 Gilles Caulier <caulier.gilles at gmail.com>:
> I fixed Mysql Internal server support with this commit :
>
> http://commits.kde.org/digikam/74adf4f5dcdacc4b6574e61f55dac956fa4c7611
>
> I need feedback now...
>
> Gilles Caulier
>
> 2015-11-18 16:10 GMT+01:00 Gilles Caulier <caulier.gilles at gmail.com>:
>
>> 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/20151120/01d25990/attachment.html>
More information about the Digikam-users
mailing list