Schema does not work with shared databases (that use prefixes)

I have some time this evening so I'll take a look.

Here's what I did in an attempt to recreate the issue:

Installed a fresh Drupal 6 (I named the database 'd6'), installed schema 6.x-1.7, edited my settings.php and set my $db_prefix as follows:

$db_prefix = array(
  'main' => 'd6.',
  'users' => 'someotherdb.',
);

The 'someotherdb' was from a previous Drupal 6 installation, to confirm my $db_prefix was working I went to admin/user/user and sure enough, the users from the 'someotherdb' database showed up.

Even after clearing the cache all tables appeared in the 'Match' fieldset, no tables appeared in 'Missing'.

Can you spot anything I am missing in my process that would prevent the error showing up? If not, can you confirm you get the error when you perform the same process as I have done here?

same issue for me, 3 databases and the module indicates that 82 out of 102 tables are missing (so 20 tables needs to be deleted), i'll try to set the $db_prefix.

Hello, It's me again, Sorry but I don't understand how $db_prefix works, i have 3 db : prefix_code1, prefix_code2, prefix_code3, do I put:

<?php
$db_prefix = array(
  'main' => 'prefix_',
  'users' => '????',
);
?>

and in place of "prefix_code1" I write "code1" in the settings.php?
I installed the module on another of my sites with just 1 db and it works like a charm, thx.
May I suggest to add the possibility to delete tables and fix errors directly from the module configuration page without opening phpmyadmin.

Hi sempotcool, Sylvain Lecoy,

Here's the help comment from settings.php regarding $db_prefix:

 * You can optionally set prefixes for some or all database table names
 * by using the $db_prefix setting. If a prefix is specified, the table
 * name will be prepended with its value. Be sure to use valid database
 * characters only, usually alphanumeric and underscore. If no prefixes
 * are desired, leave it as an empty string ''.
 *
 * To have all database names prefixed, set $db_prefix as a string:
 *
 *   $db_prefix = 'main_';
 *
 * To provide prefixes for specific tables, set $db_prefix as an array.
 * The array's keys are the table names and the values are the prefixes.
 * The 'default' element holds the prefix for any tables not specified
 * elsewhere in the array. Example:
 *
 *   $db_prefix = array(
 *     'default'   => 'main_',
 *     'users'     => 'shared_',
 *     'sessions'  => 'shared_',
 *     'role'      => 'shared_',
 *     'authmap'   => 'shared_',
 *   );
 *

My understanding then of $db_prefix is that it serves several purposes:

a) It enables you to run several Drupal sites using one database - this is achieved by setting $db_prefix = 'a_' in your settings.php file for site a and $db_prefix = 'b_' in your settings.php file for site b etc.
b) It enables you set prefixes for specific tables which in turn enables you to share certain tables between Drupal sites - you could set site b to share the users table by setting $db_prefix = array('default' => 'b_', 'users' => 'a_'); in your settings.php on site b.
c) Other usages?

Sylvain Lecoy's usage of $db_prefix above was new to me, I am not sure whether $db_prefix was designed to be used that way, the instructions in settings.php I quoted above state:

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

However, using a . (dot) appears to work, it would still prefix the table (if you included something after the .) and additionally it specifies the database name to use - so the $db_prefix configuration from the initial report:

<?php
$db_prefix = array(
  'default'     => 'web1.',
  'users'       => 'web_shared.',
  'sessions'    => 'web_shared.',
  'role'        => 'web_shared.',
  'authmap'     => 'web_shared.',
);
?>

...would use un-prefixed tables (there's nothing following the . there) from a database called web1 except for tables users, sessions, role and authmap for which it would use un-prefixed tables in a database called web_shared. I guess this would only work if the user/pass was the same for both databases or would you have to set $db_url = array('web1' => 'mysql://username:password@localhost/web1', 'web_shared' => 'mysql://username:password@localhost/web_shared'); ??? I don't know off hand...

From a quick look at the code in Drupal 7 it appears that using a . in $db_prefix is fully supported.

Well I don't know whether that sheds any light on anything?

I have the same problem. I have in settings.php:

$db_url = array(
    'default' => 'mysqli://xy@localhost/shop',
    'shared'  => 'mysqli://xy@localhost/shared',
);

$db_prefix = array(
  'default'        => 'shop.',
  'authmap'        => 'shared.',
  'role'           => 'shared.',
  'sessions'       => 'shared.',
  'users'          => 'shared.',
);

Drupal works fine but Schema compare shows 4 missing tables (which are in second shared DB used also for another sites):

Tables in the schema that are not present in the database.
system
    * sessions
user
    * authmap
    * role
    * users

How may I correct this in Schema module?

I would suggest using an underscore rather than a . (dot) to prefix tables.

I have the same problem but to more detail about the issue ..

Database is not default data in the prefix .. but its an external database in same server alongside Drupal Default .. so adding prefix with dot (.) make the query to external database .. where underscore will not work ..

in this case when you compare schema .. it say table is missing ..

$db_url = array(
'default' => 'mysqli://xy@localhost/shop',
'shared' => 'mysqli://xy@localhost/shared', >>>>>>>> database is shared not the default Drupal one ..
);

$db_prefix = array(
'default' => 'shop.',
'authmap' => 'shared.', >>>>>>> this table will be queried as shared.authmap in Druapal instead of default ..
'role' => 'shared.',
'sessions' => 'shared.',
'users' => 'shared.',
);

For sure you need to select the correct database in the setting of schema to see the correct table in compare tab .. but problem is when some other module call for this function compareSchema() .. it show tables as missing which are coming from non default databases ..

I hope it clear your confusions ..

I have the same multisite arhitecture: several databases is used for shared and custom tables.
My settings.php config of "Main" site:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'main',
      'username' => 'mysql',
      'password' => 'mysql',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => array(
        'default' => 'main.',
        'authmap' => 'shared.',
        'cache_libraries' => 'shared.',
        'cache_l10n_update' => 'shared.',
        'cache_update' => 'shared.',
        'ckeditor_input_format' => 'shared.',
        'ckeditor_settings' => 'shared.',
        'date_formats' => 'shared.',
        'date_format_locale' => 'shared.',
        'date_format_type' => 'shared.',
        'elfinder_file_extinfo' => 'shared.',
        'elfinder_profile' => 'shared.',
        'field_config' => 'shared.',
        'field_config_instance' => 'shared.',
        'filter' => 'shared.',
        'filter_format' => 'shared.',
        'image_effects' => 'shared.',
        'image_styles' => 'shared.',
        ...etc
      ),
    ),
  ),
);

included patch solve this problem for me.
It check database configuration array of prefixes for databases references (like "shared.", @see default.settings.php) and include them in table select queries.

Corrected patch
Please, ignore previous #14

Marked #1488842: Schema reports 'EXTRA' tables, for tables that are not 'drupal' tables as a duplicate of this issue.

I think this should be the proper way to support the prefixes using information we can collect easily.

Fixed test to actually use drupal_get_schema() so this will actually give us a better picture of how testing works.

Committed #18 to 7.x-1.x: http://drupalcode.org/project/schema.git/commit/64c6631