Liquibase snowflake is failing on second run

I am using Liquibase latest docker version and deploying script to snowflake.
First run is running fine, but for some reason 2nd run fails complaining liquibase metadata table already exists.
I tried different liquibase docker version, everything gave me the same results.

Below is the error message.

Starting Liquibase at 18:55:49 (version 4.6.2 #886 built at 2021-11-30 16:20+0000)
Liquibase Version: 4.6.2
Liquibase Community 4.6.2 by Liquibase
[2022-01-04 18:56:06] SEVERE [liquibase.integration] SQL compilation error:
Object ‘OPS.DBCHANGELOG’ already exists. [Failed SQL: (2002) CREATE TABLE ops.dbchangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]
liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: Unexpected error running Liquibase: SQL compilation error:
Object ‘OPS.DBCHANGELOG’ already exists.

We have been using the same Liquibase container for deploying SQL Server script they have been running fine with no issues,.

Below is the command we use to run

/liquibase/liquibase --logLevel=severe --defaultsFile=src/snowflake/liquibase.snowflakeops.properties --classpath=/liquibase/lib/snowflake-jdbc-3.13.5.jar:src/snowflake --url=“jdbc:snowflake://xxx.snowflakecomputing.com/?user=dbmonitor&db=dbmonitor&schema=public;” --username=dbmonitor --password=‘xxxx’ update

My changelog property file looks like this

<?xml version="1.0" encoding="UTF-8"?>




Anyhelp would be highly appreciated.

Thanks
Muthu

Are you doing something different with creating and defining your changelog table. Liquibase’s default changelog table is called DATABASECHANGELOG. If the name of the table in your instance is called DBCHANGELOG, maybe there is a script that you are using to create it outside of Liquibase? If so, I would check there.
Are you using multiple instances of snowflake databases and each one is writing to the OPS schema. Again by default, liquibase will create the DATABASECHANGELOG table in the schema you are using, in this case public, since it is creating it in the ops schema are there other instances that are also creating it there?

Hi Mike
Thanks for you reply. I got this working locally.
Using this xsd mentioned in below git repo

but when I run it through pipeline…I am getting error as below.

Get certified courses at learn.liquibase.com

Free schema change activity reports at

####################################################

Starting Liquibase at 16:07:17 (version 4.6.2 #886 built at 2021-11-30 16:20+0000)

Liquibase Version: 4.6.2

Liquibase Community 4.6.2 by Liquibase

[2022-01-06 16:07:26] SEVERE [liquibase.integration] Could not find directory or directory was empty for includeAll ‘/src/snowflake/Migrations/’

liquibase.exception.CommandExecutionException: liquibase.exception.LiquibaseException: Unexpected error running Liquibase: Could not find directory or directory was empty for includeAll ‘/src/snowflake/Migrations/’

at liquibase.command.CommandScope.execute(CommandScope.java:156)

I am lost here…why it working locally but not in pipeline.

It could be that your configuration is different locally than it is in the pipeline.
The file you shared has include file
the error you show has includeAll
so I don’t know what is real vs example code.
In a pipeline that you are building, typically src is not in the path for running liquibase but the target directory is.
relativeToChangelogFile = true means look in the same place as the changelog file you have defined.
Hope this helps.

Hi Mike,
sorry for the confusion. This what we have in property file.
includeAll path="/src/snowflake/Migrations"
includeAll path="/src/snowflake/Repeatable"

Below is the error msg we get
Caused by: liquibase.exception.ChangeLogParseException: liquibase.exception.SetupException: Could not find directory or directory was empty for includeAll ‘/src/snowflake/Migrations/’

at liquibase.parser.core.xml.AbstractChangeLogParser.parse(AbstractChangeLogParser.java:25)

but when I change the xsd to either xml/ns/dbchangelog/dbchangelog-3.8.xsd or xml/ns/dbchangelog/dbchangelog-r.4.3.xsd

liquibase pipeline runs fine…but no update to snowflake.

but I get info as below
[2022-01-06 18:35:33] INFO [liquibase.database] Set default schema name to null

[2022-01-06 18:35:33] INFO [liquibase.database] Error getting default schema

liquibase.exception.DatabaseException: Error executing SQL call current_schema: SQL compilation error:
syntax error line 1 at position 19 unexpected ‘’.
[2022-01-06 18:35:33] INFO [liquibase.database] Set default schema name to null

Thanks

Hi @emamuthu .
One I would check the target directory for paths since maven is using that as the directory tree for its top level directory
Second I would remove the beginning / in your path and possibly use snowflake/Migrations and snowflake/Repeatable as your directory structure.
Maven will copy your resources folder into target/classes and run from there for your install goal.

Sorry, you said you were using docker.
You have defaults file = src/snowflake/liquibase.snowflakeops.properties
And you have includeAll path =/src/snowflake/Migrations
And you have relativeToChangelogFile=true

And inside of the docker container that you are using for Liquibase, there is not a /src folder.
If you mount the directories using the -v option to your local directory then you have access to /liquibase/changelog subdirectories and /liquibase/classpath directories.
See here for more information.

Hi Mike
Thanks for you reply. we made it working both in local docker and using Concurse pipeline. Only issue is no changes were made if you execute from pipeline. Local docker container is running fine.
Completely lost …since both of them using same code.

Hi @emamuthu
Taking a step back here, so is the original error you were seeing is fixed:

Object ‘OPS.DBCHANGELOG’ already exists. [Failed SQL: (2002) CREATE TABLE ops.dbchangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]

And now you are seeing a different problem?

I am facing the same issue Object ‘DATABASECHANGELOG’ already exists if i run second time without any changes in the change log files. can you please share how to resovle this issue

Hi @Kowsalya Thanks for joining the community. Which version of Liquibase are you running?
We fixed that in later versions of Liquibase. Please upgrade to the latest version and try again.