Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
24.02.4
-
None
Description
When using causal_reads MaxScale seems to get confused by the fact that INSERT INTO...RETURNING returns a resultset and doesn't update to the new GTID. Thus the next select has a wrong GTID inserted in the MASTER_GTID_WAIT prefix. This causes stale data to be returned to the client.
How to replicate:
- A simple primary-replica setup, 1 primary - 1 replica
- MaxScale 24.02.4 with `readwritesplit` and `causal_reads=global`
connect through maxscale and run
MariaDB [test]> drop database if exists test; create database test; create table test.test (id int); insert into test.test (id) values (1) returning 1; select count(*) from test.test; |
Query OK, 1 row affected (0.016 sec)
|
|
|
Query OK, 1 row affected (0.001 sec)
|
|
|
Query OK, 0 rows affected (0.004 sec) |
|
|
+---+ |
| 1 |
|
+---+ |
| 1 |
|
+---+ |
1 row in set (0.002 sec) |
|
|
+----------+ |
| count(*) | |
+----------+ |
| 0 |
|
+----------+ |
1 row in set (0.004 sec) |
Note how the final SELECT returned 0
As a contrast, when using a normal INSERT instead of the INSERT...RETURNING
MariaDB [test]> drop database if exists test; create database test; create table test.test (id int); insert into test.test (id) values (1); select count(*) from test.test; |
Query OK, 1 row affected (0.016 sec)
|
|
|
Query OK, 1 row affected (0.001 sec)
|
|
|
Query OK, 0 rows affected (0.005 sec) |
|
|
Query OK, 1 row affected (0.003 sec)
|
|
|
+----------+ |
| count(*) | |
+----------+ |
| 1 |
|
+----------+ |
1 row in set (0.004 sec) |
I have attached two logs , one with a normal insert and one with RETURNING.
You will notice how in the RETURNING case MASTER_GTID_WAIT is waiting on the last transaction before the INSERT instead of the INSERT itself.
I took the liberty to recompile maxscale so I could add 1 line of code in the `add_prefix_wait_gtid` function to log the actual GTID user in the `MASTER_GTID_WAIT` prefix. This makes the whole issue much easier to see.
This was specifically tested with `causal_reads=global` but most likely the problem exists with the other modes, too.
This was tested with 24.02.4, but most likely is an old bug that exists in many versions.
Attachments
Issue Links
- is blocked by
-
-
- Closed
-
Activity
for reference
- initial test was done with server 10.11.8
- I now did a quick test with 11.7.1 - same result
This seems to be a bug in MariaDB itself. The network dump shows that it doesn't return the state tracker for the GTID when RETURNING is used:
T 127.0.0.1:48676 -> 127.0.0.1:3000 [AP] #7
|
1f 00 00 00 03 69 6e 73 65 72 74 20 69 6e 74 6f .....insert into
|
20 74 65 73 74 2e 74 31 20 76 61 6c 75 65 73 20 test.t1 values
|
28 31 29 (1)
|
#
|
T 127.0.0.1:3000 -> 127.0.0.1:48676 [AP] #8
|
1f 00 00 01 00 01 00 02 40 00 00 00 16 00 14 09 ........@.......
|
6c 61 73 74 5f 67 74 69 64 09 30 2d 33 30 30 30 last_gtid.0-3000
|
2d 31 31 -11
|
##
|
T 127.0.0.1:48676 -> 127.0.0.1:3000 [AP] #10
|
2c 00 00 00 03 69 6e 73 65 72 74 20 69 6e 74 6f ,....insert into
|
20 74 65 73 74 2e 74 31 20 76 61 6c 75 65 73 20 test.t1 values
|
28 31 29 20 72 65 74 75 72 6e 69 6e 67 20 69 64 (1) returning id
|
#
|
T 127.0.0.1:3000 -> 127.0.0.1:48676 [AP] #11
|
02 00 00 01 01 01 23 00 00 02 03 64 65 66 04 74 ......#....def.t
|
65 73 74 02 74 31 02 74 31 02 69 64 02 69 64 00 est.t1.t1.id.id.
|
0c 3f 00 0b 00 00 00 03 00 00 00 00 00 05 00 00 .?..............
|
03 fe 00 00 02 00 02 00 00 04 01 31 05 00 00 05 ...........1....
|
fe 00 00 02 40 ....@
|
#
|
So it turns out that I was wrong: the GTID for resultsets is only sent if the client uses the DEPRECATE_EOF protocol. When the client declares the capability, it is correctly returned. This needs to be added to the documentation as a limitation that mostly affects INSERT ... RETURNING.
I also did find a bug in readwritesplit where it doesn't look for the last_gtid variable if the result is classified as a resultset. This is probably why it didn't work in your case.