[Bug 28523] New: Patrons with the most checkouts (bor_issues_top.pl) is failing with MySQL 8

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 28523] New: Patrons with the most checkouts (bor_issues_top.pl) is failing with MySQL 8

bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28523

            Bug ID: 28523
           Summary: Patrons with the most checkouts (bor_issues_top.pl) is
                    failing with MySQL 8
 Change sponsored?: ---
           Product: Koha
           Version: 21.05
          Hardware: All
                OS: All
            Status: NEW
          Severity: critical
          Priority: P5 - low
         Component: Reports
          Assignee: [hidden email]
          Reporter: [hidden email]
        QA Contact: [hidden email]

CGI::Compile::ROOT::usr_share_koha_intranet_cgi_2dbin_reports_bor_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, borrowers.borrowernumber AS ID FROM
`old_issues`
                  LEFT JO' at line 1 [for Statement "SELECT  CONCAT_WS('',
borrowers.surname , ",\t", borrowers.firstname),  COUNT(*) AS RANK,
borrowers.borrowernumber AS ID FROM `old_issues`
                  LEFT JOIN  borrowers  USING(borrowernumber)
                  LEFT JOIN    items    USING(itemnumber)
                  LEFT JOIN biblioitems USING(biblioitemnumber)
                  WHERE old_issues.borrowernumber IS NOT NULL
                   AND old_issues.issuedate > '2021-06-01'  AND
old_issues.issuedate < '2021-06-07'  GROUP BY borrowers.borrowernumber ORDER BY
RANK DESC LIMIT 5"] at
/usr/share/koha/intranet/cgi-bin/reports/bor_issues_top.pl line 70


Rank is a MySQL reserved word defined in MySQL version 8.0.2

https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-R

--
You are receiving this mail because:
You are watching all bug changes.
You are the assignee for the bug.
_______________________________________________
Koha-bugs mailing list
[hidden email]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 28523] Patrons with the most checkouts (bor_issues_top.pl) is failing with MySQL 8

bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28523

Jonathan Druart <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           See Also|                            |https://bugs.koha-community
                   |                            |.org/bugzilla3/show_bug.cgi
                   |                            |?id=28524

--
You are receiving this mail because:
You are watching all bug changes.
You are the assignee for the bug.
_______________________________________________
Koha-bugs mailing list
[hidden email]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 28523] Patrons with the most checkouts (bor_issues_top.pl) is failing with MySQL 8

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28523

Jonathan Druart <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           See Also|                            |https://bugs.koha-community
                   |                            |.org/bugzilla3/show_bug.cgi
                   |                            |?id=23997
                 CC|                            |jonathan.druart+koha@gmail.
                   |                            |com
             Blocks|                            |23996


Referenced Bugs:

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23996
[Bug 23996] MySQL 8 support [Omnibus]
--
You are receiving this mail because:
You are watching all bug changes.
You are the assignee for the bug.
_______________________________________________
Koha-bugs mailing list
[hidden email]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 28523] Patrons with the most checkouts (bor_issues_top.pl) is failing with MySQL 8

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28523

Jonathan Druart <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Version|21.05                       |unspecified

--
You are receiving this mail because:
You are watching all bug changes.
You are the assignee for the bug.
_______________________________________________
Koha-bugs mailing list
[hidden email]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 28523] Patrons with the most checkouts (bor_issues_top.pl) is failing with MySQL 8

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28523

Jonathan Druart <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |Needs Signoff

--
You are receiving this mail because:
You are watching all bug changes.
You are the assignee for the bug.
_______________________________________________
Koha-bugs mailing list
[hidden email]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 28523] Patrons with the most checkouts (bor_issues_top.pl) is failing with MySQL 8

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28523

--- Comment #1 from Jonathan Druart <[hidden email]> ---
Created attachment 122037
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=122037&action=edit
Bug 28523: Escape 'rank' in bor_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 > Patrons with the most checkouts
Submit the form

Without this patch you got:
    You have an error in your SQL syntax; check the manual that corresponds to
your MySQL s
erver version for the right syntax to use near 'RANK, borrowers.borrowernumber
AS ID FROM `old_issues`

With this patch applied you see the report result view

--
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 28523] Patrons with the most checkouts (bor_issues_top.pl) is failing with MySQL 8

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28523

Jonathan Druart <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Assignee|[hidden email]-commun |jonathan.druart+koha@gmail.
                   |ity.org                     |com

--
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
Reply | Threaded
Open this post in threaded view
|

[Bug 28523] Patrons with the most checkouts (bor_issues_top.pl) is failing with MySQL 8

bugzilla-daemon
In reply to this post by bugzilla-daemon
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28523

David Nind <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #2 from David Nind <[hidden email]> ---
I attempted to test this.

With MySQL 8 I was able to get this to work following the test plan (starting
KTD with ku=my8).

However, if I change back to MariaDB (starting KTD with ku) I get a different
error message:

CGI::Compile::ROOT::kohadevbox_koha_reports_bor_issues_top_2epl::calculate():
DBI Exception: DBD::mysql::st execute failed: 'koha_kohadev.borrowers.surname'
isn't in GROUP BY [for Statement "SELECT  CONCAT_WS('', borrowers.surname ,
",\t", borrowers.firstname),  COUNT(*) AS `RANK`, borrowers.borrowernumber AS
ID FROM `old_issues`
                  LEFT JOIN  borrowers  USING(borrowernumber)
                  LEFT JOIN    items    USING(itemnumber)
                  LEFT JOIN biblioitems USING(biblioitemnumber)
                  WHERE old_issues.borrowernumber IS NOT NULL
                   AND old_issues.issuedate > '2021-06-01'  AND
old_issues.issuedate < '2021-06-30'  AND old_issues.returndate > '2021-06-01'
AND old_issues.returndate < '2021-06-30'  GROUP BY borrowers.borrowernumber
ORDER BY `RANK` DESC LIMIT 5"] at /kohadevbox/koha/reports/bor_issues_top.pl
line 70
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77


Testing notes (koha-testing-docker):
- Start KTD with MySQL 8 (rather than MariaDB): ku-my8
- Turn off strict mode
(https://www.linode.com/community/questions/17070/how-can-i-disable-mysql-strict-mode):
  . access database container: docker exec -it koha_db_1 bash
  . mysql -uroot -ppassword
  . SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
  . SELECT @@GLOBAL.sql_mode;

--
You are receiving this mail because:
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
[hidden email]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/