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:
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:
When you use CopySchemaShard to copy this to a new keyspace, it will try to create
a
beforeb
, but error with a SQL errorForeign key constraint is incorrectly formed
.Proposal
Vitess should wrap each
CREATE TABLE
(or the entire batch) withWorkarounds
You can use
GetSchema <tablet-alias>
and pipe tojq
or similar to pull out the definitions:And loop over them, using:
or similar to copy schema.