Integrity constraint violation

Is that MySQL or something else?

sah62, There's some history to this issue which I'm not personally familiar with and haven't had adequate time to investigate. While it's not a widely reported problem, those who have reported it seemed repeatedly plagued by it. I've been busy with the day job and it may be some time before I can look into this further. In the meantime, could you submit a patch file? I'd like to try it out.

Yes, I know. I committed it earlier today but haven't pushed it up, yet. Slipped out to the movies. Coming soon.

This has been committed to 7.x-1.x-dev and is in 7.x-1.0-rc6.

Thanks for this patch (from nearly a year ago). Sorry it took me so long to get it added.

tl;dr: Is there a reason that you aren't attempting to catch any exceptions, like the code on the Drupal docs page for transactions (https://drupal.org/node/355875) shows?

Full Post:
I'm using the latest version of the module 7.x-1.0, with a date of 2012-Nov-21.
I am having similar issues as sah62, specifically I'm sending multiple emails via drush when this issue occurs, and then they stop getting sent when the problem occurs:

Drush command terminated abnormally due to an unrecoverable error. [error]
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '127.0.0.1' 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] => 127.0.0.1
[:db_insert_placeholder_1] => 0
[:db_insert_placeholder_2] => 1391502301
)
in _httpbl_cache_set() (line 607 of /var/www/html/sites/all/modules/httpbl/httpbl.module).

My guess is that the following is happening:
cron deletes the 127.0.0.1 entry from httpbl table.
Then, multiple emails getting sent at the same time from my drush command causes multiple httpbl lookups.
httpbl doesn't have anything in the httpbl table (as the entry was just deleted by cron), so the httpbl module makes multiple httpbl table insert attempts for 127.0.0.1, one for each email being sent, all at the same time.
Since IP is the primary key for all of these insert attempts, all but one of these insert attempts fail, throwing exceptions which crash running Drupal sessions and anything drupal is currently doing..... such as sending my emails via drush.
That's just my guess as to whats happening... but if anyone has a different hypothesis, I'm all ears.

Is there a reason that you aren't attempting to catch any exceptions, like the code on the Drupal docs page for transactions (https://drupal.org/node/355875) shows?

We have the latest version installed (7.x-1.0), have checked the code and see that the patch has been applied, but unfortunately we are still getting this error.

Someone else has also reported this problem, their request should maybe be marked duplicate and this one re-opened: https://www.drupal.org/node/2311539

I am using the latest version of the module. However, I still experience this problem. Here it is my log:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '205.144.215.109' 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] => 205.144.215.109 [:db_insert_placeholder_1] => 2 [:db_insert_placeholder_2] => 1412442511 ) in _httpbl_cache_set() (line 607 of /public_html/sites/all/modules/httpbl/httpbl.module).

Same here, just different IPs (and 1416174181), even after patch aplication.

Add me to the list. Dev version doesn't help either.

I too facing the same problem. I'm using latest version of this module.

I also got this error, everyday. latest version.Using Http:BL 7.x-1.0

Same as everyone else.

TYPE	php
DATE	Thursday, March 12, 2015 - 08:29
USER	Anonymous (not verified)
LOCATION	http://townscanada.com/
REFERRER	http://townscanada.com/
MESSAGE	PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '46.191.149.210' 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] => 46.191.149.210 [:db_insert_placeholder_1] => 0 [:db_insert_placeholder_2] => 1426184990 ) in _httpbl_cache_set() (line 607 of /home/townscan/public_html/sites/all/modules/httpbl/httpbl.module).
SEVERITY	error
HOSTNAME	46.191.149.210
OPERATIONS	

I'm also getting this error occasionally.

I noticed the code causing the error is a db_merge() call, which should work. I'm guessing that means that there's a concurrency issue. I guess we can use Drupal's Locking mechanism to ensure two requests don't try to run a db_merge() query at the same time.

I'll look into writing a patch tomorrow, but I'm leaving the issue unassigned in case someone else gets to it first :P

Adding "Needs issue summary update" tag since the issue summary doesn't conform to the template.
Unassigning @bryrock as he's been assigned for ~3 years.
Updating Version as I'm seeing this on 7.x-1.0.

@mparker17, I actually do check in now and then, but if you've got a notion to write a patch, then please go for it.

Also wondering if this might relate to #2373967: Masquerading leads to deadlock - Incompatibility with masquerade module!, because otherwise I find that issue un-reproduce-able.

I got the same error.
I made a path and the error stopped happening.

Could you help me test?

I'll give the patch a try in just a bit... we're seeing this error quite regularly in our logs.

We've been running patch #24 on a live site for the past 10 days and have not seen the integrity constraint violation again. Previously we were seeing it at least 2-3 times per day.

I'm marking as 'needs review' to encourage others to test. Thanks for the patch!

I am getting this problem as well. It happens occasionally on the site and I get the errors in the Drupal logs. I can give the log if needed.

I added the patch #24 and the last 2 weeks we didn't see the path_integrity_constraint_violation anymore. Looks to work fine now.

Can someone push this patch to a release????

@netgeek123, if the patch is working for you, mark it RTBC.

Module owners aren't going to release something that is still "Needs review."

This patch works perfectly. Thank you!

I'll agree to call this "fixed." I think this code has about gone full circle. If you look back at past commits, going back to D6, this issue was #989056 and it was fixed with a db_query REPLACE (commit c75cfe8b8004e3e654623041501c978cc7f98328).

And then it blew up on somebody, again, and then it got changed to a merge, and so on, and so on.

Historically, and in theory, the only time a record got replaced was if a greylisted IP failed the challenge and became blacklisted. Otherwise, an IP either got added because they weren't in the table, or not added because they were found, and that was all that needed to happen. I think one of the problems has been that the table never really used any sort of index other than the IP address (string).

Anyway, if anyone is interested there is (finally) a D8 dev release. It's all new and radically different. The old records are now manageable "Host' entities, with core services handling the entity and storage management (no more hand-painted SQL).

The old Views reports are gone, and have been replaced by an Admin View where you can manage the hosts (if you're so inclined), including a bunch of bulk actions.

And the logging logic is all out of the way of the mainline logic. You want to log a debug message, just go ahead and do it, and a LogTrapper service then decides whether or not to actually send it, based on the config settings.

Fix included in release candidate.