Bug 31222 - DBIC queries for batch mod can be very large
Summary: DBIC queries for batch mod can be very large
Status: Signed Off
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Nick Clemens
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-07-22 14:47 UTC by Nick Clemens
Modified: 2022-08-09 08:03 UTC (History)
2 users (show)

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


Attachments
Bug 31222: Reduce query size for batchMod (2.94 KB, patch)
2022-07-22 14:48 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 31222: Reduce query size for batchMod (3.00 KB, patch)
2022-08-05 20:43 UTC, Rachael
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens 2022-07-22 14:47:30 UTC
We had an issue with  mysql logging for a site doing many batch modifications.

The object search is currently:
Koha::Items->search({ barcode => \@contentlist }

Which generate code like:
barcode = 1 OR barcode = 2 OR barcode = 3 ....

This can get quite large

We can reduce the query size by using -in:
Koha::Items->search({ barcode => { -in => \@contentlist } }

Which generates code like:
barcode in ( 1, 2, 3 )
Comment 1 Nick Clemens 2022-07-22 14:48:34 UTC
Created attachment 138038 [details] [review]
Bug 31222: Reduce query size for batchMod

The object search is currently:
Koha::Items->search({ barcode => \@contentlist }

Which generate code like:
barcode = 1 OR barcode = 2 OR barcode = 3 ....

This can get quite large

We can reduce the query size by using -in:
Koha::Items->search({ barcode => { -in => \@contentlist } }

Which generates code like:
barcode in ( 1, 2, 3 )

To test:
1 - Apply patch
2 - Perform batch modifications
3 - Confirm nothing has changed
Comment 2 Rachael 2022-08-02 18:21:55 UTC
Batch modifications changes each item. Ex. Modifying the Source of classification or shelving scheme to Dewey Decimal Classification gets applied.
Comment 3 Nick Clemens 2022-08-03 10:15:16 UTC
(In reply to Rachael from comment #2)
> Batch modifications changes each item. Ex. Modifying the Source of
> classification or shelving scheme to Dewey Decimal Classification gets
> applied.

Hi Rachel,  I am not sure what you mean here? Batch modification should update each item, is it updating more fields than intended?
Comment 4 Rachael 2022-08-03 12:47:08 UTC
(In reply to Nick Clemens from comment #3)
> (In reply to Rachael from comment #2)
> > Batch modifications changes each item. Ex. Modifying the Source of
> > classification or shelving scheme to Dewey Decimal Classification gets
> > applied.
> 
> Hi Rachel,  I am not sure what you mean here? Batch modification should
> update each item, is it updating more fields than intended?

Hi Nick,
No, nothing else changes, just the fields that I specified. Should I sign it off then?
Comment 5 Nick Clemens 2022-08-05 20:06:40 UTC
(In reply to Rachael from comment #4)
> (In reply to Nick Clemens from comment #3)
> > (In reply to Rachael from comment #2)
> > > Batch modifications changes each item. Ex. Modifying the Source of
> > > classification or shelving scheme to Dewey Decimal Classification gets
> > > applied.
> > 
> > Hi Rachel,  I am not sure what you mean here? Batch modification should
> > update each item, is it updating more fields than intended?
> 
> Hi Nick,
> No, nothing else changes, just the fields that I specified. Should I sign it
> off then?

If everything works as expected with the patch, yes, please :-)
Comment 6 Rachael 2022-08-05 20:43:42 UTC
Created attachment 138737 [details] [review]
Bug 31222: Reduce query size for batchMod

The object search is currently:
Koha::Items->search({ barcode => \@contentlist }

Which generate code like:
barcode = 1 OR barcode = 2 OR barcode = 3 ....

This can get quite large

We can reduce the query size by using -in:
Koha::Items->search({ barcode => { -in => \@contentlist } }

Which generates code like:
barcode in ( 1, 2, 3 )

To test:
1 - Apply patch
2 - Perform batch modifications
3 - Confirm nothing has changed

Signed-off-by: Rachael Laritz <rachael.laritz@inlibro.com>
Comment 7 Jonathan Druart 2022-08-09 08:00:12 UTC
Hum, what was the thing with the IN limit?
Comment 8 Jonathan Druart 2022-08-09 08:01:33 UTC
IN limit is max_allowed_packet, which is 16M by default.
Comment 9 Jonathan Druart 2022-08-09 08:03:42 UTC
Why do we want to reduce mysql query length exactly? You are not supposed to have mysql logging in production servers, you will face perf issues (but I guess you know that already).