Version: 2.0.0 (using Devel) OS: Linux Hi, digikam needs a user with super privileges in order to access the mysql database.... see more here http://scribblesandsnaps.wordpress.com/2010/10/15/using-digikam-with-mysql/ alin's comment. However I find this excessive... other kde apps use mysql databases (amarok/akonadi) without the need for super privileges... wouldbe nice if the mysql would work without the super,,, Alin Reproducible: Didn't try OS: Linux (x86_64) release 2.6.37-rc8-desktop Compiler: gcc
Reproductible always with 2.0dev In fact after creating a user database, and giving him full rights ( GRANT ALL on ) this instruction doesn't work in dbconfig 702 <dbaction name="CheckPriv_CREATE_TRIGGER"><statement mode="plain"> 703 CREATE TRIGGER privcheck_trigger AFTER DELETE ON PrivCheck 704 FOR EACH ROW BEGIN 705 END; 706 </statement> 707 </dbaction> mysql> CREATE TRIGGER privcheck_trigger AFTER DELETE ON PrivCheck -> FOR EACH ROW BEGIN -> END; ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) mysql>
I can confirm this issue. I have MySQL in a hosting without super privileges and i can't use this feature. I'm using digikam 2 Beta 4, from ubuntu
So it seems the check for the needed privileges are too demanding on some MySQL versions? Any patches or suggestions?
Marcel, in fact I don't know how you can do that, due to upstream (bug/feature) the creation of triggers apparently are root reserved in mysql, due to replication feature of the database engine. ref http://dev.mysql.com/doc/refman/5.0/en/stored-programs-logging.html So for the "normal" embedded mysql there's no troubles as it's run like superuser by end user. The real impact is about using digikam in shared mode (centralized mysql server). So digikam can said : hey we didn't support that, but as it has, it will become a big regression. Time to interface digikam to virtuozzo ? :D
From my point of view it is possible to avoid using triggers and move the functionality to the concrete sql statements - I've done that once for the tags tree. Maybe one can extend the affected statements (and move some more statements to the dbconfig.xml file). I'm running out of free time to do that in near future :-( (But I will give some help to do that).
Same issue... but appears not only in digiKam context. You can find a lot of threads about it. I also found: "The TRIGGER privilege enables trigger operations. You must have this privilege for a table to create, drop, or execute triggers for that table. This privilege was added in MySQL 5.1.6. (Prior to MySQL 5.1.6, trigger operations required the SUPER privilege.)" ( http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_trigger ). Ok, current stable in the 5.1 branch is 5.1.57 and 5.1.6 never got stable status? Somebody on MySQL 5.5.x should check if a database level privilege "TRIGGER" is available because it would probably fix this issue. Is somebody with this issue not on MySQL 5.1.x?
Hi, I've got the following message from digiKam after switching to my MySQL-Server: "You have insufficient privileges on the database. Following privileges are not assigned to you: CREATE TRIGGER Check your privileges on the database and restart digiKam." digiKam version is: 2.0.0-rc openSUSE: 11.4 64Bit KDE: 4.7.00 release 6 MySQL (on a different server): 5.5.15-log The user I use for digiKam is in the mysql.user table with no privileges, but in the table mysql.db it is granted full access to the digiKam-DB, where it also has the privilege "TRIGGER_PRIV". I tried to create a trigger by myself with the digiKam user and it worked. Just for your information: Giving digiKam root access solves the trigger problem but then another problem pops up: "Failed to update the database schema from version 5 to version 6. Please read the error messages printed on the console and report this error as a bug at bugs.kde.org." But for this there is already a bug report. If you need more information or help on this issue let me know. Cheers Regi
*** This bug has been confirmed by popular vote. ***
(In reply to comment #7) > Hi, > > I've got the following message from digiKam after switching to my MySQL-Server: > > "You have insufficient privileges on the database. > Following privileges are not assigned to you: > CREATE TRIGGER > Check your privileges on the database and restart digiKam." > > digiKam version is: 2.0.0-rc > openSUSE: 11.4 64Bit > KDE: 4.7.00 release 6 > MySQL (on a different server): 5.5.15-log > > The user I use for digiKam is in the mysql.user table with no privileges, but > in the table mysql.db it is granted full access to the digiKam-DB, where it > also has the privilege "TRIGGER_PRIV". > > I tried to create a trigger by myself with the digiKam user and it worked. > > Just for your information: > Giving digiKam root access solves the trigger problem but then another problem > pops up: > "Failed to update the database schema from version 5 to version 6. Please read > the error messages printed on the console and report this error as a bug at > bugs.kde.org." > But for this there is already a bug report. > > If you need more information or help on this issue let me know. > > Cheers > Regi It should work with privileges only on the database, try a "FLUSH PRIVILEGES;" command. A strong suggestion, for the moment do not use different databases for images and thumbnails, there are known bugs that will be fixed (tentatively in 2.1 possibly in 2.2) mysql> SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE GRANTEE LIKE '%digikam%'; +---------------+---------------+----------------+--------------+ | GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | +---------------+---------------+----------------+--------------+ | 'digikam'@'%' | NULL | USAGE | NO | +---------------+---------------+----------------+--------------+ 1 row in set (0.01 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE GRANTEE LIKE '%digikam%' AND TABLE_SCHEMA LIKE 'digikam19'; +---------------+---------------+--------------+-------------------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE | +---------------+---------------+--------------+-------------------------+--------------+ | 'digikam'@'%' | NULL | digikam19 | SELECT | YES | | 'digikam'@'%' | NULL | digikam19 | INSERT | YES | | 'digikam'@'%' | NULL | digikam19 | UPDATE | YES | | 'digikam'@'%' | NULL | digikam19 | DELETE | YES | | 'digikam'@'%' | NULL | digikam19 | CREATE | YES | | 'digikam'@'%' | NULL | digikam19 | DROP | YES | | 'digikam'@'%' | NULL | digikam19 | REFERENCES | YES | | 'digikam'@'%' | NULL | digikam19 | INDEX | YES | | 'digikam'@'%' | NULL | digikam19 | ALTER | YES | | 'digikam'@'%' | NULL | digikam19 | CREATE TEMPORARY TABLES | YES | | 'digikam'@'%' | NULL | digikam19 | LOCK TABLES | YES | | 'digikam'@'%' | NULL | digikam19 | EXECUTE | YES | | 'digikam'@'%' | NULL | digikam19 | CREATE VIEW | YES | | 'digikam'@'%' | NULL | digikam19 | SHOW VIEW | YES | | 'digikam'@'%' | NULL | digikam19 | CREATE ROUTINE | YES | | 'digikam'@'%' | NULL | digikam19 | ALTER ROUTINE | YES | | 'digikam'@'%' | NULL | digikam19 | EVENT | YES | | 'digikam'@'%' | NULL | digikam19 | TRIGGER | YES | +---------------+---------------+--------------+-------------------------+--------------+ 18 rows in set (0.00 sec)
(In reply to comment #9) > It should work with privileges only on the database, try a "FLUSH PRIVILEGES;" > command. > > [MySQL privileges] Are these your actual privileges digikam runs without problems? I have the following privileges, but I still do get the error message. mysql> SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE GRANTEE LIKE '%alex%'; +------------+---------------+----------------+--------------+ | GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | +------------+---------------+----------------+--------------+ | 'alex'@'%' | NULL | USAGE | NO | +------------+---------------+----------------+--------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE GRANTEE LIKE '%alex%' AND TABLE_SCHEMA LIKE 'digikam'; +------------+---------------+--------------+-------------------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE | +------------+---------------+--------------+-------------------------+--------------+ | 'alex'@'%' | NULL | digikam | SELECT | YES | | 'alex'@'%' | NULL | digikam | INSERT | YES | | 'alex'@'%' | NULL | digikam | UPDATE | YES | | 'alex'@'%' | NULL | digikam | DELETE | YES | | 'alex'@'%' | NULL | digikam | CREATE | YES | | 'alex'@'%' | NULL | digikam | DROP | YES | | 'alex'@'%' | NULL | digikam | REFERENCES | YES | | 'alex'@'%' | NULL | digikam | INDEX | YES | | 'alex'@'%' | NULL | digikam | ALTER | YES | | 'alex'@'%' | NULL | digikam | CREATE TEMPORARY TABLES | YES | | 'alex'@'%' | NULL | digikam | LOCK TABLES | YES | | 'alex'@'%' | NULL | digikam | EXECUTE | YES | | 'alex'@'%' | NULL | digikam | CREATE VIEW | YES | | 'alex'@'%' | NULL | digikam | SHOW VIEW | YES | | 'alex'@'%' | NULL | digikam | CREATE ROUTINE | YES | | 'alex'@'%' | NULL | digikam | ALTER ROUTINE | YES | | 'alex'@'%' | NULL | digikam | EVENT | YES | | 'alex'@'%' | NULL | digikam | TRIGGER | YES | +------------+---------------+--------------+-------------------------+--------------+ 18 rows in set (0.00 sec) Note: This is digikam 2.0.0
I have exactly the same privileges granted with digikam 2.1 and I still get the error message abount the missing "CREATE TRIGGER" privilege. Note that the "TRIGGER" privilege is granted for the digikam user. This bug is VERY anoying and prevents me to use digikam at all. Are there any news about this? Thanks. davide (In reply to comment #10) > (In reply to comment #9) > > It should work with privileges only on the database, try a "FLUSH PRIVILEGES;" > > command. > > > > [MySQL privileges] > > Are these your actual privileges digikam runs without problems? > I have the following privileges, but I still do get the error message. > > mysql> SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE GRANTEE LIKE > '%alex%'; > +------------+---------------+----------------+--------------+ > | GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | > +------------+---------------+----------------+--------------+ > | 'alex'@'%' | NULL | USAGE | NO | > +------------+---------------+----------------+--------------+ > 1 row in set (0.00 sec) > > mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE GRANTEE LIKE > '%alex%' AND TABLE_SCHEMA LIKE 'digikam'; > +------------+---------------+--------------+-------------------------+--------------+ > | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | > IS_GRANTABLE | > +------------+---------------+--------------+-------------------------+--------------+ > | 'alex'@'%' | NULL | digikam | SELECT | YES > | > | 'alex'@'%' | NULL | digikam | INSERT | YES > | > | 'alex'@'%' | NULL | digikam | UPDATE | YES > | > | 'alex'@'%' | NULL | digikam | DELETE | YES > | > | 'alex'@'%' | NULL | digikam | CREATE | YES > | > | 'alex'@'%' | NULL | digikam | DROP | YES > | > | 'alex'@'%' | NULL | digikam | REFERENCES | YES > | > | 'alex'@'%' | NULL | digikam | INDEX | YES > | > | 'alex'@'%' | NULL | digikam | ALTER | YES > | > | 'alex'@'%' | NULL | digikam | CREATE TEMPORARY TABLES | YES > | > | 'alex'@'%' | NULL | digikam | LOCK TABLES | YES > | > | 'alex'@'%' | NULL | digikam | EXECUTE | YES > | > | 'alex'@'%' | NULL | digikam | CREATE VIEW | YES > | > | 'alex'@'%' | NULL | digikam | SHOW VIEW | YES > | > | 'alex'@'%' | NULL | digikam | CREATE ROUTINE | YES > | > | 'alex'@'%' | NULL | digikam | ALTER ROUTINE | YES > | > | 'alex'@'%' | NULL | digikam | EVENT | YES > | > | 'alex'@'%' | NULL | digikam | TRIGGER | YES > | > +------------+---------------+--------------+-------------------------+--------------+ > 18 rows in set (0.00 sec) > > Note: This is digikam 2.0.0
Same here on gentoo with digika 2.1.0 and mysql 5.1.58
I'm on gentoo too, and I'm using mysql 5.5.14. (In reply to comment #12) > Same here on gentoo with digika 2.1.0 and mysql 5.1.58
Any news on this?
Digikam 2.3.0 is out http://www.digikam.org/drupal/node/632 But it looks like noone took care of this bug since 2.0.0 Will it ever be fixed? BTW, this is tagged as wishlist, but I do not really think that's correct. This is a bug. (In reply to comment #14) > Any news on this?
Francesco work in it, but in a separate branch until code is stabilized... DB interface is tedious code. Please wait... Gilles Caulier
Ok, thanks.. It's nice to see that someone is taking care of this bug. Sorry for bothering, but it looked like it had been forgotten.. If you require someone to test te fix before release, let me know: I will be glad to help! davide (In reply to comment #16) > Francesco work in it, but in a separate branch until code is stabilized... DB > interface is tedious code. Please wait... > > Gilles Caulier
I finally achieved to work around this issue by starting mysql (version 5.1.61) with the --log-bin-trust-function-creators=1 option. As far as I understood, this makes the server trust unprivileged users with the TRIGGER privilege and lets them actually create triggers, without the need of the SUPER privilege. More details here: http://stackoverflow.com/questions/1627383/cant-create-mysql-trigger-with-trigger-privilege-on-5-1-32 http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html Hope it helps someone. davide
Hi Davide, thanks for that, just tried it on MySQL 5.5.20 with DigiKan 2.5.0. So the error of this task seems to be gone cause DigiKam doesn't complain about trigger privileges anymore. But now I get some other errors - may I run into other bugs. So at this point I'm giving up trying to use MySQL as the back-end and stay with SQLite. Cheers Regi
digikam 2.9.0!!! mysql 5.1.62 on Gentoo Linux http://scribblesandsnaps.com/2010/10/15/using-digikam-with-mysql/ [code]Sie besitzen nicht die benötigten Rechte für die Datenbank. Sie besitzen folgende Rechte nicht: CREATE TRIGGER Überprüfen Sie Ihre Rechte für die Datenbank und starten Sie digiKam neu.[/code] 2011-01-06 17:19:13 !!!
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
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
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
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".
(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.
*** Bug 280678 has been marked as a duplicate of this bug. ***
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