Bug 28524

Summary: Most-circulated items (cat_issues_top.pl) is failing with MySQL 8
Product: Koha Reporter: Humberto Chula <development>
Component: ReportsAssignee: Jonathan Druart <jonathan.druart>
Status: Pushed to oldoldoldstable --- QA Contact: Marcel de Rooy <m.de.rooy>
Severity: critical    
Priority: P5 - low CC: jonathan.druart, kyle, m.de.rooy, victor, wainuiwitikapark
Version: unspecified   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23997
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28523
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
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
Bug Depends on:    
Bug Blocks: 23996    
Attachments: Bug 28524: Escape 'rank' in cat_issues_top.pl
Bug 28524: Escape 'rank' in cat_issues_top.pl
Bug 28524: Escape 'rank' in cat_issues_top.pl

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