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

[Henrique Santos Fernandes]

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