2

I currently have a multisite with a single database. Each instance has a different prefix for their tables, except for one with no prefix which is considered to be the main instance. Now there is a custom table I made that contains custom data. That table is meant to be shared across all instances. In a function I'm updating a field like so

$database = \Drupal::database();
$database->update('custom_table')->condition('id', $my_id, '=')->fields(['full_name' => $fullname, 'address' => $address])->execute();

This works on my main site. Unfortunately in the other multisite instances that's using the same function, this is causing an error. In the error I have

Base table or view not found: 1146 Table 'database.myprefix_custom_table' doesn't exist

I realize that it's trying to prepend the prefix for the table. But it's a table that every site is meant to share. I don't want a prefix to be used.

I noticed that the update method can use options as a parameter https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Connection.php/function/Connection%3A%3Aupdate/8.2.x

But I'm not sure if I can tell it not to use a prefix in the options I can supply.

I understand that I can just use the query method instead of using the update method (I think) but I would like to know: Is it possible to force the update method for the connection object to not use the database prefix?

UPDATE: This was suggested as a duplicate: How to identify multi-site programmatically? But that isn't related to my problem.

In that example it's identifying what site it's on, but it's not performing anything with a database. I know how I can check what site it's on. That's not a problem. I need to call an actual database method and get rid of the prefix it's automatically applying.

In my code

$database->update('custom_table')->condition('id', $my_id, '=')->fields(['full_name' => $fullname, 'address' => $address])->execute();

I'm not even supplying the prefix. It's automatically applying it without my knowledge if it's being run on another site.

  • Possible duplicate of How to identify multi-site programmatically? – leymannx Feb 4 at 15:59
  • Not sure how that would help. In that example it's identifying what site it's on, but it's not performing anything with a database. I know how I can check what site it's on. That's not a problem. I need to call an actual database method and get rid of the prefix it's automatically applying. See my question update for more details. – Matt Feb 4 at 16:02
  • 2
    I think you'll have to add another database connection in settings.php, with the same connection details but without prefix, and switch to that connection before performing you custom table query. The database prefix intentionally covers the whole of a single connection, you can't opt out of it conditionally as far as I know – Clive Feb 4 at 16:08
  • How did the custom table got created? Maybe you should recreate it properly to ensure it'll be prefixed? – leymannx Feb 4 at 16:51
  • 1
    @leymannx All of the sites are on same database. The other sites just use a different prefix in their tables. The main site does not have a prefix. Every other site needs to use the same exact table, not a version they created themselves. The table I made I generate manually from a custom module install on the main site. Aside from that, it's completely separate from drupal as far as nodes or configuration goes. It's just a custom table of ids and info. However since it's so separate from Drupal, writing a manual query wouldn't be too difficult. I just wanted to know if it was possible. – Matt Feb 4 at 17:21
2

Looking at the comments in settings.php, you may well be able to do something like this:

$databases['default']['default'] = [
  'database' => 'db_name',
  'username' => 'root',
  'password' => 'root',
  'host' => 'localhost',
  'port' => '3306',
  'driver' => 'mysql',
  'prefix' => [
    'default' => 'shared_',
    'custom_table' => '',
  ],
];

I haven't tried it, but in theory that should set the prefix for all tables to shared_, except for the custom one, which would have no prefix.

  • 2
    Wow, it seems as if it would be possible then to have a shared user table for example across all instances. I definitely need to try that out. – leymannx Feb 4 at 20:24
  • 1
    @leymannx it is possible. I ended up using this same method to handle that so if you're logged into one you're logged into all of them. – Matt Feb 4 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.