digiKam › digikam-users

Mysql/MariaDb database expert needs...

Classic

List

Threaded

50 messages Options

123

Richard Mortimer-2

Re: Mysql/MariaDb database expert needs...

On 12/11/2015 16:38, Henrique Santos Fernandes wrote:

>
> The one advantage of having separate databases is for backup purposes.
> The thumbnail database in particular can be massive and having it
> separate makes it easier especially since it is basically throwaway data
> anyway.
>
>
> This is true. But still easy to remove or drop the tables related to it.
> Only "problem" would be the backup size, as it would include the
> thumbnails..

Yes. That's my point. Dropping the tables isn't practical on a nightly
basis. I currently have separated databases and the "main" database
backup is 1.5 MB compressed but the thumbnails database is 650 MB
compressed.

>
>
> The performance of MySQL doesn't really come into it. You can in theory
> partition different databases into different locations/disks etc. and
> that might be of interest to some but it would be a pretty special
> use case.
>
> I think that the correct default would be to use a common name for all
> three but allow separate names if required.
>
>
> I think teh default should be one database also.
>
> I guess, having the possibility to separate the databases would
> introduce much more work right? I am not sure.. not a developer.. i
> only know some about infrastructure .

You can already separate them and I would like to see that capability
remain if possible.

Regards

Richard

>
>
>
> _______________________________________________
> 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 eldk

2015-11-12 17:11 GMT+01:00 eric <[hidden email]>:

Hello,

>
>
> Why do digikam need super?

>
>
> I don't know. I just found this solution in a bugzilla entry and it
> work.

>
>
> Someone like you have posted the same question without a response.
>
>
> Here my knowledge of mysql is limited.

I think digikam need super for this :

"to create or alter a stored function, you must have the SUPER
privilege, in addition to the CREATE ROUTINE or ALTER ROUTINE privilege
that is normally required. (Depending on the DEFINER value in the
function definition, SUPER might be required regardless of whether
binary logging is enabled. See Section 13.1.12, “CREATE PROCEDURE and
CREATE FUNCTION Syntax”.)"

http://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html

In the web, I don't know any CMS, which use MYSQl, that doesn't need at
least the knowledge to create a database, its user and right, except
when the hosting service do it for its customer. (and no CMS that need
SUPER privileges)

So, using MYSQL with Digikam needs at least the same knowledge.

What is "surprising" and not usual is the super privileges needed, and
all I've read on configuring Digikam with MYSQL is about to get this
working.

I don't know how this is handle with AMAROK (local or remote MYSQL), but
a look at it should be done.

Ok, now i found why grant privileges are need : we have 2 functions created to handle indexes.

In digiKam, the database schema descriptions are managed in a single XML configuration file. This permit to adjust it without to recompile the application.

Look this function here :

https://projects.kde.org/projects/extragear/graphics/digikam/repository/revisions/master/entry/data/database/dbconfig.xml.cmake.in#L1057

and here :

https://projects.kde.org/projects/extragear/graphics/digikam/repository/revisions/master/entry/data/database/dbconfig.xml.cmake.in#L1390

As i can see in bugzilla, these 2 method must be re-written differently to prevent to require grant privileges.

Gilles Caulier

_______________________________________________
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 Richard Mortimer-2

This what's i suspected. Thanks for these clarifications.

Gilles Caulier

2015-11-12 16:52 GMT+01:00 Richard Mortimer <[hidden email]>:

On 12/11/2015 13:51, Gilles Caulier wrote:
>
> The here another pending and very important question :
>
> With digiKam 5.0.0, we will have 3 database to manage :
>
> 1/ Core DB
> 2/ Thumbnails DB
> 3/ Face DB (since libkface is merged in digiKam core)
>
> For each digiKam database we can use a specific name. By default i used
> the name name (digikamdb) here to test, and it work for Core and
> Thumbnails DB. Face DB don't work and investiguations are under progress...
>
> So, the question is : why we have the capability to separate Core,
> Thumbnails, and Face DB with different name (as for ex digikamcoredb,
> digkamthumbsdb, and digikamfacedb) ?
>
> In my /var/lib/mysql, i can see separated subfolders to host DB ? What's
> the advantage ? backup facilities ? Performances ? What's must be the
> default settings : common name or separated name ?

The one advantage of having separate databases is for backup purposes.
The thumbnail database in particular can be massive and having it
separate makes it easier especially since it is basically throwaway data
anyway.

The performance of MySQL doesn't really come into it. You can in theory
partition different databases into different locations/disks etc. and
that might be of interest to some but it would be a pretty special use case.

I think that the correct default would be to use a common name for all
three but allow separate names if required.

Regards

Richard

>
> All this point are not clear for me (and also for end users as i can see
> in bugzilla).
>
>
>
> I prefere mysql cause it would be possible/easier to use digikam on
> multiple computers with the same database!
>
>
> Sure, this is another important feature, which introduce other
> dysfunctions as i can see in bugzilla.
>
> We must investigate step by step, first with simple user cases...
>
> 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

2015-11-12 17:38 GMT+01:00 Henrique Santos Fernandes <[hidden email]>:

The one advantage of having separate databases is for backup purposes.
The thumbnail database in particular can be massive and having it
separate makes it easier especially since it is basically throwaway data
anyway.

This is true. But still easy to remove or drop the tables related to it.
Only "problem" would be the backup size, as it would include the thumbnails..

The performance of MySQL doesn't really come into it. You can in theory
partition different databases into different locations/disks etc. and
that might be of interest to some but it would be a pretty special use case.

I think that the correct default would be to use a common name for all
three but allow separate names if required.

I think teh default should be one database also.

I guess, having the possibility to separate the databases would introduce much more work right? I am not sure.. not a developer.. i only know some about infrastructure .

much more work to prepare the databases. Yes. All the rest in digiKam database interface is ready.

Perhaps there are some point to test in this configuration. We will see...

Gilles Caulier

_______________________________________________
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 Richard Mortimer-2

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

On 12/11/2015 16:38, Henrique Santos Fernandes wrote:
>
> The one advantage of having separate databases is for backup purposes.
> The thumbnail database in particular can be massive and having it
> separate makes it easier especially since it is basically throwaway data
> anyway.
>
>
> This is true. But still easy to remove or drop the tables related to it.
> Only "problem" would be the backup size, as it would include the
> thumbnails..
Yes. That's my point. Dropping the tables isn't practical on a nightly
basis. I currently have separated databases and the "main" database
backup is 1.5 MB compressed but the thumbnails database is 650 MB
compressed.

>
>
> The performance of MySQL doesn't really come into it. You can in theory
> partition different databases into different locations/disks etc. and
> that might be of interest to some but it would be a pretty special
> use case.
>
> I think that the correct default would be to use a common name for all
> three but allow separate names if required.
>
>
> I think teh default should be one database also.
>
> I guess, having the possibility to separate the databases would
> introduce much more work right? I am not sure.. not a developer.. i
> only know some about infrastructure .
You can already separate them and I would like to see that capability
remain if possible.

yes, this will stay like this in code. I will improved the configuration dialog with an advanced settings view when users want to use separated databases.

This will be more clear for non technical users.

Gilles Caulier

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

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

If somebody is interrested, there are 3 Sqlite SQL statement to convert to MySql in digiKam DB XML config file.

From there :

https://projects.kde.org/projects/extragear/graphics/digikam/repository/revisions/master/entry/data/database/dbconfig.xml.cmake.in#L1427

to :

https://projects.kde.org/projects/extragear/graphics/digikam/repository/revisions/master/entry/data/database/dbconfig.xml.cmake.in#L1491

This about Face database table, indexes, and triggers to create in relevant database.

This code is the same for SQlite, but it don't yet ported. I must be easy to do it as we have a similar sall dataabse definition for thumbnails storage.

The XML config file is divided in too part : on for Sqlite on the first, one others for Mysql. Comparing both section is easy since i commented all parts to be maintainable easily.

Gilles Caulier

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

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

On 12/11/2015 16:38, Henrique Santos Fernandes wrote:
>
> The one advantage of having separate databases is for backup purposes.
> The thumbnail database in particular can be massive and having it
> separate makes it easier especially since it is basically throwaway data
> anyway.
>
>
> This is true. But still easy to remove or drop the tables related to it.
> Only "problem" would be the backup size, as it would include the
> thumbnails..
Yes. That's my point. Dropping the tables isn't practical on a nightly
basis. I currently have separated databases and the "main" database
backup is 1.5 MB compressed but the thumbnails database is 650 MB
compressed.

>
>
> The performance of MySQL doesn't really come into it. You can in theory
> partition different databases into different locations/disks etc. and
> that might be of interest to some but it would be a pretty special
> use case.
>
> I think that the correct default would be to use a common name for all
> three but allow separate names if required.
>
>
> I think teh default should be one database also.
>
> I guess, having the possibility to separate the databases would
> introduce much more work right? I am not sure.. not a developer.. i
> only know some about infrastructure .
You can already separate them and I would like to see that capability
remain if possible.

yes, this will stay like this in code. I will improved the configuration dialog with an advanced settings view when users want to use separated databases.

This will be more clear for non technical users.

Gilles Caulier

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

Martin Burnicki-2

Re: Mysql/MariaDb database expert needs...

In reply to this post by Gilles Caulier-4

Gilles Caulier wrote:

> 2015-11-12 15:15 GMT+01:00 Myriam Schweingruber <[hidden email]
> <mailto:[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] <mailto:[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...

As far as I have seen there are 3 possible database options: SQlite,
"embedded" MySQL, and a real MySQL server. I think a difference may be
between "embedded" MySQL and a real MySQL server, but (assuming a fast
network connection) it shouldn't matter much if a real MySQL server runs
on the same machine as the DK software, or on a different one.

Until about one year ago I've been running an old openSUSE Linux machine
with digikam 2.5 or 2.6. since I've got a huge number of photos the
SQlite database was very slow.

I also had a real MySQL server running on that machine which was already
used by Amarok3 to store its data base. So i reconfigured the old DK to
use the local MySQL server, too. This was still working well with the
old DK version, and DK became *much* faster.

In the meantime I've bought a new PC with faster CPU, a large normal
hard disk to store the photos, and a SSD to store the Linux system
(openSUSE 13.2). Since current DK versions were said not to work
properly with MySQL anymore I configured DK to use the SQlite DB and
store it on the SSD. I'm still using Amarok3 with MySQL. ;-)

Anyway things became much faster with DK due to hardware enhancements,
but I still think it would be great to get MySQL support back. I don't
think we'd need support for the "embedded" MySQL stuff, just a network
connection to a real MySQL server on localhost or on a different
machine, if SQlite is too slow.

It would be great if DK had a "button" where it could create the MySQL
user(s) and table(s) easily, but users could also do this manually if
some instructions are included.

Unfortunately I'm also not a database expert, so I can't help much.

Martin

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

Myriam Schweingruber-2

Re: Mysql/MariaDb database expert needs...

Hi Martin,

On Fri, Nov 13, 2015 at 12:34 AM, Martin Burnicki
<[hidden email]> wrote:

> Gilles Caulier wrote:
>> 2015-11-12 15:15 GMT+01:00 Myriam Schweingruber <[hidden email]
>> <mailto:[hidden email]>>:
>>
>> ...
>> 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...
>
> As far as I have seen there are 3 possible database options: SQlite,
> "embedded" MySQL, and a real MySQL server. I think a difference may be
> between "embedded" MySQL and a real MySQL server, but (assuming a fast
> network connection) it shouldn't matter much if a real MySQL server runs
> on the same machine as the DK software, or on a different one.

Not in Amarok at all, I presume that is what you are talking about.
Believe me, there is only one default which is MySQL embedded, and an
optional one for an external MySQL server. Amarok 2.x has never had
anything else in 8 years!

The previous version had 3 options: SQLite was the default, and one
could configure an external MySQL or an external PostgreSQL server,
but that is only valid for Amarok 1.x

...

> I also had a real MySQL server running on that machine which was already
> used by Amarok3 to store its data base.

There is no Amarok3... The latest stable release is 2.8, the latest
beta is 2.9 and a Qt5 port is currently under way

Just to make things clear: we do not recommend to use an external
MySQL server, unless you really have to because you need to share the
database across different computers. There is strictly no difference
in terms of performance or scalability between an embedded MySQL and
an external server, despite what some people keep telling in various
sources. Really, we tested this in over 8 years and there is no
difference.

Hope this clarifies some points...

Regards, Myriam

--
Protect your freedom and join the Fellowship of FSFE:
http://www.fsfe.org
Please don't send me proprietary file formats,
use ISO standard ODF instead (ISO/IEC 26300)
_______________________________________________
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 Gilles Caulier-4

Hi all,

I back in this thread, with some fresh news :

The database schema for Faces Recognition database integration to digiKam core is now complete. With Mysql, the face database is now integrated as well. There is a new setting in DB setup to specify a Face Schema Name on your server.

I fixed also some typo in SQL statements for the database creation. After few days to try to understand how it's work, i can said that Mysql code is really more complex than SQlite. For ex, about the famous functions to create Indexes which require grant privileges, i don't yet found an alternative, to be able to prevent databases creation with these settings and to restore internal server.

Any way, i review ALL database code, renamed, tried, cleaned, factored, simplified, documented implementations when it's possible. I found some glitches in code but I'm not sure if this will really fix serious problems.

As now the code is better to ready and maintain, i will start to hack really MySql support. The bugs are already well identified in bugzilla and some entries are reproducible on my computer.

So, if i need help, it's about to test with all possible user cases.

There are 3 configurations :

1/ using a server (local or remote) with 3 separated databases ==> It's definitively the best configuration. All is well separated and clear. it's already set on my computer and this will be the first stage to hack.

2/ using a server (local or remote) with a single and common database ==> second stage to hack

3/ using internal server : do not work due to functions in DB. ==> last stage to hack, later...

So if there are some volunteers to help me to test, no need to report new entries in bugzilla, but please review existing files and check if problems still reproducible with the current implementation from git/master (digiKam 5.0.0 ported to Qt5)

Thanks in advance

PS : for information, the case 1/ give me the DB content below on my server :

[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# ls -al
total 176184
drwxr-xr-x 8 mysql mysql 4096 Nov 17 17:18 ./
drwxr-xr-x 42 root root 4096 Nov 16 23:48 ../
-rw-rw---- 1 mysql mysql 16384 Nov 17 00:05 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Nov 17 00:05 aria_log_control
drwx------ 2 mysql mysql 4096 Nov 17 17:51 digikamcoredb/
drwx------ 2 mysql mysql 4096 Nov 16 23:52 digikamfacedb/
drwx------ 2 mysql mysql 4096 Nov 16 23:50 digikamthumbsdb/
-rw-rw---- 1 mysql mysql 79691776 Nov 17 17:54 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Nov 17 17:54 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Nov 16 23:48 ib_logfile1
-rw-rw---- 1 mysql mysql 0 Nov 16 23:48 multi-master.info
drwx------ 2 mysql mysql 4096 Nov 16 23:48 mysql/
srwxrwxrwx 1 mysql mysql 0 Nov 17 17:18 mysql.sock=
drwx------ 2 mysql mysql 4096 Nov 16 23:48 performance_schema/
-rw-rw---- 1 mysql mysql 2760 Nov 17 17:56 sql_errors.log
drwx------ 2 mysql mysql 4096 Nov 16 23:48 test/
[root@localhost mysql]# ls -al digikamcoredb/
total 4244
drwx------ 2 mysql mysql 4096 Nov 17 17:51 ./
drwxr-xr-x 8 mysql mysql 4096 Nov 17 17:18 ../
-rw-rw---- 1 mysql mysql 1582 Nov 16 23:50 AlbumRoots.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:51 AlbumRoots.ibd
-rw-rw---- 1 mysql mysql 1612 Nov 16 23:50 Albums.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:51 Albums.ibd
-rw-rw---- 1 mysql mysql 65 Nov 16 23:49 db.opt
-rw-rw---- 1 mysql mysql 38 Nov 16 23:50 delete_image.TRN
-rw-rw---- 1 mysql mysql 36 Nov 16 23:50 delete_tag.TRN
-rw-rw---- 1 mysql mysql 1553 Nov 16 23:50 DownloadHistory.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:50 DownloadHistory.ibd
-rw-rw---- 1 mysql mysql 2470 Nov 16 23:50 ImageComments.frm
-rw-rw---- 1 mysql mysql 180224 Nov 16 23:51 ImageComments.ibd
-rw-rw---- 1 mysql mysql 2042 Nov 16 23:50 ImageCopyright.frm
-rw-rw---- 1 mysql mysql 360448 Nov 16 23:51 ImageCopyright.ibd
-rw-rw---- 1 mysql mysql 1045 Nov 16 23:50 ImageHaarMatrix.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:50 ImageHaarMatrix.ibd
-rw-rw---- 1 mysql mysql 1604 Nov 16 23:50 ImageHistory.frm
-rw-rw---- 1 mysql mysql 573440 Nov 16 23:51 ImageHistory.ibd -rw-rw---- 1 mysql mysql 1713 Nov 16 23:50 ImageInformation.frm -rw-rw---- 1 mysql mysql 311296 Nov 16 23:51 ImageInformation.ibd
-rw-rw---- 1 mysql mysql 1514 Nov 16 23:50 ImageMetadata.frm
-rw-rw---- 1 mysql mysql 311296 Nov 16 23:51 ImageMetadata.ibd -rw-rw---- 1 mysql mysql 1292 Nov 16 23:50 ImagePositions.frm -rw-rw---- 1 mysql mysql 196608 Nov 16 23:51 ImagePositions.ibd -rw-rw---- 1 mysql mysql 999 Nov 16 23:50 ImageProperties.frm -rw-rw---- 1 mysql mysql 114688 Nov 16 23:51 ImageProperties.ibd -rw-rw---- 1 mysql mysql 1959 Nov 16 23:50 ImageRelations.frm
-rw-rw---- 1 mysql mysql 147456 Nov 16 23:51 ImageRelations.ibd -rw-rw---- 1 mysql mysql 3226 Nov 16 23:50 Images.frm
-rw-rw---- 1 mysql mysql 655360 Nov 16 23:51 Images.ibd
-rw-rw---- 1 mysql mysql 1408 Nov 16 23:50 Images.TRG -rw-rw---- 1 mysql mysql 2003 Nov 16 23:50 ImageTagProperties.frm -rw-rw---- 1 mysql mysql 147456 Nov 17 17:54 ImageTagProperties.ibd -rw-rw---- 1 mysql mysql 1932 Nov 16 23:50 ImageTags.frm -rw-rw---- 1 mysql mysql 262144 Nov 17 17:53 ImageTags.ibd
-rw-rw---- 1 mysql mysql 1017 Nov 16 23:50 Searches.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:50 Searches.ibd
-rw-rw---- 1 mysql mysql 970 Nov 16 23:50 Settings.frm -rw-rw---- 1 mysql mysql 114688 Nov 17 17:52 Settings.ibd -rw-rw---- 1 mysql mysql 996 Nov 16 23:50 TagProperties.frm
-rw-rw---- 1 mysql mysql 114688 Nov 17 17:53 TagProperties.ibd -rw-rw---- 1 mysql mysql 1094 Nov 16 23:50 Tags.frm
-rw-rw---- 1 mysql mysql 98304 Nov 17 17:53 Tags.ibd
-rw-rw---- 1 mysql mysql 567 Nov 16 23:50 TagsTree.frm
-rw-rw---- 1 mysql mysql 489 Nov 16 23:50 Tags.TRG
-rw-rw---- 1 mysql mysql 1241 Nov 16 23:50 VideoMetadata.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:50 VideoMetadata.ibd
[root@localhost mysql]# ls -al digikamthumbsdb/
total 19120
drwx------ 2 mysql mysql 4096 Nov 16 23:50 ./
drwxr-xr-x 8 mysql mysql 4096 Nov 17 17:18 ../
-rw-rw---- 1 mysql mysql 1456 Nov 16 23:50 CustomIdentifiers.frm
-rw-rw---- 1 mysql mysql 131072 Nov 17 17:53 CustomIdentifiers.ibd
-rw-rw---- 1 mysql mysql 65 Nov 16 23:49 db.opt
-rw-rw---- 1 mysql mysql 1450 Nov 16 23:50 FilePaths.frm
-rw-rw---- 1 mysql mysql 212992 Nov 17 17:53 FilePaths.ibd
-rw-rw---- 1 mysql mysql 970 Nov 16 23:50 Settings.frm
-rw-rw---- 1 mysql mysql 114688 Nov 17 17:52 Settings.ibd
-rw-rw---- 1 mysql mysql 1061 Nov 16 23:50 Thumbnails.frm
-rw-rw---- 1 mysql mysql 18874368 Nov 17 17:53 Thumbnails.ibd
-rw-rw---- 1 mysql mysql 1603 Nov 16 23:50 UniqueHashes.frm
-rw-rw---- 1 mysql mysql 212992 Nov 17 17:53 UniqueHashes.ibd
[root@localhost mysql]# ls -al digikamfacedb/
total 552
drwx------ 2 mysql mysql 4096 Nov 16 23:52 ./
drwxr-xr-x 8 mysql mysql 4096 Nov 17 17:18 ../
-rw-rw---- 1 mysql mysql 65 Nov 16 23:49 db.opt
-rw-rw---- 1 mysql mysql 42 Nov 16 23:52 delete_identities.TRN
-rw-rw---- 1 mysql mysql 948 Nov 16 23:52 Identities.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:52 Identities.ibd
-rw-rw---- 1 mysql mysql 375 Nov 16 23:52 Identities.TRG
-rw-rw---- 1 mysql mysql 1018 Nov 16 23:52 IdentityAttributes.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:52 IdentityAttributes.ibd
-rw-rw---- 1 mysql mysql 1131 Nov 16 23:52 OpenCVLBPHistograms.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:52 OpenCVLBPHistograms.ibd
-rw-rw---- 1 mysql mysql 1066 Nov 16 23:52 OpenCVLBPHRecognizer.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:52 OpenCVLBPHRecognizer.ibd
-rw-rw---- 1 mysql mysql 970 Nov 16 23:52 Settings.frm
-rw-rw---- 1 mysql mysql 114688 Nov 17 17:52 Settings.ibd

Gilles Caulier

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

Henrique Santos Fernandes

Re: Mysql/MariaDb database expert needs...

Great news!

Gilles, if i may ask can you run this comands on your mysql?

> show databases;

it should show:

digikamcoredb

digikamfacedb

digikamthumbsdb

Bytheway, this names are configurable?

> show grants for 'user'@'%'; now this user and the % will depend on what user are you using!

I guess it does not matter the content on mysql folders, only what the database shows right? i dont thing we suppose to make chnages directly on the disk for mysql.

Anyway, i wish i could help but i am a bit without time.. i might try to install and etc some other time on a virtual machine but no promises!

Great Job!

Em ter, 17 de nov de 2015 às 15:22, Gilles Caulier <[hidden email]> escreveu:

Hi all,

I back in this thread, with some fresh news :

The database schema for Faces Recognition database integration to digiKam core is now complete. With Mysql, the face database is now integrated as well. There is a new setting in DB setup to specify a Face Schema Name on your server.

I fixed also some typo in SQL statements for the database creation. After few days to try to understand how it's work, i can said that Mysql code is really more complex than SQlite. For ex, about the famous functions to create Indexes which require grant privileges, i don't yet found an alternative, to be able to prevent databases creation with these settings and to restore internal server.

Any way, i review ALL database code, renamed, tried, cleaned, factored, simplified, documented implementations when it's possible. I found some glitches in code but I'm not sure if this will really fix serious problems.

As now the code is better to ready and maintain, i will start to hack really MySql support. The bugs are already well identified in bugzilla and some entries are reproducible on my computer.

So, if i need help, it's about to test with all possible user cases.

There are 3 configurations :

1/ using a server (local or remote) with 3 separated databases ==> It's definitively the best configuration. All is well separated and clear. it's already set on my computer and this will be the first stage to hack.

2/ using a server (local or remote) with a single and common database ==> second stage to hack

3/ using internal server : do not work due to functions in DB. ==> last stage to hack, later...

So if there are some volunteers to help me to test, no need to report new entries in bugzilla, but please review existing files and check if problems still reproducible with the current implementation from git/master (digiKam 5.0.0 ported to Qt5)

Thanks in advance

PS : for information, the case 1/ give me the DB content below on my server :

[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# ls -al
total 176184
drwxr-xr-x 8 mysql mysql 4096 Nov 17 17:18 ./
drwxr-xr-x 42 root root 4096 Nov 16 23:48 ../
-rw-rw---- 1 mysql mysql 16384 Nov 17 00:05 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Nov 17 00:05 aria_log_control
drwx------ 2 mysql mysql 4096 Nov 17 17:51 digikamcoredb/
drwx------ 2 mysql mysql 4096 Nov 16 23:52 digikamfacedb/
drwx------ 2 mysql mysql 4096 Nov 16 23:50 digikamthumbsdb/
-rw-rw---- 1 mysql mysql 79691776 Nov 17 17:54 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Nov 17 17:54 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Nov 16 23:48 ib_logfile1
-rw-rw---- 1 mysql mysql 0 Nov 16 23:48 multi-master.info
drwx------ 2 mysql mysql 4096 Nov 16 23:48 mysql/
srwxrwxrwx 1 mysql mysql 0 Nov 17 17:18 mysql.sock=
drwx------ 2 mysql mysql 4096 Nov 16 23:48 performance_schema/
-rw-rw---- 1 mysql mysql 2760 Nov 17 17:56 sql_errors.log
drwx------ 2 mysql mysql 4096 Nov 16 23:48 test/
[root@localhost mysql]# ls -al digikamcoredb/
total 4244
drwx------ 2 mysql mysql 4096 Nov 17 17:51 ./
drwxr-xr-x 8 mysql mysql 4096 Nov 17 17:18 ../
-rw-rw---- 1 mysql mysql 1582 Nov 16 23:50 AlbumRoots.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:51 AlbumRoots.ibd
-rw-rw---- 1 mysql mysql 1612 Nov 16 23:50 Albums.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:51 Albums.ibd
-rw-rw---- 1 mysql mysql 65 Nov 16 23:49 db.opt
-rw-rw---- 1 mysql mysql 38 Nov 16 23:50 delete_image.TRN
-rw-rw---- 1 mysql mysql 36 Nov 16 23:50 delete_tag.TRN
-rw-rw---- 1 mysql mysql 1553 Nov 16 23:50 DownloadHistory.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:50 DownloadHistory.ibd
-rw-rw---- 1 mysql mysql 2470 Nov 16 23:50 ImageComments.frm
-rw-rw---- 1 mysql mysql 180224 Nov 16 23:51 ImageComments.ibd
-rw-rw---- 1 mysql mysql 2042 Nov 16 23:50 ImageCopyright.frm
-rw-rw---- 1 mysql mysql 360448 Nov 16 23:51 ImageCopyright.ibd
-rw-rw---- 1 mysql mysql 1045 Nov 16 23:50 ImageHaarMatrix.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:50 ImageHaarMatrix.ibd
-rw-rw---- 1 mysql mysql 1604 Nov 16 23:50 ImageHistory.frm
-rw-rw---- 1 mysql mysql 573440 Nov 16 23:51 ImageHistory.ibd -rw-rw---- 1 mysql mysql 1713 Nov 16 23:50 ImageInformation.frm -rw-rw---- 1 mysql mysql 311296 Nov 16 23:51 ImageInformation.ibd
-rw-rw---- 1 mysql mysql 1514 Nov 16 23:50 ImageMetadata.frm
-rw-rw---- 1 mysql mysql 311296 Nov 16 23:51 ImageMetadata.ibd -rw-rw---- 1 mysql mysql 1292 Nov 16 23:50 ImagePositions.frm -rw-rw---- 1 mysql mysql 196608 Nov 16 23:51 ImagePositions.ibd -rw-rw---- 1 mysql mysql 999 Nov 16 23:50 ImageProperties.frm -rw-rw---- 1 mysql mysql 114688 Nov 16 23:51 ImageProperties.ibd -rw-rw---- 1 mysql mysql 1959 Nov 16 23:50 ImageRelations.frm
-rw-rw---- 1 mysql mysql 147456 Nov 16 23:51 ImageRelations.ibd -rw-rw---- 1 mysql mysql 3226 Nov 16 23:50 Images.frm
-rw-rw---- 1 mysql mysql 655360 Nov 16 23:51 Images.ibd
-rw-rw---- 1 mysql mysql 1408 Nov 16 23:50 Images.TRG -rw-rw---- 1 mysql mysql 2003 Nov 16 23:50 ImageTagProperties.frm -rw-rw---- 1 mysql mysql 147456 Nov 17 17:54 ImageTagProperties.ibd -rw-rw---- 1 mysql mysql 1932 Nov 16 23:50 ImageTags.frm -rw-rw---- 1 mysql mysql 262144 Nov 17 17:53 ImageTags.ibd
-rw-rw---- 1 mysql mysql 1017 Nov 16 23:50 Searches.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:50 Searches.ibd
-rw-rw---- 1 mysql mysql 970 Nov 16 23:50 Settings.frm -rw-rw---- 1 mysql mysql 114688 Nov 17 17:52 Settings.ibd -rw-rw---- 1 mysql mysql 996 Nov 16 23:50 TagProperties.frm
-rw-rw---- 1 mysql mysql 114688 Nov 17 17:53 TagProperties.ibd -rw-rw---- 1 mysql mysql 1094 Nov 16 23:50 Tags.frm
-rw-rw---- 1 mysql mysql 98304 Nov 17 17:53 Tags.ibd
-rw-rw---- 1 mysql mysql 567 Nov 16 23:50 TagsTree.frm
-rw-rw---- 1 mysql mysql 489 Nov 16 23:50 Tags.TRG
-rw-rw---- 1 mysql mysql 1241 Nov 16 23:50 VideoMetadata.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:50 VideoMetadata.ibd
[root@localhost mysql]# ls -al digikamthumbsdb/
total 19120
drwx------ 2 mysql mysql 4096 Nov 16 23:50 ./
drwxr-xr-x 8 mysql mysql 4096 Nov 17 17:18 ../
-rw-rw---- 1 mysql mysql 1456 Nov 16 23:50 CustomIdentifiers.frm
-rw-rw---- 1 mysql mysql 131072 Nov 17 17:53 CustomIdentifiers.ibd
-rw-rw---- 1 mysql mysql 65 Nov 16 23:49 db.opt
-rw-rw---- 1 mysql mysql 1450 Nov 16 23:50 FilePaths.frm
-rw-rw---- 1 mysql mysql 212992 Nov 17 17:53 FilePaths.ibd
-rw-rw---- 1 mysql mysql 970 Nov 16 23:50 Settings.frm
-rw-rw---- 1 mysql mysql 114688 Nov 17 17:52 Settings.ibd
-rw-rw---- 1 mysql mysql 1061 Nov 16 23:50 Thumbnails.frm
-rw-rw---- 1 mysql mysql 18874368 Nov 17 17:53 Thumbnails.ibd
-rw-rw---- 1 mysql mysql 1603 Nov 16 23:50 UniqueHashes.frm
-rw-rw---- 1 mysql mysql 212992 Nov 17 17:53 UniqueHashes.ibd
[root@localhost mysql]# ls -al digikamfacedb/
total 552
drwx------ 2 mysql mysql 4096 Nov 16 23:52 ./
drwxr-xr-x 8 mysql mysql 4096 Nov 17 17:18 ../
-rw-rw---- 1 mysql mysql 65 Nov 16 23:49 db.opt
-rw-rw---- 1 mysql mysql 42 Nov 16 23:52 delete_identities.TRN
-rw-rw---- 1 mysql mysql 948 Nov 16 23:52 Identities.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:52 Identities.ibd
-rw-rw---- 1 mysql mysql 375 Nov 16 23:52 Identities.TRG
-rw-rw---- 1 mysql mysql 1018 Nov 16 23:52 IdentityAttributes.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:52 IdentityAttributes.ibd
-rw-rw---- 1 mysql mysql 1131 Nov 16 23:52 OpenCVLBPHistograms.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:52 OpenCVLBPHistograms.ibd
-rw-rw---- 1 mysql mysql 1066 Nov 16 23:52 OpenCVLBPHRecognizer.frm
-rw-rw---- 1 mysql mysql 98304 Nov 16 23:52 OpenCVLBPHRecognizer.ibd
-rw-rw---- 1 mysql mysql 970 Nov 16 23:52 Settings.frm
-rw-rw---- 1 mysql mysql 114688 Nov 17 17:52 Settings.ibd

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

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

2015-11-17 18:52 GMT+01:00 Henrique Santos Fernandes <[hidden email]>:

Great news!

Gilles, if i may ask can you run this comands on your mysql?

> show databases;

it should show:
digikamcoredb
digikamfacedb
digikamthumbsdb

yes :

Bytheway, this names are configurable?

Sure, in database setup/migration dialog

> show grants for 'user'@'%'; now this user and the % will depend on what user are you using!

I guess it does not matter the content on mysql folders, only what the database shows right? i dont thing we suppose to make chnages directly on the disk for mysql.

MariaDB [(none)]> show grants for 'digikam'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for digikam@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD '*B86D61DED45FEAAB193591C66C302416B0E64CA6' |
| GRANT ALL PRIVILEGES ON `digikamcoredb`.* TO 'digikam'@'localhost'                                             |
| GRANT ALL PRIVILEGES ON `digikamthumbsdb`.* TO 'digikam'@'localhost'                                           |
| GRANT ALL PRIVILEGES ON `digikamfacedb`.* TO 'digikam'@'localhost'                                             |
+----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Gilles

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

Henrique Santos Fernandes

Re: Mysql/MariaDb database expert needs...

> show grants for 'user'@'%'; now this user and the % will depend on what user are you using!

I guess it does not matter the content on mysql folders, only what the database shows right? i dont thing we suppose to make chnages directly on the disk for mysql.

MariaDB [(none)]> show grants for 'digikam'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for digikam@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD '*B86D61DED45FEAAB193591C66C302416B0E64CA6' |
| GRANT ALL PRIVILEGES ON `digikamcoredb`.* TO 'digikam'@'localhost'                                             |
| GRANT ALL PRIVILEGES ON `digikamthumbsdb`.* TO 'digikam'@'localhost'                                           |
| GRANT ALL PRIVILEGES ON `digikamfacedb`.* TO 'digikam'@'localhost'                                             |
+----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Gilles

I am no expert but it seens that user 'digikam'@'localhost' dont need a password to the databases digikamcoredb, digikamthumbsdb and digikamfacedb

It only need password when using things when need super privileges right?

Just to make sure, run this comand on linux shell:

$ mysql -u digikam

and if it connects:

> show databases

You may try to insert and delete stuff on anydatabase just to verify the permissiosn if you will!

This test will verify if my assumpting is right.. Sorry to keep asking for commans.. i should fo it myself..

many people wont like this permissions for that user!

Thanks, great job!

Digikam is great!

_______________________________________________
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

Richard Mortimer-2

Re: Mysql/MariaDb database expert needs...

On 18/11/2015 12:02, Henrique Santos Fernandes wrote:

> MariaDB [(none)]> show grants for 'digikam'@'localhost';
> +----------------------------------------------------------------------------------------------------------------+
>
> | Grants for digikam@localhost
> |
>
> +----------------------------------------------------------------------------------------------------------------+
>
> | GRANT SUPER ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
> '*B86D61DED45FEAAB193591C66C302416B0E64CA6' |
> | GRANT ALL PRIVILEGES ON `digikamcoredb`.* TO 'digikam'@'localhost'
> |
> | GRANT ALL PRIVILEGES ON `digikamthumbsdb`.* TO
> 'digikam'@'localhost' |
> | GRANT ALL PRIVILEGES ON `digikamfacedb`.* TO 'digikam'@'localhost'
> |
> +----------------------------------------------------------------------------------------------------------------+
>
> 4 rows in set (0.00 sec)
>
> Gilles
>
>
> I am no expert but it seens that user 'digikam'@'localhost' dont need a
> password to the databases digikamcoredb, digikamthumbsdb and digikamfacedb
> It only need password when using things when need super privileges right?

No. The password is a global connection setting for that user/host
combination and applies to all databases.

I really am surprised that digikam needs SUPER privileges. Usual setup
would be to set the password using USAGE privilege.

GRANT USAGE ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
'*B86D61DED45FEAAB193591C66C302416B0E64CA6';

If SUPER really is required I suspect it is because of the stored
procedure that is used to emulate "IF EXISTS" when adding the indexes.
Even then I would suspect that we could come up with a reduced set of
privileges to access the "mysql" meta database.

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.

Regards

Richard

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

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

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

On 18/11/2015 12:02, Henrique Santos Fernandes wrote:
> MariaDB [(none)]> show grants for 'digikam'@'localhost';
> +----------------------------------------------------------------------------------------------------------------+
>
> | Grants for digikam@localhost
> |
>
> +----------------------------------------------------------------------------------------------------------------+
>
> | GRANT SUPER ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
> '*B86D61DED45FEAAB193591C66C302416B0E64CA6' |
> | GRANT ALL PRIVILEGES ON `digikamcoredb`.* TO 'digikam'@'localhost'
> |
> | GRANT ALL PRIVILEGES ON `digikamthumbsdb`.* TO
> 'digikam'@'localhost' |
> | GRANT ALL PRIVILEGES ON `digikamfacedb`.* TO 'digikam'@'localhost'
> |
> +----------------------------------------------------------------------------------------------------------------+
>
> 4 rows in set (0.00 sec)
>
> Gilles
>
>
> I am no expert but it seens that user 'digikam'@'localhost' dont need a
> password to the databases digikamcoredb, digikamthumbsdb and digikamfacedb
> It only need password when using things when need super privileges right?
No. The password is a global connection setting for that user/host
combination and applies to all databases.

I really am surprised that digikam needs SUPER privileges. Usual setup
would be to set the password using USAGE privilege.

Me too...

GRANT USAGE ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
'*B86D61DED45FEAAB193591C66C302416B0E64CA6';

Interresting to investiguate. But see below...

If SUPER really is required I suspect it is because of the stored
procedure that is used to emulate "IF EXISTS" when adding the indexes.
Even then I would suspect that we could come up with a reduced set of
privileges to access the "mysql" meta database.

yes it is. My investigations revelate that we need to create table on server through this commands :

CREATE DATABASE digikamcoredb; GRANT ALL PRIVILEGES ON digikamcoredb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;

CREATE DATABASE digikamthumbsdb; GRANT ALL PRIVILEGES ON digikamthumbsdb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;

CREATE DATABASE digikamfacedb; GRANT ALL PRIVILEGES ON digikamfacedb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;

... and to be able to run index creation procedures, we need :

GRANT SUPER ON *.* TO 'digikam'@'localhost';FLUSH PRIVILEGES;

... because in SQL procedure code we have :

SQL SECURITY INVOKER

If i drop it, "GRANT SUPER ON *.* TO 'digikam'@'localhost';FLUSH PRIVILEGES;" command at init table is not necessary anymore...

This is a first step in the right direction, i hope.

But, for an internal server solution, this continue to stop at the first run with an error message about the index creation procedure which cannot be executed. I don't know why...

Gilles

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.

Regards

Richard

_______________________________________________
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 Richard Mortimer-2

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

On 18/11/2015 12:02, Henrique Santos Fernandes wrote:
> MariaDB [(none)]> show grants for 'digikam'@'localhost';
> +----------------------------------------------------------------------------------------------------------------+
>
> | Grants for digikam@localhost
> |
>
> +----------------------------------------------------------------------------------------------------------------+
>
> | GRANT SUPER ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
> '*B86D61DED45FEAAB193591C66C302416B0E64CA6' |
> | GRANT ALL PRIVILEGES ON `digikamcoredb`.* TO 'digikam'@'localhost'
> |
> | GRANT ALL PRIVILEGES ON `digikamthumbsdb`.* TO
> 'digikam'@'localhost' |
> | GRANT ALL PRIVILEGES ON `digikamfacedb`.* TO 'digikam'@'localhost'
> |
> +----------------------------------------------------------------------------------------------------------------+
>
> 4 rows in set (0.00 sec)
>
> Gilles
>
>
> I am no expert but it seens that user 'digikam'@'localhost' dont need a
> password to the databases digikamcoredb, digikamthumbsdb and digikamfacedb
> It only need password when using things when need super privileges right?
No. The password is a global connection setting for that user/host
combination and applies to all databases.

I really am surprised that digikam needs SUPER privileges. Usual setup
would be to set the password using USAGE privilege.

GRANT USAGE ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
'*B86D61DED45FEAAB193591C66C302416B0E64CA6';

If SUPER really is required I suspect it is because of the stored
procedure that is used to emulate "IF EXISTS" when adding the indexes.
Even then I would suspect that we could come up with a reduced set of
privileges to access the "mysql" meta database.

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

Gilles Caulier

_______________________________________________
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 Gilles Caulier-4

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

2015-11-18 13:34 GMT+01:00 Richard Mortimer <[hidden email]>:
On 18/11/2015 12:02, Henrique Santos Fernandes wrote:
> MariaDB [(none)]> show grants for 'digikam'@'localhost';
> +----------------------------------------------------------------------------------------------------------------+
>
> | Grants for digikam@localhost
> |
>
> +----------------------------------------------------------------------------------------------------------------+
>
> | GRANT SUPER ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
> '*B86D61DED45FEAAB193591C66C302416B0E64CA6' |
> | GRANT ALL PRIVILEGES ON `digikamcoredb`.* TO 'digikam'@'localhost'
> |
> | GRANT ALL PRIVILEGES ON `digikamthumbsdb`.* TO
> 'digikam'@'localhost' |
> | GRANT ALL PRIVILEGES ON `digikamfacedb`.* TO 'digikam'@'localhost'
> |
> +----------------------------------------------------------------------------------------------------------------+
>
> 4 rows in set (0.00 sec)
>
> Gilles
>
>
> I am no expert but it seens that user 'digikam'@'localhost' dont need a
> password to the databases digikamcoredb, digikamthumbsdb and digikamfacedb
> It only need password when using things when need super privileges right?
No. The password is a global connection setting for that user/host
combination and applies to all databases.

I really am surprised that digikam needs SUPER privileges. Usual setup
would be to set the password using USAGE privilege.

Me too...

GRANT USAGE ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
'*B86D61DED45FEAAB193591C66C302416B0E64CA6';

Interresting to investiguate. But see below...

If SUPER really is required I suspect it is because of the stored
procedure that is used to emulate "IF EXISTS" when adding the indexes.
Even then I would suspect that we could come up with a reduced set of
privileges to access the "mysql" meta database.

yes it is. My investigations revelate that we need to create table on server through this commands :

CREATE DATABASE digikamcoredb; GRANT ALL PRIVILEGES ON digikamcoredb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;
CREATE DATABASE digikamthumbsdb; GRANT ALL PRIVILEGES ON digikamthumbsdb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;
CREATE DATABASE digikamfacedb; GRANT ALL PRIVILEGES ON digikamfacedb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;

... and to be able to run index creation procedures, we need :

GRANT SUPER ON *.* TO 'digikam'@'localhost';FLUSH PRIVILEGES;

... because in SQL procedure code we have :

SQL SECURITY INVOKER

If i drop it, "GRANT SUPER ON *.* TO 'digikam'@'localhost';FLUSH PRIVILEGES;" command at init table is not necessary anymore...

This is a first step in the right direction, i hope.

As it work fine without "SQL SECURITY INVOKER" in index procedure creation, it safe to remove it ?

After all it's about security stuff. This point is important...

Gilles Caulier

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

Henrique Santos Fernandes

Re: Mysql/MariaDb database expert needs...

I dont understando that much but i have 2 questions.

Why dont create the index normaly instead of using this if exist procedure?

If the procedure is really needed, you need to create it with digikam user?

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..

Well, as i said, i dont know much..

Hope this helps!

Em qua, 18 de nov de 2015 às 11:24, Gilles Caulier <[hidden email]> escreveu:

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

2015-11-18 13:34 GMT+01:00 Richard Mortimer <[hidden email]>:
On 18/11/2015 12:02, Henrique Santos Fernandes wrote:
> MariaDB [(none)]> show grants for 'digikam'@'localhost';
> +----------------------------------------------------------------------------------------------------------------+
>
> | Grants for digikam@localhost
> |
>
> +----------------------------------------------------------------------------------------------------------------+
>
> | GRANT SUPER ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
> '*B86D61DED45FEAAB193591C66C302416B0E64CA6' |
> | GRANT ALL PRIVILEGES ON `digikamcoredb`.* TO 'digikam'@'localhost'
> |
> | GRANT ALL PRIVILEGES ON `digikamthumbsdb`.* TO
> 'digikam'@'localhost' |
> | GRANT ALL PRIVILEGES ON `digikamfacedb`.* TO 'digikam'@'localhost'
> |
> +----------------------------------------------------------------------------------------------------------------+
>
> 4 rows in set (0.00 sec)
>
> Gilles
>
>
> I am no expert but it seens that user 'digikam'@'localhost' dont need a
> password to the databases digikamcoredb, digikamthumbsdb and digikamfacedb
> It only need password when using things when need super privileges right?
No. The password is a global connection setting for that user/host
combination and applies to all databases.

I really am surprised that digikam needs SUPER privileges. Usual setup
would be to set the password using USAGE privilege.

Me too...

GRANT USAGE ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
'*B86D61DED45FEAAB193591C66C302416B0E64CA6';

Interresting to investiguate. But see below...

If SUPER really is required I suspect it is because of the stored
procedure that is used to emulate "IF EXISTS" when adding the indexes.
Even then I would suspect that we could come up with a reduced set of
privileges to access the "mysql" meta database.

yes it is. My investigations revelate that we need to create table on server through this commands :

CREATE DATABASE digikamcoredb; GRANT ALL PRIVILEGES ON digikamcoredb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;
CREATE DATABASE digikamthumbsdb; GRANT ALL PRIVILEGES ON digikamthumbsdb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;
CREATE DATABASE digikamfacedb; GRANT ALL PRIVILEGES ON digikamfacedb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;

... and to be able to run index creation procedures, we need :

GRANT SUPER ON *.* TO 'digikam'@'localhost';FLUSH PRIVILEGES;

... because in SQL procedure code we have :

SQL SECURITY INVOKER

If i drop it, "GRANT SUPER ON *.* TO 'digikam'@'localhost';FLUSH PRIVILEGES;" command at init table is not necessary anymore...

This is a first step in the right direction, i hope.

As it work fine without "SQL SECURITY INVOKER" in index procedure creation, it safe to remove it ?

After all it's about security stuff. This point is important...

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

Gilles Caulier-4

Re: Mysql/MariaDb database expert needs...

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...

Gilles Caulier

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

Richard Mortimer-2

Re: Mysql/MariaDb database expert needs...

In reply to this post by Gilles Caulier-4

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

Henrique Santos Fernandes

Re: Mysql/MariaDb database expert needs...

In reply to this post by Gilles Caulier-4

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

123