Liquibase unable to execute schema (Snowflake) with mixed case eg. (This_Schema)

Question

I have tried using liquibase tool for our snowflake db. It is all working with where SCHEMA name is in all CAPITAL(UPPERCASE). But liquibase is not picking up any of my schema's with mixed case, eg (This_Schema).

I have tried putting this but didn't help.

<defaultSchemaName>This_Schema</defaultSchemaName>

POM.XML configuration example:

          <driver>net.snowflake.client.jdbc.SnowflakeDriver</driver>
          <url>jdbc:snowflake://${env.SNOWFLAKE_ACCOUNT}.eu-central-1.snowflakecomputing.com/?db=${env.SNOWFLAKE_DB}&amp;schema=${env.SNOWFLAKE_SCHEMA}&amp;warehouse=${env.SNOWFLAKE_WH}&amp;role=${env.SNOWFLAKE_ROLE}</url>
          <username>${env.SNOWFLAKE_USERNAME}</username>
          <password>${env.SNOWFLAKE_PASSWORD}</password>

Error setting up or running Liquibase: liquibase.exception.DatabaseException: SQL compilation error: [ERROR] Schema 'LIQUIBASE_DB.THIS_SCHEMA' does not exist. [Failed SQL: CREATE TABLE THIS_SCHEMA.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP_NTZ, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]

NOTE: "This_Schema" is the name of my schema as it is showing here, but upon executing liquibase update this automatically changes to UPPERCASE value as in error above.

Not familiar with LiquidBase, but can you actually put quotes around the schema in your env.SNOWFLAKE_SCHEMA value? That is how Snowflake would expect to have a mixed-case schema name. — Mike Walton
Hi @MikeWalton I tried what you said, it ran the liquibase once and created both DATABASECHANGELOG and DATABASECHANELOGLOCK. It is executing, but stuck with another error now: Error setting up or running Liquibase: SQL compilation error: [ERROR] Object 'DATABASECHANGELOG' already exists. — hello_ish
What liquibase trying to do is: INFO 12/10/19 09:04: liquibase: Successfully acquired change log lock **INFO 12/10/19 09:05: liquibase: Creating database history table with name: DATABASECHANGELOG** INFO 12/10/19 09:05: liquibase: Successfully released change log lock But what it should actually do is: INFO 12/10/19 09:00: liquibase: Successfully acquired change log lock **INFO 12/10/19 09:00: liquibase: Reading from DATABASECHANGELOG** INFO 12/10/19 09:00: liquibase: Successfully released change log lock — hello_ish

Eric Mamet Eric Mamet · Accepted Answer · 2019-10-14T15:38:04

Found this comment in the README file from the liquibase snowflake extension.

The Snowflake JDBC drivers implementation of DatabaseMetadata.getTables() hard codes quotes around the catalog, schema and table names, resulting in queries of the form:
show tables like 'DATABASECHANGELOG' in schema "sample_db"."sample_schema"
This results in the DATABASECHANGELOG table not being found, even after it has been created. Since Snowflake stores catalog and schema names in upper case, the getJdbcCatalogName returns an upper case value.

Could this explain your problems?...

Liquibase unable to execute schema (Snowflake) with mixed case eg. (This_Schema)

1 Answers