By smartin-1 on

Hello,

I have a multisite setup with drupal 5.9 iis postgresql.

i am sharing the users table, and have prefixed it with shared_.

As i install modules i am getting pg_query() errors because the modules select statements don't include the users table prefix of shared_

warning: pg_query() [function.pg-query]: Query failed: ERROR: relation "users" does not exist in...

I think modules are attempting to query the users table when its actual name is shared_users

Also, tables inserted by modules are being prefixed, however query's are failing because they don't include the prefix.

I don't want to be hacking modules and renaming tables....I must be missing something in the configuration

Any suggestions please?

Comments

styro’s picture

styro commented

Basically if queries in a drupal module are to work on prefixed tables they need to be written a certain way. Its the way the modules are written, not a misconfiguration on your part.

eg: the table names need to be surrounded by curly brackets (braces) eg "SELECT * FROM {users} ..." rather than "SELECT * FROM users ...". Drupal uses the braces to automatically prefix the table names before sending the query to the database.

Have a look in your addon modules - if they don't use braces around table names, that is a bug in the module and you should file an issue.

--
Anton

smartin-1’s picture

smartin-1 commented

Thanks Anton! you have been a great help, you were spot on with that solution.

Additional details (maybe stating the obvious) for anyone else who encounters this problem:
use {} around table names (not field names) eg users.uid becomes {users}.uid

Problem number 2!

This may be related...When i placed the modules and themes inside the sites/subdomain.domain.com/modules and ''/''/themes, i found that some of the paths to images and stylesheets as well as the includes were pointing to the root directory not the sites directory. I have reluctantly placed the modules and themes back in the root modules and themes directory as a temp fix.

All suggestions welcome.

matkeane’s picture

matkeane commented

Hi,

If Drupal is not recognizing that a module has been moved, it is because it stores the file path in the system table - you can either update this by hand or, probably easier & safer, deactivate the modules, move them to the sites/mysite/modules/ folder and then reactivate them.

styro’s picture

styro commented

Additional details (maybe stating the obvious) for anyone else who encounters this problem:
use {} around table names (not field names) eg users.uid becomes {users}.uid

for a slightly cleaner approach, I recommend using aliases for tables - eg:

SELECT u.uid FROM {users} u WHERE ...

As far as I'm aware, that technique is optional.

But here are are the not so optional SQL coding standards that Drupal modules should obey:
http://drupal.org/node/2497

--
Anton