Posted by joyseeker on August 19, 2012 at 12:37am
When I set up my D7 multisite shared tables with the $db_prefix in settings.php link in D6, I'm getting a PDOException error.
Does anyone have shared tables up in their D7 multisite setup? If so, could you document how it was set up?
Any help would be appreciated.
Susan
Comments
Multisite DB shared
Hi:
I have worked with D6 multisites. I think that in D7 the same idea is behind that.
When you install a new site with D6 you do as you were installing a new site and after you have installed the settings.php file is configured. You don't do manually. You can do manually but it's better to do automated.
Perhaps is the same thing with D7.
When you install you can choose to use the same DB with tables prefix or different DB. In D7 i think would be the same.
Is $db_prefix array not working with D7?
Thanks, Manuel, for your answer. The $db_prefix code is causing a PDOException error when I use it with a D7 site. Using the same code in the settings.php file from D6 isn't working in D7. Drupal is obviously not accessing the shared table because the PDOException error says the users table isn't found. I placed the users table in a shared database and deleted the users table from the main site's database. From what I understand a PDOException has something to do with database integrity and that this integrity is stronger in D7 than it was in D6.
Again my question, please, does anyone have a D7 multisite site working where the users table is being shared? Is multisite "dead in the water" in D7 where sharing the users table is concerned?
Prefix code in settings.php in D7 different than in D6!
My D7 multisites are now sharing the users etc. tables just like in D6. Whew!!
I got the PDOException error because my prefix code was incorrect in settings.php. Thanks to a guy at a Drupal meeting this week (BTMash) who, when I explained the problem, said check the code. And, I did just that.
There's no $db_prefix variable in the settings.php file in D7 now -- you just add the prefix for the shared table as an array inside the $databases variable. Thanks to a Drupal guy from Argentina (Rodre) who posted the complete $databases variable arrays at http://drupal.org/node/547862. Copied here:
<?php$databases = array (
'default' =>
array (
'default' =>
array (
'database' => 'drupal_site1',
'username' => 'user',
'password' => 'password',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => array(
'default' => '',
'authmap' => 'drupal_shared_tables.',
'role' => 'drupal_shared_tables.',
'sessions' => 'drupal_shared_tables.',
'users' => 'drupal_shared_tables.'
),
),
),
);
?>
Susan
Have you try entity reference
Have you try entity reference field as shared table ?
<?php'field_data_field_departement' => 'shared_', // entity reference field
'field_revision_field_departement' => 'shared_', // entity reference field
?>
I still have a problem with it, my departement field did not stored using shared_ prefix.
Works with all fields...
when you have the correct set up in each settings.php file. I'm not sure where you're using that code snippet you've quoted, but if it's in settings.php, you would need to use the complete name of the database as it exists in MySQL. So the code you quoted would only work if the database you're pointing to is actually named "shared_" (which seems unlikely). So I think all you need to do is:
'field_data_field_departement' => 'ENTIRE_DATABASE_NAME',
That could be "shared_mydatabasename' as opposed to your individual site's databases, i.e., "site1_mydatabasename", but there is no variable to set the prefix; you need to write the entire database name out for each table.
Don't forget that your revision tables also need to be in the same database.
'field_revision_field_departement' => 'ENTIRE_DATABASE_NAME',
Take a look at http://groups.drupal.org/node/170929 and the posts farther down this page.
How can i make the same but
How can i make the same but using different database for the second domain, in drupal 7
multisite Drupal 7 share tables
What is working for me on multisite Drupal 7
For news.mysite.com
$databases = array (
'default' =>
array (
'default' =>
array (
'database' => 'web_main',
'username' => 'user_main',
'password' => 'xxxxyyyy',
'host' => 'server.name.domain',
'port' => '3306',
'driver' => 'mysql',
),
);
For profiles.mysite.com - same as above except
'prefix' => array (
'default' => 'profiles_',
'users' => 'shared_',
'users_roles' => 'shared_',
'role' => 'shared_',
'sessions' => 'shared_',
'authmap' => 'shared_',
),
Architecture
I'm planing to set up a multisite architecture, a master site with several slave sites.
All the slave sites will use the same modules except the master site, while database will be all different except shared users table.
But some one was telling me to use SSO single sign on instead, but SSO seems is not available in D7...
Can anyone give me a hint what will be the best choice for such a purpose...
Thanks,
Sam
I would consider using Domain
I would consider using Domain Access, which basically lets you create subsites off of a master site and uses node access permissions to determine what content can be visible on what subsite. Since it's still a single Drupal instance behind the scenes, sharing users between sites will just work (though you can set up user permissions to restrict user groups to creating content only on certain subsites), so no extra SSO features will be necessary, and all subsites will easily use the same modules as well. No need for any messy shared tables.
The Boise Drupal Guy!
Thanks
Domain access seems quite a good solution, but can each subsite have its own theme...
Sure. One of the submodules
Sure. One of the submodules that comes with Domain Access allows you to override variables on a per-domain basis. Just use that to set a different theme for your subsites. Easy peasy. I built a site years ago which changes up themes on subsites this way; compare Northern California SBDC versus Sonoma SBDC versus North Coast SBDC. The first one is the parent site which uses a parent theme (itself a Zen subtheme), and the latter two are Domain Access subsites which use subthemes of the parent theme which have a few image and CSS tweaks.
The Boise Drupal Guy!
great! thanks
I will give a try...
Sorry, 2 more questions:
I have 2 websites which need to share the user, location (Enterprise location) and product (Ubercart) table after some market responses...DA should be a good solution for that.
Meanwhile, the 3rd website (or maybe more), with different database, is planed to share only the user table or just to be authenticated with SSO, CAS seems a good solution, but will DA and CAS work together?
Your comment is highly appreciated.
I have no experience with CAS
I have no experience with CAS and generally despise the concept of "social" authentication, so I can't help you there. But I note that you're still talking about sharing tables. Note that Domain Access doesn't really "share tables" in the traditional sense, since behind the scenes it's all just one standard Drupal install using one standard Drupal database set-up; it's just occasionally limiting itself to a subset of the data in order to pretend to be one sub-site or another. Or perhaps you could think of it as having several sites which are concurrently sharing all of their database tables with each other, if it helps to think that way.
The Boise Drupal Guy!
Understood,
DA is not sharing table, but using same db with different domain access, thanks...
shared tables
I have a multisite set up with 6 separate sites, each with its own theme, but users and most content is shared across the sites.
The first instance I have set up as a "base". When I use drush, this is "@s0"; it's where all the modules and scripts go, and where I update core when that's necessary. The other five I use to demonstrate various "looks" for my client, so the theming is different for each.
Currently my shared database has 91 tables; the databases that are unique to each site have slightly different numbers - 59, 60, 61, or 62 depending on what I have installed.
I've discovered that I've had to move tables around after the installation, so that certain things could be shared among all the instances. The most common of these are the "field_data_" and "field_revision_" tables. These are created every time you create a custom field for data entry. Many modules create these fields also. Easy enough to revise the database after you have a working installation, but it takes some time and familiarity working with SQL and phpMyAdmin. Of course, if you move tables, you have to move the references for them in settings.php.
The next time I do this kind of setup, I'll make my default database the shared database, because it's gotten to be 50% larger than the unique database, but that's just me. I suspect most multisite users won't be sharing so much across their different sites.
So it can be done. I assume you've started here: http://drupal.org/documentation/install/multi-site
There's a lot of trial and error involved. The definitive guide still hasn't been written because of the seemingly endless permutations each different multisite can have. It would probably be a book by itself, and would need to be revised constantly. However, this page can give you some insight about sharing users: http://groups.drupal.org/node/170929
My sites are set up so that once a user is logged in, they're logged in across all the different sites. For this you need to point the cookies to the same domain for all sites, in each settings.php file in each sites folder. I've also set up OpenID as a means to log in, although this is more as a demonstration than something my clients currently use.
So to get this working for me, I've put all the acl, authmap, contact, history, openid, role, sessions, and user tables are in the shared database.
Thanks a lot
for the detail explanation, will dive into it...
Academic Question About Shared Tables
D7 installs generate a considerable number of tables. I was wondering exactly what tables are installed with the slave sites when one database is used? Are all of the tables duplicated or just those that are required to make the slave site unique (ie, nodes)?
Just wondering.
RE: shared tables
@Lakeside:
If only one database is used, all the tables would have to go into that one, and virtually everything would be the same in all the different sites, except for those things which are not controlled by data in the tables.
I admit I don't know what the default behavior is, as I've always started by customizing the settings.php file before installing the sites, then dropping the shared database after installing each site before installing the next one (of course, not dropping the last one). In the page referenced above ( http://groups.drupal.org/node/170929 ), DaneAU writes that he was starting by only sharing the following tables to grant user permissions across all subdomains:
'users'
'sessions'
'role'
'authmap'
Of course, only the authmap and sessions tables are singletons. You need to share 'users_roles' and 'role_permission' also if you want the users to have the same experience across the different sites.
Many of the tables in my databases come from modules and custom fields. Remember, I'm using these sites for theme development, so I want the content to be the same across all of them. At the risk of maybe giving too much information, here are the tables from one of the unique sites:
accesslog
actions
backup_migrate_destinations
backup_migrate_profiles
backup_migrate_schedules
batch
block
block_custom
block_node_type
block_role
cache
cache_block
cache_bootstrap
cache_field
cache_filter
cache_form
cache_image
cache_libraries
cache_menu
cache_page
cache_path
cache_rules
cache_token
cache_update
cache_views
cache_views_data
ctools_css_cache
ctools_object_cache
filter
filter_format
flood
menu_custom
menu_links
menu_router
node_counter
page_manager_handlers
page_manager_pages
page_manager_weights
queue
rdf_mapping
registry
registry_file
rules_config
rules_dependencies
rules_scheduler
rules_tags
rules_trigger
search_dataset
search_index
search_node_links
search_total
semaphore
sequences
shortcut_set
shortcut_set_users
stylizer
system
tracker_node
tracker_user
variable
Most of these tables are in each of the unique sites' databases.
Notice that the cache tables reference some of the shared modules, like ctools and views, for instance. As far as I can tell, the variable, semaphore and system tables shouldn't (or can't) be shared. I'm not entirely sure about the backup_migrate tables; I haven't looked at the data in them, but I'm keeping them separate to make sure that all the unique databases are backed up. That's something I'll have to investigate, but I'm again doing this for a demo. On my production sites I have automated backups that include the databases and that don't use any Drupal functionality.
Because I want all the content to be the same on each site, all my node tables are shared:
node
node_access
node_comment_statistics
node_revision
node_type
Also, my views tables are shared:
views_display
views_view
as are my taxonomy tables:
taxonomy_display
taxonomy_index
taxonomy_term_data
taxonomy_term_hierarchy
taxonomy_vocabulary
I don't want rules and menus to be shared, although they could be. Block has given me the most issues. On one hand, I like being able to set up the layout of each site differently. On the other hand, I don't like having to enter the same data 5 times, when I'm actually entering text that will be displayed in a block (these are mostly small ads).
I know I didn't really answer your question, but maybe someone who is currently doing a "default" multisite install will come across this thread and answer it instead. In my case, I don't have any reason to do a "default" installation.