Connection.php 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678
  1. <?php
  2. namespace Drupal\Core\Database\Driver\mysql;
  3. use Drupal\Core\Database\DatabaseAccessDeniedException;
  4. use Drupal\Core\Database\DatabaseExceptionWrapper;
  5. use Drupal\Core\Database\Database;
  6. use Drupal\Core\Database\DatabaseNotFoundException;
  7. use Drupal\Core\Database\TransactionCommitFailedException;
  8. use Drupal\Core\Database\DatabaseException;
  9. use Drupal\Core\Database\Connection as DatabaseConnection;
  10. use Drupal\Component\Utility\Unicode;
  11. /**
  12. * @addtogroup database
  13. * @{
  14. */
  15. /**
  16. * MySQL implementation of \Drupal\Core\Database\Connection.
  17. */
  18. class Connection extends DatabaseConnection {
  19. /**
  20. * Error code for "Unknown database" error.
  21. */
  22. const DATABASE_NOT_FOUND = 1049;
  23. /**
  24. * Error code for "Access denied" error.
  25. */
  26. const ACCESS_DENIED = 1045;
  27. /**
  28. * Error code for "Can't initialize character set" error.
  29. */
  30. const UNSUPPORTED_CHARSET = 2019;
  31. /**
  32. * Driver-specific error code for "Unknown character set" error.
  33. */
  34. const UNKNOWN_CHARSET = 1115;
  35. /**
  36. * SQLSTATE error code for "Syntax error or access rule violation".
  37. */
  38. const SQLSTATE_SYNTAX_ERROR = 42000;
  39. /**
  40. * Flag to indicate if the cleanup function in __destruct() should run.
  41. *
  42. * @var bool
  43. */
  44. protected $needsCleanup = FALSE;
  45. /**
  46. * The minimal possible value for the max_allowed_packet setting of MySQL.
  47. *
  48. * @link https://mariadb.com/kb/en/mariadb/server-system-variables/#max_allowed_packet
  49. * @link https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
  50. *
  51. * @var int
  52. */
  53. const MIN_MAX_ALLOWED_PACKET = 1024;
  54. /**
  55. * The list of MySQL reserved key words.
  56. *
  57. * @link https://dev.mysql.com/doc/refman/8.0/en/keywords.html
  58. */
  59. private $reservedKeyWords = [
  60. 'accessible',
  61. 'add',
  62. 'admin',
  63. 'all',
  64. 'alter',
  65. 'analyze',
  66. 'and',
  67. 'as',
  68. 'asc',
  69. 'asensitive',
  70. 'before',
  71. 'between',
  72. 'bigint',
  73. 'binary',
  74. 'blob',
  75. 'both',
  76. 'by',
  77. 'call',
  78. 'cascade',
  79. 'case',
  80. 'change',
  81. 'char',
  82. 'character',
  83. 'check',
  84. 'collate',
  85. 'column',
  86. 'condition',
  87. 'constraint',
  88. 'continue',
  89. 'convert',
  90. 'create',
  91. 'cross',
  92. 'cube',
  93. 'cume_dist',
  94. 'current_date',
  95. 'current_time',
  96. 'current_timestamp',
  97. 'current_user',
  98. 'cursor',
  99. 'database',
  100. 'databases',
  101. 'day_hour',
  102. 'day_microsecond',
  103. 'day_minute',
  104. 'day_second',
  105. 'dec',
  106. 'decimal',
  107. 'declare',
  108. 'default',
  109. 'delayed',
  110. 'delete',
  111. 'dense_rank',
  112. 'desc',
  113. 'describe',
  114. 'deterministic',
  115. 'distinct',
  116. 'distinctrow',
  117. 'div',
  118. 'double',
  119. 'drop',
  120. 'dual',
  121. 'each',
  122. 'else',
  123. 'elseif',
  124. 'empty',
  125. 'enclosed',
  126. 'escaped',
  127. 'except',
  128. 'exists',
  129. 'exit',
  130. 'explain',
  131. 'false',
  132. 'fetch',
  133. 'first_value',
  134. 'float',
  135. 'float4',
  136. 'float8',
  137. 'for',
  138. 'force',
  139. 'foreign',
  140. 'from',
  141. 'fulltext',
  142. 'function',
  143. 'generated',
  144. 'get',
  145. 'grant',
  146. 'group',
  147. 'grouping',
  148. 'groups',
  149. 'having',
  150. 'high_priority',
  151. 'hour_microsecond',
  152. 'hour_minute',
  153. 'hour_second',
  154. 'if',
  155. 'ignore',
  156. 'in',
  157. 'index',
  158. 'infile',
  159. 'inner',
  160. 'inout',
  161. 'insensitive',
  162. 'insert',
  163. 'int',
  164. 'int1',
  165. 'int2',
  166. 'int3',
  167. 'int4',
  168. 'int8',
  169. 'integer',
  170. 'interval',
  171. 'into',
  172. 'io_after_gtids',
  173. 'io_before_gtids',
  174. 'is',
  175. 'iterate',
  176. 'join',
  177. 'json_table',
  178. 'key',
  179. 'keys',
  180. 'kill',
  181. 'lag',
  182. 'last_value',
  183. 'lead',
  184. 'leading',
  185. 'leave',
  186. 'left',
  187. 'like',
  188. 'limit',
  189. 'linear',
  190. 'lines',
  191. 'load',
  192. 'localtime',
  193. 'localtimestamp',
  194. 'lock',
  195. 'long',
  196. 'longblob',
  197. 'longtext',
  198. 'loop',
  199. 'low_priority',
  200. 'master_bind',
  201. 'master_ssl_verify_server_cert',
  202. 'match',
  203. 'maxvalue',
  204. 'mediumblob',
  205. 'mediumint',
  206. 'mediumtext',
  207. 'middleint',
  208. 'minute_microsecond',
  209. 'minute_second',
  210. 'mod',
  211. 'modifies',
  212. 'natural',
  213. 'not',
  214. 'no_write_to_binlog',
  215. 'nth_value',
  216. 'ntile',
  217. 'null',
  218. 'numeric',
  219. 'of',
  220. 'on',
  221. 'optimize',
  222. 'optimizer_costs',
  223. 'option',
  224. 'optionally',
  225. 'or',
  226. 'order',
  227. 'out',
  228. 'outer',
  229. 'outfile',
  230. 'over',
  231. 'partition',
  232. 'percent_rank',
  233. 'persist',
  234. 'persist_only',
  235. 'precision',
  236. 'primary',
  237. 'procedure',
  238. 'purge',
  239. 'range',
  240. 'rank',
  241. 'read',
  242. 'reads',
  243. 'read_write',
  244. 'real',
  245. 'recursive',
  246. 'references',
  247. 'regexp',
  248. 'release',
  249. 'rename',
  250. 'repeat',
  251. 'replace',
  252. 'require',
  253. 'resignal',
  254. 'restrict',
  255. 'return',
  256. 'revoke',
  257. 'right',
  258. 'rlike',
  259. 'row',
  260. 'rows',
  261. 'row_number',
  262. 'schema',
  263. 'schemas',
  264. 'second_microsecond',
  265. 'select',
  266. 'sensitive',
  267. 'separator',
  268. 'set',
  269. 'show',
  270. 'signal',
  271. 'smallint',
  272. 'spatial',
  273. 'specific',
  274. 'sql',
  275. 'sqlexception',
  276. 'sqlstate',
  277. 'sqlwarning',
  278. 'sql_big_result',
  279. 'sql_calc_found_rows',
  280. 'sql_small_result',
  281. 'ssl',
  282. 'starting',
  283. 'stored',
  284. 'straight_join',
  285. 'system',
  286. 'table',
  287. 'terminated',
  288. 'then',
  289. 'tinyblob',
  290. 'tinyint',
  291. 'tinytext',
  292. 'to',
  293. 'trailing',
  294. 'trigger',
  295. 'true',
  296. 'undo',
  297. 'union',
  298. 'unique',
  299. 'unlock',
  300. 'unsigned',
  301. 'update',
  302. 'usage',
  303. 'use',
  304. 'using',
  305. 'utc_date',
  306. 'utc_time',
  307. 'utc_timestamp',
  308. 'values',
  309. 'varbinary',
  310. 'varchar',
  311. 'varcharacter',
  312. 'varying',
  313. 'virtual',
  314. 'when',
  315. 'where',
  316. 'while',
  317. 'window',
  318. 'with',
  319. 'write',
  320. 'xor',
  321. 'year_month',
  322. 'zerofill',
  323. ];
  324. /**
  325. * Constructs a Connection object.
  326. */
  327. public function __construct(\PDO $connection, array $connection_options = []) {
  328. parent::__construct($connection, $connection_options);
  329. // This driver defaults to transaction support, except if explicitly passed FALSE.
  330. $this->transactionSupport = !isset($connection_options['transactions']) || ($connection_options['transactions'] !== FALSE);
  331. // MySQL never supports transactional DDL.
  332. $this->transactionalDDLSupport = FALSE;
  333. $this->connectionOptions = $connection_options;
  334. }
  335. /**
  336. * {@inheritdoc}
  337. */
  338. public function query($query, array $args = [], $options = []) {
  339. try {
  340. return parent::query($query, $args, $options);
  341. }
  342. catch (DatabaseException $e) {
  343. if ($e->getPrevious()->errorInfo[1] == 1153) {
  344. // If a max_allowed_packet error occurs the message length is truncated.
  345. // This should prevent the error from recurring if the exception is
  346. // logged to the database using dblog or the like.
  347. $message = Unicode::truncateBytes($e->getMessage(), self::MIN_MAX_ALLOWED_PACKET);
  348. $e = new DatabaseExceptionWrapper($message, $e->getCode(), $e->getPrevious());
  349. }
  350. throw $e;
  351. }
  352. }
  353. /**
  354. * {@inheritdoc}
  355. */
  356. public static function open(array &$connection_options = []) {
  357. if (isset($connection_options['_dsn_utf8_fallback']) && $connection_options['_dsn_utf8_fallback'] === TRUE) {
  358. // Only used during the installer version check, as a fallback from utf8mb4.
  359. $charset = 'utf8';
  360. }
  361. else {
  362. $charset = 'utf8mb4';
  363. }
  364. // The DSN should use either a socket or a host/port.
  365. if (isset($connection_options['unix_socket'])) {
  366. $dsn = 'mysql:unix_socket=' . $connection_options['unix_socket'];
  367. }
  368. else {
  369. // Default to TCP connection on port 3306.
  370. $dsn = 'mysql:host=' . $connection_options['host'] . ';port=' . (empty($connection_options['port']) ? 3306 : $connection_options['port']);
  371. }
  372. // Character set is added to dsn to ensure PDO uses the proper character
  373. // set when escaping. This has security implications. See
  374. // https://www.drupal.org/node/1201452 for further discussion.
  375. $dsn .= ';charset=' . $charset;
  376. if (!empty($connection_options['database'])) {
  377. $dsn .= ';dbname=' . $connection_options['database'];
  378. }
  379. // Allow PDO options to be overridden.
  380. $connection_options += [
  381. 'pdo' => [],
  382. ];
  383. $connection_options['pdo'] += [
  384. \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
  385. // So we don't have to mess around with cursors and unbuffered queries by default.
  386. \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
  387. // Make sure MySQL returns all matched rows on update queries including
  388. // rows that actually didn't have to be updated because the values didn't
  389. // change. This matches common behavior among other database systems.
  390. \PDO::MYSQL_ATTR_FOUND_ROWS => TRUE,
  391. // Because MySQL's prepared statements skip the query cache, because it's dumb.
  392. \PDO::ATTR_EMULATE_PREPARES => TRUE,
  393. ];
  394. if (defined('\PDO::MYSQL_ATTR_MULTI_STATEMENTS')) {
  395. // An added connection option in PHP 5.5.21 to optionally limit SQL to a
  396. // single statement like mysqli.
  397. $connection_options['pdo'] += [\PDO::MYSQL_ATTR_MULTI_STATEMENTS => FALSE];
  398. }
  399. try {
  400. $pdo = new \PDO($dsn, $connection_options['username'], $connection_options['password'], $connection_options['pdo']);
  401. }
  402. catch (\PDOException $e) {
  403. if ($e->getCode() == static::DATABASE_NOT_FOUND) {
  404. throw new DatabaseNotFoundException($e->getMessage(), $e->getCode(), $e);
  405. }
  406. if ($e->getCode() == static::ACCESS_DENIED) {
  407. throw new DatabaseAccessDeniedException($e->getMessage(), $e->getCode(), $e);
  408. }
  409. throw $e;
  410. }
  411. // Force MySQL to use the UTF-8 character set. Also set the collation, if a
  412. // certain one has been set; otherwise, MySQL defaults to
  413. // 'utf8mb4_general_ci' (MySQL 5) or 'utf8mb4_0900_ai_ci' (MySQL 8) for
  414. // utf8mb4.
  415. if (!empty($connection_options['collation'])) {
  416. $pdo->exec('SET NAMES ' . $charset . ' COLLATE ' . $connection_options['collation']);
  417. }
  418. else {
  419. $pdo->exec('SET NAMES ' . $charset);
  420. }
  421. // Set MySQL init_commands if not already defined. Default Drupal's MySQL
  422. // behavior to conform more closely to SQL standards. This allows Drupal
  423. // to run almost seamlessly on many different kinds of database systems.
  424. // These settings force MySQL to behave the same as postgresql, or sqlite
  425. // in regards to syntax interpretation and invalid data handling. See
  426. // https://www.drupal.org/node/344575 for further discussion. Also, as MySQL
  427. // 5.5 changed the meaning of TRADITIONAL we need to spell out the modes one
  428. // by one.
  429. $connection_options += [
  430. 'init_commands' => [],
  431. ];
  432. $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';
  433. // NO_AUTO_CREATE_USER is removed in MySQL 8.0.11
  434. // https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html#mysqld-8-0-11-deprecation-removal
  435. $version_server = $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION);
  436. if (version_compare($version_server, '8.0.11', '<')) {
  437. $sql_mode .= ',NO_AUTO_CREATE_USER';
  438. }
  439. $connection_options['init_commands'] += [
  440. 'sql_mode' => "SET sql_mode = '$sql_mode'",
  441. ];
  442. // Execute initial commands.
  443. foreach ($connection_options['init_commands'] as $sql) {
  444. $pdo->exec($sql);
  445. }
  446. return $pdo;
  447. }
  448. /**
  449. * {@inheritdoc}
  450. */
  451. public function escapeField($field) {
  452. $field = parent::escapeField($field);
  453. return $this->quoteIdentifier($field);
  454. }
  455. /**
  456. * {@inheritdoc}
  457. */
  458. public function escapeAlias($field) {
  459. // Quote fields so that MySQL reserved words like 'function' can be used
  460. // as aliases.
  461. $field = parent::escapeAlias($field);
  462. return $this->quoteIdentifier($field);
  463. }
  464. /**
  465. * Quotes an identifier if it matches a MySQL reserved keyword.
  466. *
  467. * @param string $identifier
  468. * The field to check.
  469. *
  470. * @return string
  471. * The identifier, quoted if it matches a MySQL reserved keyword.
  472. */
  473. private function quoteIdentifier($identifier) {
  474. // Quote identifiers so that MySQL reserved words like 'function' can be
  475. // used as column names. Sometimes the 'table.column_name' format is passed
  476. // in. For example,
  477. // \Drupal\Core\Entity\Sql\SqlContentEntityStorage::buildQuery() adds a
  478. // condition on "base.uid" while loading user entities.
  479. if (strpos($identifier, '.') !== FALSE) {
  480. list($table, $identifier) = explode('.', $identifier, 2);
  481. }
  482. if (in_array(strtolower($identifier), $this->reservedKeyWords, TRUE)) {
  483. // Quote the string for MySQL reserved keywords.
  484. $identifier = '"' . $identifier . '"';
  485. }
  486. return isset($table) ? $table . '.' . $identifier : $identifier;
  487. }
  488. /**
  489. * {@inheritdoc}
  490. */
  491. public function serialize() {
  492. // Cleanup the connection, much like __destruct() does it as well.
  493. if ($this->needsCleanup) {
  494. $this->nextIdDelete();
  495. }
  496. $this->needsCleanup = FALSE;
  497. return parent::serialize();
  498. }
  499. /**
  500. * {@inheritdoc}
  501. */
  502. public function __destruct() {
  503. if ($this->needsCleanup) {
  504. $this->nextIdDelete();
  505. }
  506. }
  507. public function queryRange($query, $from, $count, array $args = [], array $options = []) {
  508. return $this->query($query . ' LIMIT ' . (int) $from . ', ' . (int) $count, $args, $options);
  509. }
  510. public function queryTemporary($query, array $args = [], array $options = []) {
  511. $tablename = $this->generateTemporaryTableName();
  512. $this->query('CREATE TEMPORARY TABLE {' . $tablename . '} Engine=MEMORY ' . $query, $args, $options);
  513. return $tablename;
  514. }
  515. public function driver() {
  516. return 'mysql';
  517. }
  518. public function databaseType() {
  519. return 'mysql';
  520. }
  521. /**
  522. * Overrides \Drupal\Core\Database\Connection::createDatabase().
  523. *
  524. * @param string $database
  525. * The name of the database to create.
  526. *
  527. * @throws \Drupal\Core\Database\DatabaseNotFoundException
  528. */
  529. public function createDatabase($database) {
  530. // Escape the database name.
  531. $database = Database::getConnection()->escapeDatabase($database);
  532. try {
  533. // Create the database and set it as active.
  534. $this->connection->exec("CREATE DATABASE $database");
  535. $this->connection->exec("USE $database");
  536. }
  537. catch (\Exception $e) {
  538. throw new DatabaseNotFoundException($e->getMessage());
  539. }
  540. }
  541. public function mapConditionOperator($operator) {
  542. // We don't want to override any of the defaults.
  543. return NULL;
  544. }
  545. public function nextId($existing_id = 0) {
  546. $new_id = $this->query('INSERT INTO {sequences} () VALUES ()', [], ['return' => Database::RETURN_INSERT_ID]);
  547. // This should only happen after an import or similar event.
  548. if ($existing_id >= $new_id) {
  549. // If we INSERT a value manually into the sequences table, on the next
  550. // INSERT, MySQL will generate a larger value. However, there is no way
  551. // of knowing whether this value already exists in the table. MySQL
  552. // provides an INSERT IGNORE which would work, but that can mask problems
  553. // other than duplicate keys. Instead, we use INSERT ... ON DUPLICATE KEY
  554. // UPDATE in such a way that the UPDATE does not do anything. This way,
  555. // duplicate keys do not generate errors but everything else does.
  556. $this->query('INSERT INTO {sequences} (value) VALUES (:value) ON DUPLICATE KEY UPDATE value = value', [':value' => $existing_id]);
  557. $new_id = $this->query('INSERT INTO {sequences} () VALUES ()', [], ['return' => Database::RETURN_INSERT_ID]);
  558. }
  559. $this->needsCleanup = TRUE;
  560. return $new_id;
  561. }
  562. public function nextIdDelete() {
  563. // While we want to clean up the table to keep it up from occupying too
  564. // much storage and memory, we must keep the highest value in the table
  565. // because InnoDB uses an in-memory auto-increment counter as long as the
  566. // server runs. When the server is stopped and restarted, InnoDB
  567. // reinitializes the counter for each table for the first INSERT to the
  568. // table based solely on values from the table so deleting all values would
  569. // be a problem in this case. Also, TRUNCATE resets the auto increment
  570. // counter.
  571. try {
  572. $max_id = $this->query('SELECT MAX(value) FROM {sequences}')->fetchField();
  573. // We know we are using MySQL here, no need for the slower db_delete().
  574. $this->query('DELETE FROM {sequences} WHERE value < :value', [':value' => $max_id]);
  575. }
  576. // During testing, this function is called from shutdown with the
  577. // simpletest prefix stored in $this->connection, and those tables are gone
  578. // by the time shutdown is called so we need to ignore the database
  579. // errors. There is no problem with completely ignoring errors here: if
  580. // these queries fail, the sequence will work just fine, just use a bit
  581. // more database storage and memory.
  582. catch (DatabaseException $e) {
  583. }
  584. }
  585. /**
  586. * Overridden to work around issues to MySQL not supporting transactional DDL.
  587. */
  588. protected function popCommittableTransactions() {
  589. // Commit all the committable layers.
  590. foreach (array_reverse($this->transactionLayers) as $name => $active) {
  591. // Stop once we found an active transaction.
  592. if ($active) {
  593. break;
  594. }
  595. // If there are no more layers left then we should commit.
  596. unset($this->transactionLayers[$name]);
  597. if (empty($this->transactionLayers)) {
  598. if (!$this->connection->commit()) {
  599. throw new TransactionCommitFailedException();
  600. }
  601. }
  602. else {
  603. // Attempt to release this savepoint in the standard way.
  604. try {
  605. $this->query('RELEASE SAVEPOINT ' . $name);
  606. }
  607. catch (DatabaseExceptionWrapper $e) {
  608. // However, in MySQL (InnoDB), savepoints are automatically committed
  609. // when tables are altered or created (DDL transactions are not
  610. // supported). This can cause exceptions due to trying to release
  611. // savepoints which no longer exist.
  612. //
  613. // To avoid exceptions when no actual error has occurred, we silently
  614. // succeed for MySQL error code 1305 ("SAVEPOINT does not exist").
  615. if ($e->getPrevious()->errorInfo[1] == '1305') {
  616. // If one SAVEPOINT was released automatically, then all were.
  617. // Therefore, clean the transaction stack.
  618. $this->transactionLayers = [];
  619. // We also have to explain to PDO that the transaction stack has
  620. // been cleaned-up.
  621. $this->connection->commit();
  622. }
  623. else {
  624. throw $e;
  625. }
  626. }
  627. }
  628. }
  629. }
  630. }
  631. /**
  632. * @} End of "addtogroup database".
  633. */