Bug Report: `information_schema.key_column_usage.table_schema` has different value than with MySQL in `vttestserver`

[janpio]

Overview of the Issue

I have been testing the new foreignKeyMode=managed and in that context for the first time created foreign key constraints with Vitess, and then realized that our standard queries to get the foreign keys leads to different result with vttestserver than on a MySQL instance, and the information_schema.key_column_usage.table_schema value is prefixed with vt_ and suffixed with _0. This leads to the results not being present, and functionality breaking. It prevents us from running our test suite and making sure that Prisma ORM fully works with the new foreignKeyMode=managed.

(Interestingly enough, this is not present on the planetscale.com deployment of this new functionality.)

Reproduction Steps

With this DDL:

-- CreateTable
CREATE TABLE `User` (
    `id` VARCHAR(191) NOT NULL,
    `paymentId` VARCHAR(191) NOT NULL,

    UNIQUE INDEX `User_paymentId_key`(`paymentId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `Payment` (
    `id` VARCHAR(191) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `User` ADD CONSTRAINT `User_paymentId_fkey` FOREIGN KEY (`paymentId`) REFERENCES `Payment`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

this query:

SELECT
    *
FROM information_schema.key_column_usage AS kcu
WHERE
    BINARY kcu.table_schema = 'tests'

leads to this result for MySQL:

(tests is foreign_key_testing2 in this screenshot)

but this result for Vitess:

If I instead use this query:

SELECT
    *
FROM information_schema.key_column_usage AS kcu
WHERE
    BINARY kcu.table_schema = 'vt_tests_0'

I get the desired result:

(tests is test-0000-00000000 here)

Binary Version

Dockerhub vitess/vttestserver:mysql80@sha256:1381e4cbbd390e5ac0edc1e67236fe49bd69cefd68f3e9b865c8883f57986587

Operating System and Environment details

Windows 11

Log Fragments

No response

[GuptaManan100]

I ran vttestserver docker image today and verified that this is indeed the case. But I am not sure if this is something we would want to (even can) fix.

The way vttestserver works is that it simulates a vitess cluster, but only runs a single MySQL instance. The way it simulates having multiple shards is by creating multiple databases in the same MySQL instance with different names. So for example, if we run the vttestserver as written in the docs at https://vitess.io/docs/18.0/get-started/vttestserver-docker-image/, then the databases created in MySQL for the test keyspace are vt_test_80- and vt_test_-80.

So, when we run a information schema query, then we end up seeing the actual database names and not the keyspace name.

mysql> SELECT
    ->     *
    -> FROM information_schema.key_column_usage AS kcu;
+--------------------+--------------------+---------------------+---------------+--------------------+------------------------------------------------------+-----------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA  | CONSTRAINT_NAME     | TABLE_CATALOG | TABLE_SCHEMA       | TABLE_NAME                                           | COLUMN_NAME           | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------------+--------------------+---------------------+---------------+--------------------+------------------------------------------------------+-----------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| def                | vt_test_80-        | PRIMARY             | def           | vt_test_80-        | Payment                                              | id                    |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_-80        | PRIMARY             | def           | vt_test_-80        | Payment                                              | id                    |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_-80        | PRIMARY             | def           | vt_test_-80        | User                                                 | id                    |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_-80        | User_paymentId_key  | def           | vt_test_-80        | User                                                 | paymentId             |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_-80        | User_paymentId_fkey | def           | vt_test_-80        | User                                                 | paymentId             |                1 |                             1 | vt_test_-80             | Payment               | id                     |
| def                | vt_test_80-        | PRIMARY             | def           | vt_test_80-        | User                                                 | id                    |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_80-        | User_paymentId_key  | def           | vt_test_80-        | User                                                 | paymentId             |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_80-        | User_paymentId_fkey | def           | vt_test_80-        | User                                                 | paymentId             |                1 |                             1 | vt_test_80-             | Payment               | id                     |
+--------------------+--------------------+---------------------+---------------+--------------------+------------------------------------------------------+-----------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+

The problem is two-fold. One the database name is wrong and two, we get the results twice because of 2 shards. This problem is specifically unique for vttestserver and vtgate doesn't suffer from the same, because it only routes information schema queries to one shard and changes the database name with the keyspace name.

I don't think there is an easy way to fix it in vttestserver and it would probably be better if you just changed the query on your end. Maybe run a query like so -

mysql> SELECT * FROM information_schema.key_column_usage AS kcu where table_schema like '%test%';
+--------------------+-------------------+---------------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME     | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------------+-------------------+---------------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| def                | vt_test_-80       | PRIMARY             | def           | vt_test_-80  | Payment    | id          |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_-80       | PRIMARY             | def           | vt_test_-80  | User       | id          |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_-80       | User_paymentId_key  | def           | vt_test_-80  | User       | paymentId   |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_-80       | User_paymentId_fkey | def           | vt_test_-80  | User       | paymentId   |                1 |                             1 | vt_test_-80             | Payment               | id                     |
| def                | vt_test_80-       | PRIMARY             | def           | vt_test_80-  | Payment    | id          |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_80-       | PRIMARY             | def           | vt_test_80-  | User       | id          |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_80-       | User_paymentId_key  | def           | vt_test_80-  | User       | paymentId   |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | vt_test_80-       | User_paymentId_fkey | def           | vt_test_80-  | User       | paymentId   |                1 |                             1 | vt_test_80-             | Payment               | id                     |
+--------------------+-------------------+---------------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
8 rows in set (0.02 sec)

This is what I think, but we should consult @systay @harshit-gangal and @frouioui for their thoughts too.

[deepthi]

Using a single MySQL to fake a sharded vitess database is a design choice in vttestserver in order to keep it small / possible to run on small machines. It's something that people need to account for / work around in their testing. It's not worth changing any production code to mask this, test code will need to adapt.

[janpio]

I understand where you are coming from. In our case we need to adapt production code, which is executed during tests.

Prisma's introspection feature detects foreign keys via a information_schema query. Prisma's migrate reset gets the list of foreign keys to drop first before it tries to drop tables from the database (This is how I noticed this issue - our reset mechanism always failed running against vttestserver).

The "multiple shards" situation is not actually that relevant for us, as we only test with 1 shard (should we test with more as well to make sure Planetscale is properly supported?). But the different table schema name is what throws our code off here.

How are the names for the test databases generated? The ones here do not match the pattern that I described above, if I picked that up correctly.

If we can find a reliable way to do that it might be easier to jsut hard code that on our side than modifying vttestserver.

[GuptaManan100]

@janpio I found the code that determines the name of the database that vtcombo creates in MySQL. The code is

dbname = fmt.Sprintf("vt_%v_%v", keyspace, shard)

For a new keyspace created from the vtgate command line the shardname is always 0. Therefore you can expect the database name to be vt_<keyspace>_0 form.

For example, in the description, you created a keyspace called tests via create database tests and its underlying database name was vt_tests_0

[janpio]

Thank you, that looks good.

Any idea how this can lead to the 2 names you shared above?

[GuptaManan100]

2 or more names come when you create a sharded keyspace. But this is not possible from the vtgate mysql shell.

sharded keyspaces can be created from the docker image/binary when it starts up using flags like so -

--keyspaces=test,unsharded \
--num_shards=2,1

I am closing this issue since we seem to have agreed on a workaround. Please feel free to reopen it if you have further comments or follow-ups.

[janpio]

(Do not disagree with the closing, but just a note: Us normal people not being contributors here actually can not reopen closed issues 😆)

[GuptaManan100]

@janpio I had no idea! Thank-you for pointing it out and I'll be more careful from now on! I just assumed if anyone can open an issue, then anyone would be able to reopen issues too! 😄

[janpio]

I know, that would be utter chaos though 🤣