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?