Description:
When autocommit=0, it is possible to create a SAVEPOINT without an active transaction.
Savepoints are created within a transaction:
https://dev.mysql.com/doc/refman/8.0/en/savepoint.html
Futhermore, according to the documentation savepoints are supported by (only?) InnoDB.
A savepoint created outside a transaction is automatically released. This is expected and perfectly fine. For example, running the following:
SET AUTOCOMMIT=1;
SAVEPOINT s1;
ROLLBACK TO SAVEPOINT s1;
Will lead to this error:
ERROR 1305 (42000): SAVEPOINT s1 does not exist
On the other end, if autocommit=0, the savepoint is created:
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.01 sec)
mysql> SAVEPOINT s1;
Query OK, 0 rows affected (0.00 sec)
mysql> ROLLBACK TO SAVEPOINT s1;
Query OK, 0 rows affected (0.01 sec)
According to tcpdump (output below), in the OK packet the status flags (https://dev.mysql.com/doc/internals/en/status-flags.html) have SERVER_STATUS_IN_TRANS disabled:
08:58:23.667274 IP 127.0.0.1.52144 > 127.0.0.1.3306: Flags [P.], seq 3523084303:3523084324, ack 1082911944, win 512, options [nop,nop,TS val 1248673832 ecr 1248658729], length 21
0x0000: 4500 0049 7754 4000 4006 c558 7f00 0001 E..IwT@.@..X....
0x0010: 7f00 0001 cbb0 0cea d1fe 000f 408b ecc8 ............@...
0x0020: 8018 0200 fe3d 0000 0101 080a 4a6d 4028 .....=......Jm@(
0x0030: 4a6d 0529 1100 0000 0353 4554 2041 5554 Jm.).....SET.AUT
0x0040: 4f43 4f4d 4d49 543d 30 OCOMMIT=0
08:58:23.667309 IP 127.0.0.1.3306 > 127.0.0.1.52144: Flags [.], ack 21, win 512, options [nop,nop,TS val 1248673832 ecr 1248673832], length 0
0x0000: 4500 0034 4354 4000 4006 f96d 7f00 0001 E..4CT@.@..m....
0x0010: 7f00 0001 0cea cbb0 408b ecc8 d1fe 0024 ........@......$
0x0020: 8010 0200 fe28 0000 0101 080a 4a6d 4028 .....(......Jm@(
0x0030: 4a6d 4028 Jm@(
08:58:23.670336 IP 127.0.0.1.3306 > 127.0.0.1.52144: Flags [P.], seq 1:31, ack 21, win 512, options [nop,nop,TS val 1248673835 ecr 1248673832], length 30
0x0000: 4500 0052 4355 4000 4006 f94e 7f00 0001 E..RCU@.@..N....
0x0010: 7f00 0001 0cea cbb0 408b ecc8 d1fe 0024 ........@......$
0x0020: 8018 0200 fe46 0000 0101 080a 4a6d 402b .....F......Jm@+
0x0030: 4a6d 4028 1a00 0001 0000 0000 4000 0000 Jm@(........@...
0x0040: 1100 0f0a 6175 746f 636f 6d6d 6974 034f ....autocommit.O
0x0050: 4646 FF
08:58:23.670355 IP 127.0.0.1.52144 > 127.0.0.1.3306: Flags [.], ack 31, win 512, options [nop,nop,TS val 1248673835 ecr 1248673835], length 0
0x0000: 4500 0034 7755 4000 4006 c56c 7f00 0001 E..4wU@.@..l....
0x0010: 7f00 0001 cbb0 0cea d1fe 0024 408b ece6 ...........$@...
0x0020: 8010 0200 fe28 0000 0101 080a 4a6d 402b .....(......Jm@+
0x0030: 4a6d 402b Jm@+
08:58:32.072814 IP 127.0.0.1.52144 > 127.0.0.1.3306: Flags [P.], seq 21:38, ack 31, win 512, options [nop,nop,TS val 1248682238 ecr 1248673835], length 17
0x0000: 4500 0045 7756 4000 4006 c55a 7f00 0001 E..EwV@.@..Z....
0x0010: 7f00 0001 cbb0 0cea d1fe 0024 408b ece6 ...........$@...
0x0020: 8018 0200 fe39 0000 0101 080a 4a6d 60fe .....9......Jm`.
0x0030: 4a6d 402b 0d00 0000 0353 4156 4550 4f49 Jm@+.....SAVEPOI
0x0040: 4e54 2073 31 NT.s1
08:58:32.072864 IP 127.0.0.1.3306 > 127.0.0.1.52144: Flags [.], ack 38, win 512, options [nop,nop,TS val 1248682238 ecr 1248682238], length 0
0x0000: 4500 0034 4356 4000 4006 f96b 7f00 0001 E..4CV@.@..k....
0x0010: 7f00 0001 0cea cbb0 408b ece6 d1fe 0035 ........@......5
0x0020: 8010 0200 fe28 0000 0101 080a 4a6d 60fe .....(......Jm`.
0x0030: 4a6d 60fe Jm`.
08:58:32.073131 IP 127.0.0.1.3306 > 127.0.0.1.52144: Flags [P.], seq 31:42, ack 38, win 512, options [nop,nop,TS val 1248682238 ecr 1248682238], length 11
0x0000: 4500 003f 4357 4000 4006 f95f 7f00 0001 E..?CW@.@.._....
0x0010: 7f00 0001 0cea cbb0 408b ece6 d1fe 0035 ........@......5
0x0020: 8018 0200 fe33 0000 0101 080a 4a6d 60fe .....3......Jm`.
0x0030: 4a6d 60fe 0700 0001 0000 0000 0000 00 Jm`............
08:58:32.073154 IP 127.0.0.1.52144 > 127.0.0.1.3306: Flags [.], ack 42, win 512, options [nop,nop,TS val 1248682238 ecr 1248682238], length 0
0x0000: 4500 0034 7757 4000 4006 c56a 7f00 0001 E..4wW@.@..j....
0x0010: 7f00 0001 cbb0 0cea d1fe 0035 408b ecf1 ...........5@...
0x0020: 8010 0200 fe28 0000 0101 080a 4a6d 60fe .....(......Jm`.
0x0030: 4a6d 60fe Jm`.
08:58:48.140493 IP 127.0.0.1.52144 > 127.0.0.1.3306: Flags [P.], seq 38:67, ack 42, win 512, options [nop,nop,TS val 1248698305 ecr 1248682238], length 29
0x0000: 4500 0051 7758 4000 4006 c54c 7f00 0001 E..QwX@.@..L....
0x0010: 7f00 0001 cbb0 0cea d1fe 0035 408b ecf1 ...........5@...
0x0020: 8018 0200 fe45 0000 0101 080a 4a6d 9fc1 .....E......Jm..
0x0030: 4a6d 60fe 1900 0000 0352 4f4c 4c42 4143 Jm`......ROLLBAC
0x0040: 4b20 544f 2053 4156 4550 4f49 4e54 2073 K.TO.SAVEPOINT.s
0x0050: 31 1
08:58:48.140542 IP 127.0.0.1.3306 > 127.0.0.1.52144: Flags [.], ack 67, win 512, options [nop,nop,TS val 1248698306 ecr 1248698305], length 0
0x0000: 4500 0034 4358 4000 4006 f969 7f00 0001 E..4CX@.@..i....
0x0010: 7f00 0001 0cea cbb0 408b ecf1 d1fe 0052 ........@......R
0x0020: 8010 0200 fe28 0000 0101 080a 4a6d 9fc2 .....(......Jm..
0x0030: 4a6d 9fc1 Jm..
08:58:48.140819 IP 127.0.0.1.3306 > 127.0.0.1.52144: Flags [P.], seq 42:53, ack 67, win 512, options [nop,nop,TS val 1248698306 ecr 1248698305], length 11
0x0000: 4500 003f 4359 4000 4006 f95d 7f00 0001 E..?CY@.@..]....
0x0010: 7f00 0001 0cea cbb0 408b ecf1 d1fe 0052 ........@......R
0x0020: 8018 0200 fe33 0000 0101 080a 4a6d 9fc2 .....3......Jm..
0x0030: 4a6d 9fc1 0700 0001 0000 0000 0000 00 Jm.............
08:58:48.140864 IP 127.0.0.1.52144 > 127.0.0.1.3306: Flags [.], ack 53, win 512, options [nop,nop,TS val 1248698306 ecr 1248698306], length 0
0x0000: 4500 0034 7759 4000 4006 c568 7f00 0001 E..4wY@.@..h....
0x0010: 7f00 0001 cbb0 0cea d1fe 0052 408b ecfc ...........R@...
0x0020: 8010 0200 fe28 0000 0101 080a 4a6d 9fc2 .....(......Jm..
0x0030: 4a6d 9fc2 Jm..
According to InnoDB there is no transaction running:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
Empty set (0.00 sec)
The problem here is that in MySQL autocommit=0 doesn't start an explicit transaction, and in fact SERVER_STATUS_IN_TRANS is not set if a transaction-safe table is not queried. That is, a transaction is active when autocommit=0 *and* a query is executed against a transaction-safe table.
This creates some contradiction, because it seems that MySQL server is handling the savepoint as if a transaction was created, while:
a) at protocol level SERVER_STATUS_IN_TRANS is not set
b) at storage engine (InnoDB) level, no transaction is started yet
How to repeat:
Run the following queries on mysql client against a MySQL server, and verify the
SERVER_STATUS_IN_TRANS flag after the savepoint is created:
SET AUTOCOMMIT=0;
SAVEPOINT s1;
ROLLBACK TO SAVEPOINT s1;
Suggested fix:
I think 2 possible fixes are possible:
a) do not allow the creation of a savepoint if a transaction is not started yet (either with an explicit START TRANSACTION or equivalent, or running a query against a transaction-safe table)
b) if a savepoint is successfully created and still exists, SERVER_STATUS_IN_TRANS should be enabled
