CiviCRM multisite (not Drupal multisite) with different MySQL hosts... and Views

[Laryn]

I'm working on integrating two (and then a third) sites that are in separate accounts on the server. So far I've stumbled into a solution that seems to be working for the most part. They both need separate IPs and SSL certificates, so I can't house them in the same account (at least in my VPS/cPanel hosting environment). I've managed to allow remote MySQL access to the CiviCRM database for the IP of the second site and get that functioning. There were some persistent errors due to extensions and directory permissions but those seem to have magically gone away as I clicked through and saved various settings in the new installation. For the most part, things seem to be functioning just fine.

Now I'd like to use Views on the new installation. Unfortunately this separate database host may be causing a problem here. I've added the extra code to settings.php as noted here, but I'm getting this error in my Views:

Code: [Select]

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'mysqluser2'@'localhost' for table 'civicrm_contact'
I see the instructions lead off with this: "These instructions assume CiviCRM and Drupal are on separate databases located on the same physical host."

So my question is, can I set this up for separate databases on separate hosts?

[Eileen]

So my question is, can I set this up for separate databases on separate hosts?

I don't think so. Views constructs queries that include CiviCRM & Drupal tables within the same query. I can't see how you can make it work if you can't run it from a mysql query console

[Laryn]

Thanks, Eileen!

So... if I am already serving the Civi db from a different host (same VPS), how much of a performance hit if I just serve the Drupal db from the same account as well?

L.

[Eileen]

You should be able to try the query from the command line / mysql console & see how it goes/ performs. If you can't make that work I don't think you'll get it to work in views

[Laryn]

There were some persistent errors due to extensions and directory permissions but those seem to have magically gone away as I clicked through and saved various settings in the new installation.

I retract slightly, I am still getting sporadic errors due to extensions that have been added. It remains to be seen how severe they will be in terms of functionality or whether I just live without those extensions...