Good work @desmondchew !
Now we have something to work with. Licenses seem fine. Done.
The observed Latency is not a performance issue, but a side effect of a functionality/implementation issue. As I suggested earlier Replicate thinks it can clear an update error by simply reconnecting and retrying.
However, where several 'sqlState: HY000' situation may recover, the specific NativeError: 1267 will never clear. And the task is in a never ending loop (personally I see such repeats as a longstanding implementation deficiency. I feel they should only try to reconnect for errors know to possibly clear like say Db down, deadlock, network not reachable. I've seen too many of these futile retry attempts.
Anyway, you have found the core error yourself:
1267 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.21]Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' [1022502] (ar_odbc_stmt.c:3974)]
I'd say you have a legit support case here. Submit the log you created along with table definition on source and target for 'DWDM'.'DB_TABLE_A' and if you can grab a create statement for replicate internal net changes table `DB_DEV1`.`attrep_changes61C12A0BEBFDC43C`. Just 'kill' a tasks such that it has no change to clean it up or enable trace logging for TARGET_APPLY and catch the create there.
If you want to work this yourself some more, you need to figure out which column triggers the error, or maybe all string columns cause this because the source and target databases are too different - butthat's unlikely because the net changes table and target table in the update are on the same db.
Ask yourself how(which) columns do not have a default collation sequence or why.
You may want to manually try to execute the update statement in studio in the hope the error handling gives further details (which columns)
I would also recommend to try to run in transactional apply mode as a workaround and as a knowledge point in further discovery. I suspect it will work in non-batch mode but it may not work fast enough to be workable long term.
good luck!
Hein.
