Liquibase试图创建DATABASECHANGELOG，尽管它存在(Vertica)

Question

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

它没有检测到DATABASECHANGELOG已经存在，所以它试图创建它，但失败了。

Liquibase究竟是如何发现该表是否存在的？我看了github的代码，但没能弄清楚（我的Java技能不够强，这个项目结构让我死了…）

我正在使用Vertica，我的数据库中有多个模式，我正试图部署在一个名为"someschema"的特定模式上。执行用户someuser在someschema上有一个默认的搜索路径。

嗯，让我们看看一些输出：

1. 首先，我使用updateSQL:运行

   $ 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');
   

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
   

3. 正如您所看到的，表和元数据表都已创建：

   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)
   

4. 但当我再次运行时，我没有得到任何操作，而是得到了一个错误：

   $ 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+扩展