Changing data type of parent primary key

Polyglot Projects Percona Toolkit
1

I need to convert my auto-increment PK data type from mediumInt(9) to int(11) as we’re running out of records. This key is the parent for other foreign keys.
As far as I know, this isn’t possible with pt-online-schema-change as when the id data type is changed on the server, and the foreign key attempts to be added back for the child row, it still has the type set to mediumInt(9). All whilst data is coming in

What’s the best way I should manage this migration? Currently seems my only option to get this to work would be:

  • Manually drop foreign key constraint on the child table
  • Manually update the type for the child table
  • Run pt-online-shema-change for updating the parent PK from mediumInt(9) to int(11)
  • Once this is completed, go and add the foreign key constraint back in

This seems like a normal use case but I couldn’t find any method where PT helps with this conversion. Am I using it incorrectly? Should I be using pt-online-schema to do this? Should I be using it for more of the steps?

Should I be creating a new key on the parent, then point the child to this new key then drop the previous key?

MySQL 5.6

2

Could you specify your MySQL version?

3

Apologies, it’s MySQL 5.6, InnoDB

4

Does anyone have guidance on this? I’ve looked all over for understanding the best way of doing this and making use of pt-online-schema-change but it doesn’t mention this case anywhere? I think if we can answer it, we could put this use case into documentation

5

Anybody have an idea on this?