liquibase - Liquibase 试图创建同一个表两次

Question

我是 liquibase 的新手，我正在尝试运行一些测试（版本 3.6.3）。

1. 我创建了一个名为“liquibase_testing”的空数据库
2. 我创建了一个如下所示的 migrations.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-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
        <includeAll path="migrations" relativeToChangelogFile="true"/>
    </databaseChangeLog>

3. 我创建了一个简单的 CREATE TABLE 脚本并在migrations目录中保存为001-liquibase-testing-create-names-table.sql. 它看起来像这样：

CREATE TABLE dbo.names (
name_id int identity,
first_name nvarchar(100),
last_name nvarchar(100),
primary key clustered (name_id)
)

4. 我使用 cli 运行 liquibase 迁移，如下所示：

    ./_liquibase/liquibase-3.6.3-bin/liquibase 
    --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver 
    --classpath=./_jdbc/sqljdbc_7.2/enu/mssql-jdbc-7.2.0.jre8.jar      
    --changeLogFile=./liquibase_testing/resources/migrations.xml 
    --url="jdbc:sqlserver://myDbServer;database=liquibase_testing"      
    --username=liquibase_testing      
    --password=password     
    --logLevel=debug      
    migrate 

我得到这个错误：

    Starting Liquibase at Thu, 14 Feb 2019 12:54:52 EST (version 3.6.3 built at 2019-01-29 11:34:48)
    Unexpected error running Liquibase: There is already an object named 'names' in the database. [Failed SQL: CREATE TABLE dbo.names (
    name_id int identity,
    first_name nvarchar(100),
    last_name nvarchar(100),
    primary key clustered (name_id)
    )]
    liquibase.exception.MigrationFailedException: Migration failed for change set liquibase_testing/resources/migrations/001-liquibase-testing-create-names-table.sql::raw::includeAll:
         Reason: liquibase.exception.DatabaseException: There is already an object named 'names' in the database. [Failed SQL: CREATE TABLE dbo.names (
    name_id int identity,
    first_name nvarchar(100),
    last_name nvarchar(100),
    primary key clustered (name_id)
    )]
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)
        at liquibase.Liquibase.update(Liquibase.java:202)
        at liquibase.Liquibase.update(Liquibase.java:179)
        at liquibase.integration.commandline.Main.doMigration(Main.java:1220)
        at liquibase.integration.commandline.Main.run(Main.java:199)
        at liquibase.integration.commandline.Main.main(Main.java:137)
    Caused by: liquibase.exception.DatabaseException: There is already an object named 'names' in the database. [Failed SQL: CREATE TABLE dbo.names (
    name_id int identity,
    first_name nvarchar(100),
    last_name nvarchar(100),
    primary key clustered (name_id)
    )]
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:356)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:125)
        at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1229)
        at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1211)
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:600)
        ... 7 common frames omitted
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: There is already an object named 'names' in the database.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2930)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:744)
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352)
        ... 12 common frames omitted


    For more information, please use the --logLevel flag

因此该names表被创建，但由于某种原因，Liquibase 尝试第二次创建它并失败。我在数据库上运行了探查器跟踪，可以看到 2 个CREATE TABLE调用。

标签： liquibase

Answer 1

与 liquibase 3.2.0 + postgresql 相同的问题。Postgresql 报告异常/错误“关系表名已存在”-但在通过 SpringLiquibase 自动更新开始之前，数据库中没有该表。这意味着 liquibase 多次执行 CREATE TABLE。最奇怪的是从整个 sql 脚本之类的

<sql>
    CREATE TABLE ... (...);
    CREATE INDEX ...;
</sql>

liquibase 只执行两次 CREATE TABLE 语句。CREATE INDEX 没有任何问题！好吧，postgresql 允许轻松修复它：

<sql>
    CREATE TABLE IF NOT EXISTS ... (...);
    CREATE INDEX ...;
</sql>