CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Installing CiviCRM »
  • WordPress Installations (Moderators: Kurund Jalmi, Coleman Watts) »
  • DB permissions
Pages: [1]

Author Topic: DB permissions  (Read 1181 times)

david.christie

  • I’m new here
  • *
  • Posts: 3
  • Karma: 0
  • CiviCRM version: 4.5.4 WordPress
  • CMS version: Version 4.1
  • MySQL version: MySQL 5.1.73
  • PHP version: PHP version 5.5.14
DB permissions
December 30, 2014, 01:40:52 am
The following text is on the CiviCRM website...

Quote
If your version of MySQL is greater than 5.1.6 you will need the following for all typical uses of CiviCRM 4.3+ including installing extensions:

NOTE: The extra TRIGGER permission.
-- Assuming a database called "civicrm" and a user called "civicrm_user"
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, TRIGGER, CREATE ROUTINE, ALTER ROUTINE ON civicrm.* TO 'civicrm_user'@'localhost' IDENTIFIED BY 'realpasswordhere';

My provider set me all these permissions explicitly, and still I cannot install CiviCRM. This has become very annoying!

So I tried the "test for trigger rights" steps carried out by the install script one by one, getting the following error message:

Quote
#1419 - 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)

So, it seems that the SUPER privilege is also needed? Is this really true? This strikes me as being overkill, but in any case, your documentation is insufficient.

What is particularly annoying is that the installation is so tedious, involving circuits via the help desk of my Internet provider, for an application which is supposed to be as easy as Wordpress to install. I've been at it for a month now.

Can anyone help with any advice?

haystack

  • I post occasionally
  • **
  • Posts: 61
  • Karma: 2
  • CiviCRM version: 4.n
  • CMS version: WordPress 4.n
  • MySQL version: 5.n
  • PHP version: 5.n
Re: DB permissions
December 30, 2014, 03:15:35 am
David: as per the docs, yes you will need the SUPER privilege in MySQL 5.1 if binary logging is enabled. I agree with you that Civi's documentation often falls short, but I can't find anywhere in it where it suggests it's as easy to install as WordPress. That would most certainly be misleading!

Something to bear in mind once you do get Civi installed: when you're backing up or exporting your Civi DB, avoid using a GUI such as PhpMyAdmin and export using the command line so that you get all the stuff that's tucked away in the database:

Code: [Select]
mysqldump -u dbusername -p --opt --triggers --routines --events dbname > filename.sql
Good luck!

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: DB permissions
December 30, 2014, 07:38:10 am

you might want to consider using a service like CiviHosting which will install WP + CiviCRM. Will probably save you a lot of time going forward
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

david.christie

  • I’m new here
  • *
  • Posts: 3
  • Karma: 0
  • CiviCRM version: 4.5.4 WordPress
  • CMS version: Version 4.1
  • MySQL version: MySQL 5.1.73
  • PHP version: PHP version 5.5.14
Re: DB permissions
December 30, 2014, 07:40:36 am
Thanks haystack. Nevertheless, it would be just great to list all required permissions on that page I quoted. This would save simple users like myself from having to go through loops like this... ;-)

Thanks Donald, but actually no as I have all my hosting together in one place to set up users etc. But, a good point.

haystack

  • I post occasionally
  • **
  • Posts: 61
  • Karma: 2
  • CiviCRM version: 4.n
  • CMS version: WordPress 4.n
  • MySQL version: 5.n
  • PHP version: 5.n
Re: DB permissions
December 30, 2014, 07:45:12 am
Which page are you referring to, David? The installation guide does say "SUPER privileges are required in MySQL 5.1 if binary logging is enabled" here:

http://wiki.civicrm.org/confluence/display/CRMDOC/WordPress+Installation+Guide+for+CiviCRM+4.5

david.christie

  • I’m new here
  • *
  • Posts: 3
  • Karma: 0
  • CiviCRM version: 4.5.4 WordPress
  • CMS version: Version 4.1
  • MySQL version: MySQL 5.1.73
  • PHP version: PHP version 5.5.14
Re: DB permissions
January 01, 2015, 03:59:38 am
I was referring to this page: http://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+MySQL+Permission+Requirements

It does indeed say (lower down) that SUPER is needed in those particular logging circumstances. The trouble is that I (like many others I suspect) am using MySQL hosted by my ISP and do not have much to say about things like logging...

Thanks anyhow  ;)

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: DB permissions
January 01, 2015, 05:14:11 am
Quote from: david.christie on January 01, 2015, 03:59:38 am
It does indeed say (lower down) that SUPER is needed in those particular logging circumstances. The trouble is that I (like many others I suspect) am using MySQL hosted by my ISP and do not have much to say about things like logging...

Yes, most folks don't have any say regarding such technical details, and in such a case you would have to ask your hosting provider for assistance.

If there is still an issue with the Wiki, feel free to edit it to clarify--it is a community venture. :)
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

clarkac

  • Administrator
  • Ask me questions
  • *****
  • Posts: 399
  • Karma: 11
  • CiviCRM version: 4.4.11 & 4.5.5
  • CMS version: Drupal 7
  • MySQL version: 5.1.61-cll
  • PHP version: 5.3.27
Re: DB permissions
January 06, 2015, 04:52:40 am
My hosting provider has given the exact permissions to my backup username as documented in the Wiki but when I run the backup as described by david.christie (which is: mysqldump -u dbusername -p --opt --triggers --routines --events dbname > filename.sql)  I get the following message from cron:

mysqldump: dcmadmin_backup has insufficent privileges to SHOW CREATE FUNCTION `civicrm_strip_non_numeric`!

I don't have binary logging set, so can someone explain this error?
Andy Clark

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: DB permissions
January 06, 2015, 05:47:51 am
This error is not related to logging. It's a separate privilege, which is required for all CiviCRM sites.
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

clarkac

  • Administrator
  • Ask me questions
  • *****
  • Posts: 399
  • Karma: 11
  • CiviCRM version: 4.4.11 & 4.5.5
  • CMS version: Drupal 7
  • MySQL version: 5.1.61-cll
  • PHP version: 5.3.27
Re: DB permissions
January 06, 2015, 07:27:27 am
thanks Hershel.... but I'm not clear what you mean.  What privilege does my username need that I haven't got?
Andy Clark

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: DB permissions
January 06, 2015, 08:42:12 am
The error says you lack:

Quote
privileges to SHOW CREATE FUNCTION

I believe you need this:

http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html#priv_create-routine
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

clarkac

  • Administrator
  • Ask me questions
  • *****
  • Posts: 399
  • Karma: 11
  • CiviCRM version: 4.4.11 & 4.5.5
  • CMS version: Drupal 7
  • MySQL version: 5.1.61-cll
  • PHP version: 5.3.27
Re: DB permissions
January 06, 2015, 08:55:05 am
That link doesn't mention 'SHOW CREATE FUNCTION' - my hosting guy gave the user CREATE ROUTINE, ALTER ROUTINE which is according to the wiki and which Mysql link says is for procedures and functions.

Sorry, I'm out of my depth here but I do appreciate your help!!  Comments?
Andy Clark
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Installing CiviCRM »
  • WordPress Installations (Moderators: Kurund Jalmi, Coleman Watts) »
  • DB permissions

This forum was archived on 2017-11-26.