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 👍

[dweitzman]

There was an old PR for this: #4722

[derekperkins]

This would be very helpful for us

[mattlord]

Closing this as done via #15448 as that's where we started disabling FK checks when deploying the schema here:

vitess/go/vt/vtctl/workflow/materializer.go

Lines 394 to 400 in 3743f09

	_, err = mz.tmc.ApplySchema(mz.ctx, targetTablet.Tablet, &tmutils.SchemaChange{
	SQL: sql,
	Force: false,
	AllowReplication: true,
	SQLMode: vreplication.SQLMode,
	DisableForeignKeyChecks: true,
	})