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.

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.

Hi!

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. 

---------------

http://www.php.net/manual/en/mysqlnd-ms.plugin-ini-json.php#ini.mysqlnd-ms-plugin-config-v2.trx-stickiness


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.

Hi, 

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_ENTER("mysql_handle_quoter");
        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" database.inc
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-  }
1082-
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.

Ulf

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.

Thank you for your bug report. This issue has already been fixed
in the latest released version of PHP, which you can download at 
http://www.php.net/downloads.php

See explanations and latest release.