Achilles analysis run gets stuck, no error message or anything

Implementers
1

Hi,

I am following this instructions to run Achilles on our backend CDM tables, and after issue this command in R,

achilles(connectionDetails,
  cdmDatabaseSchema = "cdm_std",
  resultsDatabaseSchema="results_phi",
  vocabDatabaseSchema = "cdm_std",
  numThreads = 1,
  sourceName = "MSDW2",
  cdmVersion = "5")

I see the following output,

Connecting using SQL Server driver
2021-06-07 10:13:06	Beginning single-threaded execution
Connecting using SQL Server driver
  |======================================================================| 100%
Executing SQL took 0.0216 secs
2021-06-07 10:13:07	warning: The 'oracleTempSchema' argument is deprecated. Use 'tempEmulationSchema' instead.
This warning is displayed once every 8 hours.

2021-06-07 10:13:08	Executing multiple queries. This could take a while
2021-06-07 10:13:08	Analysis 0 (Source name) -- START
  |======================================================================| 100%
Executing SQL took 8.88 secs
2021-06-07 10:13:17	[Main Analysis] [COMPLETE] 0 (8.883032 secs)
2021-06-07 10:13:17	Analysis 1 (Number of persons) -- START
  |======================================================================| 100%
Executing SQL took 4.12 secs
2021-06-07 10:13:21	[Main Analysis] [COMPLETE] 1 (4.118535 secs)
2021-06-07 10:13:21	Analysis 2 (Number of persons by gender) -- START
  |======================================================================| 100%
Executing SQL took 4.14 secs
2021-06-07 10:13:25	[Main Analysis] [COMPLETE] 2 (4.145491 secs)
2021-06-07 10:13:25	Analysis 3 (Number of persons by year of birth) -- START
  |======================================================================| 100%
Executing SQL took 5.26 secs
2021-06-07 10:13:30	[Main Analysis] [COMPLETE] 3 (5.258203 secs)
2021-06-07 10:13:30	Analysis 4 (Number of persons by race) -- START
  |======================================================================| 100%
Executing SQL took 5.42 secs
2021-06-07 10:13:36	[Main Analysis] [COMPLETE] 4 (5.419431 secs)
2021-06-07 10:13:36	Analysis 5 (Number of persons by ethnicity) -- START
  |======================================================================| 100%
Executing SQL took 5.22 secs
2021-06-07 10:13:41	[Main Analysis] [COMPLETE] 5 (5.224588 secs)
2021-06-07 10:13:41	Analysis 7 (Number of persons with invalid provider_id) -- START
  |======================================================================| 100%
Executing SQL took 2.34 secs
2021-06-07 10:13:43	[Main Analysis] [COMPLETE] 7 (2.340697 secs)
2021-06-07 10:13:43	Analysis 8 (Number of persons with invalid location_id) -- START
  |======================================================================| 100%
Executing SQL took 8.57 secs
2021-06-07 10:13:52	[Main Analysis] [COMPLETE] 8 (8.568147 secs)
2021-06-07 10:13:52	Analysis 9 (Number of persons with invalid care_site_id) -- START
  |======================================================================| 100%
Executing SQL took 0.816 secs
2021-06-07 10:13:53	[Main Analysis] [COMPLETE] 9 (0.818444 secs)
2021-06-07 10:13:53	Analysis 10 (Number of all persons by year of birth by gender) -- START
  |======================================================================| 100%
Executing SQL took 5.07 secs
2021-06-07 10:13:58	[Main Analysis] [COMPLETE] 10 (5.067017 secs)
2021-06-07 10:13:58	Analysis 11 (Number of non-deceased persons by year of birth by gender) -- START
  |======================================================================| 100%
Executing SQL took 10.1 secs
2021-06-07 10:14:08	[Main Analysis] [COMPLETE] 11 (10.098807 secs)
2021-06-07 10:14:08	Analysis 12 (Number of persons by race and ethnicity) -- START
  |======================================================================| 100%
Executing SQL took 5.13 secs
2021-06-07 10:14:13	[Main Analysis] [COMPLETE] 12 (5.127117 secs)
2021-06-07 10:14:13	Analysis 101 (Number of persons by age, with age at first observation period) -- START
  |======================================================================| 100%
Executing SQL took 4.38 secs
2021-06-07 10:14:17	[Main Analysis] [COMPLETE] 101 (4.377698 secs)
2021-06-07 10:14:17	Analysis 102 (Number of persons by gender by age, with age at first observation period) -- START
  |======================================================================| 100%
Executing SQL took 4.36 secs
2021-06-07 10:14:22	[Main Analysis] [COMPLETE] 102 (4.357273 secs)
2021-06-07 10:14:22	Analysis 103 (Distribution of age at first observation period) -- START
  |                                                                      |   0%

and then it just stopped - not continue, not error out, just stuck there. Could someone please let us know what could be wrong? there is no error messages, not logs. There is only one table created, ACHILLES_analysis, no other tables.

Thanks!

2

similar issue but we are stuck on 117. i will have a look on how 116 and 117 are different that one is done and one is stuck. both seem to run on the same observation_period table.

i am not confident i will find the solution just comparing the sql but it seems it is the only weapon i have.

we are on postgresql

3

The solution I had was to add this to your achilles’ call, i.e., this call, achilles(connectionDetails,cdmDatabase....:

outputFolder = "/any/path/you/want"

this will give you some logging information, and that was what helped me to see what went wrong. hope this helps you.

4

Oh I think I have my outputFolder correct. It generates a error report, only if I force the SQL to stop from psql itself.

However, the current issue is that query #117 is stuck, so no error report is generated there is nothing I can read lol

5

oh okay. just in case - are you running using multi-thread mode? if NOT, it could take long time to run and give you the feeling it is stuck…

6

We used single threaded mode and it is stuck on query 117.

We tried multi threaded mode later and obviously things were faster. But it turns out that it is still stuck on query 117. All other threads were also locked up at around 200 or 201. Mostly I think the issue was observation_period was locked by query 117. Checked that on psql query that looks like most queries are stuck on lock/LWlock.

BTW, query 117 was stuck for like 2 days over the weekend. observation_period table is a relatively small one in our dataset so I think it is not that we have to wait. And it was those better tables where all cells are defined, so there are no empty or NULL cells. I didn’t check if all cells are valid though as the dataset was provided by a outside vender. Sometimes I really wonder do I have to check if a bunch of dates are on the 13th month lol

7

oh I see. so you did everything I know of. Did you try to run Achilles Heel, which gives you a check on the data quality? wondering if that might help a little.

8

Also, since you are also on Achilles, you might know the answer to my issue -

did you have to run the following?

https://yourserver/WebAPI/ddl/results?dialect=yourDBFlavor&schema=results&vocabSchema=yourVocabSchema&tempSchema=your_atlas_temp&initConceptHierarchy=true

which gives me a lots of SQL queries. If you did have to run the above, how did you use the generated queries?

9

oh sorry don’t really have the capacity to build the webAPI although I am mildly interested.

There is an underlying issue. The real reason I am running Achilles is that our group intended to join another study. However, running the script, it is stuck at 3% and it never worked. So it was suggested that wen can run Achilles to see if our database is actually working.

Well, it worked most of the time, until it hitted query 117. I really have no idea what is right and what is wrong.

I tried to do very basic query on each table, sometime as trival as

SELECT count(1) from schema.person;

It works, despite it might take quite some time since it is scanning the whole table.