Bug Report: Incorrect behaviour of VTGate Parameter transaction-mode=SINGLE

[ankitthakwani]

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

1. 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"
        }
      ]
    }
  }
}

2. 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;

3. 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

[harshit-gangal]

The problem here is that t2 does not have the data. So, the vindex lookup returns an empty result.
To send the field information for the query back to the client.

To do that Vitess generates an impossible query with where condition 1 != 1 removing the original where condition as it is unnecessary. This query is being sent to a random shard.

This is causing the multi-db transaction.