Liquibase尝试创建DATABASECHANGELOG（尽管它存在）（Vertica）

Question

我对Liquibase（最新版本3.2.2)有一个奇怪的问题。

它没有检测到DATABASECHANGELOG已经存在，因此尝试创建它但失败。

$ java -jar liquibase.jar --driver com.vertica.jdbc.Driver --classpath /opt/vertica/java/lib/vertica-jdbc.jar --changeLogFile databaseChangeLog.json --url "jdbc:vertica://myverticahost.com:5433/insight" --username someuser --password mypwd updateSQL
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: databaseChangeLog.json
-- Ran at: 9/8/14 10:19 PM
-- Against: someuser@jdbc:vertica://myverticahost.com:5433/insight
-- Liquibase version: 3.2.2
-- *********************************************************************

-- Create Database Lock Table
CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM DATABASECHANGELOGLOCK;

INSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, FALSE);

-- Lock Database
-- Create Database Change Log Table
CREATE TABLE 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));

-- Changeset databaseChangeLog.json::1::eyaly
create table eyal_test (id varchar(20));

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, LIQUIBASE) VALUES ('1', 'eyaly', 'databaseChangeLog.json', CURRENT_TIMESTAMP, 1, '7:dc0cc25bf42c4738799e73a22426a51a', 'sql', '', 'EXECUTED', '3.2.2');

$ java -jar liquibase.jar --driver com.vertica.jdbc.Driver --classpath /opt/vertica/java/lib/vertica-jdbc.jar --changeLogFile databaseChangeLog.json --url "jdbc:vertica://myverticahost.com:5433/insight" --username someuser --password mypwd update

如您所见，创建了表和元数据表：

dbadmin=> select * from tables where table_Schema='someschema' and (table_name ilike 'databasechange%' or table_name = 'eyal_test');
  table_schema_id  | table_schema |     table_id      |      table_name       |     owner_id      | owner_name  | is_temp_table | is_system_table | is_flextable | system_table_creator | partition_expression |          create_time          | table_definition 
-------------------+--------------+-------------------+-----------------------+-------------------+-------------+---------------+-----------------+--------------+----------------------+----------------------+-------------------------------+------------------
 45035996273719012 | someschema   | 63050394834440938 | DATABASECHANGELOGLOCK | 45035996273750006 | someuser    | f             | f               | f            |                      |                      | 2014-09-08 15:20:12.147975-07 | 
 45035996273719012 | someschema   | 63050394834441092 | DATABASECHANGELOG     | 45035996273750006 | someuser    | f             | f               | f            |                      |                      | 2014-09-08 15:20:13.965053-07 | 
 45035996273719012 | someschema   | 63050394834441216 | eyal_test             | 45035996273750006 | someuser    | f             | f               | f            |                          |                      | 2014-09-08 15:20:14.304429-07 | 
(3 rows)
dbadmin=> select * from stage.databasechangelog;
 ID | AUTHOR |        FILENAME        |        DATEEXECUTED        | ORDEREXECUTED | EXECTYPE |               MD5SUM               | DESCRIPTION | COMMENTS | TAG | LIQUIBASE 
----+--------+------------------------+----------------------------+---------------+----------+------------------------------------+-------------+----------+-----+-----------
 1  | eyaly  | databaseChangeLog.json | 2014-09-08 15:20:14.327397 |             1 | EXECUTED | 7:dc0cc25bf42c4738799e73a22426a51a | sql         |          |     | 3.2.2
(1 row)
dbadmin=> select * from stage.databasechangeloglock;
 ID | LOCKED |       LOCKGRANTED       |                 LOCKEDBY                  
----+--------+-------------------------+-------------------------------------------
  1 | t      | 2014-09-08 22:20:12.626 | mymachine (fe80:0:0:0:2000:aff:fea4:69e%2)
(1 row)

但当我再次运行时,不是没有任何操作,而是得到一个错误：

$ java -jar liquibase.jar --driver com.vertica.jdbc.Driver --classpath /opt/vertica/java/lib/vertica-jdbc.jar --changeLogFile databaseChangeLog.json --url "jdbc:vertica://myverticahost.com:5433/insight" --username someuser --password mypwd update
Unexpected error running Liquibase: Error executing SQL CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)): [Vertica][VJDBC](4213) ROLLBACK: Object "DATABASECHANGELOGLOCK" already exists

Answer 1

有一个Liquibase Vertica扩展，它在Liquibase中添加了对Vertica的支持。参见https://liquibase.jira.com/wiki/display/contrib/liquibase+vertica+extension