liquibase常用命令总结

Usage: java -jar liquibase.jar [options] [command]

Standard Commands:
 update                         Updates database to current version
 updateSQL                      Writes SQL to update database to current
                                version to STDOUT
 updateCount <num>              Applies next NUM changes to the database
 updateCountSQL <num>           Writes SQL to apply next NUM changes
                                to the database
 updateToTag <tag>              Updates the database to the changeSet with the
                                specified tag
 updateToTagSQL <tag>           Writes (to standard out) the SQL to update to
                                the changeSet with the specified tag
 rollback <tag>                 Rolls back the database to the the state is was
                                when the tag was applied
 rollbackSQL <tag>              Writes SQL to roll back the database to that
                                state it was in when the tag was applied
                                to STDOUT
 rollbackToDate <date/time>     Rolls back the database to the the state is was
                                at the given date/time.
                                Date Format: yyyy-MM-dd'T'HH:mm:ss
 rollbackToDateSQL <date/time>  Writes SQL to roll back the database to that
                                state it was in at the given date/time version
                                to STDOUT
 rollbackCount <value>          Rolls back the last <value> change sets
                                applied to the database
 rollbackCountSQL <value>       Writes SQL to roll back the last
                                <value> change sets to STDOUT
                                applied to the database
 futureRollbackSQL              Writes SQL to roll back the database to the 
                                current state after the changes in the 
                                changeslog have been applied
 futureRollbackSQL <value>      Writes SQL to roll back the database to the 
                                current state after <value> changes in the 
                                changeslog have been applied
 futureRollbackFromTagSQL <tag> Writes (to standard out) the SQL to roll back
                                the database to its current state after the
                                changes up to the specified tag have been
                                applied
 updateTestingRollback          Updates database, then rolls back changes before
                                updating again. Useful for testing
                                rollback support
 generateChangeLog              Writes Change Log XML to copy the current state
                                of the database to standard out
 snapshot                       Writes the current state
                                of the database to standard out
 snapshotReference              Writes the current state
                                of the referenceUrl database to standard out

Diff Commands
 diff [diff parameters]          Writes description of differences
                                 to standard out
 diffChangeLog [diff parameters] Writes Change Log XML to update
                                 the database
                                 to the reference database to standard out

Documentation Commands
 dbDoc <outputDirectory>         Generates Javadoc-like documentation
                                 based on current database and change log

Maintenance Commands
 tag <tag string>          'Tags' the current database state for future rollback
 tagExists <tag string>    Checks whether the given tag is already existing
 status [--verbose]        Outputs count (list if --verbose) of unrun changesets
 unexpectedChangeSets [--verbose]
                           Outputs count (list if --verbose) of changesets run
                           in the database that do not exist in the changelog.
 validate                  Checks changelog for errors
 calculateCheckSum <id>    Calculates and prints a checksum for the changeset
                           with the given id in the format filepath::id::author.
 clearCheckSums            Removes all saved checksums from database log.
                           Useful for 'MD5Sum Check Failed' errors
 changelogSync             Mark all changes as executed in the database
 changelogSyncSQL          Writes SQL to mark all changes as executed 
                           in the database to STDOUT
 markNextChangeSetRan      Mark the next change changes as executed 
                           in the database
 markNextChangeSetRanSQL   Writes SQL to mark the next change 
                           as executed in the database to STDOUT
 listLocks                 Lists who currently has locks on the
                           database changelog
 releaseLocks              Releases all locks on the database changelog
 dropAll                   Drop all database objects owned by user

Required Parameters:
 --changeLogFile=<path and filename>        Migration file
 --username=<value>                         Database username
 --password=<value>                         Database password. If values
                                            is PROMPT, Liquibase will
                                            prompt for a password
 --url=<value>                              Database URL

Optional Parameters:
 --classpath=<value>                        Classpath containing
                                            migration files and JDBC Driver
 --driver=<jdbc.driver.ClassName>           Database driver class name
 --databaseClass=<database.ClassName>       custom liquibase.database.Database
                                            implementation to use
 --propertyProviderClass=<properties.ClassName>  custom Properties
                                            implementation to use
 --defaultSchemaName=<name>                 Default database schema to use
 --contexts=<value>                         ChangeSet contexts to execute
 --labels=<expression>                      Expression defining labeled
                                            ChangeSet to execute
 --defaultsFile=</path/to/file.properties>  File with default option values
                                            (default: ./liquibase.properties)
 --delimiter=<string>                       Used with executeSql command to set
                                            the string used to break up files
                                            that consist of multiple statements.
 --driverPropertiesFile=</path/to/file.properties>  File with custom properties
                                            to be set on the JDBC connection
                                            to be created
 --changeExecListenerClass=<ChangeExecListener.ClassName>     Custom Change Exec
                                            listener implementation to use
 --changeExecListenerPropertiesFile=</path/to/file.properties> Properties for
                                            Custom Change Exec listener
 --liquibaseCatalogName=<name>              The name of the catalog with the
                                            liquibase tables
 --liquibaseSchemaName=<name>               The name of the schema with the
                                            liquibase tables
 --databaseChangeLogTableName=<name>        The name of the Liquibase ChangeLog
                                            table (default: DATABASECHANGELOG)
 --databaseChangeLogLockTableName=<name>    The name of the Liquibase ChangeLog
                                            Lock table
                                            (default: DATABASECHANGELOGLOCK)
 --liquibaseSchemaName=<name>               The name of the schema with the
                                            liquibase tables
 --includeSystemClasspath=<true|false>      Include the system classpath
                                            in the Liquibase classpath
                                            (default: true)
 --promptForNonLocalDatabase=<true|false>   Prompt if non-localhost
                                            databases (default: false)
 --logLevel=<level>                         Execution log level
                                            (debug, info, warning, severe, off
 --logFile=<file>                           Log file
 --currentDateTimeFunction=<value>          Overrides current date time function
                                            used in SQL.
                                            Useful for unsupported databases
 --outputDefaultSchema=<true|false>         If true, SQL object references
                                            include the schema name, even if
                                            it is the default schema. 
                                            Defaults to true
 --outputDefaultCatalog=<true|false>        If true, SQL object references
                                            include the catalog name, even if
                                            it is the default catalog.
                                            Defaults to true
 --outputFile=<file>                        File to write output to for commands
                                            that write output, e.g. updateSQL.
                                            If not specified, writes to sysout.
 --help                                     Prints this message
 --version                                  Prints this version information

Required Diff Parameters:
 --referenceUsername=<value>                Reference Database username
 --referencePassword=<value>                Reference Database password. If
                                            value is PROMPT, Liquibase will
                                            prompt for a password
 --referenceUrl=<value>                     Reference Database URL

Optional Diff Parameters:
 --defaultCatalogName=<name>                Default database catalog to use
 --defaultSchemaName=<name>                 Default database schema to use
 --referenceDefaultCatalogName=<name>       Reference database catalog to use
 --referenceDefaultSchemaName=<name>        Reference database schema to use
 --schemas=<name1,name2>                    Database schemas to include
                                            objects from in comparison
 --referenceSchemas=<name1,name2>           Reference database schemas to
                                            include objects from in comparison
                                            only needed if different than
                                            --schemas
 --outputSchemaAs=<name1,name2>             On diffChangeLog/generateChangeLog,
                                            use these names as schemaName
                                            instead of the real names.
 --includeCatalog=<true|false>              If true, the catalog will be
                                            included in generated changeSets
                                            Defaults to false
 --includeSchema=<true|false>               If true, the schema will be
                                            included in generated changeSets
                                            Defaults to false
 --referenceDriver=<jdbc.driver.ClassName>  Reference database driver class name
 --dataOutputDirectory=DIR                  Output data as CSV in the given 
                                            directory
 --diffTypes                                List of diff types to include in
                                            Change Log expressed as a comma
                                            separated list from: tables, views,
                                            columns, indexes, foreignkeys,
                                            primarykeys, uniqueconstraints
                                            data.
                                            If this is null then the default
                                            types will be: tables, views,
                                            columns, indexes, foreignkeys,
                                             primarykeys, uniqueconstraints.

Change Log Properties:
 -D<property.name>=<property.value>         Pass a name/value pair for
                                            substitution in the change log(s)

Default value for parameters can be stored in a file called
'liquibase.properties' that is read from the current working directory.

Full documentation is available at
http://www.liquibase.org/documentation/command_line.html


Process finished with exit code 0

生成某个changeSet的md5Sum

md5Sum生成规则(md5Sum与filepath无关)
如果一个changeSet中有多个操作,如下:
    <changeSet author="alexy" id="bizbase-20181113-1811" dbms="mysql">
        <preConditions onFail="MARK_RAN">
            <not>
                <columnExists schemaName="${schema.dlsys}" tableName="tc_wf_trans_def" columnName="trans_priority" ></columnExists>
            </not>
        </preConditions>
        <addColumn schemaName="${schema.dlsys}" tableName="tc_wf_trans_def">
            <column name="trans_priority" type="int" defaultValue="-100"/>
        </addColumn>
        <addColumn schemaName="${schema.dlsys}" tableName="temp_trans_inst">
            <column name="trans_priority" type="int"/>
        </addColumn>
    </changeSet>
则liquibase会为每个<addColumn>生成一个md5(这里有两个addColumn对象),然后拼接这两个对象的md5,再生成整个changeLog对象的md5Sum。
见:liquibase.changelog.ChangeSet#generateCheckSum
    public CheckSum generateCheckSum() {
        if (checkSum == null) {
            StringBuffer stringToMD5 = new StringBuffer();
            for (Change change : getChanges()) {
                stringToMD5.append(change.generateCheckSum()).append(":");
            }

            for (SqlVisitor visitor : this.getSqlVisitors()) {
                stringToMD5.append(visitor.generateCheckSum()).append(";");
            }


            checkSum = CheckSum.compute(stringToMD5.toString());
        }

        return checkSum;
    }

注意:Change接口有generateCheckSu()方法有多个子类重写了。

changeSet的id规则

liquibase判断两个changset是否是同一个就是根据id来判断的。id是由“filepath::id::author”组成的。对应databasechangelog表中的以下字段:

logicalFilePath

假设有A和B两个项目,如果我想用B项目单独测试A项目的某个changeSet,我新建一个B/changelog.xml文件,存放待验证的changeset。但是 A项目的FilePath(changelog文件到路径为) A/a-changelog.xml 。而liquibase判断两个changset是否是同一个就是根据id来判断的,id是由“filepath::id::author”组成的,所以如果A和B的filepath(即changelog.xml文件路径)不同,则liquibase就不会认为这个同一个changeset了。所以,这就是logicalFilePath属性的意义。
可以放在<databaseChangeLog>标签内,即整个changelog.xml都会用这个作为逻辑地址。
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog logicalFilePath="META-INF/resources/bizbase-db-changelog.xml"
        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.0.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

或者<changeSet>标签内:

 <changeSet author="alexy" id="bizbase-20181113-1811" dbms="mysql" logicalFilePath="META-INF/resources/bizbase-db-changelog.xml">
        <preConditions onFail="MARK_RAN">
            <not>
                <columnExists schemaName="${schema.dlsys}" tableName="tc_wf_trans_def" columnName="trans_priority" ></columnExists>
            </not>
        </preConditions>
        <addColumn schemaName="${schema.dlsys}" tableName="tc_wf_trans_def">
            <column name="trans_priority" type="int" defaultValue="-100"/>
        </addColumn>
        <addColumn schemaName="${schema.dlsys}" tableName="temp_trans_inst">
            <column name="trans_priority" type="int"/>
        </addColumn>
    </changeSet>

 

问题1:修改changeSet,避免md5Sum校验问题

当一个changelog执行后,则会为在databasechangelog表中为每一个changeSet生成一个md5Sum。如果我们后面因为某些原因再次修改通一个changeSet就会有类似下面的报错:

2017-05-06 10:52:15,082 | ERROR | Error while applying liquibase changelogs db/changelog/wiki.db.changelog-1.0.0.xml - Cause : Validation Failed:
     1 change sets check sum
          db/changelog/wiki.db.changelog-1.0.0.xml::1.0.0-42::wiki is now: 7:21d239448c33f39a9300ea433349d470

这是因为liquibase每次启动都会将已执行的changeSet与changelog.xml中的id相同的changeSet进行md5Sum比较,如果你修改了某个changeSet,而导致md5Sum的改变就会有上面的报错了。

解决办法:

方法1.使用使用<validCheckSum>标签。

使用具体的md5Sum,该值既可以从报错中获得,也可以直接使用liquibase的calculateCheckSum命令来生成。

<changeSet id="1.0.0-42" author="wiki">
    <validCheckSum>7:21d239448c33f39a9300ea433349d470</validCheckSum>
    … // content of the changeset (with the update)
</changeSet>

或者

<changeSet id="1.0.0-42" author="wiki">
    <validCheckSum>any</validCheckSum>
    … // content of the changeset (with the update)
</changeSet>

或者

<changeSet id="1.0.0-42" author="wiki">
    <validCheckSum>*</validCheckSum>
    … // content of the changeset (with the update)
</changeSet>

或者

<changeSet id="1.0.0-42" author="wiki">
    <validCheckSum>all</validCheckSum>
    … // content of the changeset (with the update)
</changeSet>

方法2. 手动修改databasechangelog表中的数据

自己去databasechangelog表中,将对应的changeSet的md5Sum字段改为最新的md5值或者是把该字段清空。

官方的文档有些不是很详细,最好的办法是直接看Liquibase的源代码,下面附上liquibase中changeSet的md5Sum比对的代码和注释

liquibase/changelog/ChangeSet.java:

    
	public boolean isCheckSumValid(CheckSum storedCheckSum) {
		//注意:storedCheckSum:databasechangelog表中该changeSet的md5Sum值。
		//validCheckSums : 即<validCheckSum>标签的值所组成的列表,既然是列表,则说明一个changeSet元素可以添加多个<validCheckSum>标签
        // no need to generate the checksum if any has been set as the valid checksum
        for (CheckSum validCheckSum : validCheckSums) {
			//如果<validCheckSum>标签的值为“any”,“all”或者“*”,则认为该changeSet没有修改过
            if (validCheckSum.toString().equalsIgnoreCase("1:any") || validCheckSum.toString().equalsIgnoreCase("1:all") || validCheckSum.toString().equalsIgnoreCase("1:*")) {
                return true;
            }
        }
		//currentMd5Sum : 表示当前changeSet的md5Sum值
        CheckSum currentMd5Sum = generateCheckSum();
		//currentMd5Sum为空,则返回true
        if (currentMd5Sum == null) {
            return true;
        }
		//storedCheckSum(意思是表里的md5Sum)为空,则返回true
        if (storedCheckSum == null) {
            return true;
        }
		//如果表里的与xml中的md5Sum一致,则返回true
        if (currentMd5Sum.equals(storedCheckSum)) {
            return true;
        }
		//或者是当前xml中changeSet的md5Sum值与任何一个<validCheckSum>标签的值相等时,则返回true
        for (CheckSum validCheckSum : validCheckSums) {
            if (currentMd5Sum.equals(validCheckSum) || storedCheckSum.equals(validCheckSum)) {
                return true;
            }
        }
        return false;
    }

 

问题2  表字段名中包含关键字,导致sql执行失败

由于有些表的字段与数据库的关键字一样,就会导致sql执行失败,我们需要将这些字段用反引号(quotetick)包裹起来,liquibase为我们提供了配置项。

可以在 “<databaseChangeLog> 或者 <changeset>中加入 objectQuotingStrategy=”QUOTE_ALL_OBJECTS” 属性。

如下:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog logicalFilePath="META-INF/resources/bizbase-db-changelog.xml"
                   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.0.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd"  objectQuotingStrategy="QUOTE_ALL_OBJECTS" >

<changeSet id="bizbase-20190413-1541" author="dabu" objectQuotingStrategy="QUOTE_ALL_OBJECTS">
        <insert tableName="testquote">
            <column name="join">xxx</column>
        </insert>
    </changeSet>

 

objectQuotingStrategy有三种策略:

  • LEGACY //默认策略
  • QUOTE_ALL_OBJECTS //对所有字段加反引号(建议使用)
  • QUOTE_ONLY_RESERVED_WORDS  //只对关键字加反引号

测试如下:

当objectQuotingStrategy=”QUOTE_ALL_OBJECTS ” ,使用liquibase的updateSQL,输出如下:

--  Changeset META-INF/resources/bizbase-db-changelog.xml::bizbase-20190413-1541::xxxx
INSERT INTO `cgdb1025`.`testquote` (`join`) VALUES ('xxx');

 

 

 


总结:

公司用了liquibase后遇到不少问题,但是感觉貌似他们也不是太熟悉Liquibase的用法之类的,索性抽个时间看了下Liquibase的源代码,其中md5Sum的值的计算方式比我想的要复杂,看来果然还是不能瞎猜。看了Liquibase代码后,在为知笔记了写了点笔记,很乱,所以本文只是从笔记中抽出了部分整理后放到博客上,希望对大家有用,后面可能会不定期更新吧,有问题可以留言,我尽力解答。

2 thoughts on “Liquibase常见问题总结”
  1. 请教一个问题,我初始化完以后,总是提示 Table ‘databasechangelog’ already exists这个错误,用的是mysql5.7

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.