Bug Report: reference table for update/insert counter errors.(delete/select works well)

[lujiashun]

Overview of the Issue

1. meta is unsharded keyspace,
   customer is sharded keyspace,
2. use the vschema json for customer:

{
  "sharded": true,
  "vindexes": {},
  "tables": {
    "user_dst": {
      "type": "reference",
      "column_vindexes": [],
      "auto_increment": null,
      "columns": [],
      "pinned": "",
      "column_list_authoritative": false,
      "source": "meta.user_src"
    }
  },
  "require_explicit_routing": false,
  "foreign_key_mode": "unspecified"
}

vtctldclient --server=localhost:15999 ApplyVSchema --vschema-file=./vschema_reference_table.json customer
3. the SQL is below:

mysql> create table customer.user_dst(id int primary key, user_name varchar(10), over1 varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql> create table meta.user_src(id int primary key, user_name varchar(10), over1 varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert customer.user_dst values(1,"one", "one1"), (2,"two", "two2"), (3,"three", "three3");
ERROR 1146 (42S02): target: meta.0.primary: vttablet: rpc error: code = NotFound desc = Table 'vt_meta.user_dst' doesn't exist (errno 1146) (sqlstate 42S02) (CallerID: userData1): Sql: "insert into user_dst(id, user_name, over1) values (:vtg1 /* INT64 */, :vtg2 /* VARCHAR */, :vtg3 /* VARCHAR */), (:vtg4 /* INT64 */, :vtg5 /* VARCHAR */, :vtg6 /* VARCHAR */), (:vtg7 /* INT64 */, :vtg8 /* VARCHAR */, :vtg9 /* VARCHAR */)", BindVars: {vtg1: "type:INT64 value:\"1\""vtg2: "type:VARCHAR value:\"one\""vtg3: "type:VARCHAR value:\"on
mysql> delete from customer.user_dst;
Query OK, 0 rows affected (0.00 sec)

mysql> update customer.user_dst set user_name="bbb";
ERROR 1146 (42S02): target: meta.0.primary: vttablet: rpc error: code = NotFound desc = Table 'vt_meta.user_dst' doesn't exist (errno 1146) (sqlstate 42S02) (CallerID: userData1): Sql: "update user_dst set user_name = :user_name /* VARCHAR */", BindVars: {user_name: "type:VARCHAR value:"bbb""}
mysql> select * from customer.user_dst;
Empty set (0.00 sec)

Reproduction Steps

{
"sharded": true,
"vindexes": {},
"tables": {
"user_dst": {
"type": "reference",
"column_vindexes": [],
"auto_increment": null,
"columns": [],
"pinned": "",
"column_list_authoritative": false,
"source": "meta.user_src"
}
},
"require_explicit_routing": false,
"foreign_key_mode": "unspecified"
}
vtctldclient --server=localhost:15999 ApplyVSchema --vschema-file=./vschema_reference_table.json customer
3. the SQL is below:

mysql> create table customer.user_dst(id int primary key, user_name varchar(10), over1 varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql> create table meta.user_src(id int primary key, user_name varchar(10), over1 varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert customer.user_dst values(1,"one", "one1"), (2,"two", "two2"), (3,"three", "three3");
ERROR 1146 (42S02): target: meta.0.primary: vttablet: rpc error: code = NotFound desc = Table 'vt_meta.user_dst' doesn't exist (errno 1146) (sqlstate 42S02) (CallerID: userData1): Sql: "insert into user_dst(id, user_name, over1) values (:vtg1 /* INT64 /, :vtg2 / VARCHAR /, :vtg3 / VARCHAR /), (:vtg4 / INT64 /, :vtg5 / VARCHAR /, :vtg6 / VARCHAR /), (:vtg7 / INT64 /, :vtg8 / VARCHAR /, :vtg9 / VARCHAR */)", BindVars: {vtg1: "type:INT64 value:"1""vtg2: "type:VARCHAR value:"one""vtg3: "type:VARCHAR value:"on
mysql> delete from customer.user_dst;
Query OK, 0 rows affected (0.00 sec)

mysql> update customer.user_dst set user_name="bbb";
ERROR 1146 (42S02): target: meta.0.primary: vttablet: rpc error: code = NotFound desc = Table 'vt_meta.user_dst' doesn't exist (errno 1146) (sqlstate 42S02) (CallerID: userData1): Sql: "update user_dst set user_name = :user_name /* VARCHAR */", BindVars: {user_name: "type:VARCHAR value:"bbb""}
mysql> select * from customer.user_dst;
Empty set (0.00 sec)

Binary Version

[vitess@localhost local]$ vtgate --version
vtgate version Version: 19.0.0-SNAPSHOT (Git revision aecf79325b64fcf120c6a16a243badb335e6414b branch 'main') built on Sun Apr 21 22:38:06 PDT 2024 by root@localhost.localdomain using go1.22.1 linux/amd64

Operating System and Environment details

[vitess@localhost local]$ cat /etc/os-release
NAME="CentOS Linux"
VERSION="8"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="CentOS Linux 8"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:8"
HOME_URL="https://centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"
CENTOS_MANTISBT_PROJECT="CentOS-8"
CENTOS_MANTISBT_PROJECT_VERSION="8"

[vitess@localhost local]$ uname -sr
Linux 4.18.0-348.7.1.el8_5.x86_64

[vitess@localhost local]$ uname -m
x86_64

Log Fragments

No response

[rohit-nayak-ps]

It looks like, today based on the documentation and test cases, Vitess expects reference tables to have the same name on the sharded keyspace as the unsharded. I was able to reproduce this separately as well.

Can you confirm that it works for you, if you use the same name?

[lujiashun]

It looks like, today based on the documentation and test cases, Vitess expects reference tables to have the same name on the sharded keyspace as the unsharded. I was able to reproduce this separately as well.

Can you confirm that it works for you, if you use the same name?

yes, the same name works for me. in V16 version, it works for different name. I also test v18.0.2, only works for the same name.

[rohit-nayak-ps]

OK, then there is a regression. We had made a few changes around reference tables in v17, so that might have caused it or possibly some other refactoring in vtgate was responsible.

Is this blocking you from anything at the moment? I think I have a possible fix for it but I need to investigate/discuss it further. It might take me a couple of days to get to it.

[lujiashun]

OK, then there is a regression. We had made a few changes around reference tables in v17, so that might have caused it or possibly some other refactoring in vtgate was responsible.

Is this blocking you from anything at the moment? I think I have a possible fix for it but I need to investigate/discuss it further. It might take me a couple of days to get to it.

yes, this is blocking me for using reference table , i am using v18.0.2 for it, if possible, i wish it can be fixed in this version V18.0.2 or V18.0 firstly . the difference beteween main branch and V18.0.2 is a bit big.

[lujiashun]

For release-18.0 , the reference table works well with the patch, Close the issue.