Bug 21723 - batch_anonymise.pl SQL-query runs out of memory
Summary: batch_anonymise.pl SQL-query runs out of memory
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Patrons (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on: 20182
Blocks:
  Show dependency treegraph
 
Reported: 2018-10-30 10:18 UTC by Andreas Jonsson
Modified: 2019-04-19 06:28 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 21723: Do no retrieve already anonymised entries (1.45 KB, patch)
2018-11-01 16:22 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Andreas Jonsson 2018-10-30 10:18:39 UTC
batch_anonymise.pl fails eventually when the system preference AnonymousPatron set.

- The query does not filter out AnonymousPatron.
- The query has a seemingly unnecessary GROUP BY clause.


DBD::mysql::st execute failed: Out of sort memory, consider increasing server sort buffer size [for Statement "SELECT `me`.`borrowernumber`, `me`.`cardnumber`, `me`.`surname`, `me`.`firstname`, `me`.`title`, `me`.`othernames`, `me`.`initials`, `me`.`streetnumber`, `me`.`streettype`, `me`.`address`, `me`.`address2`, `me`.`city`, `me`.`state`, `me`.`zipcode`, `me`.`country`, `me`.`email`, `me`.`phone`, `me`.`mobile`, `me`.`fax`, `me`.`emailpro`, `me`.`phonepro`, `me`.`B_streetnumber`, `me`.`B_streettype`, `me`.`B_address`, `me`.`B_address2`, `me`.`B_city`, `me`.`B_state`, `me`.`B_zipcode`, `me`.`B_country`, `me`.`B_email`, `me`.`B_phone`, `me`.`dateofbirth`, `me`.`branchcode`, `me`.`categorycode`, `me`.`dateenrolled`, `me`.`dateexpiry`, `me`.`date_renewed`, `me`.`gonenoaddress`, `me`.`lost`, `me`.`debarred`, `me`.`debarredcomment`, `me`.`contactname`, `me`.`contactfirstname`, `me`.`contacttitle`, `me`.`guarantorid`, `me`.`borrowernotes`, `me`.`relationship`, `me`.`sex`, `me`.`password`, `me`.`flags`, `me`.`userid`, `me`.`opacnote`, `me`.`contactnote`, `me`.`sort1`, `me`.`sort2`, `me`.`altcontactfirstname`, `me`.`altcontactsurname`, `me`.`altcontactaddress1`, `me`.`altcontactaddress2`, `me`.`altcontactaddress3`, `me`.`altcontactstate`, `me`.`altcontactzipcode`, `me`.`altcontactcountry`, `me`.`altcontactphone`, `me`.`smsalertnumber`, `me`.`sms_provider_id`, `me`.`privacy`, `me`.`privacy_guarantor_checkouts`, `me`.`checkprevcheckout`, `me`.`updated_on`, `me`.`lastseen`, `me`.`lang`, `me`.`login_attempts`, `me`.`overdrive_auth_token` FROM `borrowers` `me` LEFT JOIN `old_issues` `old_issues` ON `old_issues`.`borrowernumber` = `me`.`borrowernumber` WHERE ( ( `old_issues`.`borrowernumber` IS NOT NULL AND `privacy` <> ? AND `returndate` < ? ) ) GROUP BY `me`.`borrowernumber`, `me`.`cardnumber`, `me`.`surname`, `me`.`firstname`, `me`.`title`, `me`.`othernames`, `me`.`initials`, `me`.`streetnumber`, `me`.`streettype`, `me`.`address`, `me`.`address2`, `me`.`city`, `me`.`state`, `me`.`zipcode`, `me`.`country`, `me`.`email`, `me`.`phone`, `me`.`mobile`, `me`.`fax`, `me`.`emailpro`, `me`.`phonepro`, `me`.`B_streetnumber`, `me`.`B_streettype`, `me`.`B_address`, `me`.`B_address2`, `me`.`B_city`, `me`.`B_state`, `me`.`B_zipcode`, `me`.`B_country`, `me`.`B_email`, `me`.`B_phone`, `me`.`dateofbirth`, `me`.`branchcode`, `me`.`categorycode`, `me`.`dateenrolled`, `me`.`dateexpiry`, `me`.`date_renewed`, `me`.`gonenoaddress`, `me`.`lost`, `me`.`debarred`, `me`.`debarredcomment`, `me`.`contactname`, `me`.`contactfirstname`, `me`.`contacttitle`, `me`.`guarantorid`, `me`.`borrowernotes`, `me`.`relationship`, `me`.`sex`, `me`.`password`, `me`.`flags`, `me`.`userid`, `me`.`opacnote`, `me`.`contactnote`, `me`.`sort1`, `me`.`sort2`, `me`.`altcontactfirstname`, `me`.`altcontactsurname`, `me`.`altcontactaddress1`, `me`.`altcontactaddress2`, `me`.`altcontactaddress3`, `me`.`altcontactstate`, `me`.`altcontactzipcode`, `me`.`altcontactcountry`, `me`.`altcontactphone`, `me`.`smsalertnumber`, `me`.`sms_provider_id`, `me`.`privacy`, `me`.`privacy_guarantor_checkouts`, `me`.`checkprevcheckout`, `me`.`updated_on`, `me`.`lastseen`, `me`.`lang`, `me`.`login_attempts`, `me`.`overdrive_auth_token`" with ParamValues: 0=0, 1='2018-10-27 00:00:00'] at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1834.
Comment 1 Jonathan Druart 2018-11-01 16:21:58 UTC
Hi Andreas,
Could you compare the execution time between this query and the same but with the GROUP BY clause that would contain 'borrowernumber' only?
Comment 2 Jonathan Druart 2018-11-01 16:22:48 UTC
Created attachment 81831 [details] [review]
Bug 21723: Do no retrieve already anonymised entries
Comment 3 Jonathan Druart 2018-11-01 16:23:53 UTC
Also I would be interesting to compare with the query generated with this patch.
Comment 4 Andreas Jonsson 2018-11-02 08:28:59 UTC
With the full GROUP BY clause the query fails because it runs out of sort memory.  With only GROUP BY `borrowernumber` the query takes 2.87 seconds and matches 9529 lines.

Just adding filtering on AnonymousPatron doesn't actually help me at the moment.  Since it has taken us a while to notice this problem, we already have 43797 entries that needs to be anonymised.  These entries needs to be sorted for the GROUP BY clause, which cause the sort buffer memory to run out.  It would have prevented this situation, though.

But it seems that all we need from search_patrons_to_anonymise is `SELECT DISTINCT borrowernumber FROM old_issues JOIN borrowers USING (borrowernumber) WHERE privacy <> 0 AND returndate < ? AND borrowernumber <> ?`

Or why not do the anonymisation in SQL-code directly:

  CREATE TEMPORARY TABLE old_issues_to_anonymise (issue_id INT);
  INSERT INTO old_issues_to_anonymise 
  SELECT issue_id FROM old_issues JOIN borrowers USING (borrowernumber) WHERE privacy <> 0 AND returndate < ? AND borrowernumber <> ?;
  UPDATE old_issues SET borrowernumer = ? WHERE issue_id IN (SELECT * FROM old_issues_to_anonymise);
  DROP TABLE old_issues_to_anonymise;