I was migrating Drupal 7 website to a newer infrastructure (PHP 7.4) and during the migration process when tried to connect to the MySQL database I faced following error - SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'.
Here is the error code:
Resolve all issues below to continue the installation. For help configuring your database server, see the installation handbook, or contact your hosting provider. Failed to connect to your database server. The server reports the following message: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'. Is the database server running? Does the database exist, and have you entered the correct database name? Have you entered the correct username and password? Have you entered the correct database hostname?
No_auto_create_user
After quick research I was able to find a patch (Mysql 8 Support on Drupal 7 #24) which involves overwriting core database.inc file (inclues/database/mysql - > database.inc)
here I'm pasting full working example (for a patch see here)
<?php
/**
* @file
* Database interface code for MySQL database servers.
*/
/**
* @addtogroup database
* @{
*/
class DatabaseConnection_mysql extends DatabaseConnection {
/**
* Flag to indicate if the cleanup function in __destruct() should run.
*
* @var boolean
*/
protected $needsCleanup = FALSE;
public function __construct(array $connection_options = array()) {
// This driver defaults to transaction support, except if explicitly passed FALSE.
$this->transactionSupport = !isset($connection_options['transactions']) || ($connection_options['transactions'] !== FALSE);
// MySQL never supports transactional DDL.
$this->transactionalDDLSupport = FALSE;
$this->connectionOptions = $connection_options;
$charset = 'utf8';
// Check if the charset is overridden to utf8mb4 in settings.php.
if ($this->utf8mb4IsActive()) {
$charset = 'utf8mb4';
}
// The DSN should use either a socket or a host/port.
if (isset($connection_options['unix_socket'])) {
$dsn = 'mysql:unix_socket=' . $connection_options['unix_socket'];
}
else {
// Default to TCP connection on port 3306.
$dsn = 'mysql:host=' . $connection_options['host'] . ';port=' . (empty($connection_options['port']) ? 3306 : $connection_options['port']);
}
// Character set is added to dsn to ensure PDO uses the proper character
// set when escaping. This has security implications. See
// https://www.drupal.org/node/1201452 for further discussion.
$dsn .= ';charset=' . $charset;
$dsn .= ';dbname=' . $connection_options['database'];
// Allow PDO options to be overridden.
$connection_options += array(
'pdo' => array(),
);
$connection_options['pdo'] += array(
// So we don't have to mess around with cursors and unbuffered queries by default.
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
// Because MySQL's prepared statements skip the query cache, because it's dumb.
PDO::ATTR_EMULATE_PREPARES => TRUE,
);
if (defined('PDO::MYSQL_ATTR_MULTI_STATEMENTS')) {
// An added connection option in PHP 5.5.21+ to optionally limit SQL to a
// single statement like mysqli.
$connection_options['pdo'] += array(PDO::MYSQL_ATTR_MULTI_STATEMENTS => FALSE);
}
parent::__construct($dsn, $connection_options['username'], $connection_options['password'], $connection_options['pdo']);
// Force MySQL to use the UTF-8 character set. Also set the collation, if a
// certain one has been set; otherwise, MySQL defaults to 'utf8_general_ci'
// for UTF-8.
if (!empty($connection_options['collation'])) {
$this->exec('SET NAMES ' . $charset . ' COLLATE ' . $connection_options['collation']);
}
else {
$this->exec('SET NAMES ' . $charset);
}
// Set MySQL init_commands if not already defined. Default Drupal's MySQL
// behavior to conform more closely to SQL standards. This allows Drupal
// to run almost seamlessly on many different kinds of database systems.
// These settings force MySQL to behave the same as postgresql, or sqlite
// in regards to syntax interpretation and invalid data handling. See
// http://drupal.org/node/344575 for further discussion. Also, as MySQL 5.5
// changed the meaning of TRADITIONAL we need to spell out the modes one by
// one.
$connection_options += array(
'init_commands' => array(),
);
$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'",
);
// Execute initial commands.
foreach ($connection_options['init_commands'] as $sql) {
$this->exec($sql);
}
}
public function __destruct() {
if ($this->needsCleanup) {
$this->nextIdDelete();
}
}
public function query($query, array $args = array(), $options = array()) {
$query = preg_replace('/{([^}]+)}/', '`\1`', $query);
// This to make Drush work
$query = str_replace(' system.', ' `system`.', $query);
$query = str_replace('`system` system', '`system` `system`', $query);
return parent::query($query, $args, $options);
}
public function queryRange($query, $from, $count, array $args = array(), array $options = array()) {
return $this->query($query . ' LIMIT ' . (int) $from . ', ' . (int) $count, $args, $options);
}
public function queryTemporary($query, array $args = array(), array $options = array()) {
$tablename = $this->generateTemporaryTableName();
$this->query('CREATE TEMPORARY TABLE {' . $tablename . '} Engine=MEMORY ' . $query, $args, $options);
return $tablename;
}
public function driver() {
return 'mysql';
}
public function databaseType() {
return 'mysql';
}
public function mapConditionOperator($operator) {
// We don't want to override any of the defaults.
return NULL;
}
public function nextId($existing_id = 0) {
$new_id = $this->query('INSERT INTO {sequences} () VALUES ()', array(), array('return' => Database::RETURN_INSERT_ID));
// This should only happen after an import or similar event.
if ($existing_id >= $new_id) {
// If we INSERT a value manually into the sequences table, on the next
// INSERT, MySQL will generate a larger value. However, there is no way
// of knowing whether this value already exists in the table. MySQL
// provides an INSERT IGNORE which would work, but that can mask problems
// other than duplicate keys. Instead, we use INSERT ... ON DUPLICATE KEY
// UPDATE in such a way that the UPDATE does not do anything. This way,
// duplicate keys do not generate errors but everything else does.
$this->query('INSERT INTO {sequences} (value) VALUES (:value) ON DUPLICATE KEY UPDATE value = value', array(':value' => $existing_id));
$new_id = $this->query('INSERT INTO {sequences} () VALUES ()', array(), array('return' => Database::RETURN_INSERT_ID));
}
$this->needsCleanup = TRUE;
return $new_id;
}
public function nextIdDelete() {
// While we want to clean up the table to keep it up from occupying too
// much storage and memory, we must keep the highest value in the table
// because InnoDB uses an in-memory auto-increment counter as long as the
// server runs. When the server is stopped and restarted, InnoDB
// reinitializes the counter for each table for the first INSERT to the
// table based solely on values from the table so deleting all values would
// be a problem in this case. Also, TRUNCATE resets the auto increment
// counter.
try {
$max_id = $this->query('SELECT MAX(value) FROM {sequences}')->fetchField();
// We know we are using MySQL here, no need for the slower db_delete().
$this->query('DELETE FROM {sequences} WHERE value < :value', array(':value' => $max_id));
}
// During testing, this function is called from shutdown with the
// simpletest prefix stored in $this->connection, and those tables are gone
// by the time shutdown is called so we need to ignore the database
// errors. There is no problem with completely ignoring errors here: if
// these queries fail, the sequence will work just fine, just use a bit
// more database storage and memory.
catch (PDOException $e) {
}
}
/**
* Overridden to work around issues to MySQL not supporting transactional DDL.
*/
protected function popCommittableTransactions() {
// Commit all the committable layers.
foreach (array_reverse($this->transactionLayers) as $name => $active) {
// Stop once we found an active transaction.
if ($active) {
break;
}
// If there are no more layers left then we should commit.
unset($this->transactionLayers[$name]);
if (empty($this->transactionLayers)) {
if (!PDO::commit()) {
throw new DatabaseTransactionCommitFailedException();
}
}
else {
// Attempt to release this savepoint in the standard way.
try {
$this->query('RELEASE SAVEPOINT ' . $name);
}
catch (PDOException $e) {
// However, in MySQL (InnoDB), savepoints are automatically committed
// when tables are altered or created (DDL transactions are not
// supported). This can cause exceptions due to trying to release
// savepoints which no longer exist.
//
// To avoid exceptions when no actual error has occurred, we silently
// succeed for MySQL error code 1305 ("SAVEPOINT does not exist").
if ($e->errorInfo[1] == '1305') {
// If one SAVEPOINT was released automatically, then all were.
// Therefore, clean the transaction stack.
$this->transactionLayers = array();
// We also have to explain to PDO that the transaction stack has
// been cleaned-up.
PDO::commit();
}
else {
throw $e;
}
}
}
}
}
public function utf8mb4IsConfigurable() {
return TRUE;
}
public function utf8mb4IsActive() {
return isset($this->connectionOptions['charset']) && $this->connectionOptions['charset'] === 'utf8mb4';
}
public function utf8mb4IsSupported() {
// Ensure that the MySQL driver supports utf8mb4 encoding.
$version = $this->getAttribute(PDO::ATTR_CLIENT_VERSION);
if (strpos($version, 'mysqlnd') !== FALSE) {
// The mysqlnd driver supports utf8mb4 starting at version 5.0.9.
$version = preg_replace('/^\D+([\d.]+).*/', '$1', $version);
if (version_compare($version, '5.0.9', '<')) {
return FALSE;
}
}
else {
// The libmysqlclient driver supports utf8mb4 starting at version 5.5.3.
if (version_compare($version, '5.5.3', '<')) {
return FALSE;
}
}
// Ensure that the MySQL server supports large prefixes and utf8mb4.
try {
$this->query("CREATE TABLE {drupal_utf8mb4_test} (id VARCHAR(255), PRIMARY KEY(id(255))) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ROW_FORMAT=DYNAMIC ENGINE=INNODB");
}
catch (Exception $e) {
return FALSE;
}
$this->query("DROP TABLE {drupal_utf8mb4_test}");
return TRUE;
}
}
/**
* @} End of "addtogroup database".
*/
Disqus