3

Drupal 7.41 CiviCRM: 4.6.9

Hi, new to CiviCRM and Drupal. Maybe I'm overlooking something but no matter what I try I cannot create a View from Drupal of any Civi data structure. For example, to show whether a logged in user is registered for a CiviEvent, I want to create a view of Civi_Participant but get the error:

Base table or view not found: 1146 Table 'drupal.civicrm_participant' doesn't exist

Check setting.php file (a snippet of which is below). Looks fine. Prefix is set to nothing but that shouldn't be a problem since default is properly set:

$databases = array ( 'default' => array ( 'default' => array ( 'database' => 'drupal', 'username' => 'dpal', 'password' => '*********', 'host' => 'localhost', 'port' => '', 'driver' => 'mysql', 'prefix' => '', ), ), );

/** CiviCRM Views Integration $databases['default']['default']['prefix']= array( 'default' => '', 'civicrm_acl' => 'civicrm.', 'civicrm_acl_cache' => 'civicrm.', 'civicrm_acl_contact_cache' => 'civicrm.', 'civicrm_acl_entity_role' => 'civicrm.',

Forgot to grant the drupal user SELECT access to the civicrm database? Don't think so. From mysql command line:

mysql> SHOW GRANTS for dpal@localhost; Grants for dpal@localhost | | GRANT ALL PRIVILEGES ON . TO 'dpal'@'localhost' IDENTIFIED BY PASSWORD | GRANT SELECT ON civicrm.* TO 'dpal'@'localhost'

I tried another example where I tried to select civicrm_contact and selected the 'Show SQL' option for my view. I get:

SELECT civicrm_contact.id AS id FROM {civicrm_contact} civicrm_contact LIMIT 10 OFFSET 0

I'm no SQL pro by any means but how does that query address the database civicrm? Where's the reference to the database? This is why I get the error:

Table 'drupal.civicrm_contact' doesn't exist

Views is defaulting to the Drupal database, not the civicrm database even when selecting Show CiviCRM Contact, or CiviCRM Participants or CiviCRM PrettyMuchAnythingElse. How do I fix that?

I tried a query from mysql directly and this works:

SELECT civicrm_contact.id AS id FROM civicrm.civicrm_contact;

How do I get drupal to do that? What are those kooky curly brackets. {civicrm_contact} .... Is that wrong?

Just in case you are wondering, yes, I am being driven crazy by this problem

Improve this question

6 Answers 6

Reset to default
0

I noticed something else.

Your code contains:

/** CiviCRM Views Integration

Where /* is the START of a php comment, but I see no end of the comment. If it is like that in your settings.php then everything after this is a comment to php, until the next */ And that would include your whole $databases['default']['default']['prefix'] array;

Improve this answer
1
  • Holy Hit Myself in the Head with a Shovel. Jeez. Let me look at that right now.
    – carclubadmin
    Nov 12, 2015 at 17:57
4

This is because you don't have civicrm_participant as a key in the array defined under CiviCRM Views Integration section. To make sure copy all the tables listed in UF Integration page to settings.php file.

  1. Navigate to Administer -> System Settings -> CMS Database Integration
  2. Copy and paste the $databases['default']['default']['prefix'] array in settings.php file.
  3. Save and clear the cache of your site.

More Information at : http://wiki.civicrm.org/confluence/display/CRMDOC/Views3+Integration

Improve this answer
3
  • My bad guys, when I posted
    – carclubadmin
    Nov 10, 2015 at 19:10
  • My bad guys, when I posted the settings.php above, I simply posted a snippet. Not the entire file. Yes, my settings.php file does include civic_participant. Keep in mind that no matter what I choose I get the same error regarding a missing table. It's as though View +Civi Integration is completely broken regardless of what is in the settings.php file.
    – carclubadmin
    Nov 10, 2015 at 19:17
  • Is Civi in a separate database from Drupal?
    – pmoz
    Nov 10, 2015 at 22:24
1

In views you can have the SQL be shown.

Are you able to run that sql when you login into the drupal database with the drupal login credentials?

Likely you can not but that would often give more information about what is wrong.

Improve this answer
1
  • That's a great tip and it may have helped get me to where I need to go. I've given the Show SQL a try and something very interesting has shown up. Please see my question at the top. The comment format doesn't allow me to use the <code> tag so I had to add information to the original question. Thanks.
    – carclubadmin
    Nov 11, 2015 at 15:46
1

Yes, that was the problem. I forgot the ending */

I just donated $25 (about $9000 Canadian) to CiviCRM. My 'dumb' tax. It's like a swear jar. But if I had to contribute to that over the last few days CiviCRM probably would have met their $200,000 goal.

Why do computers do what they're told to do and not what we intended them to do?

Thanks all. All tips provided great help in getting to know more about CiviCRM.

Improve this answer
0

Drupal caches can be the issue here. Trying clearing them then refreshing the View.

Improve this answer
5
  • Gave cache clearing a try but no luck unfortunately. Cleared Civi caches as well. Same result. Is there a way to debug the problem? I checked my mysql.log and mysql.error.log file and found nothing. Could I be missing a module or have left something disabled?
    – carclubadmin
    Nov 11, 2015 at 5:12
  • During an update of Views I got multiple errors of the form "Notice: Undefined index: admin/structure/types/manage/civicrm_activity/fields in civicrm_entity_menu_alter() (line 23 of /home/mysite/www/www/sites/all/modules/civicrm_entity/civicrm_entity.module)." I fixed this problem using the following link (drupal.org/node/2564757#comment-10498976) and patching /var/www/drupal/sites/all/modules/civicrm_entity/civicrm_entity.module. Still no luck though. Darn.
    – carclubadmin
    Nov 11, 2015 at 5:53
  • sorry no other ideas. ah one other thing. i have noticed that there are often double ups of 'fields' in the list of fields in Views and for example there will be both "CiviCRM Contacts: Gender Error: missing help" AND "CiviCRM Contacts: Gender: Contact's Gender" and that choosing a field with the 'error missing help' is likely to generate an sql issue
    – petednz - fuzion ♦
    Nov 11, 2015 at 9:31
  • I am using CiviCRM Entities 7.x-2.0-beta1, the job of which is to "Declare CiviCRM entities as Drupal entities". I wonder if that is the problem
    – carclubadmin
    Nov 11, 2015 at 22:04
  • 1
    you don't need CivICRM Entities just to make Views work with civi data.
    – petednz - fuzion ♦
    Nov 12, 2015 at 1:26
0

Yours looks slightly different than mine and I wonder if that could in any way be causing the issue. I am assuming your database just has a name and isn't formatted like this: username_databasename which is how my server requires my databases to be called.

$databases['default']['default']['prefix']= array( 'default' => '', 'civicrm_acl' => 'databasename.',

Notice that mine doesn't have a space and it has extra punctuation before the name of the database begins.

Improve this answer
3
  • Good eye spotting that. Odd it shows a space. I just checked the settings.php file and discovered it actually 'civicrm_acl' => 'backtickcivicrmbacktick.' The civicrm is surrounded by backticks but when It try to post them it comes out as a space
    – carclubadmin
    Nov 12, 2015 at 5:50
  • I removed the back ticks/quotes and . . it still doesn't work. Arrrrgggg. Even with fingers crossed on both hands it still seems to be a problem. Question: Are you using CiviCRM Entities?
    – carclubadmin
    Nov 12, 2015 at 13:48
  • No, I'm not. I wonder if that is what is causing your issues, then.
    – Jenni Simonis
    Nov 15, 2015 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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