DB permissions

[david.christie]

The following text is on the CiviCRM website...

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:

#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]

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]

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

[david.christie]

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]

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

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.

[clarkac]

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?

[Hershel]

This error is not related to logging. It's a separate privilege, which is required for all CiviCRM sites.

[clarkac]

thanks Hershel.... but I'm not clear what you mean.  What privilege does my username need that I haven't got?

[Hershel]

The error says you lack:

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

[clarkac]

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?