Bug Report: Deferring Secondary Keys Does Not Work With Foreign Keys

[mattlord]

Overview of the Issue

The MoveTables and Reshard commands/workflows support deferring secondary key generation until after the copy phase completes — adding all of them in one shot per table at the end — via the --defer-secondary-keys flag. This can improve the overall time the copy phase takes for very large tables by 2x or more so it's often desirable.

In v18 we began adding full support for foreign keys and MoveTables now supports migrating tables with foreign keys using the new --atomic-copy mode (see #13137 for more details).

These two features, however, do not work together as MySQL will return an error when you try to drop a secondary key that is used by a foreign key constraint.

Reproduction Steps

./101_initial_cluster.sh; mysql < ../common/insert_commerce_data.sql

mysql commerce -v -e "ALTER TABLE corder ADD FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON DELETE CASCADE"

vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer create --source-keyspace commerce --tables "customer,corder" --
defer-secondary-keys

vtctldclient MoveTables --workflow commerce2customer --target-keyspace customer show | grep "foreign key constraint"

You will see this in the final command's output:

                  "message": "Cannot drop index 'customer_id': needed in a foreign key constraint (errno 1553) (sqlstate HY000) during query: alter table vt_customer.corder drop key customer_id",

Binary Version

vtgate version Version: 19.0.0-SNAPSHOT (Git revision 29a9bf4ef4ceaf503ece831a79ca2b73d79efea1 branch 'main') built on Sun Nov 19 08:44:05 CET 2023 by matt@pslord.local using go1.21.4 darwin/arm64

Operating System and Environment details

N/A

Log Fragments

No response