[MXS-4413] UPDATE with user variable breaks replication Created: 2022-11-24  Updated: 2022-11-25  Resolved: 2022-11-25

Status: Closed
Project: MariaDB MaxScale
Component/s: qc_sqlite
Affects Version/s: 2.5, 6.4, 22.08
Fix Version/s: 6.4.4, 22.08.3

Type: Bug Priority: Critical
Reporter: Asel Magzhanova Assignee: Johan Wikman
Resolution: Fixed Votes: 0
Labels: None


 Description   

I have maxscale (version 22.08.2) and 4 mariadb nodes (master and slaves).
The problem has appeared in one of mariadb replicas:

An attempt was made to binlog GTID 1-2-75990 which would create an out-of-order sequence number with existing GTID 1-1-75990, and gtid strict mode is enabled

I found a problem query:

 UPDATE objects o,
 
                    (
 
                        SELECT newIndex, o_id FROM (SELECT @n := IF(@n = '2' - 1,@n + 2,@n + 1) AS newIndex, o_id
 
                        FROM objects,
 
                        (SELECT @n := -1) variable
 
                        WHERE o_id != '2142' AND o_parentId = '343' AND o_type IN ('object','variant','folder')
 
                            ORDER BY o_index, o_id='2142'
 
                        ) tmp
 
                    ) order_table
 
                    SET o.o_index = order_table.newIndex
 
                    WHERE o.o_id=order_table.o_id

So, as I understood, the problem is in using user-defined variable.
I found a bug https://jira.mariadb.org/browse/MXS-4269, but there are no comments and it is closed. How can I fix my problem?
What interesting, I had this error only on one of three replicas.
On one of them version 10.5.13, and on the other version 10.5.17. Could this be the reason?
Generated at Thu Feb 08 04:28:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.