Overview of the Issue
With VTGate Parameter transaction-mode=SINGLE, in a transaction Secondary vindex interactions are counted are Cross Shard even though application data is on a single shard
Reproduction Steps
- In a 2 shard keyspace, deploy VSchema
{
"sharded": true,
"vindexes": {
"unicode_loose_md5_vdx": {
"type": "unicode_loose_md5"
},
"hash_vdx": {
"type": "hash"
},
"t1_id_vdx": {
"type": "consistent_lookup_unique",
"params": {
"autocommit": "true",
"from": "id",
"table": "t1_id_vdx",
"to": "keyspace_id"
},
"owner": "t1"
},
"t2_id_vdx": {
"type": "consistent_lookup_unique",
"params": {
"autocommit": "true",
"from": "id",
"table": "t2_id_vdx",
"to": "keyspace_id"
},
"owner": "t2"
}
},
"tables": {
"t1": {
"columnVindexes": [
{
"column": "txn_id",
"name": "unicode_loose_md5_vdx"
},
{
"column": "id",
"name": "t1_id_vdx"
}
]
},
"t2": {
"columnVindexes": [
{
"column": "txn_id",
"name": "unicode_loose_md5_vdx"
},
{
"column": "id",
"name": "t2_id_vdx"
}
]
},
"t1_id_vdx": {
"columnVindexes": [
{
"column": "id",
"name": "hash_vdx"
}
]
},
"t2_id_vdx": {
"columnVindexes": [
{
"column": "id",
"name": "hash_vdx"
}
]
}
}
}
- Apply Schema
DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` (
`id` bigint(20) NOT NULL,
`txn_id` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS t1_id_vdx;
CREATE TABLE `t1_id_vdx` (
`id` bigint(20) NOT NULL,
`keyspace_id` varbinary(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS t2;
CREATE TABLE `t2` (
`id` bigint(20) NOT NULL,
`txn_id` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS t2_id_vdx;
CREATE TABLE `t2_id_vdx` (
`id` bigint(20) NOT NULL,
`keyspace_id` varbinary(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
- Execute following SQL
BEGIN;
INSERT INTO t1(id, txn_id) VALUES (1, "t1");
SELECT * FROM t2 WHERE id = 1;
ROLLBACK;
Sample log
mysql> SHOW VSCHEMA VINDEXES ON t1;
+---------+-----------------------+--------------------------+-----------------------------------------------------------+-------+
| Columns | Name | Type | Params | Owner |
+---------+-----------------------+--------------------------+-----------------------------------------------------------+-------+
| txn_id | unicode_loose_md5_vdx | unicode_loose_md5 | | |
| id | t1_id_vdx | consistent_lookup_unique | autocommit=true; from=id; table=t1_id_vdx; to=keyspace_id | t1 |
+---------+-----------------------+--------------------------+-----------------------------------------------------------+-------+
2 rows in set (0.01 sec)
mysql> SHOW VSCHEMA VINDEXES ON t2;
+---------+-----------------------+--------------------------+-----------------------------------------------------------+-------+
| Columns | Name | Type | Params | Owner |
+---------+-----------------------+--------------------------+-----------------------------------------------------------+-------+
| txn_id | unicode_loose_md5_vdx | unicode_loose_md5 | | |
| id | t2_id_vdx | consistent_lookup_unique | autocommit=true; from=id; table=t2_id_vdx; to=keyspace_id | t2 |
+---------+-----------------------+--------------------------+-----------------------------------------------------------+-------+
2 rows in set (0.01 sec)
mysql> SHOW TABLES;
+-------------------------+
| Tables_in_debug_sharded |
+-------------------------+
| t1 |
| t1_id_vdx |
| t2 |
| t2_id_vdx |
+-------------------------+
4 rows in set (0.01 sec)
mysql>
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
mysql> SELECT * FROM t2;
Empty set (0.01 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO t1(id, txn_id) VALUES (1, "t1");
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t2 WHERE id = 1;
ERROR 1317 (70100): multi-db transaction attempted: [target:{keyspace:"debug_sharded" shard:"-80" tablet_type:PRIMARY} transaction_id:1685629742014607709 tablet_alias:{cell:"dc1" uid:701} target:{keyspace:"debug_sharded" shard:"80-" tablet_type:PRIMARY} transaction_id:1685629898601231223 tablet_alias:{cell:"dc1" uid:801}]
mysql>
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql>
Binary Version
Version: 16.0.1 (Git revision d1ba6258ea2462d5d28d67661aace7b79bb7e27b branch 'HEAD') built on Thu Mar 30 17:03:35 UTC 2023 by runner@fv-az280-176 using go1.20.2 linux/amd64
Operating System and Environment details
[vitess@workernode-upi-v1-10011441 multiDbTxn]$ uname -sr
Linux 3.10.0-1160.83.1.el7.x86_64
[vitess@workernode-upi-v1-10011441 multiDbTxn]$ cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"
CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"
[vitess@workernode-upi-v1-10011441 multiDbTxn]$ uname -m
x86_64
Log Fragments
No response
Overview of the Issue
With VTGate Parameter transaction-mode=SINGLE, in a transaction Secondary vindex interactions are counted are Cross Shard even though application data is on a single shard
Reproduction Steps
Sample log
Binary Version
Version: 16.0.1 (Git revision d1ba6258ea2462d5d28d67661aace7b79bb7e27b branch 'HEAD') built on Thu Mar 30 17:03:35 UTC 2023 by runner@fv-az280-176 using go1.20.2 linux/amd64Operating System and Environment details
Log Fragments
No response