Trouble using pt-online schema change to add primary key (on a table that never had one)

[jushean]

I have a huge table with no primary key (I know!)

trying pt-online-schema-change with:

–no-check-alter
–no-check-unique-key-change
–alter “ADD COLUMN id INT unsigned NOT NULL AUTO_INCREMENT primary key FIRST”

Error I get is:

The new table index PRIMARY would be used for the DELETE trigger, but it uses column id which does not exist in the original table and the original table does not have a PRIMARY KEY or a unique index to use for the DELETE trigger.

Is there a workaround?

[Mauricio_Cacho]

Hi @jushean.

From the pt-osc docs, you can find the following:

The following limitations apply which, if attempted, will cause the tool to fail in unpredictable ways:

In almost all cases a PRIMARY KEY or UNIQUE INDEX needs to be present in the table. This is necessary because the tool creates a DELETE trigger to keep the new table updated while the process is running.

A notable exception is when a PRIMARY KEY or UNIQUE INDEX is being created from existing columns as part of the ALTER clause; in that case it will use these column(s) for the DELETE trigger.

pt-osc works by copying the existing data, but also creating triggers so the changes that happen while pt-osc is running are not lost; and that’s what the tool is complaining about; how could pt-osc mimic the DELETE on the table if there’s no way of ensuring the same record most be deleted.

Can you use any of the existing fields as unique as the first step?

Best,
Mauricio.