Bug 33375 - Advanced editor crashes when using MySQL 8 due to reserved rank keyword
Summary: Advanced editor crashes when using MySQL 8 due to reserved rank keyword
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Cataloging (show other bugs)
Version: Main
Hardware: All All
: P1 - high critical (vote)
Assignee: David Cook
QA Contact: Marcel de Rooy
URL:
Keywords: rel_21_11_candidate
: 29973 31859 33714 (view as bug list)
Depends on: 17515
Blocks: 23996
  Show dependency treegraph
 
Reported: 2023-03-31 00:54 UTC by David Cook
Modified: 2023-12-28 20:47 UTC (History)
8 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
23.05.00,22.11.05,22.05.13,21.11.21


Attachments
Bug 33375: Quote reserved keyword 'rank' in advanced editor (1.32 KB, patch)
2023-03-31 01:00 UTC, David Cook
Details | Diff | Splinter Review
Bug 33375: Quote reserved keyword 'rank' in advanced editor (1.37 KB, patch)
2023-03-31 20:06 UTC, David Nind
Details | Diff | Splinter Review
Bug 33375: Quote reserved keyword 'rank' in advanced editor (1.46 KB, patch)
2023-04-14 06:20 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 David Cook 2023-03-31 00:54:45 UTC
Bug 17515 added "rank" to the ORDER BY without quoting it. Since it's a reserved keyword in MySQL 8, this causes a syntax error which causes the advanced editor to throw a fatal 500 error.
Comment 1 David Cook 2023-03-31 01:00:41 UTC
Created attachment 149008 [details] [review]
Bug 33375: Quote reserved keyword 'rank' in advanced editor

This change quotes the 'rank' keyword in the advanced editor,
so that it doesn't cause fatal crashes when using MySQL 8

Test plan:
0. Apply patch and koha-plack --restart kohadev
1. Enable advanced editor
http://localhost:8081/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=EnableAdvancedCatalogingEditor
2. Go to http://localhost:8081/cgi-bin/koha/cataloguing/editor.pl#new/
3. Click on "Advanced" on the left nav
4. Note that "LIBRARY OF CONGRESS" is ordered before "NATIONAL LIBRARY OF FRANCE"
5. Rejoice!
Comment 2 David Nind 2023-03-31 20:06:49 UTC
Created attachment 149056 [details] [review]
Bug 33375: Quote reserved keyword 'rank' in advanced editor

This change quotes the 'rank' keyword in the advanced editor,
so that it doesn't cause fatal crashes when using MySQL 8

Test plan:
0. Apply patch and koha-plack --restart kohadev
1. Enable advanced editor
http://localhost:8081/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=EnableAdvancedCatalogingEditor
2. Go to http://localhost:8081/cgi-bin/koha/cataloguing/editor.pl#new/
3. Click on "Advanced" on the left nav
4. Note that "LIBRARY OF CONGRESS" is ordered before "NATIONAL LIBRARY OF FRANCE"
5. Rejoice!

Signed-off-by: David Nind <david@davidnind.com>
Comment 3 David Nind 2023-03-31 20:12:54 UTC
Testing notes using KTD:

1. To start KTD with MySQL instead of MariaDB: before starting KTD, change .env so that DB_IMAGE=mysql:8.0
2. Replicated the issue.
3. Applied the patch and followed the test plan.
Comment 4 Marcel de Rooy 2023-04-14 06:20:44 UTC
Created attachment 149633 [details] [review]
Bug 33375: Quote reserved keyword 'rank' in advanced editor

This change quotes the 'rank' keyword in the advanced editor,
so that it doesn't cause fatal crashes when using MySQL 8

Test plan:
0. Apply patch and koha-plack --restart kohadev
1. Enable advanced editor
http://localhost:8081/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=EnableAdvancedCatalogingEditor
2. Go to http://localhost:8081/cgi-bin/koha/cataloguing/editor.pl#new/
3. Click on "Advanced" on the left nav
4. Note that "LIBRARY OF CONGRESS" is ordered before "NATIONAL LIBRARY OF FRANCE"
5. Rejoice!

Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 5 Tomás Cohen Arazi 2023-04-14 14:20:18 UTC
Pushed to master for 23.05.

Nice work everyone, thanks!
Comment 6 Matt Blenkinsop 2023-04-18 12:12:03 UTC
Nice work everyone!

Pushed to stable for 22.11.x
Comment 7 Lucas Gass 2023-05-05 22:20:15 UTC
Backported to 22.05.x for upcoming 22.05.13.
Comment 8 Katrin Fischer 2023-05-17 16:04:30 UTC
*** Bug 33714 has been marked as a duplicate of this bug. ***
Comment 9 Katrin Fischer 2023-05-18 08:30:56 UTC
*** Bug 31859 has been marked as a duplicate of this bug. ***
Comment 10 Arthur Suzuki 2023-05-23 12:09:02 UTC
applied to 21.11 for 21.11.21
Comment 11 Katrin Fischer 2023-06-25 11:43:08 UTC
*** Bug 29973 has been marked as a duplicate of this bug. ***