I've seen this error pop up at very odd times, but it seems to happen most of all when clearing caches, I have no idea what series of steps it takes to reproduce but thought it was worth reporting. The full error is:

WD php: PDOException: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block: SELECT 1 AS expression
FROM 
{variable} variable
WHERE ( (name = :db_condition_placeholder_0) ) FOR UPDATE; Array
(
    [:db_condition_placeholder_0] => rules_empty_sets
)
 in variable_set() (line 972 of /var/www/drupal/ec-internet/releases/20121113-3/includes/bootstrap.inc).
Drush command terminated abnormally due to an unrecoverable error.

Any help or advice on how to get to the bottom of this would be great

Comments

wiifm’s picture

wiifm
Primary language English
Location Wellington, NZ
commented

Also seeing this in the logs, this is causing 500's on the site every now and then

PDOException: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block: SELECT 1 AS expression FROM {variable} variable WHERE ( (name = :db_condition_placeholder_0) ) FOR UPDATE; Array ( [:db_condition_placeholder_0] => rules_empty_sets ) in variable_set() (line 971 of /var/www/drupal/mts/releases/20130423-1/includes/bootstrap.inc).

Any thoughts on this?

davidwhthomas’s picture

davidwhthomas commented

Perhaps some locking issue when deleting and creating that variable in a transaction when using SELECT FOR UPDATE.

See the following in rules.module ~line 336

/**
 * Implements hook_flush_caches().
 */
function rules_flush_caches() {
  variable_del('rules_empty_sets');
  return array('cache_rules');
}

/**
 * Clears the rule set cache
 */
function rules_clear_cache() {
  cache_clear_all('*', 'cache_rules', TRUE);
  variable_del('rules_empty_sets');
  drupal_static_reset('rules_get_cache');
  drupal_static_reset('rules_fetch_data');
  drupal_static_reset('rules_config_update_dirty_flag');
  entity_get_controller('rules_config')->resetCache();
}

A little odd that rules calls variable_del in hook_flush_caches, suggests a link with the issue occurring during cache clear.

Perhaps Rules should store the rules_empty_sets cache in cache_rules instead of the variables table.

jweowu’s picture

jweowu commented

Just a note that with the "failed sql transaction ... current transaction is aborted" error, you may need to check the database's own logs to find out what actually triggered the problem, as there may well be nothing wrong with the query displayed in Drupal's error message.

acbramley’s picture

acbramley commented
Title: WD php: PDOException: SQLSTATE[25P02]: In failed sql transaction when clearing cache » PDOException: In failed sql transaction when clearing cache duplicate key value violates unique constraint "variable_pkey"
Project: Rules » Drupal core
Version: 7.x-2.2 » 7.22
Component: Rules Core » postgresql database
Priority: Normal » Major

From the db logs:

2013-08-09 11:05:21 NZST [my-db] ( 144713040) ERROR:  duplicate key value violates unique constraint "variable_pkey"
2013-08-09 11:05:21 NZST [my-db] ( 144713040) DETAIL:  Key (name)=(rules_empty_sets) already exists.
2013-08-09 11:05:21 NZST [my-db] ( 144713040) STATEMENT:  INSERT INTO variable (name, value) VALUES ('rules_empty_sets', '\xkasndkansddAWHOLEBUNCHOFDATA')
2013-08-09 11:05:21 NZST [my-db] ( 144713040) ERROR:  current transaction is aborted, commands ignored until end of transaction block
2013-08-09 11:05:21 NZST [my-db] ( 144713040) STATEMENT:  SELECT 1 AS expression
  FROM 
  variable variable
  WHERE ( (name = 'rules_empty_sets') ) FOR UPDATE

So the error is actually the INSERT. This means that variable_set() which calls db_merge is trying to insert an existing key. This is almost certainly a race condition since db_merge would do an update if the key existed? I have a feeling this is not Rules at all but I do not know where to best move this to.

I'm also currently unable to reproduce it with a set number of steps. This however occurs mostly on our release cycles where we run a series of drush commands. This time it happened after a drush cc all, there was then a drush fr feature_name and this is where the error occured

acbramley’s picture

acbramley commented
Project: Drupal core » Rules
Version: 7.22 » 7.x-2.x-dev
Component: postgresql database » Rules Core

#1555634: Improve performance of event cache by moving to a whitelist looks like it's removed the variable_set type functionality so this issue may have been mitigated by that. Moving back to rules as I think this is a rules issue.

davidwhthomas’s picture

davidwhthomas commented
Issue summary: View changes
Related issues: +#2190775: Misuse of variables table with rules_event_whitelist

Follow up to my note in #2

"Perhaps Rules should store the rules_empty_sets (now rules_event_whitelist) cache in cache_rules instead of the variables table."

I've created a patch for a related deadlock / variable table issue to move rules_event_whitelist from the variable table to cache_rules that may well resolve this issue as well.

Please review: https://drupal.org/comment/8516509#comment-8516509

cmonnow’s picture

cmonnow commented

Just out of interest I was wondering if running update.php resolved this issue for anyone. I was facing an alternate PDOException: SQLSTATE[25P02] error with another table (cache_update) that would only occur the first time after I cleared the cache (using drush cc all), but appears to work for now following update.php.

acbramley’s picture

acbramley commented

@cmonnow I experience this error intermittently, but usually on a cache clear, however I haven't encountered it in a while

tr’s picture

tr commented
Status: Active » Closed (cannot reproduce)

Haven't had a report of this error for more than five years now. The issues cited in #5 and #6, #1555634: Improve performance of event cache by moving to a whitelist and #2190775: Misuse of variables table with rules_event_whitelist, have both been committed. For these reasons, I'm going to assume that fixed the problem.

If this continues to be a problem for you, please open a new issue.