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...
Comments
Using tables from another database?
Did this break using tables from another database, using older versions of MySQL?
I was sharing the users and sessions tables from another database
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:
This resulted into an error that firstdb.tst.drupal_semaphore did not exist, although drupal_semaphore in database firstdb.tst really existed.
Could you please file a new
Could you please file a new issue as a support request?
Filed #3186120: Table name
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).https://www.drupal.org
https://www.drupal.org/project/drupal/releases/7.77 was released with a fix for table prefixes that include e.g. "schema.prefix_"
Drupal 7 and MySQL 8 Patch
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'",
);
}
With custom MySQL tables in
With custom MySQL tables in place that use special characters and/or whitespace in the column names,
class UpdateQuery_mysql
now breaks this becauseescapeField()
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?