I’ve been trying to set up the WebAPI and Atlas on Azure following the steps defined in this repo. https://github.com/microsoft/OHDSIonAzure
With setting this up I’ve come across a lot of issues most of them I was already able to fix.
For example what I’ve done:
- Translate PostGres SQL scripts to SQL server
- Update Dockerfile since it was using the latest version.
The problem is that both the webapi are able to start up but I’m still facing 2 issues.
One is the error Column 'webapi.tag.created_by_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
(full error log below).
The second occurs once I’m doing some actions in the Atlas GUI. This gives me a BAD SQL grammar error. (full error below). This error runs on Achilles cache tables which i do not have. Is this achilles required for running Atlas + WebAPI?
Is there anyone who can help me validate my setup?
Output /WebAPI/info
{
"version": "2.10.0",
"buildInfo": {
"artifactVersion": "WebAPI 2.10.0",
"build": "NA",
"timestamp": "Thu Mar 23 10:40:18 UTC 2023",
"branch": "73f36c3e914e25186e39b6c170cf330525d64ff7",
"commitId": "73f36c3",
"atlasRepositoryInfo": {
"milestoneId": 39,
"releaseTag": "*"
},
"webapiRepositoryInfo": {
"milestoneId": 39,
"releaseTag": "*"
}
},
"configuration": {
"security": {
"samlActivated": false,
"enabled": false,
"samlEnabled": false
},
"vocabulary": {
"solrEnabled": false
},
"plugins": {
"atlasgisEnabled": false
},
"person": {
"viewDatesPermitted": false
},
"heracles": {
"smallCellCount": "5"
}
}
}
Error 1
2023-03-27T14:36:34.365252976Z deploy-script stdout | 2023-03-27 14:35:22.825 WARN taskScheduler-8 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - - SQL Error: 8120, SQLState: S0001
2023-03-27T14:36:34.365257076Z 2023-03-27 14:35:22.825 ERROR taskScheduler-8 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - - Column 'webapi.tag.created_by_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Error 2
2023-03-27T15:25:12.205628122Z deploy-script stdout | 2023-03-27 15:19:29.637 ERROR taskScheduler-6 org.ohdsi.webapi.tag.TagService - - Cannot refresh tags statistics
2023-03-27T15:25:12.205632022Z 2023-03-27 15:19:29.637 INFO taskScheduler-6 org.ohdsi.webapi.tag.TagService - - Finishing tags statistics refreshing
2023-03-27T15:25:12.205635822Z deploy-script stdout | 2023-03-27 15:19:29.956 INFO taskScheduler-8 org.ohdsi.webapi.executionengine.service.ScriptExecutionServiceImpl - - Invalidating execution engine based analyses
2023-03-27T15:25:12.205639823Z deploy-script stdout | 2023-03-27 15:25:12.194 ERROR http-nio-8080-exec-7 org.ohdsi.webapi.util.GenericExceptionMapper - - org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [(SELECT
2023-03-27T15:25:12.205650523Z aa1.analysis_name AS attribute_name,
2023-03-27T15:25:12.205654423Z ar1.stratum_1 AS attribute_value
2023-03-27T15:25:12.205658123Z FROM webapi.ACHILLES_analysis aa1
2023-03-27T15:25:12.205661723Z INNER JOIN
2023-03-27T15:25:12.205665423Z webapi.achilles_results ar1
2023-03-27T15:25:12.205669124Z ON aa1.analysis_id = ar1.analysis_id
2023-03-27T15:25:12.205672724Z WHERE aa1.analysis_id = 0
2023-03-27T15:25:12.205676424Z
2023-03-27T15:25:12.205680024Z UNION
2023-03-27T15:25:12.205683624Z
2023-03-27T15:25:12.205687124Z SELECT
2023-03-27T15:25:12.205690824Z aa1.analysis_name AS attribute_name,
2023-03-27T15:25:12.205695024Z cast(ar1.count_value AS VARCHAR) AS attribute_value
2023-03-27T15:25:12.205698925Z FROM webapi.ACHILLES_analysis aa1
2023-03-27T15:25:12.205702525Z INNER JOIN
2023-03-27T15:25:12.205706125Z webapi.achilles_results ar1
2023-03-27T15:25:12.205709725Z ON aa1.analysis_id = ar1.analysis_id
2023-03-27T15:25:12.205713425Z WHERE aa1.analysis_id = 1
2023-03-27T15:25:12.205717125Z )
2023-03-27T15:25:12.205721125Z ORDER BY attribute_name DESC]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'webapi.ACHILLES_analysis'.