3

I recently update civicrm till 4.6.2 till 4.6.4 on drupal 7.38, mariadb 5.5.41. When saving a contact with a non numeric phone number (+32 123456798) I got the error:

Database foutcode: execute command denied to user 'civicrm-db-user'@'localhost' for routine 'civicrm-db.civicrm_strip_non_numeric', 1370

Array
(
[callback] => Array
    (
        [0] => CRM_Core_Error
        [1] => handle
    )

[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] => INSERT INTO civicrm_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id ) VALUES ( 26 ,  1 ,  1 ,  0 , '+32' ,  NULL ,  1 )  [nativecode=1370 ** execute command denied to user 'civicrm-db-user'@'localhost' for routine 'civicrm-db.civicrm_strip_non_numeric']
[type] => DB_Error
[user_info] => INSERT INTO civicrm_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id ) VALUES ( 26 ,  1 ,  1 ,  0 , '+32' ,  NULL ,  1 )  [nativecode=1370 ** execute command denied to user 'civicrm-db-user'@'localhost' for routine 'civicrm-db.civicrm_strip_non_numeric']
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id ) VALUES ( 26 ,  1 ,  1 ,  0 , '+32' ,  NULL ,  1 )  [nativecode=1370 ** execute command denied to user 'civicrm-db-user'@'localhost' for routine 'civicrm-db.civicrm_strip_non_numeric']"]

)

I found the solution in adding the "execute" privilege to my civicrm-db user in mariadb using phpmyadmin.

I have a second civicrm db (non related to my first) still on the old version 4.6.2 which has no issues at all. I has no "execute" privilege and does not need it.

Civicrm doc (http://forum.civicrm.org/index.php?topic=23489.0) does not mention execute privilege.

list of grants to my civicrm user: Grants for civicrm-db-user@localhost

GRANT USAGE ON *.* TO 'civicrm-db-user'@'localhost' IDENTIFIED BY PASSWORD 'pwdhere'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `civicrm`.* TO 'civicrm-db-user'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON `civi`.* TO 'civicrm-db-user'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `civicrm-db`.* TO 'civicrm-db-user'@'localhost'

trigger rebuild did not fix the issue. (/civicrm/menu/rebuild?reset=1&triggerRebuild=1)

automatic_sp_priveleges variable = on in mariadb

Question: did I miss something in the update, does the latest version need different rights? I think I found a workaround, but not the actual error. Has someone the same issue?

Kind regards

Johan.

2

I checked a site that is currently being test-upgraded from 4.4.14 to 4.6.4, so we have both the 4.4.14 and 4.6.4 versions.

In 4.4.14: GRANT EXECUTE, ALTER ROUTINE ON FUNCTION mycividb.civicrm_strip_non_numeric TO 'myuser'@'localhost'

In 4.6.4: GRANT EXECUTE, ALTER ROUTINE ON FUNCTION mycividb.civicrm_strip_non_numeric TO 'myuser'@'localhost'

I.e. the same. This permission normally gets automatically granted for this function, when the function is created, which happens when triggers are set up. This is done automatically by MySQL:

The EXECUTE privilege is required to execute stored routines. However, this privilege is granted automatically to the creator of a routine if necessary

https://dev.mysql.com/doc/refman/5.5/en/stored-routines-privileges.html

From the MariaDB docs:

To run a stored routine, the EXECUTE privilege is needed. This is also temporarily granted to the creator if they attempt to run their routine unless the automatic_sp_privileges variable is set to 0.

https://mariadb.com/kb/en/mariadb/stored-routine-privileges/

Worth checking the automatic_sp_privileges variable.

Does a trigger rebuild (/civicrm/menu/rebuild?reset=1&triggerRebuild=1) fix the problem?

You say you solved the problem by adding the "execute" privilege to your drupal-db user. Did you mean your civicrm-db user? If you post the results of SHOW GRANTS for the relevant users, that might help to diagnose what's happening.

  • Hi - trigger rebuild did not fix the issue. (/civicrm/menu/rebuild?reset=1&triggerRebuild=1) - I added the execute permission to the civicrm-db user. (not drupal, my mistake). I used phpmyadmin to do so. - automatic_sp_priveleges variable = on – Johan Van Dyck Jul 10 '15 at 11:14
  • The variable is "automatic_sp_privileges", not "automatic_sp_priveleges". – davejenx Jul 10 '15 at 11:44
0

As far as I know you also needed the execute privilige in earlier versions for the phone strip function?

0

CiviCRM requires additional DB permissions beyond what you'd normally require for a CMS like WordPress, Joomla or Drupal.

When cloning a site for testing, the same permissions setup will need to be replicated for the cloned site. Just "stock" permissions won't do the trick :(

These permissions are documented in the CiviCRM Wiki: CiviCRM MySQL Permission Requirements


Select your MySQL version below, then execute the appropriate permissions SQL after replacing DBNAME, DBUSERNAME, DBHOSTNAME and DBPASSWORD to suit your access.

MySQL >= 5.1.6

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, TRIGGER, CREATE ROUTINE, ALTER ROUTINE 
   ON DBNAME.* TO 'DBUSERNAME'@'DBHOSTNAME' IDENTIFIED BY 'DBPASSWORD';

MySQL <= 5.1.5

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE, ALTER ROUTINE 
   ON DBNAME.* TO 'DBUSERNAME'@'DBHOSTNAME' IDENTIFIED BY 'DBPASSWORD';
GRANT SUPER ON *.* TO 'DBUSERNAME'@'DBHOSTNAME';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.