Unique key constraint cannot be added

[paulozenida]

For a command to add a unique key constraint such as:

pt-online-schema-change --alter="ADD CONSTRAINT UNIQ_1 UNIQUE (user_credentials_id)" --alter-foreign-keys-method=auto --host=127.0.0.1 --port=3306 --user=root --execute D=serviceauth,t=connection;

We have a failure message similar to:

"No foreign keys reference serviceauth.connection; ignoring --alter-foreign-keys-method.
Altering serviceauth.connection...
serviceauth.connection was not altered.
You are trying to add an unique key. This can result in data loss if the data is not unique.
Please read the documentation for the --check-unique-key-change parameter.
You can check if the column(s) contain duplicate content by running this/these query/queries:

SELECT IF(COUNT(DISTINCT user_credentials_id) = COUNT(*),
'Yes, the desired unique index currently contains only unique values',
'No, the desired unique index contains duplicated values. There will be data loss'
) AS IsThereUniqueness FROM serviceauth.connection;

Keep in mind that these queries could take a long time and consume a lot of resources"

Adding the flag --nocheck-unique-key-change to the generated command, however, allows it to be successfully ran.

[adangel]

Documentation for pt-osc: https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#id7

Seems like we simply need to add this flag (--nocheck-unique-key-change) and make it sure in the documentation, that data will be silently lost, if there are any duplicate keys...

I think, this option is new with percona toolkit 3.0

[adangel]

@paulozenida
There is actually a workaround already: You can set the system property liquibase.percona.options to --nocheck-unique-key-change to add this option, when pt-osc is executed.

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

java -Dliquibase.percona.options="--nocheck-unique-key-change" -jar liquibase.jar ...

I'm working on a fix for the next version of the plugin.

[paulozenida]

Hum, I see. At the time being, I had simply added the -Dliquibase.percona.skipChanges=addUniqueConstraint,dropUniqueConstraint to avoid that issue.

With the -Dliquibase.percona.options="--nocheck-unique-key-change" both may be "unskipped"?

[adangel]

Yes, that should work.

While upgrading pt-osc to version 3.0.7, I came across an issue with adding self-referencing foreign keys... the two integration tests are failing.
Did you happen to come across the use case like in addForeignKeyConstraintSelf? And does it work for you with pt-osc?

[paulozenida]

I'm using pt-osc 3.0.5 and I also had to skip the foreignKeyConstraints from liquibase+percona to make it work. I'm not sure exactly what the problem was but I know I had to skip those two as well:
addForeignKeyConstraint and the corresponding dropForeignKeyConstraint.

[paulozenida]

Hello,
Just to let you know that I tried your suggestion to add --nockeck-unique-key-change like the following:

JAVA_OPTS="${JAVA_OPTS-} -Dliquibase.percona.options=--nocheck-unique-key-change -Dliquibase.percona.skipChanges=addForeignKeyConstraint,dropForeignKeyConstraint"

but it didn't work so I had to go back to the previous solution and use it like:
JAVA_OPTS="${JAVA_OPTS-} -Dliquibase.percona.skipChanges=addUniqueConstraint,dropUniqueConstraint,addForeignKeyConstraint,dropForeignKeyConstraint"

[adangel]

https://jira.percona.com/browse/PT-381 suggest the following fix for the self-referencing foreign keys:

Workaround

When the tool runs, it creates a new table named _<table_name>_new and run the alter on the new
table. Thus the referential constraint should really be created on this new table instead of the original
one. The following command works:

./pt-online-schema-change --execute --alter "ADD CONSTRAINT parentEntityFK FOREIGN KEY
(parentEntity_primaryKey) REFERENCES _ConfigData_new (primaryKey)" D=test,t=ConfigData

Suggested Fix

Replace table name in self-referential FK reference to the new table name.

I've added this workaround back then: adangel@21177f1

Unfortunately pt-osc 2.2.21 was never released and the bug seems to be back with pt-osc 3.

I'll enable the workaround for PT 3, too.