[Digikam-users] Mysql/MariaDb database expert needs...
Gilles Caulier
caulier.gilles at gmail.com
Thu Nov 19 15:02:39 GMT 2015
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/20151119/c4756cde/attachment.html>
More information about the Digikam-users
mailing list