Bug Report: Unable to cancel movetables with keep_data=false on database with foreign keys

[Phanatic]

Overview of the Issue

We have an external database with foreign keys that we're trying to import with MoveTables atomic copy.

The relevant database schema is :

CREATE TABLE `addresses`
  (
     `id`           INT UNSIGNED NOT NULL auto_increment,
     `uuid`         VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
     DEFAULT NULL,
     `user_id`      INT UNSIGNED NOT NULL,
     `person`       VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `company`      VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `address`      VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `address2`     VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `city`         VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `district`     VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `postal_code`  VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci
     DEFAULT NULL,
     `created_at`   TIMESTAMP NULL DEFAULT NULL,
     `updated_at`   TIMESTAMP NULL DEFAULT NULL,
     `country_code` INT UNSIGNED NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `addresses_uuid_unique` (`uuid`),
     KEY `addresses_user_id_foreign` (`user_id`),
     KEY `addresses_country_code_foreign` (`country_code`),
     CONSTRAINT `addresses_country_code_foreign` FOREIGN KEY (`country_code`)
     REFERENCES `countries` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
     CONSTRAINT `addresses_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES
     `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  )

When the MoveTables started, schema_engine claims that it has created the tables in the external datasource

I0208 00:37:16.336376       1 engine.go:550] schema engine created [addresses bookmarks countries failed_jobs favorites followers giftlists gifts invitations item_analytics items links migrations notifications oauth_access_tokens oauth_auth_codes oauth_clients oauth_personal_access_clients oauth_refresh_tokens organizations parse_errors parse_logs password_resets phones privacyoptions products retailers sms_queue social_accounts temp_photos traps users], altered [], dropped []

But when I try to run MoveTables --keep_data=false Cancel <workflow-id> the workflow fails with this error:

W0213 20:49:20.178477       1 rpc_server.go:72] TabletManager.ExecuteFetchAsDba(query:"drop table `downstream_db`.`addresses`" db_name:"downstream_db" max_rows:1)(on aws_useast1b_4-2345202768 from ) error: rpc error: code = Unknown desc = Unknown table 'downstream_db.addresses' (errno 1051) (sqlstate 42S02) during query: drop table `downstream_db`.`addresses`

Tangentially, which may or may not be related, I also see this error in vttablet

W0213 20:49:01.939230       1 rpc_server.go:72] TabletManager.ExecuteFetchAsDba(query:"drop table `downstream_db`.`giftlists`" db_name:"downstream_db" max_rows:1)(on aws_useast1b_4-2345202768 from ) error: rpc error: code = Unknown desc = Cannot drop table 'giftlists' referenced by a foreign key constraint 'items_giftlist_id_foreign' on table 'items'. (errno 3730) (sqlstate HY000) during query: drop table `downstream_db`.`giftlists`

this table's schema is :

CREATE TABLE `giftlists`
  (
     `id`               INT UNSIGNED NOT NULL auto_increment,
     `uuid`             VARCHAR(36) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT NULL,
     `name`             VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `user_id`          INT UNSIGNED NOT NULL,
     `privacy`          VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `type`             VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci NOT NULL
     DEFAULT 'wish',
     `privacyoption_id` INT UNSIGNED NOT NULL DEFAULT '1',
     `event_date`       DATE DEFAULT NULL,
     `created_at`       TIMESTAMP NULL DEFAULT NULL,
     `updated_at`       TIMESTAMP NULL DEFAULT NULL,
     `deleted_at`       TIMESTAMP NULL DEFAULT NULL,
     `description`      MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci,
     `slug`             VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci NOT NULL,
     `masthead_photo`   VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci
     DEFAULT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `giftlists_uuid_unique` (`uuid`),
     KEY `giftlists_user_id_foreign` (`user_id`),
     KEY `giftlists_privacyoption_id_foreign` (`privacyoption_id`),
     CONSTRAINT `giftlists_privacyoption_id_foreign` FOREIGN KEY (
     `privacyoption_id`) REFERENCES `privacyoptions` (`id`) ON DELETE RESTRICT
     ON UPDATE RESTRICT,
     CONSTRAINT `giftlists_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES
     `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  )
engine=innodb
auto_increment=566
DEFAULT charset=utf8mb4
COLLATE=utf8mb4_unicode_ci 

Reproduction Steps

The relevant database schema is :

CREATE TABLE `addresses`
  (
     `id`           INT UNSIGNED NOT NULL auto_increment,
     `uuid`         VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
     DEFAULT NULL,
     `user_id`      INT UNSIGNED NOT NULL,
     `person`       VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `company`      VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `address`      VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `address2`     VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `city`         VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `district`     VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci DEFAULT
     NULL,
     `postal_code`  VARCHAR(255) CHARACTER SET utf8mb4 COLLATE
     utf8mb4_unicode_ci
     DEFAULT NULL,
     `created_at`   TIMESTAMP NULL DEFAULT NULL,
     `updated_at`   TIMESTAMP NULL DEFAULT NULL,
     `country_code` INT UNSIGNED NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `addresses_uuid_unique` (`uuid`),
     KEY `addresses_user_id_foreign` (`user_id`),
     KEY `addresses_country_code_foreign` (`country_code`),
     CONSTRAINT `addresses_country_code_foreign` FOREIGN KEY (`country_code`)
     REFERENCES `countries` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
     CONSTRAINT `addresses_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES
     `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  )

Binary Version

Vitess v18.0.0

Operating System and Environment details

PlanetScale

Log Fragments

No response