Feature Request: Add support for foreign key mode managed to vttestserver

[janpio]

Feature Description

v18 added Experimental Foreign Key Support, via the managed mode:

managed [EXPERIMENTAL] -
In this experimental mode, Vitess is fully aware of foreign key relationships and actively tracks foreign key constraints using the schema tracker. VTGate will handle DML operations with foreign keys and correctly cascade updates and deletes.
It will also verify restrict constraints and validate the existence of parent rows before inserting child rows.
This ensures that all child operations are logged in binary logs, unlike the InnoDB implementation of foreign keys.
This allows the usage of VReplication workflows with foreign keys.
Implementation details are documented in the #12967.

Unfortunately that does not seem to be supported in vttestserver yet. From the CLI output:

--foreign_key_mode string      This is to provide how to handle foreign key constraint in create/alter table. Valid values are: allow, disallow (default "allow")

And the docker container script:

vitess/docker/vttestserver/run.sh

Line 40 in 225fc70

--foreign_key_mode "${FOREIGN_KEY_MODE:-allow}" \

It would be great if this would be expended to also support the new experimental managed mode.

Use Case(s)

We are using vttestserver at Prisma to run tests that pretend to be PlanetScale like:
https://github.com/prisma/prisma/blob/8fdb2bcb5b2944d9f4b6dd1a9efa20c88f0b5ab4/docker/docker-compose.yml#L77-L94
Note that we use FOREIGN_KEY_MODE: 'disallow' right now, and of course want to to managed soon.

[janpio]

Learning a bit more now, so this is a bit more involved probably:

From https://github.com/vitessio/vitess/releases/tag/v18.0.0:

A new optional field foreignKeyMode has been added to the VSchema. This field can be provided for each keyspace. The VTGate flag --foreign_key_mode has been deprecated in favor of this field.

and

After upgrading from v17 to v18, users should specify the correct foreign key mode for all their keyspaces in the VSchema using the new property.
Once this change has taken effect, the deprecated flag --foreign_key_mode can be dropped from all VTGates. Note that this is only required if running in disallow mode.

And vttestserver is currently using --foreign_key_mode:

vitess/docker/vttestserver/run.sh

Line 40 in 225fc70

--foreign_key_mode "${FOREIGN_KEY_MODE:-allow}" \

So we'll need to figure out how to replace the VTGate config with a VSchema field, that can still be configured via an environment variable.

[janpio]

Digging further, I think the challenge is deeper and vttest is involved:

vitess/go/vt/vttest/local_cluster.go

Lines 138 to 139 in 225fc70

	// How to handle foreign key constraint in CREATE/ALTER TABLE. Valid values are "allow", "disallow"
	ForeignKeyMode string

Probably that is then handed further down even.

[janpio]

(Related question: Is there a way to output the configured value of the new ForeignKeyMode via a SQL query?)

[GuptaManan100]

@janpio Like you said, foreignKeyMode is a Vschema property that can be specified for each keyspace. So, we don't need to add any environment variable or a flag to vttestserver.

vttestserver already takes in the flag schema_dir, with the following description -

      --schema_dir string                                                Directory for initial schema files. Within this dir, there should be a subdir for each keyspace. Within each keyspace dir, each file is executed as SQL after the database is created on each shard. If the directory contains a vschema.json file, it will be used as the vschema for the V3 API.

So, all you need to do is add a vschema.json file in the directory of your keyspace and specify the foreignKeyMode and you're good.

For reference as to what the vschema should look like, you can see what we are using in our tests at https://github.com/vitessio/vitess/blob/main/go/test/endtoend/vtgate/foreignkey/unsharded_vschema.json.

I am closing this issue, because there doesn't seem to be any change required on our end. Please reopen if you have follow up questions.

[janpio]

Thanks for the response, I could not connect these dots as I do not know enough about vschema and so on, it seems.

---

My investigation into this:

In the vttestserver Docker container we are using this path seems to be hardcoded via --schema_dir="/vt/schema/":
https://github.com/vitessio/vitess/blob/225fc70d58aae86da7b9172d65d8017e0fbdcaab/docker/vttestserver/run.sh#L46C16-L46C27

The subfolders for the keyspaces seem to be created automatically on startup via this script, and a vschema.json is created if there are more than 1 keyspaces:

vitess/docker/vttestserver/setup_vschema_folder.sh

Lines 49 to 59 in 225fc70

	for keyspace in "${KEYSPACES_LIST[@]}";
	do
	# create a directory for each keyspace
	mkdir "/vt/schema/$keyspace"
	num_shard=${NUM_SHARDS_LIST[$i]}
	# Create a vschema.json file only if the number of shards are more than 1
	if [[ $num_shard -gt "1" ]]; then
	printf "{\n\t\"sharded\": true\n}" > "/vt/schema/$keyspace/vschema.json"
	fi
	((i++))
	done

I guess I will try to access that directory and its subfolders, and put a vschema.json with the necessary file there before the server starts, somehow.

How can I best confirm this worked? Is there a query I can run to confirm that the configuration was picked up?

---

I sense trouble though, as our Docker container setup mentions that the keyspace config from their is not actually used:
https://github.com/prisma/prisma/blob/8fdb2bcb5b2944d9f4b6dd1a9efa20c88f0b5ab4/docker/docker-compose.yml#L84-L85

      KEYSPACES: 'unsharded' # unused in testing, but required by vttestserver
      NUM_SHARDS: '1' # unused in testing, but required by vttestserver

Our tests create a lot of databases dynamically during testing, to get isolation between the different test cases.

[janpio]

Ok, took me a bit to wrap my head around how to get the vschema.json into the container, but in the end it was as simple as:

    environment:
      PORT: '33804' 
      KEYSPACES: 'unsharded'
      NUM_SHARDS: '1' 
      FOREIGN_KEY_MODE: 'allow' # --foreign-key-mode
      ...
    volumes:
      - ./vitess:/vt/schema/unsharded

And having a vitess/vschema.json:

{
	"sharded": false,
	"foreignKeyMode": "managed"
}

That starts successfully.

But how can I confirm that the keyspace indeed is running with foreignKeyMode=managed now? Is there any command on how I can check that the foreign keys are now indeed managed properly by Vitess and not just by the underlying database because of the old --foreign-key-mode=allow? What difference should I be able to observer vs. the old allow?

(Setting foreignKeyMode to an invalid value crashes the vttestserver start, so that already is a good sign.)

[GuptaManan100]

@janpio Yes! Great! You figured out how to specify the Vschema file. I should have probably listed the steps the first time around itself.

But how can I confirm that the keyspace indeed is running with foreignKeyMode=managed now?

The only way to know that the foreign key mode is managed by Vitess is to just check the VSchema that the vtgate is using. This is visible in the /debug/vschema page.

The purpose of the managed mode is that Vitess does the cascades, but the final result should be indistinguishable from the case where the foreign keys were just allowed. So I don't think there are any queries that you can run to distinguish the two cases.

Setting foreignKeyMode to an invalid value crashes the vttestserver start, so that already is a good sign.

This is probably the best test you could have run for now 😆

@harshit-gangal Should we add a query that allows users to know which foreign key mode they are in?

[GuptaManan100]

(Related question: Is there a way to output the configured value of the new ForeignKeyMode via a SQL query?)

@janpio I have made a PR that adds a new query like SHOW VSCHEMA KEYSPACES and that might be the solution - #14505, because this will just allow you to run a query and it will tell you exactly what the foreign key mode is that vitess is running in.

If you don't add a Vschema and are running with --foreign-key-mode=allow, then it would be unmanaged otherwise it would be managed as we see in the description of the PR.

[janpio]

Thanks, the query you have proposed to add seems like the perfect solution.
We could then gain confidence by running that query to confirm the configuration of our Docker setup.

---

The one still open questio, is if CREATE DATABASE foo will create a keyspace with the same foreign key mode or not (which I could not confidently confirm because I could not figure out the state of a keyspace). We create the databases dynamically in our tests for isolation, and for --foreign-key-mode=disallow they would inherit the disabled foreign keys setting. I hope they will also inherit the foreignKeyMode=managed so we can keep creating databases dynamically.

[GuptaManan100]

You'll have to add vschema for all the databases you create before hand. I don't think there is any other way.

[janpio]

Ok, I see SHOW VSCHEMA KEYSPACES is now available via the Docker container for vttestserver, so we'll try that out now.

[janpio]

Indeed, only the configured keyspaces/databases get the mode. That makes it really hard for us.

2 follow up issues:
#14660
#14661