|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #64003 trx_stickiness and SAVEPOINT
Submitted: 2013-01-16 09:05 UTC Modified: 2013-06-07 10:06 UTC
From: ilari at exove dot com Assigned: mysql (profile)
Status: Closed Package: mysqlnd_ms (PECL)
PHP Version: 5.4.10 OS: Linux
Private report: No CVE-ID: None
 [2013-01-16 09:05 UTC] ilari at exove dot com
trx_stickiness doesn't seem to detect SAVEPOINT statement as a start of a new transaction thus trx_stickiness doesn't have an affect. Drupal 7 uses SAVEPOINT to implement nested transactions.

mysqlnd_ms version in use: 1.4.2
PHP version 5.4.10
MySQL 5.5.29 with InnoDB


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2013-01-16 10:12 UTC] ilari at exove dot com
I'm not sure (have not tested) but maybe this problem can be reproduced by installing Drupal 7 and configuring apachesolr Drupal module as a default search engine. Then you edit a content node entry and press Save button. What happens then is script execution is frozen for 50 seconds and you get PDO exception from InnoDB stating "Lock wait timeout exceeded; try restarting transaction". It seems that SELECT...FOR UPDATE somehow fails when mysqlnd_ms send the statement to the slave connection. SELECT...FOR UPDATE works only inside a transaction and mysqlnd_ms should recognize that SAVEPOINT starts a new transaction and everything after SAVEPOINT should go to a master connection, including SELECT...FOR UPDATE statements.
 [2013-01-16 10:14 UTC] ilari at exove dot com
So we expected that trx_stickiness would have resolved this issue but apparently it didn't. This is why I suspect SAVEPOINT is not recognized by mysqlnd_ms as a starting point for a new transaction.
 [2013-01-30 11:59 UTC]
-Assigned To: +Assigned To: mysql
 [2013-01-30 11:59 UTC]

mysqlnd_ms does not monitor your SQL statements. It monitors API calls only to decide on transaction boundaries, see below. We should strive for a programmatic solution based on function calls that can be monitored.

Parsing each and every SQL statement to search for SAVEPOINT may turn out error prone. mysqlnd_ms does not contain a full-fledged SQL parser only a tokenizer. What happens if one tries to utilize a not full-fledged SQL parser can be seen elsewhere in PHP (PDO).Thus, parsing is most likely no option. 


As of PHP 5.4.0 the mysqlnd library allows the plugin to monitor the autocommit mode set by calls to the libraries set_autocommit() function. If setting set_stickiness=master and autocommit gets disabled by a PHP MySQL extension invoking the mysqlnd library internal function call set_autocommit(), the plugin is made aware of the begin of a transaction. Then, the plugin stops load balancing and directs all statements to the master server until autocommit is enabled. Thus, no SQL hints are required.

An example of a PHP MySQL API function calling the mysqlnd library internal function call set_autocommit() is mysqli_autocommit().

Although setting ser_stickiness=master, the plugin cannot be made aware of autocommit mode changes caused by SQL statements such as SET AUTOCOMMIT=0.
 [2013-02-07 11:28 UTC]

this is not a bug in PECL/mysqlnd_ms. This is something between wrong usage and inconsistent PDO_MySQL implementation.

As explained in the PECL/mysqlnd_ms documentation, the plugin will not parse SQL at runtime to detect transaction boundaries. It makes no attempt to detect statements such as COMMIT, START TRANSACTION, SAVEPOINT or ROLLBACK. Instead, it monitors MySQL Client Server C library calls (in mysqlnd) to detect transaction boundaries. 

Drupal 7.19 begins a transaction using PDO::beginTransaction. PDO::beginTransaction goes down to ext/pdo_mysql/mysql_driver.c:mysql_handle_begin(pdo_dbh_t *dbh TSRMLS_DC) and there you find exactly what is NOT supported by MS:

/* {{{ mysql_handle_begin */
static int mysql_handle_begin(pdo_dbh_t *dbh TSRMLS_DC)
        PDO_DBG_INF_FMT("dbh=%p", dbh);
        PDO_DBG_RETURN(0 <= mysql_handle_doer(dbh, ZEND_STRL("START TRANSACTION") TSRMLS_CC));

As you can see, PDO_MySQL is using START TRANSACTION to begin is transaction. Because MS does not monitor SQL, it will not be made aware of the start of a transaction. Thus, load balancing will continue. Depending on the MS configuration read write splitting will also continue - to make a long story short: chaos...

Unfortunately the MySQL Client Server protocol does not properly announce transaction boundaries, so MS cannot use it. MS will not try to detect trx boundaries inspecting SQL - I would expect it to be error prone. In sum: MS is forced to monitor C API calls or needs to be hinted by the user. 

Again, PDO_MySQL does not use a C API call to start a transaction and MS is fooled. Workaround: use mysqli or wait for new PHP version with improved PDO_MySQL.

SAVEPOINT has nothing to do with the actual issue. START TRANSACTION comes first and marks the point from which on load balancing must not happen any more.

On an aside, the way Drupal uses SAVEPOINT could be improved. It would be better if Drupal used some explicit error handling upon setting a SAVEPOINT:

nixnutz@linux-fuxh:~/www/drupal-7.19/includes/database> grep -n -R -C10  "'SAVEPOINT"
1065-  public function pushTransaction($name) {
1066-    if (!$this->supportsTransactions()) {
1067-      return;
1068-    }
1069-    if (isset($this->transactionLayers[$name])) {
1070-      throw new DatabaseTransactionNameNonUniqueException($name . " is already in use.");
1071-    }
1072-    // If we're already in a transaction then we want to create a savepoint
1073-    // rather than try to create another transaction.
1074-    if ($this->inTransaction()) {
1075:      $this->query('SAVEPOINT ' . $name);
1076-    }
1077-    else {
1078-      parent::beginTransaction();
1079-    }
1080-    $this->transactionLayers[$name] = $name;
1081-  }
1083-  /**
1084-   * Decreases the depth of transaction nesting.
1085-   *

If the save guard in line 1069 would not exist or ever turns out to be buggy and autocommit mode is used it may happen that an attempt to set a SAVEPOINT is made but the SAVEPOINT is not actually set. Together with the SQL mode used by Drupal nothing but a warning would be returned from MySQL. The failure of setting the SAVEPOINT would most likely not be noted by Drupal. Whatever, this is unrelated to MS and PDO.

 [2013-03-14 15:46 UTC]
PHP 5.5.0 and PECL/mysqlnd_ms 1.5alpha (to be released) feature improved API based transaction boundary detection if using mysqli. 

My motivation fiddling with PDO, which has not seen much attention and care for a good while in general, is somewhat limited. Thus, alpha will not bringt the same improvements for PDO_MySQL. Maybe in beta...

Workaround: put some PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, ...) in the Drupal code. setAttribute() is monitored by PECL/mysqlnd_ms 1.4 with PHP 5.4.0. Disabling autocommit will stop load balancing until autocommit is turned on again, if trx_stickiness is set to trx_stickiness=master.
 [2013-06-07 10:06 UTC]
-Status: Assigned +Status: Closed
 [2013-06-07 10:06 UTC]
Thank you for your bug report. This issue has already been fixed
in the latest released version of PHP, which you can download at

See explanations and latest release.
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Mon Jul 06 02:01:28 2020 UTC