[Bug 262321] New: mysql user privileges

[krienke]

https://bugs.kde.org/show_bug.cgi?id=262321

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #21 from [hidden email] ---
I am also running digikam in a shared mysql server environment and no regular
user has a super privilege.  MYSQL is version 5.5.33 .

When I try to migrate form sqlite to mysql in digikam 3.5.0 I see a popup
dialog saying that migration failed (not a really useful message) and on the
commandline I see:

digikam(7144)/digikam (core): Insufficient rights on databse.

So I guess I have the same problem as describe in this bug report.

Rainer

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=262321

--- Comment #22 from Gilles Caulier <[hidden email]> ---
krienke,

Look in Amarok settings dialog. There is a similar setting than digiKam to use
a remote Mysql server, with instructions to setup database. I think than
something like that must be done at least in digiKam Right ?

Gilles Caulier

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Christian-2]

https://bugs.kde.org/show_bug.cgi?id=262321

Christian <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #23 from Christian <[hidden email]> ---
Hi krinke,

I solved this problem in OpenSuse several years ago - it is still the same
issue with digiKam 4.1.0 Build 11.1 or 11.8 (for KDE 4.13).

You have two options - first install the mariadb/mysql gui "mysqlworkbench"
which make things much easier, or do it on the command line. I will show the
second option here.

Note: the latest "mysqlworkbench" of the 13.1 database repository seems to be
broken, at least in my environment. Use the old rpm from Suse 12.3 for 13.1 and
it works great.
...........................................................................
Setting up a mariadb (default) or mysql db for digikam/amarok in Suse 13.1:

Important note - if you upgraded from OpenSuse 12.3, mariadb will not start.
see output of "systemctl status mysql.service". It tells you to:
 >touch /var/lib/mysql/.force_upgrade
 >systemctl restart mysql.service

1. Set mariadb/mysql root password - it is empty

#Check if service is running:
>systemctl status  mysql.service

# open mysql client on the console:
>mysql --host=127.0.0.1 --user=root --port=3306

and type:

use mysql;set password for root@localhost = password('xxxxxxx');
flush privileges;delete from user where User='';

2. change mariadb configuration for digikam before creating databases

mysql/maria db settings will use up a lot of space if binary logging is enabled
and no single tablespaces are used. To avoid troubles later on check the
/etc/my.cnf  BEFORE you create the databases for digikam and amarok.

[mysqld]
...
innodb_file_per_table=ON

Find a line that reads "log_bin" and remove or comment it as follows:
  #log_bin = ...
  # binlog_format=mixed
You also need to remove or comment following lines if present:
  #expire_logs_days        = 10
  #max_binlog_size         = 100M

It is also recommended to use "large" server settings if you have a lot of
assets/images - see maria db docs.

3. restart mysql db and check server status

Note you have to setup a root user for mariadb/mysql first. google for mysql
setup - can be done o

>systemctl restart   mysql.service
>systemctl status  mysql.service
>mysql --host=127.0.0.1 --user=root --password=** --port=3306

4. On the mysql prompt type:

CREATE DATABASE digikamdb;GRANT ALL PRIVILEGES ON digikamdb.* TO
'digikamuser'@'localhost' IDENTIFIED BY '**'; FLUSH PRIVILEGES;
GRANT SUPER ON *.* TO 'digikamuser'@'localhost'; FLUSH PRIVILEGES;

For Amarok type:

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

Note: ** must be replaced by your passwords

5. Adapt digikam and amarok database settings and check the connection:

host/rechnername:        localhost  port/anschluss: 3306
name of schema/database: see steps above
name/password of user:   see steps above

Then you are asked to setup the filepath to the main collection in digikam
again. After that you have to configure digikam which is a hard job as well.

6. Install argyllcms and dispcalGUI to create color profiles
   for semi professional usage of digikam
   Setup mariadb/mysql backup - see docs

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Christian-2]

https://bugs.kde.org/show_bug.cgi?id=262321

--- Comment #24 from Christian <[hidden email]> ---
Remark:

In Amarok this issue is solved by a short description under the db-settings
where you can copy and paste the SQL code to your mysql console.

This would be a great improvement for digikam as well - the basic code is:

CREATE DATABASE digikamdb;GRANT ALL PRIVILEGES ON digikamdb.* TO
'digikamuser'@'localhost' IDENTIFIED BY '**'; FLUSH PRIVILEGES;
GRANT SUPER ON *.* TO 'digikamuser'@'localhost'; FLUSH PRIVILEGES;

PS: Please remove the german translation for "port" in digikam the digikam db
settings - no one will understand "anschluss".

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=262321

--- Comment #25 from [hidden email] ---
(In reply to Christian from comment #24)

> Remark:
>
> In Amarok this issue is solved by a short description under the db-settings
> where you can copy and paste the SQL code to your mysql console.
>
> This would be a great improvement for digikam as well - the basic code is:
>
> CREATE DATABASE digikamdb;GRANT ALL PRIVILEGES ON digikamdb.* TO
> 'digikamuser'@'localhost' IDENTIFIED BY '**'; FLUSH PRIVILEGES;
> GRANT SUPER ON *.* TO 'digikamuser'@'localhost'; FLUSH PRIVILEGES;
>
> PS: Please remove the german translation for "port" in digikam the digikam
> db settings - no one will understand "anschluss".

That would be a good idea, or even a link to the DigiKam-Website with the
respective statements would be fine.

And to solve this "bug", please remove the Stored Procedure
"create_index_if_not_exists" and implement it in another way if still needed.

Without that Stored Procedure one could give all privileges to a dedicated User
only for the DigiKam Database, without the need for super privileges. As SUPER
is a Server privilege, the DigiKam-User could access other Databases, which is
not wanted on a shared database server.

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=262321

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|MYSQL : user privileges     |MySQL : user privileges
                   |management                  |management

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=262321

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #26 from Gilles Caulier <[hidden email]> ---
*** Bug 280678 has been marked as a duplicate of this bug. ***

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=262321

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|MySQL : user privileges     |MYSQL : user privileges
                   |management                  |management

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=262321

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Database                    |Database-Mysql

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=262321

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
   Version Fixed In|                            |5.0.0
             Status|CONFIRMED                   |RESOLVED
      Latest Commit|                            |http://commits.kde.org/digi
                   |                            |kam/4a5e7cef1f15d62701a6fa7
                   |                            |c1366f3303a687616
         Resolution|---                         |FIXED

--- Comment #27 from [hidden email] ---
Git commit 4a5e7cef1f15d62701a6fa7c1366f3303a687616 by Gilles Caulier.
Committed on 20/11/2015 at 14:54.
Pushed by cgilles into branch 'master'.

New database setup config widget with more details about Mysql initial
configuration and more help to tune settings.
The MySQl Internal is now handle as a main database type config.
Screenshots :

https://www.flickr.com/photos/digikam/23121578776
https://www.flickr.com/photos/digikam/23147681465
https://www.flickr.com/photos/digikam/22851871180
Related: bug 233762, bug 337443, bug 342124

FIXED-IN: 5.0.0

M  +2    -2    CMakeLists.txt
M  +4    -2    app/main/digikamapp.cpp
M  +7    -7    libs/album/albummanager.cpp
M  +3    -3    libs/database/coredb/coredbschemaupdater.cpp
M  +1    -1    libs/database/engine/dbenginebackend.cpp
M  +1    -1    libs/database/engine/dbengineguierrorhandler.cpp
M  +32   -32   libs/database/engine/dbengineparameters.cpp
M  +7    -7    libs/database/engine/dbengineparameters.h
M  +239  -134  libs/database/utils/dbsettingswidget.cpp
M  +22   -10   libs/database/utils/dbsettingswidget.h
M  +2    -2    libs/settings/applicationsettings.h
M  +6    -6    libs/settings/applicationsettings_database.cpp
M  +1    -1    tests/database/testdatabase.cpp
M  +14   -18   utilities/setup/setupdatabase.cpp

http://commits.kde.org/digikam/4a5e7cef1f15d62701a6fa7c1366f3303a687616

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel