Rollback options are silently ignored

[dveeden]

Bug Report

1. Minimal reproduce step (Required)

MySQL:

mysql 8.0.22 > SELECT @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql 8.0.22 > CREATE TABLE t1 (id SERIAL);
Query OK, 0 rows affected (0.07 sec)

mysql 8.0.22 > INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.02 sec)

mysql 8.0.22 > ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql 8.0.22 > SELECT * FROM t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql 8.0.22 > ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql 8.0.22 > INSERT INTO t1 VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql 8.0.22 > ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql 8.0.22 > SELECT * FROM t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql 8.0.22 > SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              18 |
+-----------------+
1 row in set (0.00 sec)

mysql 8.0.22 > ROLLBACK RELEASE;
Query OK, 0 rows affected (0.00 sec)

mysql 8.0.22 > SELECT CONNECTION_ID();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    19
Current database: test

+-----------------+
| CONNECTION_ID() |
+-----------------+
|              19 |
+-----------------+
1 row in set (0.00 sec)

TiDB:

mysql 5.7.25-TiDB-v5.0.0-nightly > SELECT @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > CREATE TABLE t1 (id SERIAL);
Query OK, 0 rows affected (0.12 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.03 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > SELECT * FROM t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > INSERT INTO t1 VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > SELECT * FROM t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               7 |
+-----------------+
1 row in set (0.00 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > ROLLBACK RELEASE;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7.25-TiDB-v5.0.0-nightly > SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               7 |
+-----------------+
1 row in set (0.00 sec)

2. What did you expect to see? (Required)

1. I expect to be in a transaction when executing ROLLBACK AND CHAIN
2. I expect a new connection after executing ROLLBACK RELEASE

3. What did you see instead (Required)

Both AND CHAIN and RELEASE options for ROLLBACK seem to get ignored silently.

4. What is your TiDB version? (Required)

*************************** 1. row ***************************
tidb_version(): Release Version: v5.0.0-nightly
Edition: Community
Git Commit Hash: e2740f54b6b29d99f3396bed4e66779549becf75
Git Branch: heads/refs/tags/v5.0.0-nightly
UTC Build Time: 2021-04-02 16:25:23
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[dveeden]

Note that PostgreSQL also has the AND CHAIN, but limits this to transaction blocks.

test=# \echo :AUTOCOMMIT
on
test=# CREATE TABLE t1 (id SERIAL);
CREATE TABLE
test=# INSERT INTO t1 VALUES(1);
INSERT 0 1
test=# ROLLBACK;
WARNING:  there is no transaction in progress
ROLLBACK
test=# SELECT * FROM t1;
 id 
----
  1
(1 row)

test=# ROLLBACK AND CHAIN;
ERROR:  ROLLBACK AND CHAIN can only be used in transaction blocks
test=# BEGIN;
BEGIN
test=*# ROLLBACK AND CHAIN;
ROLLBACK
test=*# INSERT INTO t1 VALUES(2);
INSERT 0 1
test=*# ROLLBACK;
ROLLBACK
test=# SELECT * FROM t1;
 id 
----
  1
(1 row)

https://crate.io/docs/sql-99/en/latest/chapters/36.html indicates that this is part of the SQL:1999 standard.

[dveeden]

I don't think these options are commonly used in MySQL. However I would not be surprised if some benchmarks or other tools use rollback release to force a connection reset.

[dveeden]

https://github.com/pingcap/parser/blob/7b490931db5f6fa20180d3097339bac3969f3160/parser.y#L7680 seems to be aware of the syntax.

https://github.com/pingcap/tidb/blob/9c75cfa4e2bbb854ab90562ecec0409f84a989b0/executor/simple.go#L745 doesn't seem to use the CompletionType of the RollbackStmt.

[zimulala]

Thanks for your report! Now we only support chain and release on syntax. We handle it in pingcap/parser/pull/751.