[MXS-1816] Prepared statements cause problems when done on all servers Created: 2018-04-20  Updated: 2022-03-14  Resolved: 2022-03-14

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

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

Issue Links:
Problem/Incident
causes MXS-3036 'set autocommit=0' block ddl Closed

 Description   

In some cases where a prepared statement is routed to all servers, one or more of the participating servers can fail to prepare it. The following are examples of when this can happen:

  • When a prepared statement targets a temporary table, which are created only on the master, the slaves fail to execute the session command. This causes errors to be logged as well as prevents the slaves from being used after that point.
  • SET autocommit=0 will take locks on slaves that should not be taken (slaves should never have open non-readonly transactions).
  • Preparing INSERT statements on Galera nodes can lead to deadlock errors, especially when SET autocommit=0 is used.

At this moment, there is no simple solution to this as all prepared statements are prepared on all servers. The session commands would need to be partitioned by their type to allow execution only on one type of server.



 Comments   
Comment by markus makela [ 2022-03-08 ]

The problem with SET autocommit=0 is not as severe as I originally thought it was: the empty transaction started by the statement will not have any locks and it is only a problem if something routes a read to a server which should normally not receive any.

Technically the SET autocommit=0 command could be skipped on the slave servers but that would require the history to be replayed when one of the servers ended up being promoted as the current master server. This is probably the simplest way to approach the issue as it postpones the work until the master server must be replaced which is usually a rare event. The problem with this approach is that in version 6, the history is now stored outside of the router and is not directly accessible to it.

Comment by markus makela [ 2022-03-14 ]

I'll close this as Won't Fix as the autocommit part is only caused by the routing hints and can be easily avoided by not using them and instead using a read-only transaction or enabling optimistic_trx for readwritesplit.

As for the Galera problem, we've never seen real reports from this from actual users so one can assume that the problem is exceedingly rare. In newer versions MaxScale will also hide the failures from the end user by simply discarding connections that fail instead of closing the whole session. This alone makes the problem almost trivial and re-connecting to a server will also fix it (done automatically if needed).

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