Discover cohort endpoint (temporarily) returned sqlite db issue

[karafecho]

This issue is to report an error that arose when I was preparing for an ICEES+ demo. Specifically, the "discover cohort" endpoint at the asthma prod instance returned a sqlite db error to a parameter-specified query.

Query:

curl -X 'POST' \
  'https://icees-asthma.renci.org/patient/cohort' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{"TotalEDInpatientVisits":{"operator":">","value":0}}'

Error message:

"return value": "(sqlite3.OperationalError) database is locked\n(Background on this error at: https://sqlalche.me/e/14/e3q8)"

A query that didn't specify parameters {} ran just fine.

Hong was able to replicate the issue, but then ran it again at a later time and had no issues. Kara had the same experience. The issue appears to be a temporary database lock issue, since resolved, but may resurface at a later date.

[karafecho]

Note that the issue reported above arose again last Thursday, August 17 when I ran a query and again over the weekend when an independent user ran a query. In all cases, the error occurred at the "Discover Cohort" endpoint.

Example from independent user: "return value": "(sqlite3.OperationalError) database is locked\n(Background on this error at: https://sqlalche.me/e/14/e3q8)"} when I enter {"Sex":{"operator":"=","value":"Female"}}.

[karafecho]

The issue does not occur at PCD prod instance, although the query is slightly different (year was changed):

`curl -X 'POST' \
  'https://icees-pcd.renci.org/patient/cohort' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{"Sex2":{"operator":"=","value":"Female"},"year":{"operator":"=","value":"2020"}}'`

[karafecho]

The cohort cannot be found in the Dictionary, suggesting that the cohort was not successfully written to the sqlite db.

Query that generated the error:

curl -X 'POST' \
  'https://icees-asthma.renci.org/patient/cohort' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{"Sex2":{"operator":"=","value":"Female"},"year":{"operator":"=","value":"2010"}}'

Query of the dictionary:

curl -X 'GET' \
  'https://icees-asthma.renci.org/patient/cohort/dictionary' \
  -H 'accept: application/json'

Dictionary query response:

"return value": [
   {
     "cohort_id": "COHORT:1",
     "size": 165874,
     "features": {}
   },
   {
     "cohort_id": "COHORT:2",
     "size": 157889,
     "features": {
       "Active_In_Year": {
         "operator": "=",
         "value": 1
       }
     }
   },
   {
     "cohort_id": "COHORT:3",
     "size": 165874,
     "features": {
       "year": {
         "operator": "=",
         "value": 2010
       }
     }
   },
   {
     "cohort_id": "COHORT:4",
     "size": 22758,
     "features": {
       "Active_In_Year": {
         "operator": "=",
         "value": 1
       },
       "year": {
         "operator": "=",
         "value": 2010
       }
     }
   },
   {
     "cohort_id": "COHORT:5",
     "size": 13266,
     "features": [
       {
         "feature_name": "AgeStudyStart",
         "feature_qualifier": {
           "operator": "=",
           "value": "0-2"
         }
       }
     ]
   },
   {
     "cohort_id": "COHORT:6",
     "size": 159329,
     "features": {
       "TotalEDInpatientVisits": {
         "operator": ">",
         "value": 0
       }
     }
   },
   {
     "cohort_id": "COHORT:7",
     "size": 27317,
     "features": {
       "EstHouseholdIncome": {
         "operator": "=",
         "value": 1
       }
     }
   },
   {
     "cohort_id": "COHORT:8",
     "size": 27317,
     "features": {
       "EstHouseholdIncome": {
         "operator": "<",
         "value": 2
       }
     }
   },
   {
     "cohort_id": "COHORT:9",
     "size": 40718,
     "features": {
       "AgeStudyStart": {
         "operator": ">",
         "value": 55
       }
     }
   },
   {
     "cohort_id": "COHORT:10",
     "size": 40718,
     "features": {
       "AgeStudyStart": {
         "operator": ">",
         "value": 54
       }
     }
   },
   {
     "cohort_id": "COHORT:11",
     "size": 91627,
     "features": {
       "AgeStudyStart": {
         "operator": ">",
         "value": 50
       }
     }
   },
   {
     "cohort_id": "COHORT:1000",
     "size": 165874,
     "features": {}
   },
   {
     "cohort_id": "COHORT:12",
     "size": 159329,
     "features": {
       "TotalEDInpatientVisits": {
         "operator": ">",
         "value": 0
       },
       "year": {
         "operator": "=",
         "value": "2010"
       }
     }
   },
   {
     "cohort_id": "COHORT:13",
     "size": 19308,
     "features": {
       "TotalEDInpatientVisits": {
         "operator": ">",
         "value": 1
       },
       "year": {
         "operator": "=",
         "value": "2010"
       }
     }
   },
   {
     "cohort_id": "COHORT:14",
     "size": 11027,
     "features": {
       "Sex2": {
         "operator": "=",
         "value": "Female"
       },
       "TotalEDInpatientVisits": {
         "operator": ">",
         "value": 1
       },
       "year": {
         "operator": "=",
         "value": "2010"
       }
     }
   }
 ]
}

[karafecho]

Query at Asthma dev endpoint:

curl -X 'POST' \
  'https://icees-asthma-dev.apps.renci.org/patient/cohort' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{"Sex2":{"operator":"=","value":"Female"},"year":{"operator":"=","value":"2010"}}'

Successful query response:

  "return value": {
    "cohort_id": "COHORT:4",
    "size": 90782
  }
}

[karafecho]

Update: Hong identified a fix. She restarted the ICEES+ Asthma prod instance without touching the db, thus preserving all existing cohorts. Kara then tested the query that was producing the sqlite db issue and confirmed that the fix worked as intended.

Query and response:

curl -X 'POST' \
  'https://icees-asthma.renci.org/patient/cohort' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{"Sex2":{"operator":"=","value":"Female"},"year":{"operator":"=","value":"2010"}}'

  "return value": {
    "cohort_id": "COHORT:15",
    "size": 90782
  }

[karafecho]

Update from Slack, 04.17.2024:

Kara
Sure thing. I was creating a COHORT when I received the SQLiteDB issue. In the past, I'd stop there. But this time, I tested the Features functionality, and it worked just fine. Normally, the SQLiteDB issues take a while to resolve, so this seems surprising to me. Either the issue is functionality-specific, or it self-resolved super quick.

Hong Yi
15 minutes ago
Interesting. COHORT creation involves writing to database, which somehow triggered a db locked issue. Although features endpoints and other association endpoints only involve db read, the db locked issue should have prevented any further connections to the db being created for either read or write. This is definitely good to know. Perhaps it is worth adding this info to that issue for future reference.

[karafecho]

Note that the SQLite issue has surfaced for both me and Hong when running queries at endpoints other than the Discover Cohort endpoint.

Slack post from April 15:

Kara Fecho
10:33 AM
Thanks, Hong. I ran a few tests at the PROD endpoint to check the empty cohort bug and the other bugs we fixed recently. All looked fine, except that I received a SQLite error / db locked when running one last test to check the 9 vs >9 issue at the multivariate endpoint. I may try the query again later, but I think we're in good shape for now.