0

This is a follow-up of Can I use pt-online-schema-change to change a primary key?.

Goal: I'd like to ALTER a table's primary key using pt-online-schema-change. Specifically I want migrate from single-column primary key (a) to a composite primary key (a,b) (where a is the same column in both cases).

Idea: I understand pt-online-schema-change generally doesn't work when both primary key and unique key is missing. My plan is therefor to do the following:

  1. Add a unique key: pt-online-schema-change --alter "ADD UNIQUE tmp_unique_key(a)" D=mydb,t=mytable,u=root --execute
  2. Modify the primary key: pt-online-schema-change --alter "DROP PRIMARY KEY, ADD PRIMARY KEY (a, b)" D=mydb,t=mytable,u=root --execute --check-alter. (--check-alter is needed to ignore the DROP PRIMARY KEY error)
  3. Modify the unique key: pt-online-schema-change --alter "DROP KEY tmp_unique_key" D=mydb,t=mytable,u=root --execute.

Implementation: I've tested the above on a tiny local table and it worked without a glitch.

Question: Assuming I have the disk space to hold the temporary unique primary key and can handle the load etc., are there any problems running this on a large table?

Community
  • 1
  • 1
Ztyx
  • 14,100
  • 15
  • 78
  • 114

1 Answers1

1

Yes, you can modify a PRIMARY KEY using pt-online-schema-change. Here is how pt-online-schema-change works:

  1. Acquire a brief metadata lock on origA
  2. Add triggers on origA
  3. Create new table exactly like origA, newA
  4. Apply the ALTER statement to newA.
  5. Copy the rows from origA to newA.
  6. When done, rename origA to oldA, rename newA to origA.
  7. Drop oldA

At no point is either table missing a PRIMARY KEY. There is no need to add a UNIQUE key. All you need to execute is #2 in your set and that will do it.

utdrmac
  • 731
  • 5
  • 17