[digiKam-users] migrating to internal MySQL

[karsten_df]

I'm currently using digikam with an external MySQL server located on a small
& not so powerful server in my network, For performance reasons, I'd like to
test the configuration with an internal MySQL Server, but I'm finding little
information how it works.
I had thought the internal MySQL server would work somehow like this
- I'd set up e.g. a mariadb server on the PC on the PC digikam is running at
- I'd create the three databases (core, thumbs, faces),
  and populate them with a dump I made on the server
- I'd enter the details of the co-located server into digikam's setup

However, the dialog for using an internal MySQL db looks much different to
the external one -
no way to enter db names, user/password, etc. Seems that digikam wants to
configure the databases itself, resp. digikam has a built-in MySQL server?

Is there some more information how this works, especially in a case where
existing databases need to be migrated?
(I've also looked at the Migration dialog, but before trying this I'd like
to better understand the background :-)



--
Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html

[Maik Qualmann]

Yes, set up an internal server in digiKam. Everything is managed by digiKam
(password, user name, etc.) A hidden MySQL directory is created in the
selected path. With the migration dialog you can transfer your existing
external database to the internal one.

Maik

Am Mittwoch, 4. Juli 2018, 13:00:01 CEST schrieb karsten_df:

> I'm currently using digikam with an external MySQL server located on a small
> & not so powerful server in my network, For performance reasons, I'd like
> to test the configuration with an internal MySQL Server, but I'm finding
> little information how it works.
> I had thought the internal MySQL server would work somehow like this
> - I'd set up e.g. a mariadb server on the PC on the PC digikam is running at
> - I'd create the three databases (core, thumbs, faces),
>   and populate them with a dump I made on the server
> - I'd enter the details of the co-located server into digikam's setup
>
> However, the dialog for using an internal MySQL db looks much different to
> the external one -
> no way to enter db names, user/password, etc. Seems that digikam wants to
> configure the databases itself, resp. digikam has a built-in MySQL server?
>
> Is there some more information how this works, especially in a case where
> existing databases need to be migrated?
> (I've also looked at the Migration dialog, but before trying this I'd like
> to better understand the background :-)
>
>
>
> --
> Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html

[karsten_df]

Ok, so I tried the migration (using the 5.9.0 AppImage on Tumbleweed).
I created a directory with full access to 'other' and pointed to this for
the database directory.
mysql_install_db and mysqld also seem to be pointed to correctly.
On starting the migration, an error notification pops up: "Internal server
is not used and is not active!"
(There is also already a mysql server installed on my machine independently
of digikam, however it makes no difference if this is running or stopped).

Does this work with the installed version of digikam only?
(I'm not using this currently due to the issue described in
http://digikam.1695700.n4.nabble.com/digiKam-users-Renaming-a-folder-does-not-work-tp4705322.html)

Karsten


Maik Qualmann wrote
> Yes, set up an internal server in digiKam. Everything is managed by
> digiKam
> (password, user name, etc.) A hidden MySQL directory is created in the
> selected path. With the migration dialog you can transfer your existing
> external database to the internal one.
>
> Maik





--
Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html

[Maik Qualmann]

The internal server must already be started when you open the migration
dialog. So you have to set up an internal server already in the digiKam
settings. digiKam can only manage one internal server. Do not confuse internal
server with a local MySQL server. The local MySQL server is set up as a remote
one, just with localhost or 127.0.0.1 as the address. The internal server in
digiKam launches another SQL server on your computer, which will also be
stopped when you exit digiKam. However, if the internal server is located on
an SSD, is connected over the socket file, is this the fastest database.

Maik

Am Donnerstag, 5. Juli 2018, 11:47:15 CEST schrieb karsten_df:

> Ok, so I tried the migration (using the 5.9.0 AppImage on Tumbleweed).
> I created a directory with full access to 'other' and pointed to this for
> the database directory.
> mysql_install_db and mysqld also seem to be pointed to correctly.
> On starting the migration, an error notification pops up: "Internal server
> is not used and is not active!"
> (There is also already a mysql server installed on my machine independently
> of digikam, however it makes no difference if this is running or stopped).
>
> Does this work with the installed version of digikam only?
> (I'm not using this currently due to the issue described in
> http://digikam.1695700.n4.nabble.com/digiKam-users-Renaming-a-folder-does-no
> t-work-tp4705322.html)
>
> Karsten
>
>
> Maik Qualmann wrote
>
> > Yes, set up an internal server in digiKam. Everything is managed by
> > digiKam
> > (password, user name, etc.) A hidden MySQL directory is created in the
> > selected path. With the migration dialog you can transfer your existing
> > external database to the internal one.
> >
> > Maik
>
> --
> Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html

[Gilles Caulier-4]

Did you read the basis from https://docs.kde.org/trunk5/en/extragear-graphics/digikam/using-setup.html#using-setup-database

Best

Gilles Caulier

2018-07-04 13:00 GMT+02:00 karsten_df <[hidden email]>:

I'm currently using digikam with an external MySQL server located on a small
& not so powerful server in my network, For performance reasons, I'd like to
test the configuration with an internal MySQL Server, but I'm finding little
information how it works.
I had thought the internal MySQL server would work somehow like this
- I'd set up e.g. a mariadb server on the PC on the PC digikam is running at
- I'd create the three databases (core, thumbs, faces),
  and populate them with a dump I made on the server
- I'd enter the details of the co-located server into digikam's setup

However, the dialog for using an internal MySQL db looks much different to
the external one -
no way to enter db names, user/password, etc. Seems that digikam wants to
configure the databases itself, resp. digikam has a built-in MySQL server?

Is there some more information how this works, especially in a case where
existing databases need to be migrated?
(I've also looked at the Migration dialog, but before trying this I'd like
to better understand the background :-)



--
Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html

[karsten_df]

thanks Maik,

that worked nicely. After scanning thumbnails, digikam works really fast
now..
Your explanations regarding local vs. internal SQL server actually made
things clear for me.

Karsten



--
Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html

[karsten_df]

hi Gilles,

actually yes, and the text was indeed fairly clear once I had understood the
concept ;-)

I think what confused me was the introduction about mariadb and MySQL. I was
somehow assuming this would apply both to internal and external servers,
making me think that I'd have to set up the internal server myself as well
(so to have a choice between mariadb and MySQL).
Maybe it would help to add the explanation from Maik's last post to the "The
MySQL Internal Server" section ("Do not confuse the internal server with a
local MySQL server. The local MySQL server is set up just as a remote one,
with localhost or 127.0.0.1 as the address. The internal server in digiKam
launches another SQL server on your computer, which will also be stopped
when you exit digiKam.")

Karsten



--
Sent from: http://digikam.1695700.n4.nabble.com/digikam-users-f1735189.html

[Pedro Neves]

On 06-07-2018 08:25, karsten_df wrote:
> hi Gilles,
>
> actually yes, and the text was indeed fairly clear once I had understood the
> concept ;-)
>
Hi all:

Inspired by Karsten, I'm trying to set up my Digikam to use an internal
MySQL server. However, I'm getting an error message:

An error occurred during the internal server start.

Details: Could not start database initializer.

Executable: mysql_install_db

Arguments: --datadir=/home/username/.digikam2/.mysql.digikam/db_data,
--defaults-file=/home/username/.local/share/digikam/mysql.conf

Process error: Unknown error


If I start Digikam from the command line, I get further info:

digikam.databaseserver: Stdout: "\nFATAL ERROR: Could not find
./bin/my_print_defaults\n\nIf you compiled from source, you need to
either run 'make install' to\ncopy the software into the correct
location ready for operation.\nIf you don't want to do a full install,
you can use the --srcddir\noption to only install the mysql database and
privilege tables\n\nIf you are using a binary release, you must either
be at the top\nlevel of the extracted archive, or pass the --basedir
option\npointing to that location.\n\nThe latest information about
mysql_install_db is available
at\nhttps://mariadb.com/kb/en/installing-system-tables-mysql_install_db\n"
digikam.databaseserver: Stderr: ""
digikam.databaseserver: Exit code: 1
digikam.databaseserver: Process error: "Unknown error"
digikam.databaseserver: Cannot start internal database server



Steps I performed:

1 - In Digikam/Settings/Configure digikam:

Change database configuration from Type SQLite to Mysql internal
(experimental);
Change database location
Check that the binaries were found on my system (both files have a
"check" before)
Press OK

Digikam displays a message asking if it's ok to create the folder for
the DB. Press yes

Error message shows up...


Any hints? (I'm using Arch linux and Digikam 5.9.0)

Thanks in advance:

Pedro

[Pedro Neves]

Hi again:

Trying to fix my problem, it seems that I need to pass one more
parameter to the mysql_install_db command, with the location of
"my_print_defaults". Something like: "--basedir=/usr"

(from comment #19 on this thread:
https://bbs.archlinux.org/viewtopic.php?id=78358)

How can I do this? (tell Digikam that "my_print_defaults" is located in
/usr?

Thanks again...

Cheers:

Pedro

[Maik Qualmann]

Do you use MySQL or MariaDB? Maybe this is the cause:

https://jira.mariadb.org/browse/MDEV-14580

Here with openSUSE packages the problem does not occur.

Maik

Am Freitag, 6. Juli 2018, 11:11:50 CEST schrieb Pedro Neves:

> On 06-07-2018 08:25, karsten_df wrote:
> > hi Gilles,
> >
> > actually yes, and the text was indeed fairly clear once I had understood
> > the concept ;-)
>
> Hi all:
>
> Inspired by Karsten, I'm trying to set up my Digikam to use an internal
> MySQL server. However, I'm getting an error message:
>
> An error occurred during the internal server start.
>
> Details: Could not start database initializer.
>
> Executable: mysql_install_db
>
> Arguments: --datadir=/home/username/.digikam2/.mysql.digikam/db_data,
> --defaults-file=/home/username/.local/share/digikam/mysql.conf
>
> Process error: Unknown error
>
>
> If I start Digikam from the command line, I get further info:
>
> digikam.databaseserver: Stdout: "\nFATAL ERROR: Could not find
> ./bin/my_print_defaults\n\nIf you compiled from source, you need to
> either run 'make install' to\ncopy the software into the correct
> location ready for operation.\nIf you don't want to do a full install,
> you can use the --srcddir\noption to only install the mysql database and
> privilege tables\n\nIf you are using a binary release, you must either
> be at the top\nlevel of the extracted archive, or pass the --basedir
> option\npointing to that location.\n\nThe latest information about
> mysql_install_db is available
> at\nhttps://mariadb.com/kb/en/installing-system-tables-mysql_install_db\n"
> digikam.databaseserver: Stderr: ""
> digikam.databaseserver: Exit code: 1
> digikam.databaseserver: Process error: "Unknown error"
> digikam.databaseserver: Cannot start internal database server
>
>
>
> Steps I performed:
>
> 1 - In Digikam/Settings/Configure digikam:
>
> Change database configuration from Type SQLite to Mysql internal
> (experimental);
> Change database location
> Check that the binaries were found on my system (both files have a
> "check" before)
> Press OK
>
> Digikam displays a message asking if it's ok to create the folder for
> the DB. Press yes
>
> Error message shows up...
>
>
> Any hints? (I'm using Arch linux and Digikam 5.9.0)
>
> Thanks in advance:
>
> Pedro

[Pedro Neves]

On 06-07-2018 11:23, Maik Qualmann wrote:
> Do you use MySQL or MariaDB? Maybe this is the cause:
>
> https://jira.mariadb.org/browse/MDEV-14580
>
> Here with openSUSE packages the problem does not occur.
>
I'm using MariaDB. Yes, I guess that's the problem, but I cannot seem to
be able to pass the basedir option to the command. I've added the
basedir = /usr option in /etc/mysql/my.cnf, but no luck...

Cheers:

Pedro

[Pedro Neves]

On 06-07-2018 11:23, Maik Qualmann wrote:
> Do you use MySQL or MariaDB? Maybe this is the cause:
>

Hi all:

I've managed to fix the problem by running Digikam from a terminal
inside my /usr directory.

Maybe this can save somebody some trouble...

All the best:

Pedro

[piotergmoter]

O the site mentioned below once is said that migrating from SQLite to
mysql makes sens '..'when collections include more than 100,000 items'
and few lines below 'While using a large collection, of size greater
than 10,000 items, the application tends to slow down'

So 10 000 or 100 000 is the threshold?

I have 40 000 images/video files. Does it makes sense to migrate to
internal mysql?

Well, mysql is still experimental, so in spite of the reasons above is
it safe?

Regards

Piotr


W dniu 05.07.2018 o 15:40, Gilles Caulier pisze:
> Did you read the basis from
> https://docs.kde.org/trunk5/en/extragear-graphics/digikam/using-setup.html#using-setup-database

[leoutation]

On 07/08/2018 02:00 PM, Pioter Gmoter wrote:

> O the site mentioned below once is said that migrating from SQLite to
> mysql makes sens '..'when collections include more than 100,000 items'
> and few lines below 'While using a large collection, of size greater
> than 10,000 items, the application tends to slow down'
>
> So 10 000 or 100 000 is the threshold?
>
> I have 40 000 images/video files. Does it makes sense to migrate to
> internal mysql?
>
> Well, mysql is still experimental, so in spite of the reasons above is
> it safe?
>

Hi
I have about 20.000 pictures files. Last year, I migrated from sqlite to
external mysqldb / mariadb, it works fine for me with DK 5.9 and DK git
. I consider mysql it's stable now, from far much better than sqlite.

[Gilles Caulier-4]

It's an error from the documentation. The right threshold is 100.000 items. 

Here i use 400.000 items with sqlite and a SSD and it's sometime tedious, but suitable.

Best

Gilles caulier 

2018-07-08 15:01 GMT+02:00 <[hidden email]>:

On 07/08/2018 02:00 PM, Pioter Gmoter wrote:

O the site mentioned below once is said that migrating from SQLite to
mysql makes sens '..'when collections include more than 100,000 items'
and few lines below 'While using a large collection, of size greater
than 10,000 items, the application tends to slow down'

So 10 000 or 100 000 is the threshold?

I have 40 000 images/video files. Does it makes sense to migrate to
internal mysql?

Well, mysql is still experimental, so in spite of the reasons above is
it safe?

Hi
I have about 20.000 pictures files. Last year, I migrated from sqlite to external mysqldb / mariadb, it works fine for me with DK 5.9 and DK git . I consider mysql it's stable now, from far much better than sqlite.

[piotergmoter]

So stability is ok. What about speed? Any noticeable difference? My DK
is blocking too much when starting, importing photos etc. I wonder about
mysql behavior here.

P.



W dniu 08.07.2018 o 15:01, [hidden email] pisze:

> On 07/08/2018 02:00 PM, Pioter Gmoter wrote:
>> O the site mentioned below once is said that migrating from SQLite to
>> mysql makes sens '..'when collections include more than 100,000 items'
>> and few lines below 'While using a large collection, of size greater
>> than 10,000 items, the application tends to slow down'
>>
>> So 10 000 or 100 000 is the threshold?
>>
>> I have 40 000 images/video files. Does it makes sense to migrate to
>> internal mysql?
>>
>> Well, mysql is still experimental, so in spite of the reasons above is
>> it safe?
>>
> Hi
> I have about 20.000 pictures files. Last year, I migrated from sqlite
> to external mysqldb / mariadb, it works fine for me with DK 5.9 and DK
> git . I consider mysql it's stable now, from far much better than sqlite.
>

[leoutation]

On 07/08/2018 07:36 PM, Pioter Gmoter wrote:
> So stability is ok. What about speed? Any noticeable difference? My DK
> is blocking too much when starting, importing photos etc. I wonder about
> mysql behavior here.
>
Mariadb is more faster than sqlite, that's the main reason why I
migrated to mariadb / mysql as soon I could.
First indexing collection takes time then it runs faster.

[piotergmoter]

W dniu 08.07.2018 o 19:59, [hidden email] pisze:
> On 07/08/2018 07:36 PM, Pioter Gmoter wrote:
>> So stability is ok. What about speed? Any noticeable difference? My DK
>> is blocking too much when starting, importing photos etc. I wonder about
>> mysql behavior here.
>>
> Mariadb is more faster than sqlite, that's the main reason why I
> migrated to mariadb / mysql as soon I could.
> First indexing collection takes time then it runs faster.

Hm, wonder why it is faster? Faster DB engine?
P.