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 * Constructs a Connection object.
70 public function __construct(\PDO $connection, array $connection_options = []) {
71 parent::__construct($connection, $connection_options);
73 // This driver defaults to transaction support, except if explicitly passed FALSE.
74 $this->transactionSupport = !isset($connection_options['transactions']) || ($connection_options['transactions'] !== FALSE);
76 // MySQL never supports transactional DDL.
77 $this->transactionalDDLSupport = FALSE;
79 $this->connectionOptions = $connection_options;
85 public function query($query, array $args = [], $options = []) {
87 return parent::query($query, $args, $options);
89 catch (DatabaseException $e) {
90 if ($e->getPrevious()->errorInfo[1] == 1153) {
91 // If a max_allowed_packet error occurs the message length is truncated.
92 // This should prevent the error from recurring if the exception is
93 // logged to the database using dblog or the like.
94 $message = Unicode::truncateBytes($e->getMessage(), self::MIN_MAX_ALLOWED_PACKET);
95 $e = new DatabaseExceptionWrapper($message, $e->getCode(), $e->getPrevious());
104 public static function open(array &$connection_options = []) {
105 if (isset($connection_options['_dsn_utf8_fallback']) && $connection_options['_dsn_utf8_fallback'] === TRUE) {
106 // Only used during the installer version check, as a fallback from utf8mb4.
110 $charset = 'utf8mb4';
112 // The DSN should use either a socket or a host/port.
113 if (isset($connection_options['unix_socket'])) {
114 $dsn = 'mysql:unix_socket=' . $connection_options['unix_socket'];
117 // Default to TCP connection on port 3306.
118 $dsn = 'mysql:host=' . $connection_options['host'] . ';port=' . (empty($connection_options['port']) ? 3306 : $connection_options['port']);
120 // Character set is added to dsn to ensure PDO uses the proper character
121 // set when escaping. This has security implications. See
122 // https://www.drupal.org/node/1201452 for further discussion.
123 $dsn .= ';charset=' . $charset;
124 if (!empty($connection_options['database'])) {
125 $dsn .= ';dbname=' . $connection_options['database'];
127 // Allow PDO options to be overridden.
128 $connection_options += [
131 $connection_options['pdo'] += [
132 \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
133 // So we don't have to mess around with cursors and unbuffered queries by default.
134 \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
135 // Make sure MySQL returns all matched rows on update queries including
136 // rows that actually didn't have to be updated because the values didn't
137 // change. This matches common behavior among other database systems.
138 \PDO::MYSQL_ATTR_FOUND_ROWS => TRUE,
139 // Because MySQL's prepared statements skip the query cache, because it's dumb.
140 \PDO::ATTR_EMULATE_PREPARES => TRUE,
142 if (defined('\PDO::MYSQL_ATTR_MULTI_STATEMENTS')) {
143 // An added connection option in PHP 5.5.21 to optionally limit SQL to a
144 // single statement like mysqli.
145 $connection_options['pdo'] += [\PDO::MYSQL_ATTR_MULTI_STATEMENTS => FALSE];
149 $pdo = new \PDO($dsn, $connection_options['username'], $connection_options['password'], $connection_options['pdo']);
151 catch (\PDOException $e) {
152 if ($e->getCode() == static::DATABASE_NOT_FOUND) {
153 throw new DatabaseNotFoundException($e->getMessage(), $e->getCode(), $e);
155 if ($e->getCode() == static::ACCESS_DENIED) {
156 throw new DatabaseAccessDeniedException($e->getMessage(), $e->getCode(), $e);
161 // Force MySQL to use the UTF-8 character set. Also set the collation, if a
162 // certain one has been set; otherwise, MySQL defaults to
163 // 'utf8mb4_general_ci' for utf8mb4.
164 if (!empty($connection_options['collation'])) {
165 $pdo->exec('SET NAMES ' . $charset . ' COLLATE ' . $connection_options['collation']);
168 $pdo->exec('SET NAMES ' . $charset);
171 // Set MySQL init_commands if not already defined. Default Drupal's MySQL
172 // behavior to conform more closely to SQL standards. This allows Drupal
173 // to run almost seamlessly on many different kinds of database systems.
174 // These settings force MySQL to behave the same as postgresql, or sqlite
175 // in regards to syntax interpretation and invalid data handling. See
176 // https://www.drupal.org/node/344575 for further discussion. Also, as MySQL
177 // 5.5 changed the meaning of TRADITIONAL we need to spell out the modes one
179 $connection_options += [
180 'init_commands' => [],
182 $connection_options['init_commands'] += [
183 'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY'",
185 // Execute initial commands.
186 foreach ($connection_options['init_commands'] as $sql) {
196 public function serialize() {
197 // Cleanup the connection, much like __destruct() does it as well.
198 if ($this->needsCleanup) {
199 $this->nextIdDelete();
201 $this->needsCleanup = FALSE;
203 return parent::serialize();
209 public function __destruct() {
210 if ($this->needsCleanup) {
211 $this->nextIdDelete();
215 public function queryRange($query, $from, $count, array $args = [], array $options = []) {
216 return $this->query($query . ' LIMIT ' . (int) $from . ', ' . (int) $count, $args, $options);
219 public function queryTemporary($query, array $args = [], array $options = []) {
220 $tablename = $this->generateTemporaryTableName();
221 $this->query('CREATE TEMPORARY TABLE {' . $tablename . '} Engine=MEMORY ' . $query, $args, $options);
225 public function driver() {
229 public function databaseType() {
234 * Overrides \Drupal\Core\Database\Connection::createDatabase().
236 * @param string $database
237 * The name of the database to create.
239 * @throws \Drupal\Core\Database\DatabaseNotFoundException
241 public function createDatabase($database) {
242 // Escape the database name.
243 $database = Database::getConnection()->escapeDatabase($database);
246 // Create the database and set it as active.
247 $this->connection->exec("CREATE DATABASE $database");
248 $this->connection->exec("USE $database");
250 catch (\Exception $e) {
251 throw new DatabaseNotFoundException($e->getMessage());
255 public function mapConditionOperator($operator) {
256 // We don't want to override any of the defaults.
260 public function nextId($existing_id = 0) {
261 $new_id = $this->query('INSERT INTO {sequences} () VALUES ()', [], ['return' => Database::RETURN_INSERT_ID]);
262 // This should only happen after an import or similar event.
263 if ($existing_id >= $new_id) {
264 // If we INSERT a value manually into the sequences table, on the next
265 // INSERT, MySQL will generate a larger value. However, there is no way
266 // of knowing whether this value already exists in the table. MySQL
267 // provides an INSERT IGNORE which would work, but that can mask problems
268 // other than duplicate keys. Instead, we use INSERT ... ON DUPLICATE KEY
269 // UPDATE in such a way that the UPDATE does not do anything. This way,
270 // duplicate keys do not generate errors but everything else does.
271 $this->query('INSERT INTO {sequences} (value) VALUES (:value) ON DUPLICATE KEY UPDATE value = value', [':value' => $existing_id]);
272 $new_id = $this->query('INSERT INTO {sequences} () VALUES ()', [], ['return' => Database::RETURN_INSERT_ID]);
274 $this->needsCleanup = TRUE;
278 public function nextIdDelete() {
279 // While we want to clean up the table to keep it up from occupying too
280 // much storage and memory, we must keep the highest value in the table
281 // because InnoDB uses an in-memory auto-increment counter as long as the
282 // server runs. When the server is stopped and restarted, InnoDB
283 // reinitializes the counter for each table for the first INSERT to the
284 // table based solely on values from the table so deleting all values would
285 // be a problem in this case. Also, TRUNCATE resets the auto increment
288 $max_id = $this->query('SELECT MAX(value) FROM {sequences}')->fetchField();
289 // We know we are using MySQL here, no need for the slower db_delete().
290 $this->query('DELETE FROM {sequences} WHERE value < :value', [':value' => $max_id]);
292 // During testing, this function is called from shutdown with the
293 // simpletest prefix stored in $this->connection, and those tables are gone
294 // by the time shutdown is called so we need to ignore the database
295 // errors. There is no problem with completely ignoring errors here: if
296 // these queries fail, the sequence will work just fine, just use a bit
297 // more database storage and memory.
298 catch (DatabaseException $e) {
303 * Overridden to work around issues to MySQL not supporting transactional DDL.
305 protected function popCommittableTransactions() {
306 // Commit all the committable layers.
307 foreach (array_reverse($this->transactionLayers) as $name => $active) {
308 // Stop once we found an active transaction.
313 // If there are no more layers left then we should commit.
314 unset($this->transactionLayers[$name]);
315 if (empty($this->transactionLayers)) {
316 if (!$this->connection->commit()) {
317 throw new TransactionCommitFailedException();
321 // Attempt to release this savepoint in the standard way.
323 $this->query('RELEASE SAVEPOINT ' . $name);
325 catch (DatabaseExceptionWrapper $e) {
326 // However, in MySQL (InnoDB), savepoints are automatically committed
327 // when tables are altered or created (DDL transactions are not
328 // supported). This can cause exceptions due to trying to release
329 // savepoints which no longer exist.
331 // To avoid exceptions when no actual error has occurred, we silently
332 // succeed for MySQL error code 1305 ("SAVEPOINT does not exist").
333 if ($e->getPrevious()->errorInfo[1] == '1305') {
334 // If one SAVEPOINT was released automatically, then all were.
335 // Therefore, clean the transaction stack.
336 $this->transactionLayers = [];
337 // We also have to explain to PDO that the transaction stack has
339 $this->connection->commit();
353 * @} End of "addtogroup database".