Drupal 7.27

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 {httpbl} httpbl WHERE ( (hostname = :db_condition_placeholder_0) ); Array ( [:db_condition_placeholder_0] => 192.99.36.201 ) in _httpbl_cache_set() (linea 607 di /var/www/html/drupal/modules/httpbl/httpbl.module).

599 function _httpbl_cache_set($ip, $status, $offset = 0) {
600 // DELETE and then INSERT leads to race conditions #723358: PHP Warning: Duplicate entry <snip> for key 1\nquery: INSERT INTO httpbl (hostname, status, expire) VALUES <snip>.
601 // evidence says transactions are required -- deekayen
602 $txn = db_transaction();
603
604 db_merge('httpbl')
605 ->key(array('hostname' => $ip))
606 ->fields(array('status' => $status, 'expire' => REQUEST_TIME + $offset))
607 ->execute();

[root@server1 ~]# uname -a
Linux server1.xxx.xxx 2.6.32-431.17.1.el6.x86_64 #1 SMP Wed May 7 23:32:49 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
[root@server1 ~]# rpm -qa |grep postgresql-server
postgresql-server-8.4.20-1.el6_5.x86_64

Comments

giulix’s picture

giulix CreditAttribution: giulix commented
Issue summary: View changes
giulix’s picture

giulix CreditAttribution: giulix commented
Title: Postgres error 25P02 » PostgreSQL error 25P02
bryrock’s picture

bryrock CreditAttribution: bryrock commented
Status: Active » Postponed (maintainer needs more info)

Can you provide any more information?

giulix’s picture

giulix CreditAttribution: giulix commented

Sure, just tell me what you need.

nubeli’s picture

nubeli CreditAttribution: nubeli commented

I think there's a similar sql error for mysql. httpbl tries to create a duplicate key instead of updating I suppose.

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5.248.83.92' for key 'PRIMARY': INSERT INTO {httpbl} (hostname, status, expire) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array ( [:db_insert_placeholder_0] => 5.248.83.92 [:db_insert_placeholder_1] => 0 [:db_insert_placeholder_2] => 1401351409 ) in _httpbl_cache_set() (line 607 of /var/www/vhosts/aohc.org/httpdocs/sites/all/modules/contrib/httpbl/httpbl.module).

capogeannis’s picture

capogeannis CreditAttribution: capogeannis commented

Experiencing the same. Any ideas on a fix?

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'xxx.xxx.xxx.xx' for key 'PRIMARY': INSERT INTO {httpbl} (hostname, status, expire) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array ( [:db_insert_placeholder_0] => xxx.xxx.xxx.xx [:db_insert_placeholder_1] => 0 [:db_insert_placeholder_2] => 1403834650 ) in _httpbl_cache_set() (line 607 of /srv/bindings/3edc1853fbe546328711ba260f34d365/code/sites/all/modules/httpbl/httpbl.module).

Liam Morland’s picture

Liam Morland
Primary language English
Location Ontario, CA 🇨🇦
CreditAttribution: Liam Morland commented
Issue tags: -postgresql PDOException 25P02 +postgresql PDOException, +PostgreSQL
bryrock’s picture

bryrock CreditAttribution: bryrock as a volunteer commented
Version: 7.x-1.0 » 7.x-1.1-rc1
Status: Postponed (maintainer needs more info) » Needs review

This may be a duplicate of #1343432: Integrity constraint violation and/or #989056: Drupal 7 port?. In the history of this module, going back at least to initial dev release of D6, I think changes to this update function have about come full circle. There never seems to have been any "guaranteed" solution that lives peacefully in both the mySQL and PostgreSQL worlds. It gets fixed for one and then issues come in for the other.

In the mainline logic, the only time any update ever occurs is after an already stored grey-listed user fails the white-list challenge, and is then blacklisted. Otherwise, a record either already exists for a repeat visit IP and is only read, or it's a new visit (or new since last expiration) and gets added.

I agree it's a real issue when it happens, and I have seen it myself only one or two times in a busy mySQL production environment, and while it's certainly easy to reproduce the grey-listed to black-listed scenario, I've never been able to make it blow up in a mySQL environment, and have never tried it (with any of the past "fixes") in PostgreSQL.

So, for what it's worth, there is a release candidate out (7.x-1.1-rc1) that includes the patch for #1343432: Integrity constraint violation.