Restoring CiviCRM DB to new server gripes about SUPER priv, it already has it

[mdlueck]

Greetings,

I am in the process of migrating a client to a new server. Configuration is Drupal 6 and CiviCRM 4.3.4 as that is as far as I could upgrade it on their old web server which has MySQL 5.0.95.

The new server runs MySQL version 5.5.37.

So I ran the restore with the user ID I created for CiviCRM to use:

Code: [Select]

mysql> CREATE DATABASE micc_civicrm;
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;

Which should be according to the latest CiviCRM documentation, here:
http://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+MySQL+Permission+Requirements

It restored several tables, then stopped / failed siting:

Code: [Select]

ERROR 1227 (42000) at line 452: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Restoring the database as the root user, however, succeeded.

Why is this? I thought the new GRANT statements were suppose to provide ample MySQL permissions?

I am thankful,

[Hershel]

Would appear you need to run

Code: [Select]

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.

[mdlueck]

I cleaned up the previous existing perms from the db and user tables, then tried the following GRANT:

Code: [Select]

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, TRIGGER, CREATE ROUTINE, ALTER ROUTINE, SUPER ON micc_civicrm.* TO 'umicc_civicrm'@'localhost' IDENTIFIED BY '********';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
I found this page on my own:
http://manidba.wordpress.com/2011/09/02/mysql-error-1221-hy000-incorrect-usage-of-db-grant-and-global-privileges/

Looks like either not taking into account capabilities to grant SUPER to a particular user/database combination (That page IS from back in 2011), or perhaps I have a syntax still, or or or...

Further suggestions, please.

I am thankful,

[Hershel]

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.

[mdlueck]

See:
and this thread:

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

which you may recall.

Yes I recall that. And I have maintained TRIGGER as one of the GRANT options.

Yet to restore the CiviCRM 4.3.4 database, it was necessary to restore as root, not as the CiviCRM specific ID provisioned thusly.

So is it expected to have to restore CiviCRM databases as root?

Or is there still a permissions missing from the documentation GRANT statements?

I am puzzled at the error all of a sudden.

I am thankful,

[Hershel]

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

[mdlueck]

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

I hesitate to do so... Is that still required while running MySQL version 5.5.37?

I would expect then there to be two GRANT statements documented... one for the perms for the specific database, and the other to GRANT SUPER globally. Yes, I am endeavoring to be careful to color inside the lines.

I am thankful,

[Chris Burgess]

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.)

[mdlueck]

Excellent and timely feedback, Chris.

I will go ahead and apply the double GRANT and test restore / backup to the new server.

I am thankful,

[mdlueck]

And the verdict is...

Code: [Select]

$ ./civicrm-mysql5-restore.sh
Enter password:
Finished restoring members.orgname.org civicrm RC=0
Success! So, my GRANT statements:

Code: [Select]

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.

Code: [Select]

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,

[Chris Burgess]

If you were running into the need for SUPER on your new (MySQL 5.5) server, then either you might have a bug to report to issues.civicrm.org OR the docs @ https://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+MySQL+Permission+Requirements need updating.

[mdlueck]

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,