[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: |
|
||||||||
| 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:
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). |