rohit-nayak-ps
commented
6 months ago I am not able to repro this in v19. I ran the end to end test TestReferenceRouting in go/test/endtoend/vtgate/queries/reference. I commented out the teardown // defer clusterInstance.Teardown() . Based on this test setup, I believe the similar queries to what you are running are being planned correctly. Can you check and see what is different in this setup as compared to yours, so we can repro your failure. In particular, what is the value of require_explicit_routing in your vschema?
mysql> vexplain queries select d.id, z.id from sks.delivery_failure d inner join zip_detail z on d.zip_detail_id = z.id where d.id = 1;
+------+----------+-------+---------------------------------------------------------------------------------------------------------+
| # | keyspace | shard | query |
+------+----------+-------+---------------------------------------------------------------------------------------------------------+
| 0 | sks | -80 | select d.id, z.id from delivery_failure as d, zip_detail as z where d.id = 1 and d.zip_detail_id = z.id |
+------+----------+-------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
rohit@RSs-Laptop reference % vttablet --version
vttablet version Version: 19.0.4-SNAPSHOT (Git revision cc64915d0f9c06bd498026c301bdfbca5fd6afc5 branch 'release-19.0') built on Tue Apr 23 11:37:51 CEST 2024 by rohit@RSs-Laptop.local using go1.22.2 darwin/arm64
rohit@RSs-Laptop reference % vtctldclient --server localhost:8106 GetVSchema sks
{
"sharded": true,
"vindexes": {
"hash": {
"type": "hash",
"params": {},
"owner": ""
}
},
"tables": {
"delivery_failure": {
"type": "",
"column_vindexes": [
{
"column": "id",
"name": "hash",
"columns": []
}
],
"auto_increment": null,
"columns": [],
"pinned": "",
"column_list_authoritative": false,
"source": ""
},
"zip_detail": {
"type": "reference",
"column_vindexes": [],
"auto_increment": null,
"columns": [],
"pinned": "",
"column_list_authoritative": false,
"source": "uks.zip_detail"
}
},
"require_explicit_routing": false,
"foreign_key_mode": "unspecified"
}
wiebeytec
deepthi
Edit: See this comment for the real cause. The routing breaks for all reference tables if not all reference tables are present in the unsharded vschema.
Overview of the Issue
Reference table routing may not work as documented. If I don't explicitely name the copy of a table inside the sharded keyspace in a
SELECT ... JOIN, it uses the currentUSEkeyspace. This results in the amount of queries scattered multiplying. When I use 'global routing' / 'unspecified mode', it also uses the source table, not the reference, with the same result.Expected result: at least for global routing mode, I expected the reference table to be used. And the documentation somewhat suggests that even when addressing the source table or having a
USEkeyspace defined, that it reroutes:But granted, perhaps I'm reading my wishes into that. It would be really great if it worked that way though, because only using unspecified mode is really hard when you're moving tables between keyspaces (as they exist in multiple keyspaces at some point, breaking the routing).
Reproduction Steps
I have these (simplified) tables:
The table
dataAttributesis copied tosites2023withMaterialize, and it's defined in the VSchema forsites2023as reference:When I run this query:
with a default
USEoflegacy(the defaultUSEis the argument to themysqlcommand, which will affect the tabledataAttributes), you can see it multiplies many queries to thelegacykeyspace:But when I address the
dataAttributestable directly insites2023:The result is correct:
If I don't specify a default DB with
USE, and don't fully quailfydataAttributes, it says:I don't know at this point why my 'global routing' doesn't work, but even if I fix it by adding this vschema to
legacy:it still picks the table from the
legacykeyspace. To demonstrate, this is again the query with an unqualifieddataAttributes:And I run this without a DB argument to
mysql. It still erroneously goes tolegacy:Binary Version