[Digikam-users] Mysql/MariaDb database expert needs...

[Gilles Caulier]

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/e77a88f4/attachment.html>