[MXS-3036] 'set autocommit=0' block ddl Created: 2020-06-12  Updated: 2022-03-18  Resolved: 2022-03-14

Status: Closed
Project: MariaDB MaxScale
Component/s: readwritesplit
Affects Version/s: 2.2.21
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: dapeng huang Assignee: markus makela
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MXS-1816 Prepared statements cause problems wh... Closed

 Description   

Example:

Thread 1 Thread2
SET AUTOCOMMIT = 0 ...
SELECT * FROM t1 – maxscale route to slave ...
COMMIT ...
... DROP TABLE t1

'set autocommit=0' will route to all node, but commit only route to master, so in slave the transaction will not commit, hold mdl lock on t1 util session close, then when slave apply binlog 'DROP TABLE t1' will be block



 Comments   
Comment by markus makela [ 2020-06-12 ]

I think readwritesplit should lock into a master-only mode when autocommit is disabled. It should probably also route the auto-commit to the master.

Comment by markus makela [ 2020-07-20 ]

This is a more complex issue: the SET autocommit=0 needs to be executed on the master server and re-executed every time a reconnection or a master switch takes place. This means that a separate history for the master might have to be kept in order to prevent these sorts of problems.

Comment by markus makela [ 2022-03-08 ]

The given example can only happen if a routing hint is used to override the routing logic in readwritesplit. Normally without it this won't cause problems as the transactions started on the other servers are empty and do not cause conflicts. It's still not ideal that this can happen but I would say it's rare to intentionally break the transaction logic just to offload traffic.

Comment by markus makela [ 2022-03-14 ]

I'll go ahead and close this as Won't Fix and document it as a known limitation in addition to the already documented fact that you can end up breaking things if you use routing hints in the wrong way.

Generated at Thu Feb 08 04:18:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.