...

Panel

Summary	Extension to support the tool `pt-online-schema-change` from Percona Toolkit. This extension replaces a couple of the default changes to use `pt-online-schema-change` instead of SQL. This allows to perform a non-locking database upgrade.
Current Version	1.45.10
Author	Andreas Dangel (andreas.dangel [at] adangel.org)
Issue Tracking	https://github.com/adangel/liquibase-percona/issues
Source Repository	https://github.com/adangel/liquibase-percona.git
Maven Coordinates	com.github.adangel.liquibase.ext:liquibase-percona
Supported Database	MySQL

...

October 19, 2014	1.0.0	compatible with liquibase 3.2.x
November 6, 2014	1.1.0	compatible with liquibase 3.3.0
July 26, 2015	1.1.1	Fixed #1: Tables with foreign keys
April 2, 2016	1.2.0	Fixed #2: Adding indexes via pt-online-schema-change Fixed #3: Altering column data types via pt-online-schema-change Added configuration property "liquibase.percona.skipChanges" Upgraded liquibase to 3.4.2
September 13, 2016	1.2.1	PR #4: Allow passing additional command line options to pt-online-schema-change PR #5: Support afterColum attribute
December 18, 2016	1.3.0	Upgraded liquibase to 3.5.3 Support for MySQL Connector 6.0.x in addition to 5.1.x. Fixed #7: Foreign key constraints of AddColumn is ignored Fixed #8: Support addForeignKeyConstraintChange, addUniqueConstraintChange Fixed #9: Support for enabling pt-online-schema-changes on a per-change basis Fixed #10: Build fails with java7: UnsupportedClassVersion when running DatabaseConnectionUtilTest.testGetPasswordMySQL_6
July 21, 2017	1.3.1	Fixed #12: Cannot run migrations with the percona extension on a Spring Boot app with embedded Tomcat
July 21, 2017	1.4.0	Fixed #13: Use default liquibase.properties as fallback
September 27, 2018	1.4.1	Fixed #16: Failing test PerconaAddForeignKeyConstraintChangeTest Fixed #17: Include Percona Toolkit into integration test Fixed #18: Use spotbugs instead of findbugs Fixed #19: Upgrade liquibase to 3.5.4 Fixed #21: Couldn't determine password: JdbcConnection is unsupported: dbcp.PoolingDataSource$PoolGuardConnectionWrapper Fixed #23: Add support for dbcp2

Attachments

Readme

Supported Databases

MySQL is the only supported database. The extension checks whether it is being run against a MySQL database. If not, it falls back to the default changes provided by liquibase-core.

Liquibase version(s) tested against

Liquibase 3.2.0 (liquibase-percona 1.0.0)
Liquibase 3.3.0 (liquibase-percona 1.1.1)
Liquibase 3.3.5 and 3.4.2 (liquibase-percona 1.2.1)
Liquibase 3.3.5, 3.4.2, and 3.5.1 (liquibase-percona 1.2.2)
Liquibase 3.3.5, 3.4.2, and 3.5.3 (liquibase-percona 1.3.1, 1.4.0)
Liquibase 3.3.5, 3.4.2, and 3.5.4 (liquibase-percona 1.4.1)

Supported Changes and examples

The following changes are supported:

...

September 30, 2018

1.5.0

pt-online-schema-change is executed now with the option --nocheck-unique-key-change. This enables to add unique indexes, but can cause data loss, since duplicated rows are ignored. See Percona Toolkit Documentation for more information.

The plugin is only compatible with version 3.0.12 or later of Percona Toolkit.

Upgraded liquibase to 3.5.5
Verified compatibility to liquibase 3.6.2
Fixed #14: Rollback of foreign key constraint changing constraint names problem
Fixed #15: Unique key constraint cannot be added
Fixed #20: Support "UsePercona flag" in XML changelogs
Fixed #22: Cross database bug

Attachments

Readme

Supported Databases

MySQL is the only supported database. The extension checks whether it is being run against a MySQL database. If not, it falls back to the default changes provided by liquibase-core.

Liquibase version(s) tested against

Liquibase 3.2.0 (liquibase-percona 1.0.0)
Liquibase 3.3.0 (liquibase-percona 1.1.1)
Liquibase 3.3.5 and 3.4.2 (liquibase-percona 1.2.1)
Liquibase 3.3.5, 3.4.2, and 3.5.1 (liquibase-percona 1.2.2)
Liquibase 3.3.5, 3.4.2, and 3.5.3 (liquibase-percona 1.3.1, 1.4.0)
Liquibase 3.3.5, 3.4.2, and 3.5.4 (liquibase-percona 1.4.1)
Liquibase 3.3.5, 3.4.2, 3.5.5, and 3.6.2 (liquibase-percona 1.5.0). Percona Toolkit 3.0.12.

Supported Changes and examples

The following changes are supported:

AddColumn

Since: liquibase-percona 1.0.0

Automatic rollback supported? yes

Example:

<changeSet id="2" author="Alice">
    <addColumn tableName="person">
        <column name="address" type="varchar(255)"/>
    </addColumn>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="ADD COLUMN address VARCHAR(255)" ...

AddForeignKeyConstraint

Since: liquibase-percona 1.03.0

Automatic rollback supported? yes

Example:

<changeSet id="2" author="Alice">3" author="Alice">
    <addForeignKeyConstraint constraintName="fk_person_address"
       <addColumn tableNamereferencedTableName="person"> referencedColumnNames="id"
       <column namebaseTableName="address" typebaseColumnNames="varchar(255)person_id"/>
    </addColumn>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="ADD COLUMN address VARCHAR(255-change --alter="ADD CONSTRAINT fk_person_address FOREIGN KEY (person_id) REFERENCES person (id)" ...

...

AddUniqueConstraint

Since: liquibase-percona 1.3.0

Automatic rollback supported? yes

Example:

<changeSet id="32" author="Alice">
    <addForeignKeyConstraint<addUniqueConstraint constraintNamecolumnNames="fk_person_address"
        referencedTableName="person" referencedColumnNames="id"
        baseTableName="address" baseColumnNames="person_idid, name" tableName="person" constraintName="uq_id_name"/>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="ADD CONSTRAINT fkuq_personid_addressname FOREIGN KEYUNIQUE (person_id) REFERENCES person (id, name)" ...

...

CreateIndex

Since: liquibase-percona 1.32.0

Automatic rollback supported? yes

...

<changeSet id="2" author="Alice">
    <addUniqueConstraint<createIndex columnNamesindexName="id, nameemailIdx" tableName="person" constraintName="uq_id_name"/>unique="true">
        <column name="email"/>
    </createIndex>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="ADD CONSTRAINT uq_id_name UNIQUE (id, nameUNIQUE INDEX emailIdx (email)" ...

...

DropColumn

Since: liquibase-percona 1.20.0

Automatic rollback supported? yesno

Example:

<changeSet id="2" author="Alice">
    <createIndex indexName="emailIdx" tableName="person" unique="true">
 ">
    <dropColumn  <column nametableName="person" columnName="emailage"/>
 
  </createIndex>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="ADDDROP UNIQUE INDEX emailIdx (email)COLUMN age" ...

...

DropForeignKeyConstraint

Since: liquibase-percona 1.03.0

Automatic rollback supported? no

Example:

<changeSet id="24" author="Alice">
    <dropColumn<dropForeignKeyConstraint tableNamebaseTableName="personaddress" columnNameconstraintName="agefk_person_address" />
</changeSet>

Corresponding command:

pt-online-schema-change --alter="DROP COLUMN ageFOREIGN KEY _fk_person_address" ...

...

DropUniqueConstraint

Since: liquibase-percona 1.3.0

Automatic rollback supported? no

Example:

<changeSet id="43" author="Alice">
    <dropForeignKeyConstraint<dropUniqueConstraint baseTableNametableName="addressperson" constraintName="fkuq_personid_addressname" />
</changeSet>

Corresponding command:

pt-online-schema-change --alter="DROP FOREIGN KEY uq_fkid_person_addressname" ...

...

DropIndex

Since: liquibase-percona 1.32.0

Automatic rollback supported? no

...

<changeSet id="3" author="Alice">
    <dropUniqueConstraint<dropIndex tableNameindexName="personemailIdx" constraintNametableName="uq_id_nameperson"/>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="DROP KEY uq_id_nameINDEX emailIdx" ...

...

ModifyDataType

Since: liquibase-percona 1.2.0

Automatic rollback supported? no

Example:

<changeSet id="32" author="Alice">
    <modifyDataType <dropIndex indexNametableName="person" columnName="emailIdxemail" tableNamenewDataType="personVARCHAR(400)"/>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="DROPMODIFY INDEX emailIdxemail VARCHAR(400)" ...

ModifyDataType

Since: liquibase-percona 1.2.0

Automatic rollback supported? no

Example:

<changeSet id="2" author="Alice">

`Configuration`

`UsePercona flag`

Each change allows to enable or disable the usage of percona toolkit via the property usePercona. By default, the percona toolkit is used, see also the system property liquibase.percona.defaultOn.

Example:

- changeSet:
    id: 2
    author: Alice
    changes:
      - addColumn:
         <modifyDataType tableName="person" columnName="email" newDataType="VARCHAR(400)"/>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="MODIFY email VARCHAR(400)" ...

`Configuration`

`UsePercona flag`

Each change allows to enable or disable the usage of percona toolkit via the property usePercona. By default, the percona toolkit is used, see also the system property liquibase.percona.defaultOn.

Example:

- changeSet:: person
          usePercona: false
          columns:
       id: 2    - authorcolumn:
   Alice     changes:       - addColumnname: address
         tableName: person           usePercona: false
          columns:
            - column:
      type: varchar(255)

This flag exists since liquibase-percona 1.3.0

It is supported by using the YAML format and since liquibase 3.6.0, you can use it in XML changesets, too:

<addColumn tableName="person" xmlns:liquibasePercona="http://github.com/adangel/liquibase-percona" liquibasePercona:usePercona="false">

...

<column

...

This flag exists since liquibase-percona 1.3.0

...

name="address" type="varchar(255)"/>
</addColumn>

`System Properties`

The extension supports the following java system properties:

liquibase.percona.failIfNoPT: true/false. Default: false. If set to true, the database update will fail, if the command pt-online-schema-change is not found. This can be used, to enforce, that percona toolkit is used.
liquibase.percona.noAlterSqlDryMode: true/false. Default: false. When running updateSQL or rollbackSQL in order to generate a migration SQL file, the command line, that would be executed, will be added as a comment. In addition, the SQL statements (as produced by liquibase-core) will also be generated and output into the migration file. This allows to simply execute the generated migration SQL to perform an update. However, the Percona toolkit won't be used. If this property is set to true, then no such SQL statements will be output into the migration file.
liquibase.percona.skipChanges: comma separated list of changes. Default: <empty>. This option can be used in order to selectively disable one or more changes. If a change is disabled, then the change will be executed by the default liquibase core implementation and percona toolkit won't be used. By default, this property is empty, so that all supported changes are executed using the percona toolkit. Example: Set this to addColumn,dropColumn in order to not use percona for adding/dropping a column.
liquibase.percona.options: String of options. Default: <empty>. This option allows the user to pass additional command line options to pt-online-schema-change. This e.g. can be used in complication replication setup to change the way slaves are detected and how their state is used. You can also specify a percona configuration file via --config file.conf, see Configuration Files. Multiple options are separated by space. If argument itself contains a space, it must be quoted with double-quotes, e.g. --config "filename with spaces.conf.
liquibase.percona.defaultOn: true/false. Default: true. Since liquibase-percona 1.3.0 This options allows to change the default behavior for the UsePercona flag. By default, all changes, that do not explicitly specify this flag, use the value from this system property. Settings this property to false allows to control for each single change, whether to use Percona Toolkit or not.
liquibase.password: String with the password needed to connect to the database. Default: <empty>. Since liquibase-percona 1.4.0. With this property, you can shortcut the automatic detection of the password from the underlying java.sql.Connection (if that fails) or from the default liquibase.properties file. If this property is set, then it is used for the password when executing pt-online-schema-change.
liquibase.percona.path: Path to the percona toolkit directory, where the tool pt-online-schema-change is installed. Default: <empty>. Since liquibase-percona 1.4.1. With this property, you can select a specific toolkit installation. If this property is not set, then the toolkit will be searched on the PATHon the PATH.
liquibase.percona.ptdebug: true/false. Default: false. Since liquibase-percona 1.5.0 This option enables the debug output of pt-osc by setting the environment variable PTDEBUG before starting pt-osc.

You can set these properties by using the standard java -D option:

...

After extracting the zip file of liquibase, place liquibase-percona-1.45.10.jar file in the sub directory lib. The shell script liquibase / liquibase.bat will automatically pick this up and the extension is available.

...

<dependency>
    <groupId>com.github.adangel.liquibase.ext</groupId>
    <artifactId>liquibase-percona</artifactId>
    <version>1.45.1<0</version>
</dependency>

`Using snapshots`

...

And just use the latest SNAPSHOT version for liquibase-percona dependency, e.g. 1.45.21-SNAPSHOT:

<dependency>
    <groupId>com.github.adangel.liquibase.ext</groupId>
    <artifactId>liquibase-percona</artifactId>
    <version>1.45.21-SNAPSHOT</version>
</dependency>

...

This means, you are trying to use version 1.1.1 of the extension with liquibase 3.2.x. This is an unsupported combination. For Liquibase 3.2.x, you'll need to use liquibase-percona 1.0.0

Versions Compared

Old Version 17

New Version 18

Key

Readme

Supported Databases

Liquibase version(s) tested against

Supported Changes and examples

Readme

Supported Databases

Liquibase version(s) tested against

Supported Changes and examples

AddColumn

AddForeignKeyConstraint

AddUniqueConstraint

CreateIndex

DropColumn

DropForeignKeyConstraint

DropUniqueConstraint

DropIndex

ModifyDataType

ModifyDataType

`Configuration`

`UsePercona flag`

`Configuration`

`UsePercona flag`

`System Properties`

`Using snapshots`

`References`

Page Comparison

Versions Compared

Old Version 17

New Version 18

Key

Readme

Supported Databases

Liquibase version(s) tested against

Supported Changes and examples

Readme

Supported Databases

Liquibase version(s) tested against

Supported Changes and examples

AddColumn

AddForeignKeyConstraint

AddUniqueConstraint

CreateIndex

DropColumn

DropForeignKeyConstraint

DropUniqueConstraint

DropIndex

ModifyDataType

ModifyDataType

Configuration

UsePercona flag

Configuration

UsePercona flag

System Properties

Using snapshots

References

`Configuration`

`UsePercona flag`

`Configuration`

`UsePercona flag`

`System Properties`

`Using snapshots`

`References`