[Digikam-users] Mysql/MariaDb database expert needs...
Gilles Caulier
caulier.gilles at gmail.com
Wed Nov 18 14:33:58 GMT 2015
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/20151118/d981b7f0/attachment.html>
More information about the Digikam-users
mailing list