Pt-online-schema-change says unique index is missing but it is defined

MySQL & MariaDB
,
1

I am using pt-online-schema-change to convert some large tables to the utf8mb4 character set. For a number of tables I am getting an error message similar to the following

The new table my_db._my_table_new does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

In each case, I have confirmed that a composite unique key is, in fact, defined on the original table. The error message is referencing the _new version of the table

Does pt-online-schema-change not work with composite unique keys? Is there a specific switch I should be using for it to take effect?

I’m using Aurora MySQL 5.6 and pt-online-schema-change version 3.1.0

2

Someone gave me the answer to this in a Slack discussion so I wanted to post the answer here for future reference if anyone else encounters this obscure problem.

It turns out the problem is that at least one of the columns in the UNIQUE KEY are NULLable. According to the docs:

A UNIQUE index permits multiple NULL values for columns that can contain NULL

Other RDBMS don’t behave this way. The effect is that pt-online-schema-change will not use the UNIQUE KEY defined on at least one NULLable column as a unique constraint so it is bypassed, causing the error.

1 Like