CiviCRM Community Forums (archive)

Reading through the instructions for the 4.1 upgrade, I see that MySQL will require the SUPER privilege now. Checking my syntax when I created the database, that is not one of the privileges I knew to grant. I issued:

mysql> CREATE DATABASE cir_civicrm;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON cir_civicrm.* TO 'ucir_civicrm'@'localhost' IDENTIFIED BY '************';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON cir_civicrm.* TO 'ucir_civicrm'@'127.0.0.1' IDENTIFIED BY '************';
mysql> FLUSH PRIVILEGES;

How may I add the SUPER privilege to the existing database? Should I just add that SUPER keyword to the old list, or specify it singly since the other grants have already been granted, or...

This page of the documentation seems to suggest it is there in the 5.0.95 which Ubuntu 8.04 shipped with.

http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html#priv_super

Again, I never knew there was such a privilege, so how to retroactively add that privileged after the fact?

I get this back on MySQL 5.0.95:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
Poking around a bit, it appears that the user table which has column Super_priv is not where perms are coming from. The perms actually are coming from the db table which doe not have a column for the super perm. I guess upgrading further waits until after the server upgrade.

Thank you just the same.

Are you sure you granted the permission on *.* and not on one of the DB's? (like cir_civicrm.* ) The super privilege is a global privilege and cannot be granted on a db. If you try you will get the error you reported.
The super privilege is still stored in the user table in MySQL 5.1. (I check with the DESC command).

Oh... well then I would not want to grant all ID's SUPER rights to all db's, which it sounds like the "grant to *.*" would do. Yes, I tried specifically to the one CiviCRM DB.

I have some SELECT-only (read-only) ID's and what not. I would not want those to end up with SELECT+SUPER! 

In this syntax the grant is ON *.* but only TO the specified user (that the CiviCRM App runs as).

Oh... So only the ID which has full access to the CiviCRM database would become SUPER for the entire server (*.*)? I think we could deal with that scenario.

britebyte, did I understand what you meant?

Yes, that is correct. That grant statement only affects the specified account. Again with the caveat: I don't have 5.0 installed to test on at the moment as we are using 5.1. But, if the feature is there is 5.0 it is most likely the same.

I am finally back to move this domain onto a server with higher build of MySQL: 5.1.67

I am attempting to create a user account for the new database, and am getting an error I believe since I included SUPER in the list of credentials:

mysql> CREATE DATABASE cir_civicrm;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, SUPER ON cir_civicrm.* TO 'ucir_civicrm'@'localhost' IDENTIFIED BY 'realpasswordhere';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
I thought upgrading to MySQL 5.1 was suppose to eliminate this trouble granting SUPER to the ID that CiviCRM is going to use to connect to the DB.

I am seeing posts (outside of CiviCRM) that suggest even with MySQL 5.1.* it is necessary to grant the SUPER permission as I did before running on MySQL 5.0.x. 

What is going on?

Thank you for the details. I will change the permissions to use TRIGGER next time I am doing a Civi upgrade.

For now I have the work-around you suggested second.