Support createIndex with specifying index prefix length

[vaishakhikulkarni]

In SQL:

ALTER TABLE some_table MODIFY column1 varchar(4096) CHARACTER SET utf8mb4 NOT NULL;
ALTER TABLE some_table ADD INDEX some_table_index (column1 (300),column2);

In Liquibase also we don't support override but have work around like follows:
First createIndex and use modifySql to replace column1 with column1(300) override size.

    <createIndex tableName="some_table" indexName="some_table_index">
        <column name="column1"/>
        <column name="column2"/>
    </createIndex>
    <modifySql dbms="mysql">
        <replace replace="`column1 `" with="`column1 `(300)"/>  <!-- "300" limits index size -->
    </modifySql>

While we do not have any support or work around for liquibase percona. This would be very useful feature for us.

[adangel]

Do I understand you correctly: You basically want that modifySql is also applied when the ADD INDEX statement is executed via percona? I guess, currently the whole "modifySql" is simply ignored...

Just some docs:

• https://docs.liquibase.com/change-types/community/create-index.html
• https://github.com/liquibase/liquibase-percona#createindex
• https://docs.liquibase.com/workflows/liquibase-community/modify-sql.html

I guess, this should then be supported not only for createIndex but basically for all change types.

[vaishakhikulkarni]

No I don't want modifySql to be applied for add Index.
I want a way to override the size of the column while creating a index using createIndex tag as we can do using SQL.
ALTER TABLE some_table MODIFY column1 varchar(4096) CHARACTER SET utf8mb4 NOT NULL;
ALTER TABLE some_table ADD INDEX some_table_index (column1 (300),column2);

In liquibase also we don't have way to override the size of the column while creating index for tag createIndex.
Work around used is to add modifySQL tag.

Because even if we mention type while creating index it gets ignored in liquibase as well as liquibase percona.

    <createIndex tableName="some_table" indexName="some_table_index">
        <column name="column1" type="varchar(300)"/>
        <column name="column2"/>
    </createIndex>

Please let me know accordingly.

[adangel]

Ok, I understand now.

Have you tried to create the index like this?

    <createIndex tableName="some_table" indexName="some_table_index">
        <column name="column1(300)"/>
        <column name="column2"/>
    </createIndex>

If that works, then all is fine - your use case is already supported. And it should work also with liquibase-percona.

If not (it might be the liquibase properly escapes the column name and you'll see an error like "column column1(300) not found"), then I'd suggest that you bring this into attention for core liquibase. Just file a new feature request here: https://github.com/liquibase/liquibase/issues

For me it makes only sense to implement this feature in liquibase-percona, if it is supported in liquibase-core (e.g. without percona) as well.

The feature we are talking here about is called Column Prefix Key Parts.

[vaishakhikulkarni]

So using following code:

<createIndex tableName="some_table" indexName="some_table_index">
        <column name="column1(300)"/>
        <column name="column2"/>
</createIndex>

works with liquibase.

But when we try to use this using liquibase percona we start getting error in Java as
Reason: java.lang.RuntimeException: Percona exited with 139.
The command generated looks like this:

pt-online-schema-change --alter-foreign-keys-method=auto --nocheck-unique-key-change \
    --alter="ADD INDEX some_table_index (`column1(300)`, column2)" --password=*** \
    --execute h=hostName,P=3306,u=admin,D=schmena_name,t= some_table

I also tried to run this command on command line I get following error:

pt-online-schema-change --alter-foreign-keys-method=auto --nocheck-unique-key-change \
    --alter="ADD INDEX some_table_index (`column1(300)`, column2)" --password=*** --**dry-run** \
    h=hostName,P=3306,u=admin,D=schmena_name,t= some_table

Error:
zsh: segmentation fault pt-online-schema-change --alter-foreign-keys-method=auto --dry-run

[vaishakhikulkarni]

Hi Team,

Any update on this request ?

[adangel]

I've created a fix for this, see #157.
You can download the fixed liquibase-percona extension here:
liquibase-percona-4.6.2-SNAPSHOT-fixed-148.zip

Would you mind testing this fix?

[mahameed]

@adangel My colleague @vaishakhikulkarni is out of office these days. I tested your update and it does indeed work now. However, our problem of not be able to change column size while updating index still remains. It seems like the 2 solutions to them would be to:

• Ask the liquibase team to update the size of the column in a createindex tag instead of having the modifySql
• Percona starts supporting modifySql tag, especially for createindex

Do you think the 2nd option is something viable?

[adangel]

@mahameed Thanks for testing.

Regarding your follow-up question: You can specify the column size while creating the index like this:

<createIndex tableName="some_table" indexName="some_table_index">
        <column name="column1(300)"/>
        <column name="column2"/>
</createIndex>

Is there anything else, what should be supported by liquibase? If so, please comment on liquibase/liquibase#2191

So I don't think, modifySql is required at all (at least not for this use case).

[mahameed]

@adangel ok I missed that part. Yeah that seems to be working. Thanks.
Any rough estimate on when 4.6.2 proper will be released?

[adangel]

I've released now 4.6.1.1: https://github.com/liquibase/liquibase-percona/releases/tag/liquibase-percona-4.6.1.1