Liquibase 3.3 with Vertica DB : "DATABASECHANGELOG" already exists

General Discussion
1

Hi,

I have an issue when using Liquibase on Vertica DB.
The first execution works but the second time I have the following message :

DEBUG 9/3/14 10:30 AM: liquibase: Executing UPDATE database command: UPDATE VDMFIDEV1.DATABASECHANGELOGLOCK SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1
INFO 9/3/14 10:30 AM: liquibase: Successfully released change log lock
Unexpected error running Liquibase: [Vertica]VJDBC ROLLBACK: Object “DATABASECHANGELOG” already exists

SEVERE 9/3/14 10:30 AM: liquibase: [Vertica]VJDBC ROLLBACK: Object “DATABASECHANGELOG” already exists
liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE VDMFIDEV1.DATABASECHANGELOG (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)): [Vertica]VJDBC ROLLBACK: Object “DATABASECHANGELOG” already exists
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:122)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:112)
        at liquibase.changelog.StandardChangeLogHistoryService.init(StandardChangeLogHistoryService.java:172)
        at liquibase.Liquibase.checkLiquibaseTables(Liquibase.java:716)
        at liquibase.Liquibase.update(Liquibase.java:189)
        at liquibase.Liquibase.update(Liquibase.java:174)
        at liquibase.integration.commandline.Main.doMigration(Main.java:997)
        at liquibase.integration.commandline.Main.run(Main.java:170)
        at liquibase.integration.commandline.Main.main(Main.java:89)
Caused by: java.sql.SQLSyntaxErrorException: [Vertica]VJDBC ROLLBACK: Object “DATABASECHANGELOG” already exists
        at com.vertica.util.ServerErrorData.buildException(Unknown Source)
        at com.vertica.dataengine.VQueryExecutor.executeSimpleProtocol(Unknown Source)
        at com.vertica.dataengine.VQueryExecutor.execute(Unknown Source)
        at com.vertica.jdbc.common.SStatement.executeNoParams(Unknown Source)
        at com.vertica.jdbc.common.SStatement.execute(Unknown Source)
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:310)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
        … 9 more
Caused by: com.vertica.support.exceptions.SyntaxErrorException: [Vertica]VJDBC ROLLBACK: Object “DATABASECHANGELOG” already exists

I’m using Vertica Jdbc driver : vertica-jdbc-7.1.0-0.jar.

It is weird because Liquibase can see the databasechangeloglock table but not the databasechangelog  table which are in the same schema.

I have also tried to add Vertica extension but I have this exception :

Unexpected error running Liquibase: liquibase.exception.UnexpectedLiquibaseException: java.lang.NoSuchMethodError: liquibase.datatype.DataTypeFactory.fromDescription(Ljava/lang/String;)Lliquibase/datatype/LiquibaseDataType;

SEVERE 9/3/14 10:39 AM: liquibase: liquibase.exception.UnexpectedLiquibaseException: java.lang.NoSuchMethodError: liquibase.datatype.DataTypeFactory.fromDescription(Ljava/lang/String;)Lliquibase/datatype/LiquibaseDataType;
liquibase.exception.ChangeLogParseException: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.UnexpectedLiquibaseException: java.lang.NoSuchMethodError: liquibase.datatype.DataTypeFactory.fromDescription(Ljava/lang/String;)Lliquibase/datatype/LiquibaseDataType;

Does anynone know how to fix this issue ?

Thanks
M.Boutkhil

2

Hi mohamed ,

sorry for the late response,

I wrote the Vertica extension for liquibase and i’m currently working to upgrade it for version 7.1 and liquibase 3.2.2 

(it was written for version 6.1.2 of vertica and earlier liquibase version)

If you can share your changeset i’d be happy to debug/extend since as i said i currently working to update it.

From the stack trace you attached it seems that i don’t have support for a data type you defined - do you happen to use one of the new types define in vertica 7?

Regards,

 - Jony

3

Hi Jony,

Thanks for your response.

I have 2 xml files. At first level it has only an include file.

At the second level it contains the real action to do.
See the attached files. hope it helps you.

I’m not using new Vertica types. I’m just starting to use it with basic stuff.

Regards

Mohamed

4

Hi Mohamed,

I’ve created an initial update for the extension to work with vertica 7.1 on liquibase 3.2.2 (latest stable release)

also note it’s better to use XMLs as changes since it can better rollback and manage your changes (it treats SQLs as scripts - it doesn’t parse them to know what you want)

an example changeSet could be:

  3.    
  4.        
  5.            
  6.        
  7.        
  8.            
  9.        
  10.        
  11.            
  12.        
  13.    
I hope it will prove useful to you :)

Regards,

 - Jony

5

Hi Jony,

Thank you,  your upgrade works perfectly.

Thanks also for your advice regarding using xml instead sql. Currently we have chosen to use liquibase with sql file because it is a simpler method to move all projects (we have too many of them) into this new tool and also people here are not keen to manipulate xml files, especially production DBA.

Regards,
Mohamed