Upgrading/Replicating between MySQL 5.x and MySQL 8.x
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 Tungsten operations.
It is perfectly normal, and supported, to replicate between different versions, and even different flavours of MySQL.
Between versions 5.x and 8.x of MySQL, a number of changes were made to the sql-mode - 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-mode 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 replicate between these versions and avoid this particular error, you will need to 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
If these changes were made as part of an upgrade, then once all nodes have been upgraded, simply reverse the process by removing the syntax from the configuration
and re-running tpm update.
The mapcharset MUST be removed when replicating between the same MySQL versions
For more information on the filters mentioned, see "The mapcharset Filter" and "The dropsqlmode Filter"