Bug 28977 - Most-circulated items (cat_issues_top.pl) is failing with SQL Mode ONLY_FULL_GROUP_BY
Summary: Most-circulated items (cat_issues_top.pl) is failing with SQL Mode ONLY_FULL_...
Status: Signed Off
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low normal (vote)
Assignee: Michael Hafen
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-09-08 20:54 UTC by Michael Hafen
Modified: 2021-11-26 09:39 UTC (History)
3 users (show)

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


Attachments
Proposed patch (2.84 KB, patch)
2021-09-08 21:03 UTC, Michael Hafen
Details | Diff | Splinter Review
Proposed patch (3.28 KB, patch)
2021-09-21 21:40 UTC, Michael Hafen
Details | Diff | Splinter Review
Bug 28977 - Add GROUP_CONCAT to some of the selected columns (3.28 KB, patch)
2021-09-23 16:16 UTC, David Nind
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Michael Hafen 2021-09-08 20:54:44 UTC
The Most circulated items report gives an error when run on a database that has ONLY_FULL_GROUP_BY in it's SQL_MODE.
Patch to follow shortly.
Comment 1 Michael Hafen 2021-09-08 21:03:06 UTC
Created attachment 124664 [details] [review]
Proposed patch

Test plan:

Turn on strict_sql_modes ( make sure <strict_sql_modes> is 1 in KOHA_CONF )
Go to Reports -> Most circulated items
submit the form.
Comment 2 David Nind 2021-09-11 22:36:34 UTC
I had a go at testing with koha-testing-docker (KTD), but was unable to get this to work.

- KTD = started with ku
- Strict mode already set in /etc/koha/sites/kohadev/koha-conf.xml

Error before patch applied, no change after (with a flush_memcached and restart_all):

CGI::Compile::ROOT::kohadevbox_koha_reports_cat_issues_top_2epl::calculate(): DBI Exception: DBD::mysql::st execute failed: 'koha_kohadev.biblio.title' isn't in GROUP BY at /kohadevbox/koha/reports/cat_issues_top.pl line 64
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77

I think I will leave the testing to someone who understands how this all works a bit better than I do!
Comment 3 Michael Hafen 2021-09-13 17:46:43 UTC
(In reply to David Nind from comment #2)
> I had a go at testing with koha-testing-docker (KTD), but was unable to get
> this to work.
> 
> - KTD = started with ku
> - Strict mode already set in /etc/koha/sites/kohadev/koha-conf.xml
> 
> Error before patch applied, no change after (with a flush_memcached and
> restart_all):
> 
> CGI::Compile::ROOT::kohadevbox_koha_reports_cat_issues_top_2epl::calculate():
> DBI Exception: DBD::mysql::st execute failed: 'koha_kohadev.biblio.title'
> isn't in GROUP BY at /kohadevbox/koha/reports/cat_issues_top.pl line 64
>  at /usr/share/perl5/DBIx/Class/Exception.pm line 77
> 
> I think I will leave the testing to someone who understands how this all
> works a bit better than I do!

What options did you use to generate that error?  I'm not able to reproduce it.

I didn't test this report thoroughly, so I'm sure there is more work to be done here.  Now that I'm playing with it a bit more the "distinct" is causing trouble too with Mysql 8.
Comment 4 David Nind 2021-09-13 19:53:08 UTC
> What options did you use to generate that error?  I'm not able to reproduce
> it.

This was with and without selecting any date ranges, 

With koha-testing-docker (started with ku) it is using MariaDB 10.1 with Debian 9.
Comment 5 Michael Hafen 2021-09-21 21:40:06 UTC
Created attachment 125124 [details] [review]
Proposed patch

Fresh patch with fixes for MariaDB in Debian 9.
Comment 6 David Nind 2021-09-23 16:16:08 UTC
Created attachment 125208 [details] [review]
Bug 28977 - Add GROUP_CONCAT to some of the selected columns

To get the 'Most circulated items' report to run in ONLY_FULL_GROUP_BY
Sql mode.

Test plan:

Turn on strict_sql_modes ( make sure <strict_sql_modes> is 1 in KOHA_CONF )
Go to Reports -> Most circulated items
submit the form.

Without the patch you get an error like:
CGI::Compile::ROOT::usr_local_koha_master_reports_cat_issues_top_2epl::calculate(): DBI Exception: DBD::mysql::st execute failed: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'koha_main_v4.items.itemcallnumber' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [for Statement "SELECT DISTINCT biblio.title, COUNT(biblio.biblionumber) AS `RANK`, biblio.biblionumber AS ID, itemcallnumber as CALLNUM, ccode as CCODE, location as LOC FROM `old_issues`
                  LEFT JOIN items USING(itemnumber)
                  LEFT JOIN biblio USING(biblionumber)
                  LEFT JOIN biblioitems USING(biblionumber)
                  LEFT JOIN borrowers USING(borrowernumber)
                  WHERE 1 AND old_issues.issuedate > '2020-08-03' AND old_issues.issuedate < '2021-05-17' AND old_issues.returndate > '2020-08-27' AND old_issues.returndate < '2021-06-01' AND old_issues.branchcode like '403' AND items.itype like 'F' AND ccode like 'FIC' AND borrowers.categorycode like 'ST' group by biblio.biblionumber order by `RANK` DESC"] at /usr/local/koha_master/reports/cat_issues_top.pl line 67
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77

With the patch you will see the report results view.

Signed-off-by: David Nind <david@davidnind.com>
Comment 7 David Nind 2021-09-23 16:19:16 UTC
Testing notes (koha-testing-docker):
- tested on environments available by starting KTD with ku, ku-mdb, ku-md9, ku-md10, ky-my8
- check out and check in an item (so that there was some data for the report)