Vitess doesn't return data if it was inserted directly to an incorrect shard

[vinaykr-stripe]

Overview of the Issue

When insert query is targeted to a specific shard but the inserted row's shard key doesn't fall on the provided shard, insert operation succeeds but when you query back the inserted records and provide the correct shard keys, Vitess doesn't return any results.

Reproduction Steps

Steps to reproduce this issue, example:

1. Deploy the following vschema:

   {
     "sharded": true,
     "vindexes": {
       "hash": {
         "type": "hash"
       },
     "tables": {
       "user": {
         "column_vindexes": [
           {
             "column": "user_id",
             "name": "hash"
           }
         ]
       }
     }
   }

2. Deploy the following schema:

   create table user(user_id bigint, name varchar(128), primary key(user_id));

3. Run the following on a specific shard. The insert will succeed.

   INSERT INTO user VALUES (1, "John"), (2, "Henry"), (3, "Mary")

In my test, this was done using the gRPC protocol and setting the target as the shard reference rather than the keyspace. Note that, in this case, the provided shard would not be the shard where these rows would have been added if they were inserted to a keyspace instead. This incorrect shard was chosen to test what happens, if someone accidentally inserted data into the wrong shard.

3. When you query the keyspace by providing the correct shard key, vitess will return no results.

   SELECT * FROM user where user_id=1

OUTPUT: No Rows Found

Binary version

Example:

giaquinti@workspace:~$ vtgate --version
vitess@2b9076b45a1f:/vt/local$ vtgate -version
ERROR: logging before flag.Parse: E0921 22:12:18.896672    2193 syslogger.go:149] can't connect to syslog
Version: 10.0.0-RC1 (Git revision d3313e6ac branch 'master') built on Fri Apr 23 23:11:30 UTC 2021 by vitess@dd80b143c8eb using go1.15.6 linux/amd64```

#### Operating system and Environment details

OS, Architecture, and any other information you can provide
about the environment.

- Operating system (output of `cat /etc/os-release`):
```vitess@2b9076b45a1f:/vt/local$ cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 10 (buster)"
NAME="Debian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"```

- Kernel version (output of `uname -sr`):
```vitess@2b9076b45a1f:/vt/local$ uname -sr
Linux 5.10.25-linuxkit```
- Architecture (output of `uname -m`):
```vitess@2b9076b45a1f:/vt/local$ uname -m
x86_64```
#### Log Fragments
NA
Include appropriate log fragments. If the log is longer than a few dozen lines, please
include the URL to the [gist](https://gist.github.com/) of the log instead of posting it in the issue.

[derekperkins]

This is expected behavior - Vitess purposefully only routes queries to the correct shard. What are you expecting/wanting to happen? If you run the same query targeted at the same shard, does it return results?

[vinaykr-stripe]

@derekperkins , thanks for the quick reply!

basically we are trying to avoid a situation where we accidentally insert data to an invalid shard and then query the keyspace later on and get no data back. In this case, it seems like the data is almost "lost".

Ideally, it doesn't appear bad to fail the insert, if the shard doesn't match the shard keys of the inserted rows. It will not give a false sense of security that the data was inserted but can't be queried.

[derekperkins]

Is there a reason you're using shard targeting for inserts? If you don't, you shouldn't ever have to deal with data on bad shards. When using shard targeting, Vitess takes a hands-off approach so you can do whatever "unsafe" queries that you need to that might not work in a sharded setup.

[vinaykr-stripe]

Yes, this might be a general use-case and many of your clients might have it or I might be doing something wrong. Let me describe the use-case.

We don't always have the shard keys to query data. Especially, if there is a async job that periodically runs to do some operation on rows in a specific state. The job doesn't have a list of all valid shard keys or even if it can get that, the list will be pretty long (millions/billions of possible options). Therefore, to get around that limitation, we get the list of shards and query each shard individually. For these records from each shard, we would like to either update these records or insert new objects with the same shard keys (so they will end up in the same shard). To get better performance, we want to do the inserts in bulk, ie provide a single insert statement with many values. At this point, we could do one of the following two things:

1. Use insert statement that is targeted against a keyspace
2. Use insert statement that is targeted against a specific shard

In our SDK which wraps to Vitess API, we don't want to use option 1 as clients might start sending insert statements for rows that belong to different shards. I am not sure how this is handled in Vitess and if such a statement will even succeed. Even if it works, if there are globally distributed shards, it will be a slow/unreliable API to expose to clients. Therefore, we want to have the bulk insert API where the client's can specify a Shard instead.

I hope that make sense? If you have recommendations, would love to hear them.

[derekperkins]

#1 is the recommended option, or not doing any keyspace targeting at all if you can help it (requires unique table names cluster wide). You can still do bulk inserts and they'll get routed to the correct shard. I would be surprised if you noticed any significant latency differences.

[derekperkins]

The more you just trust Vitess to handle and leave all shard-aware logic out of your code, the better your experience will be.

That being said, for the specific case you described where the initial query is against a single shard, there are benefits to doing that, since you'll be guaranteed atomic transactional updates, since updates/inserts aren't happening across shards. Depending on the use case, that may or may not be worth "dirtying" your codebase with shard-aware logic.

[vinaykr-stripe]

Thanks for confirming the validity of the use case. I am not sure how willing Vitess team is to fix this issue but here are the possible options in the order of desirability:

1. Patch the corner-case scenario where if documents are inserted into the wrong shard, the insert is rejected.
2. or, accept the inserts as it is but fix the query routing so that rows are found when a valid query is sent to the keyspace.
3. or, update the docs and vtgate proto to define the current behavior with a warning that potential "data loss" can happen.

Obviously, I prefer 1 or 2.

[derekperkins]

I'm pretty sure #2 is a no-go for performance reasons - it would turn every query into a full scatter query. #1 is above my paygrade - thoughts @deepthi / @sougou?

[harshit-gangal]

Shard targeting is a super-user feature and should not be used casually without knowing its consequences. It can speed up some of the work carried out by VTGate but should be used wisely. Vitess is not expected to fix any data issue caused by using the shard targeting feature.