Bug 37476 - RANK is a reserved word in MySQL 8.0.2+
Summary: RANK is a reserved word in MySQL 8.0.2+
Status: Needs documenting
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low normal
Assignee: Tomás Cohen Arazi (tcohen)
QA Contact: Martin Renvoize (ashimema)
URL:
Keywords:
Depends on: 26567
Blocks: 37480
  Show dependency tree / graph
 
Reported: 2024-07-25 14:21 UTC by Tomás Cohen Arazi (tcohen)
Modified: 2024-09-16 13:59 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
This fixes adding a patron to a routing list after receiving a serial - the patron was not being added to the routing list. This issue was only happing where MySQL 8.0.2 or later was used as the database for Koha. This was because the SQL syntax in the SQL used RANK, which become a reserved word in MySQL 8.0.2.
Version(s) released in:
24.11.00,24.05.04
Circulation function:

Attachments
Bug 37476: Unit tests (3.41 KB, patch)
2024-07-25 17:40 UTC, Tomás Cohen Arazi (tcohen) 		Details | Diff | Splinter Review
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8 (1.67 KB, patch)
2024-07-25 17:41 UTC, Tomás Cohen Arazi (tcohen) 		Details | Diff | Splinter Review
Bug 37476: Unit tests (3.48 KB, patch)
2024-07-26 13:19 UTC, Emily Lamancusa (emlam) 		Details | Diff | Splinter Review
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8 (1.74 KB, patch)
2024-07-26 13:19 UTC, Emily Lamancusa (emlam) 		Details | Diff | Splinter Review
Bug 37476: Unit tests (3.55 KB, patch)
2024-07-26 13:47 UTC, Martin Renvoize (ashimema) 		Details | Diff | Splinter Review
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8 (1.81 KB, patch)
2024-07-26 13:47 UTC, Martin Renvoize (ashimema) 		Details | Diff | Splinter Review
Show Obsolete (4) View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Tomás Cohen Arazi (tcohen) 2024-07-25 14:21:00 UTC 
As of MySQL 8.0.2 [1], `rank` is a reserved word and is breaking some code [2]:

```
C4::Serials::addroutingmember(): 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 FROM subscriptionroutinglist WHERE subscriptionid = '59'' at line 1 at t/db_dependent/Serials.t line 227
```

[1] https://dev.mysql.com/doc/refman/8.0/en/keywords.html
[2] https://jenkins.koha-community.org/job/Koha_Main_My8/1322/consoleFull
Comment 1 Tomás Cohen Arazi (tcohen) 2024-07-25 17:40:49 UTC 
Created attachment 169609 [details] [review]
Bug 37476: Unit tests
Comment 2 Tomás Cohen Arazi (tcohen) 2024-07-25 17:41:17 UTC 
Created attachment 169610 [details] [review]
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8

This patch fixes the fact `RANK` become a reserved word in MySQL 8.0.2
[1]

To test:
1. Launch KTD with MySQL 8:
   $ ktd down
   $ DB_IMAGE=mysql:8 ktd up -d
2. Open the logs
   $ ktd --shell
  k$ tail -f /var/log/koha/kohadev/*.log
3. Create a serial, receive an issue and try to create a routing list
4. Click on `+ Add recipients` and look for Henry
5. Click `Add` and then `Close`
=> FAIL: Henry not added
=> FAIL: The logs show an error about wrong SQL syntax
6. Run:
  k$ prove t/db_dependent/Serials.t
=> FAIL: Tests explode with the same kind of error!
6. Apply this patch
7. Restart plack
8. Repeat 3 through 6
=> SUCCESS: Henry added!
=> SUCCESS: No explosion about the SQL syntax in the logs
=> SUCCESS: Tests pass!
9. Sign off :-D

[1] https://dev.mysql.com/doc/refman/8.0/en/keywords.html
Comment 3 Emily Lamancusa (emlam) 2024-07-26 13:19:40 UTC 
Created attachment 169702 [details] [review]
Bug 37476: Unit tests

Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Comment 4 Emily Lamancusa (emlam) 2024-07-26 13:19:42 UTC 
Created attachment 169703 [details] [review]
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8

This patch fixes the fact `RANK` become a reserved word in MySQL 8.0.2
[1]

To test:
1. Launch KTD with MySQL 8:
   $ ktd down
   $ DB_IMAGE=mysql:8 ktd up -d
2. Open the logs
   $ ktd --shell
  k$ tail -f /var/log/koha/kohadev/*.log
3. Create a serial, receive an issue and try to create a routing list
4. Click on `+ Add recipients` and look for Henry
5. Click `Add` and then `Close`
=> FAIL: Henry not added
=> FAIL: The logs show an error about wrong SQL syntax
6. Run:
  k$ prove t/db_dependent/Serials.t
=> FAIL: Tests explode with the same kind of error!
6. Apply this patch
7. Restart plack
8. Repeat 3 through 6
=> SUCCESS: Henry added!
=> SUCCESS: No explosion about the SQL syntax in the logs
=> SUCCESS: Tests pass!
9. Sign off :-D

[1] https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Comment 5 Emily Lamancusa (emlam) 2024-07-26 13:21:35 UTC 
Testing notes: DB_IMAGE=mysql:8 didn't work for me, but DB_IMAGE=mysql:8.0 did. Otherwise all works as it should!
Comment 6 Tomás Cohen Arazi (tcohen) 2024-07-26 13:23:00 UTC 
(In reply to Emily Lamancusa from comment #5)
> Testing notes: DB_IMAGE=mysql:8 didn't work for me, but DB_IMAGE=mysql:8.0
> did. Otherwise all works as it should!

Thanks! I should've copied and pasted my command instead of typing it :-D
Comment 7 Martin Renvoize (ashimema) 2024-07-26 13:47:55 UTC 
Created attachment 169722 [details] [review]
Bug 37476: Unit tests

Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 8 Martin Renvoize (ashimema) 2024-07-26 13:47:58 UTC 
Created attachment 169723 [details] [review]
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8

This patch fixes the fact `RANK` become a reserved word in MySQL 8.0.2
[1]

To test:
1. Launch KTD with MySQL 8:
   $ ktd down
   $ DB_IMAGE=mysql:8 ktd up -d
2. Open the logs
   $ ktd --shell
  k$ tail -f /var/log/koha/kohadev/*.log
3. Create a serial, receive an issue and try to create a routing list
4. Click on `+ Add recipients` and look for Henry
5. Click `Add` and then `Close`
=> FAIL: Henry not added
=> FAIL: The logs show an error about wrong SQL syntax
6. Run:
  k$ prove t/db_dependent/Serials.t
=> FAIL: Tests explode with the same kind of error!
6. Apply this patch
7. Restart plack
8. Repeat 3 through 6
=> SUCCESS: Henry added!
=> SUCCESS: No explosion about the SQL syntax in the logs
=> SUCCESS: Tests pass!
9. Sign off :-D

[1] https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 9 Martin Renvoize (ashimema) 2024-07-26 13:48:29 UTC 
Thanks for the fix here Tomas.. Passing QA
Comment 10 Martin Renvoize (ashimema) 2024-07-26 13:51:16 UTC 
Thanks for all the hard work!

Pushed to main for the next 24.11.00 release as RM Assistant
Comment 11 Lucas Gass (lukeg) 2024-08-29 17:00:46 UTC 
Backported to 24.05.x for upcoming 24.05.04
Comment 12 Fridolin Somers 2024-09-16 13:59:11 UTC 
Depends on Bug 26567 not in 23.11.x