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


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;

  • Holy Hit Myself in the Head with a Shovel. Jeez. Let me look at that right now. – carclubadmin Nov 12 '15 at 17:57

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

  • My bad guys, when I posted – carclubadmin Nov 10 '15 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 '15 at 19:17
  • Is Civi in a separate database from Drupal? – pmoz Nov 10 '15 at 22:24

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.

  • 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 '15 at 15:46

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.


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

  • 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 '15 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 '15 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 '15 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 '15 at 22:04
  • 1
    you don't need CivICRM Entities just to make Views work with civi data. – petednz - fuzion Nov 12 '15 at 1:26

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.

  • 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 '15 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 '15 at 13:48
  • No, I'm not. I wonder if that is what is causing your issues, then. – Jenni Simonis Nov 15 '15 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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