Why PT-online-schema_change not giving error for duplicate primary

MySQL & MariaDB Percona Server for MySQL 5.7
1

We have faced a big issue while doing drop primary key and add primary key in pt-online-schema-change,

While doing this the new primary key is having duplicate in so it should not create a primary key but this happening successfully without any error , instead we have lost the data that are duplicate :frowning:

2

Hi @erpramodprajapat,

I believe you surely have included --no-check-alter in your pt-osc command, without that you cannot perform the ALTER of PK. This is well documented and warned behaviour –

For safety…

  • In most cases the tool will refuse to operate unless a PRIMARY KEY or UNIQUE INDEX is present in the table. See --alter for details.

Now I can answer why this happened:

look at the trigger definition: they use REPLACE INTO. This the duplicate values are overridden and hence the loss of data.

Consider reviewing the documentation and reading warnings while executing the commands.

Thanks,
Kedar.

3

Thank you @kedarpercona for quick response , Yest my miss we included --no-check-alter

4

But @kedarpercona how we can alter on primary key without --no-check-alter ?

5

You cannot… and that’s where the tool is telling you that it is your responsibility to ensure data uniqueness. If you think it’s all correct, disable this check and restart with --no-check-alter.