Running Cohort Generation fails

[Minitour]

Expected behavior

Success

Actual behavior

It is trying to write something into a column that doesn't exist.

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO ohdsi.cohort_inclusion (cohort_definition_id, design_hash, rule_sequence, name, description) VALUES (?,?,?,?,?)]; nested exception is org.postgresql.util.PSQLException: ERROR: column "design_hash" of relation "cohort_inclusion" does not exist
  Position: 59

Steps to reproduce behavior

My docker compose file. vocab-loader is a custom image I wrote to download the vocabulary from athena and load it.

Simply load the vocabulary table (any other way) and try to create a cohort definition. The creation itself works but when I try to run it fails.

version: '3.7'

services:
  db:
    image: postgres:10.6-alpine
    restart: "no"
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - "15000:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

  vocab-loader:
    image: omop-vocab-loader
    environment:
      - 'PG_HOST=db'
      - 'PG_PORT=5432'
      - 'PGUSER=postgres'
      - 'PGPASSWORD=postgres'
      - 'DATABASE=postgres'

  broadsea-webtools:
    image: ohdsi/broadsea-webtools
    ports:
      - "15005:8080"
    volumes:
      - .:/tmp/drivers/:ro
      - ./config-local.js:/usr/local/tomcat/webapps/atlas/js/config-local.js:ro
    environment:
      - WEBAPI_URL=http://192.168.99.100:8080
      - env=webapi-postgresql
      - security_enabled=false
      - security_origin=*
      - datasource_driverClassName=org.postgresql.Driver
      - datasource_url=jdbc:postgresql://db:5432/postgres
      - datasource.cdm.schema=cdm
      - datasource.ohdsi.schema=ohdsi
      - datasource_username=postgres
      - datasource_password=postgres
      - spring.jpa.properties.hibernate.default_schema=ohdsi
      - spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
      - spring.batch.repository.tableprefix=ohdsi.BATCH_
      - flyway_datasource_driverClassName=org.postgresql.Driver
      - flyway_datasource_url=jdbc:postgresql://db:5432/postgres
      - flyway_schemas=ohdsi
      - flyway.placeholders.ohdsiSchema=ohdsi
      - flyway_datasource_username=postgres
      - flyway_datasource_password=postgres
      - flyway.locations=classpath:db/migration/postgresql

volumes:
  pgdata:
    driver: local

[Minitour]

I added the column manually and then I got the following error

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DELETE FROM ohdsi.cohort_cache WHERE design_hash = 879059627; DELETE FROM ohdsi.cohort_inclusion_result_cache WHERE design_hash = 879059627; DELETE FROM ohdsi.cohort_inclusion_stats_cache WHERE design_hash = 879059627; DELETE FROM ohdsi.cohort_summary_stats_cache WHERE design_hash = 879059627; DELETE FROM ohdsi.cohort_censor_stats_cache WHERE design_hash = 879059627]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "ohdsi.cohort_cache" does not exist
  Position: 13

[konstjar]

It looks like you have some issues with Results schema. Please review documentation https://github.com/OHDSI/WebAPI/wiki/CDM-Configuration#schema-setup

[Minitour]

@konstjar Thanks, this actually solved the issue. For some reason the tables were already there but with missing columns.

The solution was to generate the DDL and recreate the tables.