Problem/Motivation

Drupal has supported table prefixing in the database since time immemorial. That is rarely useful, but occasionally is.

It has also supported per-table prefixing for about as long. That is so rarely useful that between several long-time devs we could only think of it being used once, and it was a mistake. In practice it's only meaningful use was sharing users across a multi-site, but that is error prone and buggy and with user fields in D8 is basically a no-go now. It's a vestigial feature that serves only to complicate the database layer and code that does low-level things with it.

Proposed resolution

Remove per-table prefixing. Just keep per-connection prefixing.

Although this is beta, the impact on features is nil (other than removing this feature). In IRC, Alex Pott indicated that he was open to it. So consider it tentatively approved if someone writes it.

Remaining tasks

Write the patch.

User interface changes

None.

API changes

The ability to set per-table prefixes goes away (duh). A few methods in the DB layer get more predictable as a prefix will always be a string rather than sometimes a string and sometimes an array.

Data model changes

None.

Alternatives to per-table prefixing

  1. User, sesssion and/or userrole sharing between sites. I think that this will not work in Drupal 8 and I also see no alternatives.
  2. Content and/or config synchronization between sites. For this there are alternatives. For config synchronization is supported by Drupal core. See https://www.drupal.org/documentation/administer/config for more info. For content synchronization is a bit more difficult. At the moment we have an initiative for supporting content synchronization in Drupal core. See #2721129: Workflow Initiative and http://buytaert.net/improving-drupal-content-workflow for more info.
    An alternative is to use the domain module. It uses a low-key way of of sharing content between sites.
    Yet an other alternative is the deploy module. It uses a high-level content staging mechanism to copy content between sites.
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Crell created an issue. See original summary.

alexpott’s picture

An additional reason to remove it is that is completely untested from a working Drupal - we have no test that installs two Drupals and shares the user table.

jbrauer’s picture

I'm aware of many cases this is used. In particular it's very useful for users_roles in the situation where you want to have an "edit" server with everyone having roles and a "public" server where even the admin users only have authenticated user roles. Used in combination with the MySQL Blackhole storage engine it makes it possible to accomplish this with one line of configuration (well more if you include the schema definition for the blackhole_users_roles table).

In addition it's been useful for sessions tables to similarly (though not with Blackhole) to separate say CDN-sessions tables from internal sessions. This is less useful but still adds some protection against someone hijacking a CDN session and using it on an "edit" server or vice-versa.

alexjarvis’s picture

Like jbrauer I've used the per-table prefixing extensively on several projects to split out sessions, users and roles. Doing so would become immensely more complicated without table prefixing.

alexpott’s picture

@jbrauer, @alexjarvis the problem is is that user_roles does not exist anymore. We have user__roles but this is an entity storage API created table is under that API's control.

jbrauer’s picture

Another place I'd contemplated at one time using it (but never got to implementing just thinking through the possible uses so we cover everything) ... A case of versioning a handbook for say a course catalog or other industries where having the exact version of what was the handbook in each term... Taking a snapshot of the node/node revision tables (this was a while ago) and thereby having a map of what nodes exist and what their versions are in that "version" of the site) and allowing read-only access to the Fall 2008 version..

In general there are now probably better solutions (though they may not handle the case mentioned here) like SPS, CPS, Workbench etc for the various pieces of a versioning/revision workflow.

To be clear I'm not, necessarily, advocating that we do keep it. I'm just pointing out for our conversation where it's been a very helpful, and relatively accessible, especially for small/intermediate installations/teams (Higher Ed, K12, etc) that have seen benefits. It may very well be that the ROI for maintaining it in core is far greater than the value to those who have benefited and that they need to figure out the options. I haven't done any analysis and can't speak to the cost/benefits which sound like there are many benefits. Simply speaking up for something that has been very useful in the course of my Drupal career and may, very well, need to be put out to pasture.

pwolanin’s picture

For D8 it looks like you could make a site where no roles have permissions or only the same as auth user by implanting a simple replacement for the RoleStorage class.

moshe weitzman’s picture

Version: 8.0.x-dev » 9.x-dev

Moving to D9. I really think we should drop table prefixing completely. Its a huge hassle for Drush since we often work with lower level commands like mysqldump. Databases used to be precious commodity but they aren't anymore. Does any web host actually charge significantly for them?

dawehner’s picture

One thing we have done in Drupal 8 is to make everything swappable, given that those advanced usecases (even not by just one line of configuration), are able to achieve in sort of reasonable efforts.

catch’s picture

Title: Remove per-table prefixing » Deprecate per-table prefixing
Version: 9.x-dev » 8.2.x-dev

We can mark this deprecated in 8.x with a link to alternatives, so moving back to there.

daffie’s picture

Status: Active » Needs review
Issue tags: +@deprecated
FileSize
651 bytes
Crell’s picture

I'm afraid the grammar of that sentence doesn't parse. I think you mean "Per-table prefixes are deprecated as of Drupal 8.2, and will be removed in Drupal 9.0. After that, only a single prefix for all tables will be supported."

daffie’s picture

FileSize
658 bytes

Sorry, my native language is not English.

Crell’s picture

No worries, that's true for many people.

+++ b/sites/default/default.settings.php
@@ -144,6 +144,11 @@
+ * Per-table prefixes are deprecated as of Drupal 8.2, will be removed in

I think we need an "and" after the comma, before "will".

daffie’s picture

FileSize
662 bytes

Even copying can be difficult.

Crell’s picture

Status: Needs review » Reviewed & tested by the community

Thanks, daffie!

alexpott’s picture

Status: Reviewed & tested by the community » Needs work

in #10 @catch said there should be links to alternatives - so back to needs work for discussion and adding them.

daffie’s picture

Status: Needs work » Needs review

I see two options for using per-table prefixing:

  1. User, sesssion and/or userrole sharing between sites. I think that this will not work in Drupal 8 and I also see no alternatives.
  2. Content and/or config synchronization between sites. For this there are alternatives. For config synchronization is supported by Drupal core. See https://www.drupal.org/documentation/administer/config for more info. For content synchronization is a bit more diffecult. There are several modules for that. At the moment we have an initiative for supporting content synchronization in Drupal core. See #2721129: Workflow Initiative and http://buytaert.net/improving-drupal-content-workflow for more info.
tstoeckler’s picture

I would mention http://drupal.org/project/domain as a low-key way of sharing content between sites and http://drupal.org/project/deploy as a high-level content staging mechanism, as well.

andypost’s picture

Yep, domain module using prefixes for path aliases, this is only working solution

daffie’s picture

Issue summary: View changes

Updated the issue summary with alternatives for using per-table prefixing.

daffie’s picture

Issue summary: View changes
daffie’s picture

Status: Needs review » Reviewed & tested by the community

Updated the change record with alternatives for using per-table prefixing.

Status: Reviewed & tested by the community » Needs work

The last submitted patch, 15: 2551549-15.patch, failed testing.

daffie’s picture

Status: Needs work » Reviewed & tested by the community

Back to RTBC.

xjm’s picture

chx’s picture

*Takes deep breath*

Regular disclaimer: Please, try not to take offense of anything I post about and try to find the intended meaning in them instead! I know this is near impossible but try to. Thanks!

benjy is working on adding revision UUIDs and I suggested him to add a migration which a) renames all the tables of an entity type b) uses the existing SQL storage handler as migrate source with a prefixed database info injected. Very neat and makes problematic updates easier. Every time people run into the exception thrown by requiresEntityDataMigration they could use this facility instead. Easy. Without per table prefixes, how would this work?

catch’s picture

Status: Reviewed & tested by the community » Needs review

Yeah I still think the change record with alternatives needs fleshing out, and #27 is another (albeit exceptional) use case.

daffie’s picture

@catch: You would like the change record to "fleshing out". That is fine. What alternatives would you like to be added?

daffie’s picture

Status: Needs review » Reviewed & tested by the community

The change record has been "fleshed out". If this "fleshing out" was not enough, then please specify what you would to be added.

pwolanin’s picture

@chx - Can you explain that flow more? It would seem to counter the argument in the issue summary that it's not a useful feature.

It sounds as though you are saying we need this or something similar to support complex schema changes, but I'm not deep enough into the migration code to know whether you need per-table prefixing for that or if you could use a global prefix to find those new tables?

chx’s picture

So imagine that you need to do something that triggers SqlContentEntityStorageSchema::requiresEntityDataMigration ... now you rename node to foobar_node , node__body to foobar_node__body and then create a connection with the prefix foobar_ and create a SqlContentEntityStorage with that connection injected and create a migrate source plugin which uses doLoadMultiple to get the entity data. Is this possible? Desirable? Should we just port the D7 fieldable entity source? Open questions. But if we go down this route, well, dependency injection would make it so that the entity type manager talks to a different connection ... so yeah. Global prefixing ought to be enough. Nevermind me :)

  • catch committed 168a73d on 8.3.x
    Issue #2551549 by daffie: Deprecate per-table prefixing
    
catch’s picture

Version: 8.2.x-dev » 8.3.x-dev
Status: Reviewed & tested by the community » Fixed

I added a quick summary of #32 to the change notice, that potentially provides people with a way to share tables between sites too for specific services, if they really wanted to.

Committed/pushed to 8.3.x, thanks!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

Mile23’s picture