Running Liquibase Percona Toolkit Changes

The non-locking update is achieved using triggers:

  1. A new temporary table is created, including the added or dropped columns. The data is copied in chunks.
  2. While the copy is in progress, any newly created, deleted, or updated rows are copied. This is done by adding triggers to the original table.
  3. After the copy is finished, the original table is dropped, and the temporary table is renamed.

This means that pt-online-schema-change cannot be used if the table already uses triggers. The command pt-online-schema-change is searched only on the PATH. Depending on the property liquibase.percona.failIfNoPT, the update will fail or will run without using pt-online-schema-change and lock the table for the duration of the update.

To add the update to your database, add the needed change to the changelog and run the corresponding command based on the following list of supported changes:

  • addColumn

Note: The change is supported since liquibase-percona 1.0.0. Automatic rollback is supported.

<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

Note: The change is supported since liquibase-percona 1.3.0. Automatic rollback is supported.

<changeSet id="3" author="Alice">
     <addForeignKeyConstraint constraintName="fk_person_address"
         referencedTableName="person" referencedColumnNames="id"
         baseTableName="address" baseColumnNames="person_id"/>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="ADD CONSTRAINT fk_person_address FOREIGN KEY (person_id) REFERENCES person (id)" ...
  • addPrimaryKey

Note: The change is supported since liquibase-percona 1.7.0. Automatic rollback is not supported. Automatic rollback is not supported by this Percona change (as opposed to the Liquibase addPrimaryKey Change Type). pt-online-schema-change usually needs a primary key or a unique key to operate properly.

<changeSet id="2" author="Alice">
     <addPrimaryKey tableName="person" columnNames="id, name"/>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="DROP PRIMARY KEY, ADD PRIMARY KEY (id, name)" ...

Note: If the table already includes a primary key, add the DROP PRIMARY KEY statement to the alter command first. By default, the pt-online-schema-change tool will not execute this change, you must set the additional option --no-check-alter (See check-alter). To find out whether a primary key already exists and whether you need the DROP PRIMARY KEY statement, a database connection is required.

  • addUniqueConstraint

Note: The change is supported since liquibase-percona 1.3.0. Automatic rollback is supported.

<changeSet id="2" author="Alice">
     <addUniqueConstraint columnNames="id, name" tableName="person" constraintName="uq_id_name"/>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="ADD CONSTRAINT uq_id_name UNIQUE (id, name)" ...

Note: pt-online-schema-change is executed with the option --nocheck-unique-key-change. --nocheck-unique-key-change enables adding a unique index; however, it can cause data loss since duplicated rows are ignored. For more information, see the -- [no]check-unique-key-change option.

  • createIndex

Note: The change is supported since liquibase-percona 1.2.0. Automatic rollback is supported.

<changeSet id="2" author="Alice">
     <createIndex indexName="emailIdx" tableName="person" unique="true">
         <column name="email"/>
        </createIndex>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="ADD UNIQUE INDEX emailIdx (email)" ...

Note: pt-online-schema-change is executed with the option --nocheck-unique-key-change. --nocheck-unique-key-change enables adding a unique index; however, it can cause data loss since duplicated rows are ignored. For more information, see the -- [no]check-unique-key-change option.

  • dropColumn

Note: The change is supported since liquibase-percona 1.0.0. Automatic rollback is not supported.

<changeSet id="2" author="Alice">
     <dropColumn tableName="person" columnName="age"/>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="DROP COLUMN age" ...
  • dropForeignKeyConstraint

Note: The change is supported since liquibase-percona 1.3.0. Automatic rollback is not supported.

<changeSet id="4" author="Alice">
     <dropForeignKeyConstraint baseTableName="address" constraintName="fk_person_address" />
</changeSet>

Corresponding command:

pt-online-schema-change --alter="DROP FOREIGN KEY _fk_person_address" ...
  • dropUniqueConstraint

Note: The change is supported since liquibase-percona 1.3.0. Automatic rollback is not supported.

<changeSet id="3" author="Alice">
     <dropUniqueConstraint tableName="person" constraintName="uq_id_name"/>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="DROP KEY uq_id_name" ...
  • dropIndex

Note: The change is supported since liquibase-percona 1.2.0. Automatic rollback is not supported.

<changeSet id="3" author="Alice">
     <dropIndex indexName="emailIdx" tableName="person"/>
</changeSet>

Corresponding command:

pt-online-schema-change --alter="DROP INDEX emailIdx" ...
  • modifyDataType

Note: The change is supported since liquibase-percona 1.2.0. Automatic rollback is not supported.

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

Corresponding command:

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

Troubleshooting issues

NoSuchMethodError: PerconaDropColumnChange.getColumns()Ljava/util/List

If you receive the following error message: “Unexpected error running Liquibase: liquibase.exception.UnexpectedLiquibaseException:
java.lang.NoSuchMethodError:liquibase.ext.percona.PerconaDropColumnChange.getColumn()Ljava/util/List”, check whether you use liquibase-percona 1.1.1 with Liquibase 3.2.x. This is an unsupported combination. For Liquibase 3.2.x, you need to use liquibase-percona 1.0.0.

Related Links