Challenges with Reverse Replication from MySQL 8 to MySQL 5.7

MySQL & MariaDB
1

Hi Team,

As part of our MySQL 8 upgrade, we are considering reverse replication for critical services due to uncertainties in MySQL 8 for easier rollback. We have referenced the Percona blog on the topic.
Link: Replicating from MySQL 8.0 to MySQL 5.7

Source version: Percona Mysql 8.0.35
Replicated version: Percona Mysql 5.7.39

The reverse replication from MySQL 8(master) to MySQL 5.7(replica) is failing with the following error on the MySQL 5.7 replica when connecting and writing from the MySQL 8.X JDBC connector(recommended driver post-upgrade) on mysql 8 master. The reverse replication works fine with the MySQL 5.X JDBC connector(not recommended one for Myslq 8).

Error:

Last_SQL_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file' on query. Default database: 'logisticsadaptor'. Query: 'BEGIN'

Written bingo for the same:

 /*!\C utf8mb4 *//*!*/;
 SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=192/*!*/;
 BEGIN
 /*!*/;

We have tried various database-side configurations to resolve this issue, including setting init_connect options, but the only effective solution seems to use “SET NAMES utf8” at the application level with the MySQL 8 connector (e.g., stmt.execute("SET NAMES utf8");). However, changing configurations at the application JDBC connector level for multiple remote applications is impractical. It appears that despite our database charset and collation settings, the application JDBC driver overwriting defaults to utf8mb4(session.character_set_client=255,@@session.collation_connection=255) when establishing the connection.

Sample init_connect configuration in the MySQL 8 .cnf file:

[mysqld]
init_connect = "SET character_set_client=utf8, collation_connection=utf8_unicode_ci"
or
init_connect = "SET NAMES utf8"

Additionally, the MySQL documentation advises against using SET NAMES with Connector/J:

Warning: Do not issue the query SET NAMES with Connector/J, as the driver will not detect the character set change and will continue to use the initially configured character set.
Link: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-charsets.html

Is there a recommended and effective database-side solution to address this reverse replication issue without modifying the application as its a basic need for Mysql 8 upgrade rollback approach?

2

Hi Pravata,

Just to clarify, replication from MySQL 8.0 to 5.7 is not officially supported, and problems like this one are expected.
Now, this is indeed a problem of the new collation available in MySQL 8.0, which was not present in 5.7. The server side charset/collation settings will not help as it’s the connector/driver that uses this collation when it detects that it’s connected to a version 8.0.x server. There is one workaround though - ignore the client-server charset handshake completely and stick to whatever is set by default on the server. Option ref:

More on that topic: Upgrade MySQL to 8.0? Yes, but Avoid Disaster!

3

Thanks for the update @przemek .
The suggested variable is likely deprecated in our current MySQL version(8.0.35). As mostly no control from server-side for this, we plan to enforce the necessary changes primarily from the application side.

Sample:

 
   "jdbc:mysql://<host>:<port>/<database>?characterEncoding=utf8&connectionCollation=utf8_unicode_ci";