CiviCRM Community Forums (archive)

Would appear you need to run

GRANT SUPER
also. Can you try that on your umicc_civicrm user and let us know? The docs are a community project and they could be wrong. If so, we can fix them.

See:

http://dba.stackexchange.com/questions/63404/how-to-grant-super-privilege-to-the-user

and

http://dev.mysql.com/doc/mysql-enterprise-backup/3.5/en/mysqlbackup.privileges.html

and this thread:

https://forum.civicrm.org/index.php?topic=24213.0

which you may recall.

Try GRANT SUPER globally, not just for that one database.

I hit this the other day and yes you do need to GRANT SUPER globally if granting SUPER, it needs to be granted globally not to a specific DB. It is a global privilege not a DB level privilege so it can't be granted to a specific DB.

(EDIT: Corrected above which could be read as "you need SUPER" instead of than "SUPER, if required, is a global permission".)

That (and the name! and logic at DB layer generally) makes me uncomfortable even though I think in recent MySQL SUPER doesn't confer any actual data access - only the ability to work with procedures and processes.

I restored a few sites a few days back and noticed that in the DB backups, many of CiviCRM's stored procedures get dumped in the mysql DB, not the actual CiviCRM DB. Didn't bother looking deeper into that yet.

(OK, I looked ... civicrm_strip_non_numeric as a MySQL stored procedure? It seems like we handle missing functions (CRM-13822) already by periodically restoring stored functions, although there's a suggestion CiviCRM might stop using functions and find another way to strip non-numerics from phone numbers.)

And the verdict is...

$ ./civicrm-mysql5-restore.sh
Enter password:
Finished restoring members.orgname.org civicrm RC=0
Success! So, my GRANT statements:
mysql> CREATE DATABASE micc_civicrm;
mysql> GRANT SUPER ON *.* TO 'umicc_civicrm'@'localhost';
mysql> GRANT SUPER ON *.* TO 'umicc_civicrm'@'127.0.0.1';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, TRIGGER, CREATE ROUTINE, ALTER ROUTINE ON micc_civicrm.* TO 'umicc_civicrm'@'localhost' IDENTIFIED BY '********';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, TRIGGER, CREATE ROUTINE, ALTER ROUTINE ON micc_civicrm.* TO 'umicc_civicrm'@'127.0.0.1' IDENTIFIED BY '********';
mysql> FLUSH PRIVILEGES;

I did the GRANT SUPER first in order that I might query to see the permission actually get set/created as I started by wiping/removing the user records completely.

SELECT Host,User,Password,Super_priv FROM user;
+-----------+------------------+-------------------------------------------+------------+
| Host      | User             | Password                                  | Super_priv |
+-----------+------------------+-------------------------------------------+------------+
| 127.0.0.1 | umicc_civicrm    |                                           | Y          |
| localhost | umicc_civicrm    |                                           | Y          |
+-----------+------------------+-------------------------------------------+------------+
So indeed it was getting set properly. Then I went on to issue the GRANT specific to the micc_civicrm database.

And, the restore worked properly.

So, feedback please about the correctness of this updated syntax, and then I would suggest committing to the documentation.

I am thankful,

CRM-13822

And from that page I find:

Affects Version/s: 4.3.8, 4.4.2
Fix Version/s: 4.3.9, 4.4.3

This server is running 4.3.4 as the DB upgrade for 4.3.5 included some SQL that MySQL version 5.0.95 could not handle. So for now while still on the old server CiviCRM is stuck at version 4.3.4.

I dare say that if 4.3.8 were affected by the issue, perhaps also 4.3.4 is as well.

So, until I am able to upgrade to the current version of CiviCRM, then SUPER perm for the CiviCRM MySQL account would be the correct work-around, yes?

I am thankful,