CopySchemaShard should disable FOREIGN_KEY_CHECKS when applying schema

[bnu0]

Problem Description

CopySchemaShard will error out if the schema has any foreign keys, where any table that has a lexicographically lesser name references a table that has a lexicographically greater name.

For example:

create table b (
  id int not null primary key
);

create table a (
  id int not null primary key, 
  b_ref int, 
  foreign key (b_ref) references b (id)
);

When you use CopySchemaShard to copy this to a new keyspace, it will try to create a before b, but error with a SQL error Foreign key constraint is incorrectly formed.

Proposal

Vitess should wrap each CREATE TABLE (or the entire batch) with

SET FOREIGN_KEY_CHECKS = 0;
-- tables created here --
SET FOREIGN_KEY_CHECKS = 1;

Workarounds

You can use GetSchema <tablet-alias> and pipe to jq or similar to pull out the definitions:

 vtctlclient -server x:y GetSchema foo-12345 | jq -r '. table_definitions[] | .schema'

And loop over them, using:

vtctl ExecuteFetchAsDba "SET FOREIGN_KEY_CHECKS = 0; ${SQL}; SET FOREIGN_KEY_CHECKS = 1;"

or similar to copy schema.

[deepthi]

Seems reasonable :+1:

[dweitzman]

There was an old PR for this: https://github.com/vitessio/vitess/pull/4722

[derekperkins]

This would be very helpful for us

[mattlord]

Closing this as done via https://github.com/vitessio/vitess/pull/15448 as that's where we started disabling FK checks when deploying the schema here: https://github.com/vitessio/vitess/blob/3743f095da6d0063ac9982e9226754bce117b82e/go/vt/vtctl/workflow/materializer.go#L394-L400