Vertica update fails with "DATABASECHANGELOG" already exists

nicopadu commented 3 years ago

Environment

Liquibase Version: 4.4.3

Liquibase Integration & Version: CLI

Liquibase Extension(s) & Version: liquibase-verticaDatabase-4.4.3.jar

Database Vendor & Version:

Vertica verticadocker/vertica-ce:10.1.1-0
vertica-jdbc-10.1.1-0.jar

Operating System Type & Version: Liquibase (Ubuntu 20.04), Vertica (docker image)

Description

Update command only works if database name, schema and username are all aligned with same value. In case that any of them has a different value following error occurs:

created tables might be created in default public schema (ignoring schema parameters - if database name and schema are not the same)
in all cases fails after second update command with error Unexpected error running Liquibase: [Vertica][VJDBC](4213) ROLLBACK: Object "DATABASECHANGELOG" already exists

Steps To Reproduce

Using vertica-ce docker image from here: https://hub.docker.com/r/verticadocker/vertica-ce

# run vertica (creates a defulat 'vmart' database)
docker run -d -p 5433:5433 --env APP_DB_USER='my_user' --env APP_DB_PASSWORD='my_pass' --name vertica_ce verticadocker/vertica-ce:10.1.1-0

# create a sample schema
docker exec vertica_ce /opt/vertica/bin/vsql -c "CREATE SCHEMA my_schema;"

this is the content of liquibase.properties

liquibase.hub.mode=off
changeLogFile=./changelog/changelog.xml
liquibase.liquibaseTablespaceName=my_schema
liquibase.liquibaseCatalogName=my_schema
liquibase.liquibaseSchemaName=my_schema
liquibase.command.defaultSchemaName=my_schema
liquibase.command.defaultCatalogName=my_schema

this is the changelog.xml

<?xml version="1.0" encoding="UTF-8"?>  
<databaseChangeLog  
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" 
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
    <changeSet id="create table1" author="author1">
         <sql>
            create table t1 (v varchar(50));
        </sql>
   </changeSet>
</databaseChangeLog>

first update command -- success

bin/liquibase update --url=jdbc:vertica://localhost:5433/vmart --username=my_user --password=my_pass

...
Starting Liquibase at 00:00:00 (version 4.4.3 #53 built at 2021-08-05 18:32+0000)
Liquibase Version: 4.4.3
Liquibase Community 4.4.3 by Datical
checking for vertica
Liquibase command 'update' was executed successfully.

second update command -- fail

bin/liquibase update --url=jdbc:vertica://localhost:5433/vmart --username=my_user --password=my_pass

...
Starting Liquibase at 00:00:00 (version 4.4.3 #53 built at 2021-08-05 18:32+0000)
Liquibase Version: 4.4.3
Liquibase Community 4.4.3 by Datical
checking for vertica
Unexpected error running Liquibase: [Vertica][VJDBC](4213) ROLLBACK: Object "DATABASECHANGELOG" already exists

Actual Behavior

Database, Schema and Username are different:

database: vmart
schema: my_schema
userbame: my_user
sample command: bin/liquibase update --url=jdbc:vertica://localhost:5433/vmart --username=my_user --password=my_pass

DATABASECHANGELOG & DATABASECHANGELOGLOCK tables are created in schema "my_schema", but target table "t1" is created in default "public" schema. Second execution of update command fails with Object "DATABASECHANGELOG" already exists Probably related to https://github.com/liquibase/liquibase/issues/2030

Database, Schema and Username all with same value

In case all values are the same, full procedure works... unfortunately this enforces a very rigid configuration

Steps to make it run:

# create a new schema with same name as user
docker exec vertica_ce /opt/vertica/bin/vsql -c "CREATE SCHEMA my_user;"

update liquibase.properties with following values:

liquibase.hub.mode=off
changeLogFile=./changelog/changelog.xml
liquibase.liquibaseTablespaceName=my_user
liquibase.liquibaseCatalogName=my_user
liquibase.liquibaseSchemaName=my_user
liquibase.command.defaultSchemaName=my_user
liquibase.command.defaultCatalogName=my_user

run update command

bin/liquibase update --url=jdbc:vertica://localhost:5433/my_user --username=my_user --password=my_pass

...
Starting Liquibase at 00:00:00 (version 4.4.3 #53 built at 2021-08-05 18:32+0000)
Liquibase Version: 4.4.3
Liquibase Community 4.4.3 by Datical
checking for vertica
Liquibase command 'update' was executed successfully.

following update command works as expected. all tables are created under schema "my_user"

Expected/Desired Behavior

Ideally users should be able to configure independent values for db name, schema and username

Note

Originally created here: https://github.com/liquibase/liquibase/issues/2038

┆Issue is synchronized with this Jira Bug by Unito

larissavmss commented 2 years ago

Hi Nicolas, how are you? Were you able to solve the "DATABASECHANGELOG" already exists problem?

nicopadu commented 2 years ago

Hi Larissa, nop we were not able to fix it. We're still with the known way of keeping schema & username with same value

acscott commented 9 months ago

I ran into the same issue and resolved it by making the login user to SQL Server a member of the role db_owner

hanee-shousha commented 7 months ago

Hi @nicopadu and @larissavmss , I was getting same error like you for the DATABASECHANGELOG already exists, but I have tried only the parameter below with both liquibase and liquibase-vertica jar version 4.26.0 and it works now successfully.

liquibase.liquibaseSchemaName=public

liquibase / liquibase-vertica