As is common when providers release new versions of their software, there are often small subtle changes that often go unnoticed, and then there are some changes which can completely break your application. The latter is quite common when moving between major versions of MySQL.

In MySQL 8.x this is no different, and for the most part these changes won’t impact your Tungsten Cluster or your Replication.

To allow, and enable, you to upgrade your underlying database whilst maintaining your application's availability, having your topology running with different versions of MySQL on each node is perfectly normal and supported.

Between versions 5.x and 8.x of MySQL, a number of changes were made to the SQL_MODES - some that existed in 5.7 were removed, and some new ones added in MySQL 8, additionally there are some collation (or Character Set) changes. Within your applications, you may not even notice this, but this can cause an issue with replication. Part of the workflow when Tungsten Replicator applies is to ensure the same SQL_MODES and collations that were in play when the original transaction was written in the source, are enabled when we write into the target, by extracting this information as part of the metadata. If we try to enable a SQL_MODE or enable a collation that doesn’t exist, then your replicators will go into an error state, with a message similar to the following:

java.sql.SQLSyntaxErrorException: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

or

pendingError : Event application failed: seqno=2915 fragno=0 message=Failed to apply session variables

or

Caused by: java.sql.SQLException: Unknown collation: ‘255’

The SQL_MODE exceptions may happen when replicating between two different versions in either direction, i.e. from 5.x to 8.x or vice versa. The collation mapping error is only an issue when replicating down versions, i.e. from 8.x to 5.x

So that you can seamlessly upgrade your underlying MySQL databases, and avoid this particular error, you will need to temporarily enable up to two additional filters and leave running whilst you have a mix of MySQL versions replicating to each other.

When replicating between lower to higher versions (MySQL 5.x to MySQL 8.x), you need to enable the dropsqlmode filter using the following syntax and then by running tpm update:

svc-applier-filters=dropsqlmode

When replicating between higher to lower versions (MySQL 8.x to MySQL 5.x), you need to enable both the dropsqlmode filter and the mapcharset filter, additionally you will need to configure the dropsqlmode filter differently, the following syntax can be used:

svc-applier-filters=dropsqlmode,mapcharset
property=replicator.filter.dropsqlmode.modes=TIME_TRUNCATE_FRACTIONAL

Once all nodes have been upgraded, simply reverse the process by removing the syntax from the configuration and re-running tpm update.

For more information on the filters mentioned, see Section 11.4.17, “dropsqlmode.js Filter” and Section 11.4.27, “mapcharset Filter”