OHDSI Home | Forums | Wiki | Github

MSSQL Sequence error when saving Concept sets or Cohorts

Developers
#1

Good Afternoon :),

I have been running into an error when attempting to save concept sets from atlas to a MSSQL CDM.

OHDSI CDM V5 Database [OHDSI-CDMV5]
CDM: dbo, Vocabulary: dbo, Results: ohdsi, CEM: ohdsi
sql server v5.0 17-DEC-19

Them error I am seeing when attempting to save a new concept set or cohort is:
“methodName”:“runWorker”,“fileName”:“ThreadPoolExecutor.java”,“lineNumber”:1149,“className”:“java.util.concurrent.ThreadPoolExecutor”,“nativeMethod”:false},{
“methodName”:“run”,“fileName”:“ThreadPoolExecutor.java”,“lineNumber”:624,“className”:“java.util.concurrent.ThreadPoolExecutor$Worker”,“nativeMethod”:false},{
“methodName”:“run”,“fileName”:“TaskThread.java”,“lineNumber”:61,“className”:“org.apache.tomcat.util.threads.TaskThread$WrappingRunnable”,“nativeMethod”:false},{
“methodName”:“run”,“fileName”:“Thread.java”,“lineNumber”:748,“className”:“java.lang.Thread”,“nativeMethod”:false}],“sqlstate”:“S0002”,“nextException”:null,“errorCode”:208,“message”:“Invalid object name ‘ohdsi.concept_set_sequence’.”,“localizedMessage”:“Invalid object name ‘ohdsi.concept_set_sequence’.”,“suppressed”:[]},“stackTrace”:[{
“methodName”:“convert”,“fileName”:“SQLStateConversionDelegate.java”,“lineNumber”:106,“className”:“org.hibernate.exception.internal.SQLStateConversionDelegate”,“nativeMethod”:false}

I have verified that the sequences exist in the CDM Database Ohdsi schema.

When I look at the sql being executed it is not in a familiar syntax to me and does not execute properly on MSSQL Sql Server 2016
’
Profiler - select next_val as id_val from ohdsi.concept_set_sequence with (updlock, holdlock, rowlock)"
This fails with error “Invalid object name ‘ohdsi.concept_set_sequence’”

The following sql works but is not what is being executed: SELECT NEXT VALUE FOR [ohdsi].[concept_set_sequence]

It feels like I messed up a configuration setting. Any thoughts or suggestions are very much appreciated.

The only other sql that was executed when attem[ting to save a concept set was:
“select userentity0_.ID as ID1_49_, userentity0_.last_viewed_notifications_time as last_vie2_49_, userentity0_.LOGIN as LOGIN3_49_, userentity0_.NAME as NAME4_49_ from ohdsi.SEC_USER userentity0_ where userentity0_.LOGIN=@P0 ‘,N’@P0 nvarchar(4000)’,N’anonymous’”

Is it possible that this is the root cause and I need to configure some users?

Thank you for reading!,
Dee

#2

Updated: sorry for my first post, i mis-read what you described.

Yes, the statement select next_val as id_val from ohdsi.concept_set_sequence with (updlock, holdlock, rowlock) should actually be SELECT NEXT VALUE FOR [ohdsi].[concept_set_sequence] as you said. So, you’re on the right track for what should be happening here.

We do specify the dialect in the pom.xml for mssql, my guess is that maybe you haven’t built the WAR file using the correct syntax (the syntax you have looks like it’s trying to query from a sequence table, not a sequence object.

Can you confirm which version of WebAPI you are running on?

#3

Thank you so much for taking the time to answer my question :smile:
I have a CDM Database with a dbo and ohdsi schema. When I run the Broadsea (docker containers) install the sequences and tables are created in the CDM database under the OHDSI schema.
Atlas is able to connect to those tables but saving a cohort or concept set throws an error. The error states that the sequence does not exist. This is covering the real error which lies in the syntax of the sql being executed.

The webapi and Atlas versions ar 2.7.2

It does sound like I missed a dialect setting in the Broadsea configuration files. The SQL being generated may be for Oracle? I will keep looking.

Thank you!,
Dee

#4

I will need to defer to @lee_evans, since you’re running this via broadsea. @lee_evans: can you confirm the 2.7.2 docker container that is hosting webapi and cdm databases are working properly for you? I’m unable to test this configuration.

Update:
Looking at the source code for 2.7.2 for the ConceptSet entity (defined here), the identity specification is using the SequenceStyleGenerator and specifying the concept_set_sequence. So, I am confused why the query that is executed looks like a ‘table-based-sequence’ instead of the sequence-style.

#5

Chris,
If you recommend building out the components individually rather than leveraging Broadsea I will give that a try.
It is likely what we will want to do if we implement the full OHDSI stack anyway.

Thank you,
Dee

#6

It is unlikely that issue that you are experiencing is a matter of using Broadsea–the dockerized version of the Atlas–rather than deploying the WAR directly to Tomcat. Using Broadsea is quite useful for efficiently deploying the OHDSI stack. Rather as @Chris_Knoll was suggesting, it appears that the SQL dialect may be improperly configured. Have you checked that the YAML confirguration file (docker-compose.yml) contains the correct properties. E.g something like

- env=webapi-mssql
- spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
2 Likes
#7

t_abdul_basser,
Thank you for replying.
Below is my docker-compose.yml Environment section with the IP masked.
This was based on https://github.com/OHDSI/Broadsea/blob/master/sqlserver/docker-compose.yml

environment:
 - WEBAPI_URL=http://###.###.###.###:8080
 - env=webapi-mssql
 - datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
 - datasource.url=jdbc:sqlserver://###.###.###.###:14333;databasename=CDM
 - datasource.cdm.schema=dbo
 - datasource.ohdsi.schema=ohdsi
 - datasource.username=refuser
 - datasource.password=abcd1234
 - spring.jpa.properties.hibernate.default_schema=ohdsi
 - spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect
 - spring.batch.repository.tableprefix=cdm.ohdsi.BATCH_
 - flyway.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
 - flyway.datasource.url=jdbc:sqlserver://###.###.###.###:14333;databasename=CDM
 - flyway.schemas=ohdsi
 - flyway.placeholders.ohdsiSchema=ohdsi
 - flyway.datasource.username=refuser
 - flyway.datasource.password=abcd1234
 - flyway.locations=classpath:db/migration/sqlserver

I will swap the SQLServer2008Dialect dialect for spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect and let you now how it goes.

#8

Try this as @t_abdul_basser suggests: change that line to SQLServer2012Dialect . Sql Server 2012 supports sequences.

Edit: sorry I missed the past part where you said you’d try it! Give it a shot, and please let us know if that is the issue. If it is, we can update the master branch’s docker-compose.yml with the modification.

1 Like
#9

Thank you @Chris_Knoll and @t_abdul_basser !
The dialect change resolved the issue.

1 Like
#10

PR submitted.

1 Like
t