Change record status: 
Project: 
Introduced in branch: 
7.x
Introduced in version: 
7.76
Description: 

Drupal 7.76 introduces support for MySQL 8 (and also better support for MySQL 5.7).

In order to do so a feature has been backported from D8 whereby any table with names which are MySQL reserved words (which includes the "system" table in MySQL 8) are quoted in SQL queries. This identifier quoting is configurable via a variable; this is the new entry in default.settings.php which provides some details:

/**
 * Quoting of identifiers in MySQL.
 *
 * To allow compatibility with newer versions of MySQL, Drupal will quote table
 * names and some other identifiers. The ANSI standard character for identifier
 * quoting is the double quote (") and that can be used by MySQL along with the
 * sql_mode setting of ANSI_QUOTES. However, MySQL's own default is to use
 * backticks (`). Drupal 7 uses backticks for compatibility. If you need to
 * change this, you can do so with this variable. It's possible to switch off
 * identifier quoting altogether by setting this variable to an empty string.
 *
 * @see https://www.drupal.org/project/drupal/issues/2978575
 * @see https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
 * @see \DatabaseConnection_mysql::setPrefix
 * @see \DatabaseConnection_mysql::quoteIdentifier
 */
# $conf['mysql_identifier_quote_character'] = '"';

Any sites running older versions of MySQL which experience problems because of identifier quoting (typically this will mean SQL errors) should set this variable to an empty string in settings.php to disable identifier quoting.

In addition, MySQL 8 requires a change to the sql_mode that Drupal sets, as one of the options - NO_AUTO_CREATE_USER is no longer supported.

There have been some reports of problems with some MySQL services / providers incorrectly reporting the version number (e.g. in Azure or with ProxySQL) and this may cause problems with the conditional settings of sql_mode.

Affected sites should be able to set sql_mode directly from within settings.php - for example:

$databases['default']['default']['init_commands'] = array(                         
  'sql_mode' => 'SET sql_mode="REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"'
);

See: https://git.drupalcode.org/project/drupal/-/blob/e1892643309aef2eebbbf64...

Impacts: 
Site builders, administrators, editors
Updates Done (doc team, etc.)
Online documentation: 
Not done
Theming guide: 
Not done
Module developer documentation: 
Not done
Examples project: 
Not done
Coder Review: 
Not done
Coder Upgrade: 
Not done
Other: 
Other updates done

Comments

Jan-E’s picture

Did this break using tables from another database, using older versions of MySQL?
I was sharing the users and sessions tables from another database

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
		'database' => 'seconddb',
		'username' => 'username',
		'password' => 'password',
		'host' => 'localhost',
		'charset' => 'utf8mb4',
		'collation' => 'utf8mb4_general_ci',
		'port' => '',
		'driver' => 'mysql',
		'prefix' => array(
			'default' => 'seconddb.drupal_',
			'users' => 'firstdb.drupal_',
			'sessions' => 'firstdb.drupal_',
        ),
    ),
  ),
);

After the update Drupal 7.76 complained that it could not find tables like seconddb,firstdb.drupal_users and seconddb,seconddb.drupal_semaphore. Removing 'seconddb.' from the default prefix helped a bit for finding seconddb.drupal_*.

But drupal 7.76 still said it could not find seconddb,firstdb.drupal_users.

Even copying seconddb to firstdb.tst did not help. It tried this with:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
		'database' => 'firstdb',
		'username' => 'username',
		'password' => 'password',
		'host' => 'localhost',
		'charset' => 'utf8mb4',
		'collation' => 'utf8mb4_general_ci',
		'port' => '',
		'driver' => 'mysql',
		'prefix' => array(
			'default' => 'tst.drupal_',
			'users' => 'drupal_',
			'sessions' => 'drupal_',
        ),
    ),
  ),
);

This resulted into an error that firstdb.tst.drupal_semaphore did not exist, although drupal_semaphore in database firstdb.tst really existed.

mcdruid’s picture

Could you please file a new issue as a support request?

mcdruid’s picture

Filed #3186120: Table name quoting for MySQL 8 breaks sharing tables via prefixes containing dots.

In the meantime, @Jan-E has confirmed that setting $conf['mysql_identifier_quote_character'] = ''; works (for MySQL versions before 8).

mcdruid’s picture

https://www.drupal.org/project/drupal/releases/7.77 was released with a fix for table prefixes that include e.g. "schema.prefix_"

uttamtechno’s picture

includes/database/databsae.inc

public function prefixTables($sql) {
$sql = str_replace("{system}", "{`system`}", $sql);
return str_replace($this->prefixSearch, $this->prefixReplace, $sql);
}

includes/database/mysql/databsae.inc

// NO_AUTO_CREATE_USER is removed in MySQL 8.0.11
// https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html#mysqld-...
$version_server = $this->getAttribute(PDO::ATTR_SERVER_VERSION);
if(version_compare($version_server, '8.0.11', '>=')){
$connection_options['init_commands'] += array(
'sql_mode' => "SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'",
);

$connection_options['init_commands']['isolation'] = "SET SESSION transaction_isolation='READ-COMMITTED'";

}
else{
$connection_options['init_commands'] += array(
'sql_mode' => "SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'",
);
}

marrrc’s picture

With custom MySQL tables in place that use special characters and/or whitespace in the column names, class UpdateQuery_mysqlnow breaks this because escapeField() is now enforced. We previously escaped the column names in the query with backticks, however this is not respected anymore. Is the general suggestion to convert column names to an escape-safe string, or are there ways around the new code?