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

[Gilles Caulier]

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-users/attachments/20151118/d8df183b/attachment.html>