Ghostferry doesn't support copying tables with Foreign Key constraints. Currently, we let the Ghostferry run begin to eventually crash midway through the run.
We should enforce this decision by erroring out during initialization, instead.
This PR queries the information_schemas to determine if any FOREIGN KEY constraints exist on any of the tables that will be copied by the Ghostferry (namely those after filtering / that are applicable).
Clients of Ghostferry may want to skip the enforcement, therefore, we leave it as a configurable option.
One instance, for example, is #166. That PR allows the ability to configure a specific ordering on tables, intended to be used to circumvent FOREIGN KEY issues. In the case when TablesToBeCreatedFirst is non-zero, the SkipForeignKeyConstraintsCheck is turned on.
Testing
To test this PR, one can do the following:
Setup ghostferry-copydb and the necessary MySQL instances (and their permissions) as outlined in the tutorial.
Create a database and table on the source with a FOREIGN KEY.
DROP DATABASE IF EXISTS testfk;
CREATE DATABASE testfk;
CREATE TABLE IF NOT EXISTS testfk.t1 (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
d1 VARCHAR(16)
);
CREATE TABLE IF NOT EXISTS testfk.t2 (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
d2 VARCHAR(16),
t1_id INTEGER,
FOREIGN KEY (t1_id)
REFERENCES t1(id)
);
Seed the database.
# seed.sh
if [ "$#" -ne 1 ]; then
echo "Usage: seed.sh <n>"
exit
fi
N=$1
for i in $(seq 1 $N); do
echo "INSERT INTO testfk.t1 (id, d1) VALUES (${i}, '$(cat /dev/urandom | tr -cd 'a-z0-9' | head -c 16)');"
done
for i in $(seq 1 $N); do
echo "INSERT INTO testfk.t2 (id, d2, t1_id) VALUES (${i}, '$(cat /dev/urandom | tr -cd 'a-z0-9' | head -c 16)', $(jot -r 1 1 $N));"
done
4. Run the ghostferry-copydb and observe the error printed during initialization.
Closes #3
Ghostferry doesn't support copying tables with Foreign Key constraints. Currently, we let the Ghostferry run begin to eventually crash midway through the run.
We should enforce this decision by erroring out during initialization, instead.
This PR queries the
information_schemas
to determine if anyFOREIGN KEY
constraints exist on any of the tables that will be copied by the Ghostferry (namely those after filtering / that are applicable).Clients of Ghostferry may want to skip the enforcement, therefore, we leave it as a configurable option.
One instance, for example, is #166. That PR allows the ability to configure a specific ordering on tables, intended to be used to circumvent FOREIGN KEY issues. In the case when
TablesToBeCreatedFirst
is non-zero, theSkipForeignKeyConstraintsCheck
is turned on.Testing
To test this PR, one can do the following:
Setup ghostferry-copydb and the necessary MySQL instances (and their permissions) as outlined in the tutorial.
Create a database and table on the source with a FOREIGN KEY.
N=$1
for i in $(seq 1 $N); do echo "INSERT INTO testfk.t1 (id, d1) VALUES (${i}, '$(cat /dev/urandom | tr -cd 'a-z0-9' | head -c 16)');" done
for i in $(seq 1 $N); do echo "INSERT INTO testfk.t2 (id, d2, t1_id) VALUES (${i}, '$(cat /dev/urandom | tr -cd 'a-z0-9' | head -c 16)', $(jot -r 1 1 $N));" done
DEBU[0000] loading tables from database database=testfk tag=table_schema_cache DEBU[0000] fetching table schema database=testfk table=t1 tag=table_schema_cache DEBU[0000] fetching table schema database=testfk table=t2 tag=table_schema_cache DEBU[0000] caching table schema database=testfk table=t1 tag=table_schema_cache DEBU[0000] caching table schema database=testfk table=t2 tag=table_schema_cache INFO[0000] table schemas cached tables="[testfk.t1 testfk.t2]" tag=table_schema_cache ERRO[0000] source contains foreign key constraints error="source contains at least 1 foreign key constraint" tag=ferry error: failed to initialize ferry: source contains at least 1 foreign key constraint