Bug 37292

Summary: Add an index on expires column for oauth_access_tokens
Product: Koha Reporter: Nick Clemens (kidclamp) <nick>
Component: Architecture, internals, and plumbingAssignee: Tomás Cohen Arazi (tcohen) <tomascohen>
Status: RESOLVED FIXED QA Contact: Matt Blenkinsop <matt.blenkinsop>
Severity: normal    
Priority: P5 - low CC: david, fridolin.somers, matt.blenkinsop, wainuiwitikapark
Version: Main   
Hardware: All   
OS: All   
GIT URL: Change sponsored?: ---
Patch complexity: Trivial patch Documentation contact:
Documentation submission: Text to go in the release notes:
This adds a database index to the `expires` column for the 'oauth_access_tokens' table, making it easier for database administrators to purge older records.
Version(s) released in:
25.05.00,24.11.01,24.05.06
Circulation function:
Attachments: Bug 37292: Add an index on oauth_access_tokens.expires
Bug 37292: Add an index on oauth_access_tokens.expires
Bug 37292: Add an index on oauth_access_tokens.expires

Description Nick Clemens (kidclamp) 2024-07-09 18:44:46 UTC
It was suggested by our DB admin to add this to make purging old records easier
Comment 1 Tomás Cohen Arazi (tcohen) 2024-11-29 15:12:01 UTC
Created attachment 175029 [details] [review]
Bug 37292: Add an index on oauth_access_tokens.expires

This patch adds a needed index to the column.

To test:
1. On a fresh KTD, run:
   $ ktd --shell
  k$ koha-mysql kohadev
   > SHOW CREATE TABLE oauth_access_tokens;
=> FAIL: There's no 'KEY' entry for the `expires` column
2. Apply this patch
3. Run:
  k$ updatedatabase
=> SUCCESS: A message tells the index was added
4. Repeat 1
=> SUCCESS: The index was actually added to the DB
5. Run:
  k$ reset_all
6. Repeat 1
=> SUCCESS: The index is created at install time too!
7. Run:
  k$ updatedatabase
=> SUCCESS: Nothing explodes, no message about index being created
8. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 2 David Nind 2024-11-30 16:46:16 UTC
Created attachment 175036 [details] [review]
Bug 37292: Add an index on oauth_access_tokens.expires

This patch adds a needed index to the column.

To test:
1. On a fresh KTD, run:
   $ ktd --shell
  k$ koha-mysql kohadev
   > SHOW CREATE TABLE oauth_access_tokens;
=> FAIL: There's no 'KEY' entry for the `expires` column
2. Apply this patch
3. Run:
  k$ updatedatabase
=> SUCCESS: A message tells the index was added
4. Repeat 1
=> SUCCESS: The index was actually added to the DB
5. Run:
  k$ reset_all
6. Repeat 1
=> SUCCESS: The index is created at install time too!
7. Run:
  k$ updatedatabase
=> SUCCESS: Nothing explodes, no message about index being created
8. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: David Nind <david@davidnind.com>
Comment 3 Matt Blenkinsop 2024-12-02 10:40:32 UTC
Created attachment 175047 [details] [review]
Bug 37292: Add an index on oauth_access_tokens.expires

This patch adds a needed index to the column.

To test:
1. On a fresh KTD, run:
   $ ktd --shell
  k$ koha-mysql kohadev
   > SHOW CREATE TABLE oauth_access_tokens;
=> FAIL: There's no 'KEY' entry for the `expires` column
2. Apply this patch
3. Run:
  k$ updatedatabase
=> SUCCESS: A message tells the index was added
4. Repeat 1
=> SUCCESS: The index was actually added to the DB
5. Run:
  k$ reset_all
6. Repeat 1
=> SUCCESS: The index is created at install time too!
7. Run:
  k$ updatedatabase
=> SUCCESS: Nothing explodes, no message about index being created
8. Sign off :-D

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com>
Comment 4 Katrin Fischer 2024-12-03 16:19:08 UTC
Is this a bug or an enh?
Comment 5 Katrin Fischer 2024-12-03 16:36:12 UTC
Pushed for 25.05!

Well done everyone, thank you!
Comment 6 Paul Derscheid 2024-12-10 15:07:51 UTC
Nice work everyone!

Pushed to 24.11.x for 24.11.01
Comment 7 Wainui Witika-Park 2024-12-17 23:16:02 UTC
Backported to 24.05.x for 24.05.06
Comment 8 Fridolin Somers 2024-12-18 15:54:14 UTC
Makes sens but I prefer not backport to 23.11.x for database stability
Comment 9 David Nind 2024-12-29 03:49:31 UTC
Not covered in the manual, no updates required.