When upgrading to 7.x-1.2 I get the following error during updb:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'engagement_score' cannot be null
Performed update: redhen_engagement_update_7101

The problem lies in the UPDATE statement:

UPDATE redhen_contact rc SET engagement_score = (SELECT SUM(score) FROM redhen_engagement re JOIN redhen_engagement_score res on re.engagement_score = res.name WHERE re.contact_id = rc.contact_id)

The inner select return NULL if there is no engagements logged for a contact. Locally I've solved this by adding an COALESCE(…, 0) around the inner select.

The statement takes a long time for larger databases (perhaps due to the inner select). Maybe it's better to use a JOIN and avoid NULL values altogether.

CommentFileSizeAuthor
#2 0001-Use-COALESCE-in-7101.patch1.34 KBtorotil
#1 redhen_engagement_integrity_constraint_7101.patch863 bytestorotil

Comments

torotil’s picture

torotil commented
Status: Active » Needs review
StatusFileSize
new redhen_engagement_integrity_constraint_7101.patch863 bytes

I've found a rather dumb reason for the performance issues (in my drupal setup).

Here's a patch that fixes this bug by introducing COALESCE().

torotil’s picture

torotil commented
StatusFileSize
new 0001-Use-COALESCE-in-7101.patch1.34 KB

Here is a patch that makes it possible to rerun the failed (perhaps due to this bug) update hook.

alicemoon’s picture

alicemoon commented

thanks torotil - I had the same problem and your patch allowed my site to update properly.

levelos’s picture

levelos commented
Status: Needs review » Fixed

Thanks torotil, committed in 832217f.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

  • Commit 832217f on 7.x-1.x, tests, redhen-donation authored by torotil, committed by levelos: 
    #1983336 by torotil: Integrity constraint violation  caused by update...

  • Commit 832217f on 7.x-1.x, tests, redhen-donation, relation-roles authored by torotil, committed by levelos: 
    #1983336 by torotil: Integrity constraint violation  caused by update...