Author Topic: Views integration: SELECT command denied/table not found (Read 2288 times)

pbeakley · May 27, 2010, 02:14:49 pm

(Reposted here since I originally posted in the wrong forum. Sorry!)

Okay, I've gone through the Views2 integration documentation with a fine-tooth comb. I've added the list of prefixes to the end of my settings.php file. I've gone through every step of the Redspire recipe, including editing the eight different ['table']['base'] sets in civicrm/drupal/modules/views/civicrm.views.inc.

I can see all the CiviCRM data options in my Views UI, but when I build a query (usually via a Relationship), I get the SELECT command denied for user error.

It appears the query is not switching over to the settings under $db_url['civicrm'] = in my settings.php file. It's trying to access the correct database (aaee2010civicrm) but is using the default username (aaee2010drupal).

I've got my client set up on a GoDaddy shared hosting plan because it's cheap, but GoDaddy's shared hosting does not allow you to create multiple databases with the same username and password, nor can you create multiple credential sets for a single database. We'd have to upgrade to a virtual host or better, and that's not in their budget.

Now the only step in the Views2 integration document I totally did not understand was the reference to http://drupal.org/node/18429. Under the sentence "To query a different database, simply set it as active by referencing the key name.", and the code following, I have no idea where to put that. Is that something I have to hack Views itself to do? I am really hesitant to hack the core code.

I'm at a loss here, and feel stupid posting this because Views2 integration has been beaten to death so thoroughly on this forum. I'll take any lead anyone can give me. Thank you!

EDIT: I experimented with a couple ways to build an integrated query. First way was to create a "user" type query and attempt to build in a relationship to the user's CiviCRM contact information. That one gives me the "SELECT command denied" error. Then I tried building a query starting as a CiviCRM Contact type of View, and when I added the Drupal User ID relationship I got a "table not found" error. Think that's relevant?

Rahul Bile · May 27, 2010, 09:48:16 pm

pbeakley ,

It seems that your mysql user for drupal database and that of civicrm are different and drupal database user do not have the select permissons on civicrm database.

//Drupal database details
define( 'CIVICRM_UF_DSN' , 'mysql://user1:pass1@localhost/testsite_drupal?new_link=true' );

//civicrm database details
define( 'CIVICRM_DSN' , 'mysql://user2:pass2@localhost/testsite_civicrm?new_link=true' );

user1 do not have permission to select data from testsite_civicrm database
Giving the select permisson to the drupal databse user will solve the problem.

hope this helps.

Rahul.

Hershel · May 28, 2010, 02:10:09 am

You could put the CiviCRM tables into the Drupal database. This is not the ideal situation, but it certainly works...

CiviCRM Community Forums (archive)

News:

Author Topic: Views integration: SELECT command denied/table not found (Read 2288 times)

pbeakley

Views integration: SELECT command denied/table not found

Rahul Bile

Re: Views integration: SELECT command denied/table not found

Hershel

Re: Views integration: SELECT command denied/table not found