Adverse effects of specifying --no-checkplan for pt-online-schema-change?

Polyglot Projects Percona Toolkit
1

Hi there,

I’ve recently run into a situation where I could not perform online alter table operations to modify the type of one column from tinyint(3) unsigned to int(10) unsigned using the pt-online-schema-change without specifying --nocheck-plan since the table has a multi-column primary key. I believe that there should be no problem with this operation, but I am a bit wary of specifying --nocheck-plan in a production environment since the docs do not make it clear what sort of risks are involved.

Could somebody inform me what risks I face by specifying --no-checkplan when using the pt-online-schema-change tool? Specifically, I want to know if there is any potential risk of data loss.

Thanks a bunch,
Ryan

2

Update: Since I’ve yet to receive any confirmation either way, I’m going to go the conservative route and assume that there could be potential data loss if --no-checkplan is specified until informed otherwise.

3

As I’ve faced the same interrogation 10 years later, I believe the documentation has been updated to reflect the impact of `no-check-plan`.

It may end up nowhere (« the tool will stop copying rows and die »), which means that:

  • it will try to copy rows as if the check plan was okay
  • if copying works okay, you’ll end up with what you wanted
  • else, if rows are copying too slowly, it will die, leaving the original table unchanged

Slow copying could happen as without a unique column, you’ll have to find rows based on what available. Say you have only one unique key, on two columns A and B. Suppose A and B are ranging from 1 to 1000, but you have no rows for A = 100-900. When you’ll try to find A in the range of (1, 100), and B in the range of (1, 100), you’ve got something to copy. Then A (100, 200) there’s nothing… but you have to run many SELECTs to find that out…

Having 3 or 4 columns in the only available unique index and you may spend hours just finding out where rows are.

I think that Oracle has an option for that, with the row_id, but for InnoDB in MySQL, there’s no unique id that can be used, apart from the defined indexes.