Bug 28524 - Most-circulated items (cat_issues_top.pl) is failing with MySQL 8
Summary: Most-circulated items (cat_issues_top.pl) is failing with MySQL 8
Status: Pushed to oldoldoldstable
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low critical (vote)
Assignee: Jonathan Druart
QA Contact: Marcel de Rooy
URL:
Keywords:
Depends on:
Blocks: 23996
  Show dependency treegraph
 
Reported: 2021-06-07 21:14 UTC by Humberto Chula
Modified: 2021-07-25 04:43 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Text to go in the release notes:
Version(s) released in:
21.11.00,21.05.02,20.11.08,20.05.14,19.11.20


Attachments
Bug 28524: Escape 'rank' in cat_issues_top.pl (1.70 KB, patch)
2021-06-16 12:53 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 28524: Escape 'rank' in cat_issues_top.pl (1.75 KB, patch)
2021-06-24 16:41 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 28524: Escape 'rank' in cat_issues_top.pl (1.85 KB, patch)
2021-06-25 07:13 UTC, Marcel de Rooy
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Humberto Chula 2021-06-07 21:14:15 UTC
CGI::Compile::ROOT::usr_share_koha_intranet_cgi_2dbin_reports_cat_issues_top_2epl::calculate(): DBI Exception: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RANK, biblio.biblionumber AS ID, itemcallnumber as CALLNUM, ccode as CCODE, loca' at line 1 [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 > '2021-01-01' AND old_issues.issuedate < '2021-06-30' AND old_issues.branchcode like 'p' group by biblio.biblionumber order by RANK DESC"] at /usr/share/koha/intranet/cgi-bin/reports/cat_issues_top.pl line 67

Rank is a MySQL reserved word defined in MySQL version 8.0.2
Comment 1 Jonathan Druart 2021-06-16 12:53:54 UTC
Created attachment 122036 [details] [review]
Bug 28524: Escape 'rank' in cat_issues_top.pl

It's a MySQL 8 keyword

Test plan:
Turn off strict_sql_modes (there are other problems in this script)
Hit Home Reports > Most-circulated items
Submit the form

Without this patch you got:
  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RANK, biblio.biblionumber AS ID, itemcallnumber as CALLNUM, ccode as CCODE, loca' at line 1

With this patch applied you see the report result view
Comment 2 Owen Leonard 2021-06-24 16:41:19 UTC
Created attachment 122394 [details] [review]
Bug 28524: Escape 'rank' in cat_issues_top.pl

It's a MySQL 8 keyword

Test plan:
Turn off strict_sql_modes (there are other problems in this script)
Hit Home Reports > Most-circulated items
Submit the form

Without this patch you got:
  You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near 'RANK, biblio.biblionumber AS ID, itemcallnumber as CALLNUM,
  ccode as CCODE, loca' at line 1

With this patch applied you see the report result view

Signed-off-by: Owen Leonard <oleonard@myacpl.org>
Comment 3 Marcel de Rooy 2021-06-25 07:13:33 UTC
Created attachment 122416 [details] [review]
Bug 28524: Escape 'rank' in cat_issues_top.pl

It's a MySQL 8 keyword

Test plan:
Turn off strict_sql_modes (there are other problems in this script)
Hit Home Reports > Most-circulated items
Submit the form

Without this patch you got:
  You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near 'RANK, biblio.biblionumber AS ID, itemcallnumber as CALLNUM,
  ccode as CCODE, loca' at line 1

With this patch applied you see the report result view

Signed-off-by: Owen Leonard <oleonard@myacpl.org>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 4 Jonathan Druart 2021-07-06 09:06:09 UTC
Pushed to master for 21.11, thanks to everybody involved!
Comment 5 Kyle M Hall 2021-07-09 17:45:07 UTC
Pushed to 21.05.x for 21.05.02
Comment 6 Victor Grousset/tuxayo 2021-07-22 14:38:57 UTC
Hi, Fridolin impostor here:
Backported: Pushed to 20.11.x branch for 20.11.08
Comment 7 Victor Grousset/tuxayo 2021-07-22 19:32:25 UTC
Backported: Pushed to 20.05.x branch for 20.05.14
Comment 8 wainuiwitikapark 2021-07-25 04:43:35 UTC
Backported to 19.11.x for 19.11.20