Replication from MariaDB to MySQL fails if emojis are present

[c-schmitz]

Hello,

I have two databases.
Source: MariaDB 10.3
Destination: MySQL 8.0.34
Both source and destination databases and all tables and all fields inside the tables have utf8mb4_unicode_ci collations.

However, on syncing a table with users that has an emoji in the user name the table cannot be synced. For some unknown reason a utf8mb3 encoding seems to be used

Here is the verbose log:

2023-09-21 22:25:34,768 INFO ReplicaDB:63 Running ReplicaDB version: 0.15.1
2023-09-21 22:25:34,770 INFO ReplicaDB:66 Setting verbose mode DEBUG
2023-09-21 22:25:34,770 DEBUG ReplicaDB:67 ToolOptions{
sourceConnect='jdbc:mariadb://limesurvey-3.limesurvey.org:3306/c1account',
sourceUser='replicadb',
sourcePassword='',
sourceTable='jos_users',
sourceColumns='null',
sourceWhere='null',
sourceQuery='null',
sinkConnect='jdbc:mysql://localhost:3306/c1account',
sinkUser='root',
sinkPassword='',
sinkTable='null',
sinkStagingTable='null',
sinkStagingSchema='c1account',
sinkStagingTableAlias='null',
sinkColumns='null',
sinkDisableEscape=false,
sinkDisableIndex=false,
sinkDisableTruncate=false,
sinkAnalyze=false,
jobs=5,
BandwidthThrottling=0,
quotedIdentifiers=false,
fetchSize=1000,
help=false,
version=false,
verbose=DEBUG,
optionsFile='c1account_jos_users_to_dhw.replitask',
mode='complete-atomic',
sentryDsn='null',
sourceConnectionParams={},
sinkConnectionParams={},
sourceFileFormat='null',
sinkFileformat='null'}
2023-09-21 22:25:34,892 DEBUG MySQLManager:348 Calculating the chunks size with this sql: SELECT CEIL(count() / 5) chunk_size, count() total_rows FROM jos_users
2023-09-21 22:25:34,956 DEBUG MySQLManager:353 chunkSize: 77302 totalNumberRows: 386507
2023-09-21 22:25:34,962 INFO MySQLManager:246 Creating staging table with this command: CREATE TABLE c1account.jos_usersrepdb5110 AS (SELECT * FROM jos_users WHERE 1 = 0 )
2023-09-21 22:25:35,242 INFO SqlManager:388 Atomic and asynchronous deletion of all data from the sink table with this command: DELETE FROM jos_users
2023-09-21 22:25:35,245 INFO ReplicaTask:35 Starting TaskId-1
2023-09-21 22:25:35,245 INFO ReplicaTask:35 Starting TaskId-0
2023-09-21 22:25:35,245 INFO ReplicaTask:35 Starting TaskId-2
2023-09-21 22:25:35,246 INFO ReplicaTask:35 Starting TaskId-3
2023-09-21 22:25:35,246 INFO ReplicaTask:35 Starting TaskId-4
2023-09-21 22:25:35,251 INFO SqlManager:128 TaskId-1: Executing SQL statement: SELECT * FROM jos_users LIMIT ? OFFSET ?
2023-09-21 22:25:35,253 INFO SqlManager:128 TaskId-3: Executing SQL statement: SELECT * FROM jos_users LIMIT ? OFFSET ?
[... shortened log here...]
2023-09-21 22:25:35,259 WARN ConnManager:188 Options source-columns and sink-columns are null, getting from Source ResultSetMetaData: id,name,username,email,password,block,sendEmail,registerDate,lastvisitDate,activation,params,lastResetTime,resetCount,otpKey,otep,requireReset,transfered_to_zoho_crm,authProvider
2023-09-21 22:25:35,260 INFO MySQLManager:225 Loading data with this command: LOAD DATA LOCAL INFILE 'dummy' INTO TABLE c1account.jos_usersrepdb5110 CHARACTER SET UTF8 FIELDS TERMINATED BY X'1F' (id, name, username, email, password, block, sendEmail, registerDate, lastvisitDate, activation, params, lastResetTime, resetCount, otpKey, otep, requireReset, transfered_to_zoho_crm, authProvider)
2023-09-21 22:25:35,373 INFO MySQLManager:225 Loading data with this command: LOAD DATA LOCAL INFILE 'dummy' INTO TABLE c1account.jos_usersrepdb5110 CHARACTER SET UTF8 FIELDS TERMINATED BY X'1F' (id, name, username, email, password, block, sendEmail, registerDate, lastvisitDate, activation, params, lastResetTime, resetCount, otpKey, otep, requireReset, transfered_to_zoho_crm, authProvider)
2023-09-21 22:25:35,402 INFO MySQLManager:225 Loading data with this command: LOAD DATA LOCAL INFILE 'dummy' INTO TABLE c1account.jos_usersrepdb5110 CHARACTER SET UTF8 FIELDS TERMINATED BY X'1F' (id, name, username, email, password, block, sendEmail, registerDate, lastvisitDate, activation, params, lastResetTime, resetCount, otpKey, otep, requireReset, transfered_to_zoho_crm, authProvider)
2023-09-21 22:25:35,449 INFO MySQLManager:225 Loading data with this command: LOAD DATA LOCAL INFILE 'dummy' INTO TABLE c1account.jos_usersrepdb5110 CHARACTER SET UTF8 FIELDS TERMINATED BY X'1F' (id, name, username, email, password, block, sendEmail, registerDate, lastvisitDate, activation, params, lastResetTime, resetCount, otpKey, otep, requireReset, transfered_to_zoho_crm, authProvider)
2023-09-21 22:25:35,532 ERROR ReplicaTask:69 ERROR in TaskId-2 inserting data to sink table: [(conn=136) Invalid utf8mb3 character string: '', Invalid utf8mb3 character string: '', Invalid utf8mb3 character string: '']
2023-09-21 22:25:35,575 INFO MySQLManager:225 Loading data with this command: LOAD DATA LOCAL INFILE 'dummy' INTO TABLE c1account.jos_usersrepdb5110 CHARACTER SET UTF8 FIELDS TERMINATED BY X'1F' (id, name, username, email, password, block, sendEmail, registerDate, lastvisitDate, activation, params, lastResetTime, resetCount, otpKey, otep, requireReset, transfered_to_zoho_crm, authProvider)
2023-09-21 22:25:38,199 ERROR ReplicaTask:69 ERROR in TaskId-4 inserting data to sink table: [(conn=138) Invalid utf8mb3 character string: 'PeriodT', Invalid utf8mb3 character string: 'PeriodT', Invalid utf8mb3 character string: 'PeriodT']
2023-09-21 22:25:40,905 ERROR ReplicaTask:69 ERROR in TaskId-3 inserting data to sink table: [(conn=137) Invalid utf8mb3 character string: 'Guido', Invalid utf8mb3 character string: 'Guido', Invalid utf8mb3 character string: 'Guido']
2023-09-21 22:25:41,483 ERROR ReplicaTask:69 ERROR in TaskId-1 inserting data to sink table: [(conn=135) Invalid utf8mb3 character string: 'Winter ', Invalid utf8mb3 character string: 'Winter ', Invalid utf8mb3 character string: 'Christiane Winter ']
2023-09-21 22:25:41,825 INFO ReplicaTask:67 A total of 77302 rows processed by task 0
2023-09-21 22:25:41,826 ERROR ReplicaDB:134 Got exception running ReplicaDB:
java.util.concurrent.ExecutionException: java.sql.SQLTransientConnectionException: (conn=135) Invalid utf8mb3 character string: 'Christiane Winter '
at java.util.concurrent.FutureTask.report(FutureTask.java:122) ~[?:?]
at java.util.concurrent.FutureTask.get(FutureTask.java:191) ~[?:?]
at org.replicadb.ReplicaDB.processReplica(ReplicaDB.java:115) [ReplicaDB-0.15.1.jar:0.15.1]
at org.replicadb.ReplicaDB.main(ReplicaDB.java:47) [ReplicaDB-0.15.1.jar:0.15.1]
Caused by: java.sql.SQLTransientConnectionException: (conn=135) Invalid utf8mb3 character string: 'Christiane Winter '
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:155) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:363) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.MariaDbStatement.executeUpdate(MariaDbStatement.java:627) ~[mariadb-java-client-2.7.3.jar:?]
at org.replicadb.manager.MySQLManager.copyData(MySQLManager.java:172) ~[ReplicaDB-0.15.1.jar:0.15.1]
at org.replicadb.manager.MySQLManager.insertDataToTable(MySQLManager.java:136) ~[ReplicaDB-0.15.1.jar:0.15.1]
at org.replicadb.ReplicaTask.call(ReplicaTask.java:65) ~[ReplicaDB-0.15.1.jar:0.15.1]
at org.replicadb.ReplicaTask.call(ReplicaTask.java:15) ~[ReplicaDB-0.15.1.jar:0.15.1]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Invalid utf8mb3 character string: 'Christiane Winter '
at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:262) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.MariaDbStatement.executeUpdate(MariaDbStatement.java:627) ~[mariadb-java-client-2.7.3.jar:?]
at org.replicadb.manager.MySQLManager.copyData(MySQLManager.java:172) ~[ReplicaDB-0.15.1.jar:0.15.1]
at org.replicadb.manager.MySQLManager.insertDataToTable(MySQLManager.java:136) ~[ReplicaDB-0.15.1.jar:0.15.1]
at org.replicadb.ReplicaTask.call(ReplicaTask.java:65) ~[ReplicaDB-0.15.1.jar:0.15.1]
at org.replicadb.ReplicaTask.call(ReplicaTask.java:15) ~[ReplicaDB-0.15.1.jar:0.15.1]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
Caused by: java.sql.SQLException: Invalid utf8mb3 character string: 'Winter '
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1694) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1556) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1519) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readLocalInfilePacket(AbstractQueryProtocol.java:1798) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1562) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1519) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:256) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357) ~[mariadb-java-client-2.7.3.jar:?]
at org.mariadb.jdbc.MariaDbStatement.executeUpdate(MariaDbStatement.java:627) ~[mariadb-java-client-2.7.3.jar:?]
at org.replicadb.manager.MySQLManager.copyData(MySQLManager.java:172) ~[ReplicaDB-0.15.1.jar:0.15.1]
at org.replicadb.manager.MySQLManager.insertDataToTable(MySQLManager.java:136) ~[ReplicaDB-0.15.1.jar:0.15.1]
at org.replicadb.ReplicaTask.call(ReplicaTask.java:65) ~[ReplicaDB-0.15.1.jar:0.15.1]
at org.replicadb.ReplicaTask.call(ReplicaTask.java:15) ~[ReplicaDB-0.15.1.jar:0.15.1]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:829) ~[?:?]
2023-09-21 22:25:41,840 INFO SqlManager:462 Dropping staging table with this command: DROP TABLE c1account.jos_usersrepdb5110
2023-09-21 22:25:41,975 INFO ReplicaDB:54 Total process time: 7212ms

I checked that there is a user named 'Winter 🚃" - apparently the emoji is creating the problem
What I don't understand is why there is utf8mb3 used, instead of utf8mb4.

My guess is that the line

"LOAD DATA LOCAL INFILE 'dummy' INTO TABLE c1account.jos_usersrepdb5110 CHARACTER SET UTF8 FIELDS [...]"
should say "UTF8MB4' instead of 'UTF8'!?

Could that be the reason? If yes, how can I configure this line?

Any help is appreciated.

[c-schmitz]

I checked the source code and I think it is a bug.
In the code the character set for the MySQL outfile/infile seems to be hard coded to UTF-8 and there does not seem to be any way to change the encoding by parameters. The problem is that 'UTF-8' means just 'UTF8mb3', not UTF8mb4.

See

private void copyData (String loadDataSql, StringBuilder row, MariaDbStatement mariadbStatement, JdbcPreparedStatement mysqlStatement) throws IOException, SQLException {
  if (mysqlStatement != null) {
     mysqlStatement.setLocalInfileInputStream(new ReaderInputStream(CharSource.wrap(row).openStream(), StandardCharsets.UTF_8));
     mysqlStatement.executeUpdate(loadDataSql);
  } else {
     assert mariadbStatement != null;
     mariadbStatement.setLocalInfileInputStream(new ReaderInputStream(CharSource.wrap(row).openStream(), StandardCharsets.UTF_8));
     mariadbStatement.executeUpdate(loadDataSql);
  }

}

There are also some other locations (dummy table) where a hardcoded character set is used.