Handling SQL_MODE & Collation Changes in MySQL 8

The Problem

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 - I well remember the pain during my days of being a DBA, when moving an application from MySQL 5.0 to MySQL 5.5!

In MySQL 8.x this is no different, and for the most part these changes won’t impact your Tungsten Cluster or your Replication. Our software is designed well to cope with this, and if it’s not, then we are usually pretty quick at getting a workaround or fix in place! 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.

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

The SQL_MODE Fix

So that you can seamlessly upgrade your underlying MySQL databases, and avoid this particular error, we have written a small filter that you should apply to your configuration and leave running whilst you have a mix of MySQL versions replicating to each other. This filter is called the dropsqlmode filter, and it works by ensuring that any deprecated sql_modes detected in the THL are excluded before the THL is applied to the target.

The Collation Fix

To handle the mapping of collations when you have a database running v8.x replicating to a target database of 5.x, you will need to add the mapcharset filter to your configuration, however it is important to note that this MUST only be used on the appliers when they are a lower version. As soon as the target has been upgraded, this filter MUST be removed.

Applying the Filters

The dropsqlmode filter should be applied to all nodes prior to any upgrade taking place. Simply add the following syntax into the tungsten.ini on all your nodes when the replication flow will be from 5.x to 8.x:

svc-applier-filters=dropsqlmode

…and then, after placing your cluster into MAINTENANCE mode, issue tpm update.

When the reverse replication is in effect (v8.0 to v5.6) then you will also need to add the mapcharset filter and amend the options for the dropsqlmode filter, as follows:

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

You can now upgrade your databases without fear of the replicators going offline. Once all nodes have been upgraded, simply reverse the process by removing the syntax from the configuration and re-running tpm update.

Don’t worry though!! If you forget to add this before upgrading and your replicator is already in an error state, simply add the syntax to the configuration, run the update and the replicator will recover itself!

For more information on upgrading your cluster, see https://docs.continuent.com/tungsten-clustering-7.0/operations-maintenance.html and for specific information on the filter mentioned in this article, see https://docs.continuent.com/tungsten-clustering-7.0/filters-reference-dropsqlmode.html and https://docs.continuent.com/tungsten-replicator-7.0/filters-reference-mapcharset.html