I've been using drupal in pgsql schemas for a year now with no problems. In fact, all of my Drupal sites live in separate schemas of one database, so that they can share data if need be. In my existing settings.php file, I have the following settings:

$db_url = 'pgsql://username:password@localhost/database';
$db_prefix = 'drupal.'; // note the "."

The "." on the end of "drupal" in the db prefix distinguishes it as a schema instead of a regular db prefix. Getting to the point, the installation process for Drupal 5.x doesn't let me to put a "." in my db prefix, so I have to install drupal manually. I wasn't sure if this should be a feature request or a bug, but given the fact that it's a valid prefix for pgsql, I opted for bug. If it isn't a valid prefix for mysql, then maybe it should be rejected if mysql is selected as the database.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

chx’s picture

Component: postgresql database » database system
Status: Active » Needs review
FileSize
985 bytes

Hm, I have some memories doing some tricks with giving a database specifier as prefix for MySQL. Thanks for the bug report.

The patch might use some wording love. But the preg is sound.

Steven’s picture

Status: Needs review » Fixed

Let's keep things simple and allow a dot anywhere. Using a dot at the end, you can share tables across databases with mysql.

Committed to HEAD. Thanks.

Anonymous’s picture

Status: Fixed » Closed (fixed)
hswong3i’s picture

Title: Install doesn't allow "." in db prefix for psql schema » Installer shouldn't allow "." in $db_prefix
Version: 5.x-dev » 7.x-dev
Assigned: Unassigned » hswong3i
Status: Closed (fixed) » Needs review
FileSize
1.31 KB

According to the discussion of http://drupal.org/node/371 since #72, maybe we should rollback this change since it is a bit tricky and buggy:

  • The primary goal of prefixTables() is for table prefix (e.g. table_prefix_) replacement only, but not for database_name.table_prefix_ syntax (since tables are not located in same database):

    Queries sent to Drupal should wrap all table names in curly brackets. This function searches for this syntax and adds Drupal's table prefix to all tables, allowing Drupal to coexist with other systems in the same database if necessary.

  • The comment from default.settings.php never mention dot as valid syntax clearly, but only use alphanumeric and underscore as example valid database characters:

    Be sure to use valid database characters only, usually alphanumeric and underscore.

  • Most online reference from drupal.org or even somewhere else never mention dot as valid syntax:

    Here are a host of tutorials that explain how to setup multisites with shared tables:
    http://drupal.org/node/43816
    http://groups.drupal.org/multisite
    http://www.practicalweb.co.uk/blog/08/08/07/drupal-multisite-shared-tables

  • By using database_name.table_prefix_ syntax we have a lot of limitation, e.g. both database must come with identical connection parameters, which result as similar effect of locate all different tables under same database instance:

    This is because we only define ONE SET of connection parameter correctly. In this case the use of split DB is just equal effect with all tables located within single database instance. As MySQL come with nearly none of maximum number of tables limitation within single database instance, I can't find the usefulness of split DB handling.

  • Even if we define different set of connection parameters in settings.php, as no db_set_active() is ever called there is no actual effect:

    The $db_url will only load during connection establish, where db_set_active() will take action for. As we do a tricky cross database switching with $db_prefix => database_name.table_prefix, Drupal is being cheated as all tables belongs to same database. It is function just because we hit the backdoor of both Drupal and MySQL, but not means this syntax is expected as valid :S

  • When we are considering for escape all database constraint name as reserved word safe (e.g. http://drupal.org/node/371), [{tablename}] will not able to convert as `prefix_tablename` simply, since it may result as `dbname.prefix_tablename` (if dot exists in $db_prefix) which is invalid SQL syntax. Even we can revamp prefixTables() for a proper handling, I guess this may not be our expected approach, e.g.:
    -          $sql = strtr($sql, array('{' . $key . '}' => $val . $key));
    +          $sql = strtr($sql, array('{' . $key . '}' => strtr($val, '.', '].[') . $key));
    

This patch rollback the checking from _install_settings_form_validate(), which close the backdoor of both Drupal and MySQL/PostgreSQL as expected handling. For existing incorrect Drupal site installation, we can propose some upgrade procedure for correction.

Anonymous’s picture

Assigned: hswong3i » Unassigned
Status: Needs review » Closed (fixed)

File a new report. Reopening a two year old standing closed issue isn't what you should do.

hswong3i’s picture

Status: Closed (fixed) » Closed (duplicate)

Mark duplicated with http://drupal.org/node/302327

Anonymous’s picture

Status: Closed (duplicate) » Closed (fixed)
Damien Tournoud’s picture

Title: Installer shouldn't allow "." in $db_prefix » Install doesn't allow "." in db prefix for psql schema
Version: 7.x-dev » 5.x-dev

Revert vandalism.