Problem/Motivation

D7's default.settings.php includes an example of how to share database tables from a different database using table prefixes containing a dot:

 * You can also use a reference to a schema/database as a prefix. This may be
 * useful if your Drupal installation exists in a schema that is not the default
 * or you want to access several databases from the same code base at the same
 * time.
 * Example:
 * @code
 *   'prefix' => array(
 *     'default'   => 'main.',
 *     'users'     => 'shared.',
 *     'sessions'  => 'shared.',
 *     'role'      => 'shared.',
 *     'authmap'   => 'shared.',
 *   );
 * @endcode

It looks like this configuration is not covered by core's tests and the table name quoting introduced for MySQL 8 in #2978575: Mysql 8 Support on Drupal 7 breaks sites that are set up like this.

A workaround is possible by setting this variable to an empty string to disable table quoting:

$conf['mysql_identifier_quote_character'] = '';

Steps to reproduce

Set up a site with at least one table that has a prefix containing a dot (i.e. that table is actually in another database).

Try to bootstrap Drupal e.g. via drush st.

You'll get a SQL error similar to this:

WD php: PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'drupal7x.d7shared.users' doesn't exist: SELECT base.uid AS uid, base.name AS name, base.pass AS pass, base.mail AS mail, base.theme AS theme, base.signature AS signature,       [error]
base.signature_format AS signature_format, base.created AS created, base.access AS access, base.login AS login, base.status AS status, base.timezone AS timezone, base.language AS language, base.picture AS picture, base.init AS init, base.data AS data
FROM 
{users} base
WHERE  (base.uid IN  (:db_condition_placeholder_0)) ; Array
(
    [:db_condition_placeholder_0] => 0
)
 in DrupalDefaultEntityController->load() (line 198 of /path/to/drupal-7.x/includes/entity.inc).

Proposed resolution

Can we fix this and maintain support for MySQL 5.x and 8?

Or is it going to remain something which only works when the variable is set to an empty string (and hence will never work with MySQL 8)?

Remaining tasks

User interface changes

API changes

Data model changes

Release notes snippet

CommentFileSizeAuthor
#14 3186120-14.patch3.97 KBmcdruid
#14 3186120-14_test_only.patch2.88 KBmcdruid
#14 interdiff-3186120-12-14.txt754 bytesmcdruid
#12 3186120-12.patch4.03 KBmcdruid
#12 3186120-12_test_only.patch2.88 KBmcdruid
#12 interdiff-3186120-8-12.txt1.39 KBmcdruid
#8 3186120-8.patch4.01 KBmcdruid
#8 3186120-8_test_only.patch2.88 KBmcdruid
#8 interdiff-3186120-5-8.txt2.19 KBmcdruid
#5 3186120-5.patch3.53 KBmcdruid
#5 3186120-5_test_only.patch2.4 KBmcdruid
#2 3186120-2.patch1.13 KBmcdruid

Comments

mcdruid created an issue. See original summary.

mcdruid’s picture

mcdruid commented
Status: Active » Needs review
Issue tags: +Needs tests
StatusFileSize
new 3186120-2.patch1.13 KB

Here's a rough initial patch which I think fixes the issue.

It seems that we don't have any test coverage for this though... so we'll need to look at adding some.

Do existing tests pass with this patch?

@Jan-E can you confirm if this patch gets your site working (without the variable set to an empty string) please?

jan-e’s picture

jan-e commented

@mcdruid Confirmed that this patch fixes the issue.

BTW: Just like this setup has no test coverage, it was a rare setup on my side. On my production server I have this for one site: different sources (controlled from a git repository) and a different database, that I once in a while fill with a copy of the database that really is serving the production site. I tested the core update first locally, pushed the changes and then pulled the updates to the site with the dotted prefix setup. And got the error. git reset HEAD~1 --hard got the site up'n'running again. Without that it would have been hard to test different setups.

jan-e’s picture

jan-e commented

Double check: I had applied your patch directly to the sources on the production server. git stash resulted in

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'testcentral.testcentral.drupal_semaphore' doesn't exist: SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => variable_init ) in lock_may_be_available()

patch -p1 < 3186120-2.patch fixed it once again.

mcdruid’s picture

mcdruid commented
StatusFileSize
new 3186120-5_test_only.patch2.4 KB
new 3186120-5.patch3.53 KB

Thanks for confirming that #2 seems to fix the issue @Jan-E.

I've added a basic test which creates an extra db connection with a couple of "schema.table" table prefixes (it's all actually the same tables in the same db, but this tests that the syntax works).

The test only patch should fail and is the interdiff with #2.

mcdruid’s picture

mcdruid commented

We'll probably have to skip this table prefix test in SQLite, I expect.

The last submitted patch, 5: 3186120-5_test_only.patch, failed testing. View results

mcdruid’s picture

mcdruid commented
Issue tags: -Needs tests
StatusFileSize
new interdiff-3186120-5-8.txt2.19 KB
new 3186120-8_test_only.patch2.88 KB
new 3186120-8.patch4.01 KB

Improved the test a little, including skipping it for SQLite.

The actual fix is still the same as #2

The last submitted patch, 8: 3186120-8_test_only.patch, failed testing. View results

mcdruid’s picture

mcdruid commented

It looks a bit odd, but we expected the test only patch to pass with SQLite as the failing test is being skipped; so green is good in this case.

alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
commented 
+++ b/includes/database/mysql/database.inc
@@ -393,7 +393,14 @@ class DatabaseConnection_mysql extends DatabaseConnection {
+          $this->prefixReplace[$i] = str_replace('.', '.' . $quote_char, $this->prefixReplace[$i]);

We do something very similar in D8/9... sorry for not spotting this in the original issue.

It looks like the solution here is to do something like database.'users'... in D8/9 we do 'database'.'users'.

mcdruid’s picture

mcdruid commented
StatusFileSize
new interdiff-3186120-8-12.txt1.39 KB
new 3186120-12_test_only.patch2.88 KB
new 3186120-12.patch4.03 KB

Thanks @alexpott

I did briefly think "people will just have to quote the prefixes themselves if they have reserved words as their schema / prefix names" but anyone in that position would be stuck as we're removing quotes from prefixes (because we sometimes end up with extra quotes in unit tests).

Here's another patch which should give us `database`.`users`.

The last submitted patch, 12: 3186120-12_test_only.patch, failed testing. View results

mcdruid’s picture

mcdruid commented
StatusFileSize
new interdiff-3186120-12-14.txt754 bytes
new 3186120-14_test_only.patch2.88 KB
new 3186120-14.patch3.97 KB

Tiny tweak to do everything in one line.

The last submitted patch, 14: 3186120-14_test_only.patch, failed testing. View results

mcdruid’s picture

mcdruid commented
Status: Needs review » Reviewed & tested by the community

Fabianx gave this +1 in chat.

  • mcdruid committed 6b541fb on 7.x 
    Issue #3186120 by mcdruid, Jan-E, alexpott: Table name quoting for MySQL...
mcdruid’s picture

mcdruid commented
Status: Reviewed & tested by the community » Fixed

Thanks!

Proceeding with a hotfix release for this bug.

jan-e’s picture

jan-e commented

Thanks @mcdruid !
FWIW: I tested https://www.drupal.org/files/issues/2020-12-03/3186120-14.patch on the troublesome site. It worked OK.

mcdruid’s picture

mcdruid commented

https://www.drupal.org/project/drupal/releases/7.77

jan-e’s picture

jan-e commented

Updated without problems. Thanks again.

rczurek’s picture

rczurek commented

Error in Database Optimization after update to Version 7.77:
Notice: Trying to get property of non-object in optimizedb_admin() (line 217 of sites/all/modules/contrib/optimizedb/optimizedb.module).
How to fix?

jan-e’s picture

jan-e commented

Did you update from 7.76 or from 7.75? Or even an earlier version?

mcdruid’s picture

mcdruid commented

@rczurek I've commented in #3186413: Trying to get property of non-object after update to Drupal 7.77. Let's please discuss the issue there.

everkuil’s picture

everkuil commented

I have the same problem here after an upgrade from 7.75 to 7.77: Table name quoting breaks sharing tables via prefixes containing dots.
This affects the Drupal module sql-queries on a second database that contains our administration. We query this database to expose data on our website.
Drupal seems tot work allright, but when getting data form the second database to show the data in a block: the query is broken.
After going back to 7.75 it works fine again.

mcdruid’s picture

mcdruid commented

@everkuil we'll need more information if we're going to be able to help.

Are you saying you're using a contrib or custom module to do the SQL queries which are breaking?

Can you share an example of your db config (especially the table prefixes)?

What error messages - if any - are you getting?

Have you tried setting this variable to an empty string in settings.php?

$conf['mysql_identifier_quote_character'] = '';

That should hopefully be a workaround for you as long as you're not trying to use MySQL 8.

BasH’s picture

BasH commented

I just added a new issue (https://www.drupal.org/project/1345390/issues/3186503) before I saw this one might be the same problem, adding the default database when using db_insert, but I don't think it is an option for me to change all my queries (by adding quotes for instance) !
(But maybe changing settings.php is..., read that after posting)

everkuil’s picture

everkuil commented

I use a custom module to do the SQL queries that brake.

// standaard drupal775
$databases['default']['default'] = array(
'driver' => 'mysql',
'database' => 'hx0923_cms',
'username' => '',
'password' => '',
'host' => 'localhost',
'port' => '3306'
);

// database cursisten administratie
$databases['administratie']['default'] = array(
'driver' => 'mysql',
'database' => 'hx0923_*REDACTED*',
'username' => '',
'password' => '**REDACTED**',
'host' => 'localhost',
'port' => '3306'
);

I was wrong there are no table prefixes, but dots between the tablenames like adressen.achternaam
It brakes on the dot.

I'lltry to reproduce the error and post it.

I will laso try to use the $conf['mysql_identifier_quote_character'] = ''; settings in settings.php

everkuil’s picture

everkuil commented

Sorry to have bothered you,
the $conf['mysql_identifier_quote_character'] = ''; works for me.

Thanks!

cilefen’s picture

cilefen commented

Related? #3186634: Drupal 7.77 update breaks site and drush access

Status: Fixed » Closed (fixed)

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