Sharing Drupal tables between databases using MySQL5 Views

tags:

Problem:

You have a "master" database that contains data from multiple Drupal sites and you want to share it among them. Normally, you could use table prefixing to allow each of your sites to point to a single table. But what if you do not want content from one site to "bleed" across to the other sites? Let's say you have a network of Drupal sites sharing a user database. You want to share that user's information across your entire network of sites, but only make the information visible from those sites to which the user has subscribed. Or maybe you want to populate baz.com with users who meet some arbitrary criteria. There are lots of possibilities here, but nobody's paying me to write this, so let's get on with it.

Solution:

Use MySQL5 Views. For our example, we'll use the users table. Our "master" users table (the table that contains all users for all of our sites) resides in a database called "master". The database of the our example site that will have restricted access to our masters users table is called "banana". Assuming you're starting with a fresh instance of the Drupal schema in your database "banana", do this:


use banana;
drop table users;
CREATE VIEW users AS
SELECT *
FROM master.users
WHERE uid IN (
SELECT uid
FROM somedatabase.sometable
WHERE uid = 0
OR label = 'foo'
)

Drupal will use banana.users just as it would a normal users table. No other modifications are necessary. Now only "foo" users will be included in the users table for your banana website. Note: Drupal has a dependency that is not really documented. Every users table must have an entry that contains uid=0. It's a "stub" entry that Drupal needs to function properly when a user is anonymous. A workaround for this dependency is to include "user 0" in the results set that defines your view. See, wasn't that easy? Please post questions here, and I'll update these instructions as needed.

Hi Harry,

my view:

DROP VIEW IF EXISTS `site_store`.`users`;
CREATE VIEW `site_store`.`users`
AS
( SELECT * FROM
main_site.users
WHERE uid IN (
SELECT uid
FROM main_site.users
)
)

I got 2 databases and 2 sites with 1 drupal codebase:

example.com and store.example.com

I want to share the users table at the moment (experimenting currently with a magazine plus a store for her).

It seems the view works generally well but I got an error message as follows:

The target table users of the UPDATE is not updatable query: UPDATE users SET access = 1160467076 WHERE uid = 1 in D:\AppServ\www\example.com\includes\database.mysql.inc on line 120.

What could be the solution? Do I need more Views or etc?

Views are not writeable, they are read-only (hence the term view, not 'view and edit' :). so for creating/editing user info, you should always refer to the original table, not the view.

A solution to this is not really what this article is about, but I will say that to solve this, you'll need to programatically *not* use the view when doing updates and inserts. I haven't done this, but I would start by looking at hook_user().

Yes. Everybody is looking for share users in Drupal Multisite in multidatabases.

Please provide complete tutorial to us. Please!

How I setup setting.php? How I create a "link" to master.db in all setting.php of all subdomains?

Best Regards

This article is not really targeted at novice users.

If you're not familiar with the Drupal API and how to create modules, you probably shouldn't be messing around with views at all.

When this was written did Drupal write the last logged in time to the user table? These instruction still work, but an error is generated on login...

user warning: The target table users of the UPDATE is not updatable query
UPDATE users SET login = 1186087927 WHERE uid = 1 in /includes/database.mysql.inc on line 172.