Does this view error mean CiviCRM has lost some MySQL DB perms?

Question

The last CiviCRM upgrade I was running, I received a couple of the same DB errors. Sanitized, minimally, the error was as follows:

#7 /srv/www/sites/d1.orgname.org/www/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /srv/www/sites/d1.orgname.org/www/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE OR REPLACE VIEW civicrm_domain_view AS SELECT * FROM civicrm_domain")

Does that mean that CiviCRM has lost some of its MySQL DB permissions and I must rebuild the DB (according to these instructions) ?

I went looking for where in the CiviCRM source code that view is mentioned, and found:

$ grep -r civicrm_domain_view *
CRM/Core/DAO.php:      $dao->query('CREATE OR REPLACE VIEW civicrm_domain_view AS SELECT * FROM civicrm_domain');
CRM/Core/DAO.php:          $dao->query('DROP VIEW IF EXISTS civicrm_domain_view');
CRM/Core/DAO.php:          $dao->query('DROP VIEW IF EXISTS civicrm_domain_view');
CRM/Core/DAO.php:      $dao->query('DROP VIEW IF EXISTS civicrm_domain_view');

I tried running this via PHPMyAdmin:

SELECT *
FROM civicrm_domain_view;

And receive an error that the view does not exist. "#1146 - Table 'd1_civicrm.civicrm_domain_view' doesn't exist"

Next I tried executing:

CREATE OR REPLACE VIEW civicrm_domain_view AS SELECT * FROM civicrm_domain

which told me the MySQL ID did not have permissions to create the view.

So I checked, what do you know that was not a permission I granted to the CiviCRM ID. I provisioned the MySQL account according to these instructions. They do not state that the Create_view_priv perm is required, if this is even the correct perm. (I just saw the word "view" and looked there first.)

Suggestions?

I am thankful,

Michael

Answer 1

You're on the right track--when you see a MySQL error and you can't see what's wrong with it, try it out manually. It seemed like a permission problem on the face of it, and the error you got was a lack of permissions. I think the wiki just threw you off with how it is written. It does say,

Also, to enable CiviCase you should set CREATE VIEW permission.

That's just in this aside just before the main list.

Now, if you don't use CiviCase, you still might try granting that permission and trying it out--I wouldn't put it past someone to change Civi without updating the wiki! Many of us would never notice--I typically give that permission to the DB user as part of a standard setup, and I bet many others do too.

Answer 2

Thank you, Andrew.

To add this permission to the DB in question, I logged in with the master MySQL account and issued:

mysql> GRANT CREATE VIEW ON d1_civicrm.* TO 'ud1_civicrm'@'localhost';
mysql> GRANT CREATE VIEW ON d1_civicrm.* TO 'ud1_civicrm'@'127.0.0.1';
mysql> FLUSH PRIVILEGES;

Then went back to PHPMyAdmin, logged in as ud1_civicrm, and successfully was able to execute:

CREATE OR REPLACE VIEW civicrm_domain_view AS SELECT * FROM civicrm_domain

and

SELECT *
FROM civicrm_domain_view;

At least in the latest (4.6.3) CiviCRM powered with Drupal 6 there is no separate "enable/disable" of CiviCase. It must be always enabled, thus this must be always a permission required. No?

I will similarly update the other CiviCRM instances I administer before applying the 4.6.3 update. I am relieved I did NOT have to rebuild the CiviCRM database this time to correct the error!

I am thankful,

Michael