Runaway Civi dedupe query on first 5 chars of street_address field
Closed, ResolvedPublic1 Estimated Story Points
Actions

Assigned To

Authored By

	Ejegg
	Dec 5 2019, 10:28 PM

Description

Caused deadlocks for 15 min or so around 22:00 UTC on Dec 5, 2109. @MBeat33 is asking the DS rep involved how they invoked this:

/* User : 148 */INSERT INTO civicrm_tmp_e_dedupe_f34a360ff1fd447f72009bb37b366904  (id1, id2, weight) SELECT id1, id2, weight FROM (SELECT t1.contact_id id1, t2.contact_id id2, 5 weight FROM civicrm_address t1 JOIN civicrm_address t2 ON (SUBSTR(t1.street_address, 1, 5) = SUBSTR(t2.street_address, 1, 5) AND t1.location_type_id = t2.location_type_id) WHERE t1.contact_id < t2.contact_id AND t1.contact_id IN (19222291,25665376,25995762,28032136,34432770,34846871,35144886,36900303)
        UNION SELECT t1.contact_id id1, t2.contact_id id2, 5 weight FROM civicrm_address t1 JOIN civicrm_address t2 ON (SUBSTR(t1.street_address, 1, 5) = SUBSTR(t2.street_address, 1, 5) AND t1.location_type_id = t2.location_type_id) WHERE t1.contact_id < t2.contact_id AND  t2.contact_id IN (19222291,25665376,25995762,28032136,34432770,34846871,35144886,36900303)) subunion GROUP BY id1, id2, weight ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)

Event Timeline

Ejegg created this task.Dec 5 2019, 10:28 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 5 2019, 10:28 PM

Ejegg renamed this task from Runaway Civi dedupe query on first 5 chars of address table to Runaway Civi dedupe query on first 5 chars of street_address field.Dec 5 2019, 10:28 PM

Ejegg added a subscriber: Eileenmcnaughton.

A DS agent tried to use dedupe rule 3

see how it has 5 in length? That results in it comparing calculated values not doing an index join. I'm removing 'length' from rules 3 & 17

Screen Shot 2019-12-06 at 11.40.21 AM.png (984×1 px, 159 KB)

I actually wrote a whole blog post about this a while back https://civicrm.org/blog/eileen/deduping-for-guppies

• DStrine added a project: Fundraising Sprint X-rays.Dec 6 2019, 6:09 PM

• DStrine moved this task from Triage to Current Sprint on the Fundraising-Backlog board.

Ejegg moved this task from Backlog to Deployed on the Fundraising Sprint X-rays board.Dec 6 2019, 9:51 PM

Eileenmcnaughton closed this task as Resolved.Dec 8 2019, 9:47 PM

Eileenmcnaughton claimed this task.

Eileenmcnaughton set the point value for this task to 1.

	F31458871: Screen Shot 2019-12-06 at 11.40.21 AM.png
	Dec 5 2019, 10:41 PM

Runaway Civi dedupe query on first 5 chars of street_address fieldClosed, ResolvedPublic1 Estimated Story PointsActions

Description

Event Timeline

Runaway Civi dedupe query on first 5 chars of street_address field
Closed, ResolvedPublic1 Estimated Story Points
Actions