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
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.
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] #71f 00 00 00 03 69 6e 73 65 72 74 20 69 6e 74 6f .....insert into20 74 65 73 74 2e 74 31 20 76 61 6c 75 65 73 20 test.t1 values28 31 29 (1)#T 127.0.0.1:3000 -> 127.0.0.1:48676 [AP] #81f 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-30002d 31 31 -11##T 127.0.0.1:48676 -> 127.0.0.1:3000 [AP] #102c 00 00 00 03 69 6e 73 65 72 74 20 69 6e 74 6f ,....insert into20 74 65 73 74 2e 74 31 20 76 61 6c 75 65 73 20 test.t1 values28 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] #1102 00 00 01 01 01 23 00 00 02 03 64 65 66 04 74 ......#....def.t65 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 ....@#