Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Access requests missing on the File Permission Page since upgrading from 6.0 to 6.2 #10714

Open
PaulBoon opened this issue Jul 22, 2024 · 2 comments · May be fixed by #11061
Open

Access requests missing on the File Permission Page since upgrading from 6.0 to 6.2 #10714

PaulBoon opened this issue Jul 22, 2024 · 2 comments · May be fixed by #11061
Labels
FY25 Sprint 11 FY25 Sprint 11 (2024-11-20 - 2024-12-04) Size: 3 A percentage of a sprint. 2.1 hours. Type: Bug a defect

Comments

@PaulBoon
Copy link
Contributor

PaulBoon commented Jul 22, 2024 •
edited by jggautier
Loading

We have received tickets from users that they can’t find the list of access requests anymore.
They know that their requests were there because they can find them in the notifications.

What steps does it take to reproduce the issue?

  • create a request on a restricted file in Dataverse 6.0
    The database record looks like this:
dvndb=> SELECT * FROM fileaccessrequests;
 datafile_id | authenticated_user_id |      creation_time      
-------------+-----------------------+-------------------------
           8 |                     6 | 2024-07-15 14:53:54.757
(1 row)
  • Upgrade to 6.2 (possibly to 6.1 will also work)
    The record will then look like this
dvndb=> SELECT * FROM fileaccessrequests;
 datafile_id | authenticated_user_id |      creation_time      | request_state | id | guestbookresponse_id 
-------------+-----------------------+-------------------------+---------------+----+----------------------
           8 |                     6 | 2024-07-15 14:53:54.757 |               |  1 |                     
(1 row)

The request_state is NULL instead of CREATED, which results in not being shown in the list of requests.
The flyway script (V6.0.0.1__9599-guestbook-at-request.sql) has added the ‘request_state’ column, but it then has no state (NULL).

The following SQL should fix this

UPDATE fileaccessrequests SET request_state='CREATED' WHERE request_state IS NULL;

However, when users have re-issued the requests because they were not handled, the above won't work:

ERROR:  duplicate key value violates unique constraint "created_requests"

To detect those cases use the following query:

SELECT * 
FROM fileaccessrequests 
WHERE (datafile_id, authenticated_user_id) IN (
  SELECT datafile_id, authenticated_user_id
  FROM fileaccessrequests
  GROUP BY datafile_id, authenticated_user_id
  HAVING COUNT(*) > 1
) ORDER BY datafile_id, authenticated_user_id;

And then as a solution remove that old requests using this query:

DELETE FROM fileaccessrequests 
WHERE id IN (
  SELECT id 
  FROM fileaccessrequests 
  WHERE (datafile_id, authenticated_user_id) IN (
    SELECT datafile_id, authenticated_user_id
    FROM fileaccessrequests
    GROUP BY datafile_id, authenticated_user_id
    HAVING COUNT(*) > 1
  ) AND request_state IS NULL
);
@PaulBoon PaulBoon added the Type: Bug a defect label Jul 22, 2024
@cmbz
Copy link

cmbz commented Aug 19, 2024 •
edited
Loading

  • 2024/08/19
  • Technically not a bug in the code, however it is a problem for installations that predate 6.2
  • Prioritizing

@cmbz cmbz moved this to SPRINT- NEEDS SIZING in IQSS Dataverse Project Aug 19, 2024
@cmbz cmbz added the Size: 3 A percentage of a sprint. 2.1 hours. label Aug 19, 2024
@cmbz cmbz moved this from SPRINT- NEEDS SIZING to SPRINT READY in IQSS Dataverse Project Aug 19, 2024
@cmbz cmbz added the FY25 Sprint 11 FY25 Sprint 11 (2024-11-20 - 2024-12-04) label Nov 22, 2024
@stevenwinship stevenwinship self-assigned this Nov 27, 2024
@stevenwinship stevenwinship moved this from This Sprint 🏃‍♀️ 🏃 to In Progress 💻 in IQSS Dataverse Project Nov 27, 2024
@qqmyers
Copy link
Member

qqmyers commented Nov 27, 2024

FWIW: It may be a problem putting a fix in flyway that just deletes an old one when a new one exists (as suggested) - that it would erase old requests, with their earlier creation timestamps from the db and there could be places that are tracking those/relying on those records. I'm not sure this is the case, but perhaps having a flyway query that doesn't remove the old ones if there are new ones is better, e.g. if the count of entries with request_state null or CREATED/GRANTED/REJECTED is 1, change the null (if it exists) to CREATED (the only state that used to exist), otherwise don't erase the old one (or perhaps better - back-date the new one to the old date and then delete the old one?). Once that's done the flyway could still add a non-null constraint (as is indicated in the code/would be applied on a fresh db).

@stevenwinship stevenwinship linked a pull request Dec 3, 2024 that will close this issue
Open
@stevenwinship stevenwinship removed their assignment Dec 3, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
FY25 Sprint 11 FY25 Sprint 11 (2024-11-20 - 2024-12-04) Size: 3 A percentage of a sprint. 2.1 hours. Type: Bug a defect
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql script/flyway to fix file access requests IQSS/dataverse
4 participants
@PaulBoon @qqmyers @cmbz @stevenwinship