[Koha-bugs] [Bug 21723] batch_anonymise.pl SQL-query runs out of memory

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21723

--- Comment #4 from Andreas Jonsson <andreas.jonsson at kreablo.se> ---

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;

-- 
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.