liquibase常用命令总结
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 |
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中有多个操作，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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都会用这个作为逻辑地址。
|
1 2 3 4 5 6 7 |
<?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>标签内：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<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就会有类似下面的报错：
|
1 2 3 |
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命令来生成。
|
1 2 3 4 |
<changeSet id="1.0.0-42" author="wiki"> <validCheckSum>7:21d239448c33f39a9300ea433349d470</validCheckSum> … // content of the changeset (with the update) </changeSet> |
或者
|
1 2 3 4 |
<changeSet id="1.0.0-42" author="wiki"> <validCheckSum>any</validCheckSum> … // content of the changeset (with the update) </changeSet> |
或者
|
1 2 3 4 |
<changeSet id="1.0.0-42" author="wiki"> <validCheckSum>*</validCheckSum> … // content of the changeset (with the update) </changeSet> |
或者
|
1 2 3 4 |
<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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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” 属性。
如下：
|
1 2 3 4 5 6 7 |
<?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" > |
或
|
1 2 3 4 5 |
<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，输出如下：
|
1 2 |
-- Changeset META-INF/resources/bizbase-db-changelog.xml::bizbase-20190413-1541::xxxx INSERT INTO `cgdb1025`.`testquote` (`join`) VALUES ('xxx'); |
总结：
公司用了liquibase后遇到不少问题，但是感觉貌似他们也不是太熟悉Liquibase的用法之类的，索性抽个时间看了下Liquibase的源代码，其中md5Sum的值的计算方式比我想的要复杂，看来果然还是不能瞎猜。看了Liquibase代码后，在为知笔记了写了点笔记，很乱，所以本文只是从笔记中抽出了部分整理后放到博客上，希望对大家有用，后面可能会不定期更新吧，有问题可以留言，我尽力解答。
转载请注明：大步's Blog » Liquibase常见问题总结
