Enhance MaxScale failover replica selection to consider MariaDB major version compatibility during automatic promotion.

In a situation where the GTIDs are equal, the monitor should prefer the oldest version of MariaDB.

In maxscale based failure handling of replicas no version checks are made when choosing the most up to date replica to promote to master based on multiple checks.

However, maxscale does not handle the scenario, which potentially could lead to an outage, of multiple replicas stopped and at exactly the same “latest” position being eligible for promotion.

It is not recommended to replicate from a higher major version of MariaDB to a lower major version. e.g. 11.8 → 10.6, or 12.2 → 10.6 as this is expected to potentially break, more so as the major version differences increase.

Even if upgrades are infrequent if we are upgrading a cluster of servers there is most likely a short period of time where we will be running a mixed major version setup. Traditionally we upgrade leaf nodes first (as replication allows this) and finally the master, so there is a moment when if the master fails different major versions of candidate masters may be selectable.

Ideally where this is possible, an instance of the lower (major) version would be chosen and other replicas relocated behind that lower version as that's guaranteed to be replication safe.

Policy then might dictate that the organisation prefers the most up to date replica to minimise or avoid data loss even if that means that the lower versions may not correctly replicate behind it if relocated. It could be that the preference is to recover as many replicas as possible.

Orchestrator has had similar logic in place for a number of years showing people have been aware of this problem for quite some time.