mysqlbinlog (v8) emits character_set_client=255 when db mods done through JDBC

Question

The short:

I installed MySQL 8.0.27 on a newish MacBook running OSX 11.6 (BigSur).

I use binary logging and mysqlbinlog to generate updates for older mysql 5.7 installs on separate machines.

The problem is that the mysqlbinlog-generated SQL for updates made through JDBC includes the statement SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=83/!/; which the older mysqls do not handle.

Following advice from http://minsql.com/mysql8/MySQL-8.0-to-5.7-backward-replication/, I set up the mysql8 such that mods made manually using the mysql client do not generate that line, instead setting the character_set_client=33 (utf8).

But when mods are made by the application using JDBC (mysql-connector/J v 8.0.24) that offending statement returns.

The JDBC connection URL includes the characterEncoding=UTF-8 option.

My question is: Is there some way I can get mysqlbinlog to not set the character_set_client=255 when mods are made through JDBC through some combination of mysql setup, jdbc connection configuration, and/or mysqlbinlog options?

More:

Yes, I know down-version replication (which, technically, this is not) is not officially supported. This system does non-real-time database synchronization between mostly offline clients.

The my.cnf is:

[mysqld]
port=63306
server-id=0
log-bin=lolo-bin.log
log-bin-index=bin-log.index
max_binlog_size=100M
binlog_format=mixed
binlog-do-db=database
character_set_server=utf8
collation_server=utf8_bin
init-connect='SET NAMES utf8 COLLATE utf8_bin'

[client]
# doesn't work with mysqlbinlog
# default-character-set=utf8

JDBC connection URL is: jdbc:mysql://localhost:63306/database?characterEncoding=utf8

I have added the --set-charset=UTF8 mysqlbinlog option to no avail.

Partial mysqlbinlog SQL output (with set character_set_client at end):

# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES UTF8 */;
DELIMITER /*!*/;
# at 4
#211101 10:04:33 server id 0  end_log_pos 125 CRC32 0xdd1b24c3  Start: binlog v 4, server v 8.0.27 created 211101 10:04:33 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IR6AYQ8AAAAAeQAAAH0AAAABAAQAOC4wLjI3AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAhHoBhEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBwyQb3Q==
'/*!*/;
# at 125
#211101 10:04:33 server id 0  end_log_pos 156 CRC32 0x79526201  Previous-GTIDs
# [empty]
# at 156
#211101 10:05:08 server id 0  end_log_pos 235 CRC32 0xcbe86045  Anonymous_GTID  last_committed=0    sequence_number=1   rbr_only=no original_committed_timestamp=1635786308071091   immediate_commit_timestamp=1635786308071091 transaction_length=674
# original_commit_timestamp=1635786308071091 (2021-11-01 10:05:08.071091 PDT)
# immediate_commit_timestamp=1635786308071091 (2021-11-01 10:05:08.071091 PDT)
/*!80001 SET @@session.original_commit_timestamp=1635786308071091*//*!*/;
/*!80014 SET @@session.original_server_version=80027*//*!*/;
/*!80014 SET @@session.immediate_server_version=80027*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 235
#211101 10:05:08 server id 0  end_log_pos 330 CRC32 0x38ea85f7  Query   thread_id=7818  exec_time=0 error_code=0
SET TIMESTAMP=1635786308/*!*/;
SET @@session.pseudo_thread_id=7818/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 330
#211101 10:05:08 server id 0  end_log_pos 799 CRC32 0xfd49bf12  Query   thread_id=7818  exec_time=0 error_code=0

thanks, Linus

Answer 1

When upgrading data from 5.7 to 8.0, a non-trivial script must be run the handle the differences in this major upgrade.

Review the steps you used.