Cron <instance-koha@kohaMain> $KOHA_CRON_PATH/reconcile_balances.pl --verbose Gives this message when run: SQL::Abstract::puke(): [SQL::Abstract::__ANON__] Fatal: SQL::Abstract before v1.75 used to generate incorrect SQL when the -IN operator was given an undef-containing list: !!!AUDIT YOUR CODE AND DATA!!! (the upcoming Data::Query-based version of SQL::Abstract will emit the logically correct SQL instead of raising this exception) at /usr/share/koha/lib/Koha/Objects.pm line 209 It would be nice if this wouldn't happen, so that cron doesn't send mails about this error.
Hi Liz, I do not recreate. How do you? I tried the 2 situations: - patron has one accountline with amountoutstanding < 0 - nothing in the accountlines table My understanding of the error is that 92 my $patrons = Koha::Patrons->search({ borrowernumber => { -in => \@patron_ids } }); is called with @patron_ids = ( undef );
I cannot reproduce either, but I agree the code needs to be fixed.
(In reply to Tomás Cohen Arazi from comment #2) > I cannot reproduce either, but I agree the code needs to be fixed. It's not. It should never be (undef)
Created attachment 96484 [details] [review] Bug 24266: Don't search for patrons if no borrowernumbers This patch makes the reconcile_balances.pl script skip searching for patrons from a given borrowernumbers list, if the list is empty. The code was logically incorrect, and it doesn't break because of a bug in SQL::Abstract that in recent versions started raising a noisy warning. I couldn't reproduce it in koha-testing-docker but it's been found in the wild. I submit this obvious fix so people having the issue can test it properly. Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Liz, please test it on your production site and let me know if it fixes the issue.
Tomas, the warning is about ->search({ -in => [undef] }), not ->search({ -in => [] }). So I do not think your patch will change anything.
Created attachment 100832 [details] [review] Bug 24266: (alternate patch) Only use defined borrowernumbers in reconcile_balances.pl cron I believe the error is triggered when borrowernumbers are left empty in the accountlines table. Not sure why this would happen, but it appears to be what causes the problem. Do not apply the first patch if testing this patch. To test: 1) sudo koha-mysql INSTANCENAME 2) if you don't have any accountlines where the borrowernumber is null, update one of your accountlines where the amountoutstanding is more than zero, and make the borrowernumber NULL 3) ensure the AccountAutoReconcile syspref is disabled 4) Go to [borrowernumber]'s accounting tab 5) Create a manual credit or debit. Confirm this shows in the 'Make a payment' tab as an amount that COULD be applied, but isn't automatically applied 6) in your terminal, run the reconcile_balances.pl script 7) Confirm the error does not show in the logs and the balance for [borrowernumber] is correctly reconciled. Sponsored-by: Horowhenua District Council
I totally forgot about this one. Sorry. Borrowernumbers get emptied when the patron gets deleted.
Ah that totally makes sense. Perhaps instead of going into the database in the test plan, create a test borrower, add some accountline, then delete the borrower. The script should know to skip past them.
Aleisha, double check your patch, you have "1=" instead of "!="
Created attachment 100926 [details] [review] Bug 24266: (alternate patch) Only use defined borrowernumbers in reconcile_balances.pl cron I believe the error is triggered when borrowernumbers are left empty in the accountlines table. Not sure why this would happen, but it appears to be what causes the problem. Do not apply the first patch if testing this patch. To test: 1) sudo koha-mysql INSTANCENAME 2) Create a test borrower, add any payment etc to create an accountline, then delete this borrower 3) ensure the AccountAutoReconcile syspref is disabled 4) Go to another borrower's accounting tab 5) Create a manual credit or debit. Confirm this shows in the 'Make a payment' tab as an amount that COULD be applied, but isn't automatically applied 6) in your terminal, run the reconcile_balances.pl script 7) Confirm the error does not show in the logs and the balance for the borrower is correctly reconciled. Sponsored-by: Horowhenua District Council
alternate patch ? Its more a follow-up no ?
(In reply to Fridolin SOMERS from comment #12) > alternate patch ? Its more a follow-up no ? Alternate patch, because in Comment 6 it was mentioned that the first patch doesn't really fix the problem
(In reply to Aleisha Amohia from comment #13) > (In reply to Fridolin SOMERS from comment #12) > > alternate patch ? Its more a follow-up no ? > > Alternate patch, because in Comment 6 it was mentioned that the first patch > doesn't really fix the problem Ah in this case set it obsolete or set status In Discussion :)
Let's try Aleisha patch and see if it fixes the original issue.
Created attachment 102553 [details] [review] Bug 24266: (alternate patch) Only use defined borrowernumbers in reconcile_balances.pl cron I believe the error is triggered when borrowernumbers are left empty in the accountlines table. Not sure why this would happen, but it appears to be what causes the problem. Do not apply the first patch if testing this patch. To test: 1) sudo koha-mysql INSTANCENAME 2) Create a test borrower, add any payment etc to create an accountline, then delete this borrower 3) ensure the AccountAutoReconcile syspref is disabled 4) Go to another borrower's accounting tab 5) Create a manual credit or debit. Confirm this shows in the 'Make a payment' tab as an amount that COULD be applied, but isn't automatically applied 6) in your terminal, run the reconcile_balances.pl script 7) Confirm the error does not show in the logs and the balance for the borrower is correctly reconciled. Sponsored-by: Horowhenua District Council Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
This appears to resolve the issue for me.. Signing off
Created attachment 102680 [details] [review] Bug 24266: (alternate patch) Only use defined borrowernumbers in reconcile_balances.pl cron I believe the error is triggered when borrowernumbers are left empty in the accountlines table. Not sure why this would happen, but it appears to be what causes the problem. Do not apply the first patch if testing this patch. To test: 1) sudo koha-mysql INSTANCENAME 2) Create a test borrower, add any payment etc to create an accountline, then delete this borrower 3) ensure the AccountAutoReconcile syspref is disabled 4) Go to another borrower's accounting tab 5) Create a manual credit or debit. Confirm this shows in the 'Make a payment' tab as an amount that COULD be applied, but isn't automatically applied 6) in your terminal, run the reconcile_balances.pl script 7) Confirm the error does not show in the logs and the balance for the borrower is correctly reconciled. Sponsored-by: Horowhenua District Council Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Nice work everyone! Pushed to master for 20.05
*** Bug 25148 has been marked as a duplicate of this bug. ***
backported to 19.11.x for 19.11.06
backported to 19.05.x. for 19.05.11