Rest API Database Lock - Githubissues

sprintell commented 10 months ago

Reviewing the strategy to overcome Database Lock issue in the Rest API

sprintell commented 3 months ago

[x] Schedule a meeting with Mark Head in DBA
[x] Compare different options of what could be done to bring and end to the problem
[x] Implement the solution

sprintell commented 3 months ago

We had a meeting with Mark Head concerning theis problem. The root of this problem is that whenever SPOTPRO database refresh is executed in Delphix for any of the other Oracle database environments including the SPOTPUB Database (SPOTDV1, SPOTDV2, SPOTDV3, SPOTTST, SPOTPUB, SPOTPUBFALL, SPOTCTV, SPOTPREL), the SPOTPRO "gwas user" password overwrites the password of the given database.

The agreed solution is to create other "non gwas" users and password for each Oracle database in SPOTPRO. if this "non gwas" user is used to connect from the application to the Oracle Database, the password will still be working after SPOTPRO refresh, because this new users and passwords will be thesame after DB Refresh. With this, the lock will not occur again.

Ticket INC0060321 is used to work on this fix.

sprintell commented 3 months ago

Mark is now on holiday, when this was tested on the last data release, the changes Mark Head made did not work.

Waiting for Mark to fix the changes he made ...

sprintell commented 2 months ago

Mark has recreated all the users in upper case in SPOTPRO, sorry my mistake on creating them lower case. Passwords remain the same

sprintell commented 2 months ago

We have created "non gwas" users and password for each Oracle database.
This was created in SPOTPRO and copied over when all the Databases are Refreshed including SPOTPUB
This "non gwas" user will be used connect from the application to the Rest API Db
This new users and passwords will be thesame after DB Refresh.
With this, the lock will not occur again.
The configurations will be updated in conflunce document: Confluence -> What Runs Where -> GWAS Project - Servers, Databases and Infrastructure Administration

I've tested these for the 7 databases, they are all now working perfectly.

While testing, I discovered that some Oracke View Tables that are accessible when we connect to the Oracle via the gwas user are not accessible when we connect with this new user.

We might just need to double check why this is the case.

sprintell commented 1 month ago

@sprintell test Mark head's final solution to be sure new user accounts can access the Views in Oracle

sajo-ebi commented 2 weeks ago

there were multiple issue due to which new DB pointing was not implemented

the select query was not working on the tables for the new user , Mark fixed it
the schema migration table data was not copied which was causing the migration scripts to get executed , need to replicate this in lower environment befpre trying in production

sajo-ebi commented 1 week ago

Still getting issues with running rest API locally with the new DB user , the sequence are not accessible by the API , Mark from DBA had applied some fix , still it didn't work, will continue to coordinate with DBA to fix

EBISPOT / goci-rest

Rest API Database Lock #50