user sequence gotcha

Wasted a lot of time due to this doozy in the drupal code (user.module): <?php ... $array['uid'] = db_next_id('{users}_uid'); ... ?>

So let's say you want to share a users DB between two drupal sites, A and B.

For site A, you configure your settings.php normally. This will be your master DB.

In site B, you point user related tables to site A's database: <?php ... $db_prefix = array( 'default' => '', 'users' => 'A.', 'role' => 'A.', 'users_roles' => 'A.', 'sessions' => 'A.', 'sequences' => 'A.' ); ... ?>

When you create a new user on site B, what this will actually do is create a new sequence titled 'A.users_uid' in your site B sequences table, not user 'users_uid' from site A's sequences table as you'd logically expect.

There are no references as to why this is done anywhere in the code. File this one under 'extremely annoying'.

Some Time Later, Back at the Cave

I've discovered why this is done. The primary purpose of being able to prefix table names is that so users in a single-database environment (those whose ISPs only allow a single DB) can create multiple application tables in a single. Users may create one "virtual" Drupal database with a prefix "siteA_" and another with "siteB_". In order for sequences to exist for each of these virtual DBs, the sequence names must include the prefix, else all sets of tables would share the same sequences and everything would be pretty well messed up.