Unless the connectors support some form of transaction replay, MaxScale alone cannot do this: if the MaxScale where the transaction is active goes down and the client connects to the second MaxScale, it needs to somehow indicate which session it was on the old MaxScale instance for the new MaxScale instace to know which transaction to replay. Without connector support, this cannot be done.

An alternative way to deal with these sort of situations would be to have graceful shutdowns of MaxScale nodes. This would allow open connections to be migrated to a replacement node once they're done with their active transactions. This wouldn't save transactions that are lost due to unexpected outages but the use-case for "needing to restart" would be served quite well with this.

A mechanism similar to what is described in MDEV-15935 would allow MaxScale to signal that it is about to go down and new connections would be able to redirect themselves to a different MaxScale instance. This could also work for ongoing sessions and even open transactions but that would require significant cooperation from the connectors. A minimal implementation would at least allow transparent connection migrations from one node to another without it affecting applications. This would be sort of how the Drain state for servers in MaxScale works but for MaxScale itself.

A small step towards implementing something for this would be for MaxScale to generate fake "session state change" by injecting a custom variable (e.g. the redirect_url from MDEV-15935) into the OK packet that is sent after authentication has been accepted. As connectors already expose this information (at least the C connector does), it could be used by the client applications to manually redirect to a different server. This would allow MaxScale to implement it and let the connectors catch up with their implementations when they can.

Something as simple as drain=<URL> as a listener parameter would allow this to be done on the listener level and could be wrapped into a maxctrl drain maxscale <URL> command to do it for all listeners. The only thing that would change in MaxScale would be that it would start signaling new (and possibly existing) connections with the extra information stating that they should avoid this MaxScale and redirect to the given URL. This would start out as a manual process but combining with something like the configuration synchronization (i.e. config_sync_cluster), it could be automated to have all but the current MaxScale be included in the URL.

A pre-requisite for this is that connectors support MDEV-15935. Currently there apparently is no activity on that front.

However, cooperative transaction replay would be very complex to arrange, as it would mean that the transaction recording made by one MaxScale would have to be stored in a manner that allows another MaxScale to later replay it. Furthermore, when a connection is, invisibly from the application, moved (when MDEV-15935 is implemented) to another MaxScale, the connection identity as seen by the first MaxScale would have to be transferred to the other MaxScale, so that it then would know which transaction to replay.

Recently most MaxScale leaks have been caused by MaxScale being mis-configured. In such cases, implementing very complex functionality for being able to move a transaction from one MaxScale to another would not bring any actual benefit. MXS-4161 will attempt to address that, i.e. detect whether the resource consumption implied by the configuration is in conflict with the available resources.

Moving the fix-version to 23.08 for now.

The MariaDB JDBC driver already has a built-in transaction replay feature. This is one way of solving the problem and could end up being a simpler solution than anything that is built into MaxScale.