3 namespace Drupal\Core\Database\Driver\mysql;
5 use Drupal\Core\Database\DatabaseAccessDeniedException;
6 use Drupal\Core\Database\DatabaseExceptionWrapper;
8 use Drupal\Core\Database\Database;
9 use Drupal\Core\Database\DatabaseNotFoundException;
10 use Drupal\Core\Database\TransactionCommitFailedException;
11 use Drupal\Core\Database\DatabaseException;
12 use Drupal\Core\Database\Connection as DatabaseConnection;
13 use Drupal\Component\Utility\Unicode;
16 * @addtogroup database
21 * MySQL implementation of \Drupal\Core\Database\Connection.
23 class Connection extends DatabaseConnection {
26 * Error code for "Unknown database" error.
28 const DATABASE_NOT_FOUND = 1049;
31 * Error code for "Access denied" error.
33 const ACCESS_DENIED = 1045;
36 * Error code for "Can't initialize character set" error.
38 const UNSUPPORTED_CHARSET = 2019;
41 * Driver-specific error code for "Unknown character set" error.
43 const UNKNOWN_CHARSET = 1115;
46 * SQLSTATE error code for "Syntax error or access rule violation".
48 const SQLSTATE_SYNTAX_ERROR = 42000;
51 * Flag to indicate if the cleanup function in __destruct() should run.
55 protected $needsCleanup = FALSE;
58 * The minimal possible value for the max_allowed_packet setting of MySQL.
60 * @link https://mariadb.com/kb/en/mariadb/server-system-variables/#max_allowed_packet
61 * @link https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
65 const MIN_MAX_ALLOWED_PACKET = 1024;
68 * The list of MySQL reserved key words.
70 * @link https://dev.mysql.com/doc/refman/8.0/en/keywords.html
72 private $reservedKeyWords = [
214 'master_ssl_verify_server_cert',
221 'minute_microsecond',
227 'no_write_to_binlog',
277 'second_microsecond',
292 'sql_calc_found_rows',
339 * Constructs a Connection object.
341 public function __construct(\PDO $connection, array $connection_options = []) {
342 parent::__construct($connection, $connection_options);
344 // This driver defaults to transaction support, except if explicitly passed FALSE.
345 $this->transactionSupport = !isset($connection_options['transactions']) || ($connection_options['transactions'] !== FALSE);
347 // MySQL never supports transactional DDL.
348 $this->transactionalDDLSupport = FALSE;
350 $this->connectionOptions = $connection_options;
356 public function query($query, array $args = [], $options = []) {
358 return parent::query($query, $args, $options);
360 catch (DatabaseException $e) {
361 if ($e->getPrevious()->errorInfo[1] == 1153) {
362 // If a max_allowed_packet error occurs the message length is truncated.
363 // This should prevent the error from recurring if the exception is
364 // logged to the database using dblog or the like.
365 $message = Unicode::truncateBytes($e->getMessage(), self::MIN_MAX_ALLOWED_PACKET);
366 $e = new DatabaseExceptionWrapper($message, $e->getCode(), $e->getPrevious());
375 public static function open(array &$connection_options = []) {
376 if (isset($connection_options['_dsn_utf8_fallback']) && $connection_options['_dsn_utf8_fallback'] === TRUE) {
377 // Only used during the installer version check, as a fallback from utf8mb4.
381 $charset = 'utf8mb4';
383 // The DSN should use either a socket or a host/port.
384 if (isset($connection_options['unix_socket'])) {
385 $dsn = 'mysql:unix_socket=' . $connection_options['unix_socket'];
388 // Default to TCP connection on port 3306.
389 $dsn = 'mysql:host=' . $connection_options['host'] . ';port=' . (empty($connection_options['port']) ? 3306 : $connection_options['port']);
391 // Character set is added to dsn to ensure PDO uses the proper character
392 // set when escaping. This has security implications. See
393 // https://www.drupal.org/node/1201452 for further discussion.
394 $dsn .= ';charset=' . $charset;
395 if (!empty($connection_options['database'])) {
396 $dsn .= ';dbname=' . $connection_options['database'];
398 // Allow PDO options to be overridden.
399 $connection_options += [
402 $connection_options['pdo'] += [
403 \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
404 // So we don't have to mess around with cursors and unbuffered queries by default.
405 \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
406 // Make sure MySQL returns all matched rows on update queries including
407 // rows that actually didn't have to be updated because the values didn't
408 // change. This matches common behavior among other database systems.
409 \PDO::MYSQL_ATTR_FOUND_ROWS => TRUE,
410 // Because MySQL's prepared statements skip the query cache, because it's dumb.
411 \PDO::ATTR_EMULATE_PREPARES => TRUE,
413 if (defined('\PDO::MYSQL_ATTR_MULTI_STATEMENTS')) {
414 // An added connection option in PHP 5.5.21 to optionally limit SQL to a
415 // single statement like mysqli.
416 $connection_options['pdo'] += [\PDO::MYSQL_ATTR_MULTI_STATEMENTS => FALSE];
420 $pdo = new \PDO($dsn, $connection_options['username'], $connection_options['password'], $connection_options['pdo']);
422 catch (\PDOException $e) {
423 if ($e->getCode() == static::DATABASE_NOT_FOUND) {
424 throw new DatabaseNotFoundException($e->getMessage(), $e->getCode(), $e);
426 if ($e->getCode() == static::ACCESS_DENIED) {
427 throw new DatabaseAccessDeniedException($e->getMessage(), $e->getCode(), $e);
432 // Force MySQL to use the UTF-8 character set. Also set the collation, if a
433 // certain one has been set; otherwise, MySQL defaults to
434 // 'utf8mb4_general_ci' (MySQL 5) or 'utf8mb4_0900_ai_ci' (MySQL 8) for
436 if (!empty($connection_options['collation'])) {
437 $pdo->exec('SET NAMES ' . $charset . ' COLLATE ' . $connection_options['collation']);
440 $pdo->exec('SET NAMES ' . $charset);
443 // Set MySQL init_commands if not already defined. Default Drupal's MySQL
444 // behavior to conform more closely to SQL standards. This allows Drupal
445 // to run almost seamlessly on many different kinds of database systems.
446 // These settings force MySQL to behave the same as postgresql, or sqlite
447 // in regards to syntax interpretation and invalid data handling. See
448 // https://www.drupal.org/node/344575 for further discussion. Also, as MySQL
449 // 5.5 changed the meaning of TRADITIONAL we need to spell out the modes one
451 $connection_options += [
452 'init_commands' => [],
455 $sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY';
456 // NO_AUTO_CREATE_USER is removed in MySQL 8.0.11
457 // https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html#mysqld-8-0-11-deprecation-removal
458 $version_server = $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION);
459 if (version_compare($version_server, '8.0.11', '<')) {
460 $sql_mode .= ',NO_AUTO_CREATE_USER';
462 $connection_options['init_commands'] += [
463 'sql_mode' => "SET sql_mode = '$sql_mode'",
466 // Execute initial commands.
467 foreach ($connection_options['init_commands'] as $sql) {
477 public function escapeField($field) {
478 $field = parent::escapeField($field);
479 return $this->quoteIdentifier($field);
485 public function escapeAlias($field) {
486 // Quote fields so that MySQL reserved words like 'function' can be used
488 $field = parent::escapeAlias($field);
489 return $this->quoteIdentifier($field);
493 * Quotes an identifier if it matches a MySQL reserved keyword.
495 * @param string $identifier
496 * The field to check.
499 * The identifier, quoted if it matches a MySQL reserved keyword.
501 private function quoteIdentifier($identifier) {
502 // Quote identifiers so that MySQL reserved words like 'function' can be
503 // used as column names. Sometimes the 'table.column_name' format is passed
505 // \Drupal\Core\Entity\Sql\SqlContentEntityStorage::buildQuery() adds a
506 // condition on "base.uid" while loading user entities.
507 if (strpos($identifier, '.') !== FALSE) {
508 list($table, $identifier) = explode('.', $identifier, 2);
510 if (in_array(strtolower($identifier), $this->reservedKeyWords, TRUE)) {
511 // Quote the string for MySQL reserved keywords.
512 $identifier = '"' . $identifier . '"';
514 return isset($table) ? $table . '.' . $identifier : $identifier;
520 public function serialize() {
521 // Cleanup the connection, much like __destruct() does it as well.
522 if ($this->needsCleanup) {
523 $this->nextIdDelete();
525 $this->needsCleanup = FALSE;
527 return parent::serialize();
533 public function __destruct() {
534 if ($this->needsCleanup) {
535 $this->nextIdDelete();
539 public function queryRange($query, $from, $count, array $args = [], array $options = []) {
540 return $this->query($query . ' LIMIT ' . (int) $from . ', ' . (int) $count, $args, $options);
543 public function queryTemporary($query, array $args = [], array $options = []) {
544 $tablename = $this->generateTemporaryTableName();
545 $this->query('CREATE TEMPORARY TABLE {' . $tablename . '} Engine=MEMORY ' . $query, $args, $options);
549 public function driver() {
553 public function databaseType() {
558 * Overrides \Drupal\Core\Database\Connection::createDatabase().
560 * @param string $database
561 * The name of the database to create.
563 * @throws \Drupal\Core\Database\DatabaseNotFoundException
565 public function createDatabase($database) {
566 // Escape the database name.
567 $database = Database::getConnection()->escapeDatabase($database);
570 // Create the database and set it as active.
571 $this->connection->exec("CREATE DATABASE $database");
572 $this->connection->exec("USE $database");
574 catch (\Exception $e) {
575 throw new DatabaseNotFoundException($e->getMessage());
579 public function mapConditionOperator($operator) {
580 // We don't want to override any of the defaults.
584 public function nextId($existing_id = 0) {
585 $new_id = $this->query('INSERT INTO {sequences} () VALUES ()', [], ['return' => Database::RETURN_INSERT_ID]);
586 // This should only happen after an import or similar event.
587 if ($existing_id >= $new_id) {
588 // If we INSERT a value manually into the sequences table, on the next
589 // INSERT, MySQL will generate a larger value. However, there is no way
590 // of knowing whether this value already exists in the table. MySQL
591 // provides an INSERT IGNORE which would work, but that can mask problems
592 // other than duplicate keys. Instead, we use INSERT ... ON DUPLICATE KEY
593 // UPDATE in such a way that the UPDATE does not do anything. This way,
594 // duplicate keys do not generate errors but everything else does.
595 $this->query('INSERT INTO {sequences} (value) VALUES (:value) ON DUPLICATE KEY UPDATE value = value', [':value' => $existing_id]);
596 $new_id = $this->query('INSERT INTO {sequences} () VALUES ()', [], ['return' => Database::RETURN_INSERT_ID]);
598 $this->needsCleanup = TRUE;
602 public function nextIdDelete() {
603 // While we want to clean up the table to keep it up from occupying too
604 // much storage and memory, we must keep the highest value in the table
605 // because InnoDB uses an in-memory auto-increment counter as long as the
606 // server runs. When the server is stopped and restarted, InnoDB
607 // reinitializes the counter for each table for the first INSERT to the
608 // table based solely on values from the table so deleting all values would
609 // be a problem in this case. Also, TRUNCATE resets the auto increment
612 $max_id = $this->query('SELECT MAX(value) FROM {sequences}')->fetchField();
613 // We know we are using MySQL here, no need for the slower db_delete().
614 $this->query('DELETE FROM {sequences} WHERE value < :value', [':value' => $max_id]);
616 // During testing, this function is called from shutdown with the
617 // simpletest prefix stored in $this->connection, and those tables are gone
618 // by the time shutdown is called so we need to ignore the database
619 // errors. There is no problem with completely ignoring errors here: if
620 // these queries fail, the sequence will work just fine, just use a bit
621 // more database storage and memory.
622 catch (DatabaseException $e) {
627 * Overridden to work around issues to MySQL not supporting transactional DDL.
629 protected function popCommittableTransactions() {
630 // Commit all the committable layers.
631 foreach (array_reverse($this->transactionLayers) as $name => $active) {
632 // Stop once we found an active transaction.
637 // If there are no more layers left then we should commit.
638 unset($this->transactionLayers[$name]);
639 if (empty($this->transactionLayers)) {
640 if (!$this->connection->commit()) {
641 throw new TransactionCommitFailedException();
645 // Attempt to release this savepoint in the standard way.
647 $this->query('RELEASE SAVEPOINT ' . $name);
649 catch (DatabaseExceptionWrapper $e) {
650 // However, in MySQL (InnoDB), savepoints are automatically committed
651 // when tables are altered or created (DDL transactions are not
652 // supported). This can cause exceptions due to trying to release
653 // savepoints which no longer exist.
655 // To avoid exceptions when no actual error has occurred, we silently
656 // succeed for MySQL error code 1305 ("SAVEPOINT does not exist").
657 if ($e->getPrevious()->errorInfo[1] == '1305') {
658 // If one SAVEPOINT was released automatically, then all were.
659 // Therefore, clean the transaction stack.
660 $this->transactionLayers = [];
661 // We also have to explain to PDO that the transaction stack has
663 $this->connection->commit();
677 * @} End of "addtogroup database".