Buy Percona ServicesBuy Now!

Changing data type of parent primary key

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Changing data type of parent primary key

    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
    Last edited by samgreenline; 11-07-2019, 01:06 PM.

  • #2
    Could you specify your MySQL version?

    Comment


    • #3
      Originally posted by carlos.salguero View Post
      Could you specify your MySQL version?
      Apologies, it's MySQL 5.6, InnoDB

      Comment


      • #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

        Comment


        • #5
          Anybody have an idea on this?

          Comment

          Working...
          X