Alter data type of parent primary key with pt-online-schema-change?

Polyglot Projects Percona Toolkit
1

I need to alter my auto-increment PK data type from int signed to bigint unsigned since we are approaching the max range. This key is the parent for other foreign keys.

Would the following pt-online-schema-change option also utilize the new data type (bigint) on the foreign key constraints?

--alter-foreign-keys-method=rebuild_constraints

Or would we have to manually update the foreign key constraints as such:

  • 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 and foreign keys from int to bigint
  • Once this is completed, go and add the foreign key constraint back in

I couldn’t find any example where Percona Toolkit helps with data type PK and FK conversion. Should I be using pt-online-schema to do this? Should I be using it for more of the steps? Would PT handle it all?

Here is the PT command I am envisioning
pt-online-schema-change --execute --preserve-triggers --set-vars wait_timeout=10000,innodb_lock_wait_timeout=20,lock_wait_timeout=60 --alter-foreign-keys-method=rebuild_constraints --alter "MODIFY column_name bigint unsigned NOT NULL auto-increment" h=<host_address>,D=<db>,t=<table_name>,u=<user_name>,p=<password>

MySQL 8

2

Hello @SapientZero,
int unsigned = 4.2B rows using only 4 bytes. bigint unsigned = +2T rows using 8 bytes, but wasting 4 bytes for each row until after 4.2B. Do you really need bigint? Or would int unsigned be enough? Switching to bigint without actually needing it could represent many GB of wasted space on this table.

The manual page says rebuild_constraints will drop the FKs on child tables and re-add them after the table is swapped provided that it happens within a time constraint.

You’re wait_timeouts are set a bit high. The idea behind pt-osc is that it does not impact running operations from your app. Setting these values high means your application might end up waiting or be blocked by osc.

Don’t forget about --dry-run

1 Like
3

Thank you for the advice!

Is there a blog tutorial on how to use pt-osc for changing data types on production tables with foreign key constraints?

I was looking at this tutorial and it looks like you can’t stop to review pt-osc when there are foreign keys and child tables present? Percona Blog

What would be an ideal pt-osc command that creates a duplicate table, triggers, foreign key constraints and then, once everything is in sync, stops short of swapping tables, while the triggers keep running?

4

Please read over the documentation for pt-osc

Look at --no-swap-tables and --no-drop-old-table