digiKam › digikam-users

Mysql/MariaDb database expert needs...

Classic

List

Threaded

50 messages Options

123

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

In fact when i started to port code to Qt5, i used 5.4, but i'm sure that 5.2 will work as well.

Gilles

2015-11-18 14:49 GMT+01:00 Richard Mortimer <[hidden email]>:

On 18/11/2015 13:21, Gilles Caulier wrote:
>
>
>
> I'm trying to find the time to build myself a copy of git against Ubuntu
> 14.04 (trusty) so that I can give this a look. Does anyone know if the
> qt5 library versions included with trusty are recent enough or do I need
> to load a PPA from somewhere? I digikam from the philip5/extra/ubuntu
> trusty PPA installed already.
>
>
> I don't know trusty, but i can said that Qt 5.4.0 is enough to build
> digiKam. Same for KF5 libraries.
>
> In all cases, read my DEPENDENCIES file for all details :
>
> https://quickgit.kde.org/?p=digikam-software-compilation.git&a=blob&f=DEPENDENCIES
>
Thanks. Trusty seems to have 5.2.1. I will probably build a schroot
instance of a later version of Ubuntu or Debian to get the later
libraries and test from within that.

Regards

Richard

> Gilles Caulier

>
>
> _______________________________________________
> Digikam-users mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-users
>
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

In reply to this post by Henrique Santos Fernandes

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 <[hidden email]>:

Em qua, 18 de nov de 2015 às 11:46, Gilles Caulier <[hidden email]> escreveu:
2015-11-18 14:33 GMT+01:00 Henrique Santos Fernandes <[hidden email]>:
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
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Henrique Santos Fernandes

Re: Mysql/MariaDb database expert needs...

I thought support for interval database had been dropped!
My bad

Em qua, 18 de nov de 2015 12:19, Gilles Caulier <[hidden email]> 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 <[hidden email]>:
Em qua, 18 de nov de 2015 às 11:46, Gilles Caulier <[hidden email]> escreveu:
2015-11-18 14:33 GMT+01:00 Henrique Santos Fernandes <[hidden email]>:
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
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

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 <[hidden email]>:

I thought support for interval database had been dropped!
My bad
Em qua, 18 de nov de 2015 12:19, Gilles Caulier <[hidden email]> 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 <[hidden email]>:
Em qua, 18 de nov de 2015 às 11:46, Gilles Caulier <[hidden email]> escreveu:
2015-11-18 14:33 GMT+01:00 Henrique Santos Fernandes <[hidden email]>:
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
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

The proof about common mysql server database init :

2015-11-18 15:32 GMT+01:00 Gilles Caulier <[hidden email]>:

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 <[hidden email]>:
I thought support for interval database had been dropped!
My bad
Em qua, 18 de nov de 2015 12:19, Gilles Caulier <[hidden email]> 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 <[hidden email]>:
Em qua, 18 de nov de 2015 às 11:46, Gilles Caulier <[hidden email]> escreveu:
2015-11-18 14:33 GMT+01:00 Henrique Santos Fernandes <[hidden email]>:
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
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

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 <[hidden email]>:

The proof about common mysql server database init :

[root@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 <[hidden email]>:
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 <[hidden email]>:
I thought support for interval database had been dropped!
My bad
Em qua, 18 de nov de 2015 12:19, Gilles Caulier <[hidden email]> 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 <[hidden email]>:
Em qua, 18 de nov de 2015 às 11:46, Gilles Caulier <[hidden email]> escreveu:
2015-11-18 14:33 GMT+01:00 Henrique Santos Fernandes <[hidden email]>:
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
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

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 <[hidden email]>:

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 <[hidden email]>:
The proof about common mysql server database init :

[root@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 <[hidden email]>:
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 <[hidden email]>:
I thought support for interval database had been dropped!
My bad
Em qua, 18 de nov de 2015 12:19, Gilles Caulier <[hidden email]> 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 <[hidden email]>:
Em qua, 18 de nov de 2015 às 11:46, Gilles Caulier <[hidden email]> escreveu:
2015-11-18 14:33 GMT+01:00 Henrique Santos Fernandes <[hidden email]>:
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
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

New database setup config panel :

https://www.flickr.com/photos/digikam/23121578776
https://www.flickr.com/photos/digikam/23147681465
https://www.flickr.com/photos/digikam/22851871180

It's now yet fully done. It still some buttons and tunes to add, especially to be able to config Mysql internal place to store database files (as SQlite), but at least it's more better than before.

Gilles Caulier

2015-11-19 16:02 GMT+01:00 Gilles Caulier <[hidden email]>:

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 <[hidden email]>:
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 <[hidden email]>:
The proof about common mysql server database init :

[root@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 <[hidden email]>:
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 <[hidden email]>:
I thought support for interval database had been dropped!
My bad
Em qua, 18 de nov de 2015 12:19, Gilles Caulier <[hidden email]> 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 <[hidden email]>:
Em qua, 18 de nov de 2015 às 11:46, Gilles Caulier <[hidden email]> escreveu:
2015-11-18 14:33 GMT+01:00 Henrique Santos Fernandes <[hidden email]>:
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
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Michael Rasmussen

Re: Mysql/MariaDb database expert needs...

In reply to this post by Gilles Caulier-4

Are you using MySql, the now Oracle owned product, or MariaDB the MySql fork
that the original developers of MySql started after they weren't happy with
life at Oracle.

On Thu, Nov 12, 2015 at 03:26:45PM +0100, Gilles Caulier wrote:

> 2015-11-12 15:15 GMT+01:00 Myriam Schweingruber <[hidden email]>:
>
> > Hi all,
> >
> > seems I posted from the wrong address the first time...
> >
> > On Thu, Nov 12, 2015 at 10:55 AM, Gilles Caulier
> > <[hidden email]> wrote:
> > > At least, somebody can said me if an internal Mysql solution is suitable
> > or
> > > not without to hack the DB installation outside digiKam.
> > >
> > > I read all bugzilla entries, and to setup Mysql, users need to grant
> > > privileges by hand before to configure digiKam with it.
> > >
> > > This is not possible to do the same with an internal server, since few
> > years
> > > ago, Mysql switch DB engine to ImmoDB which require granted privilege to
> > > create DB indexes. And this is typically the problem that i can see when
> > i
> > > want to create the MySql DB through the internal server.
> > >
> > > If i look how Amarok do with Mysql, internal server solution do not
> > exist.
> > > Only a remote server is avaialble in setup.
> >
> > That is not correct, Amarok uses MySQL embedded by default, setting up
> > an external MySQL server is just an additional option for people who
> > need to share a database across computers.
> >
>
> Ah, it's not SQlite... Interesting...
>
> So my question is : how to create DB indexes and triggers with an internal
> solution. When i look in bugzilla, some users have investigated and see
> that mysql indexes creation need granted privileges since immodb DB engine
> is used. And this is true : i reproduced the dysfunction and fixed it.
>
> Or perhaps Amarok do not use Indexes and triggers. In this case, DB
> performances will just down...
> Or perhaps Amarok do not use immodb engine...
>
> Gilles Caulier

> _______________________________________________
> Digikam-users mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-users

--
Michael Rasmussen, Portland Oregon
Be Appropriate && Follow Your Curiosity
Read enough of the top-10 lists that American movie critics put together
and you might wonder whether a single damn film worth watching came out
before the first of October.
~ Andrew O'Hehir
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

Michael Rasmussen

Re: Mysql/MariaDb database expert needs...

In reply to this post by Gilles Caulier-4

Ignore my previous message. Time for me to have a cup of coffee before writing.

On Thu, Nov 12, 2015 at 03:26:45PM +0100, Gilles Caulier wrote:

> _______________________________________________
> Digikam-users mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-users

--
Michael Rasmussen, Portland Oregon
Be Appropriate && Follow Your Curiosity
Bicycles are traffic
~ Critical Mass
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

123