Oracle-MySQL 8 job: Data type CHAR(255) / CHARACTER(255) / NCHAR(255) Sync update statement failed

[asiroliu]

Description

Oracle-MySQL 8 job: Data type CHAR(255) / CHARACTER(255) / NCHAR(255) Sync update statement failed

Steps to reproduce the issue

1. create dtle job

{
  "job_id": "CHAR_255_COLUMNS",
  "is_password_encrypted": false,
  "task_step_name": "all",
  "failover": true,
  "retry": 2,
  "src_task": {
    "task_name": "src",
    "node_id": "35a4ebcf-1ed0-94a8-3a77-823802fc13fe",
    "binlog_relay": false,
    "repl_chan_buffer_size": 120,
    "group_max_size": 1,
    "group_timeout": 100,
    "oracle_src_task_config": {
      "scn": 0
    },
    "connection_config": {
      "database_type": "Oracle",
      "host": "172.100.9.31",
      "port": 1521,
      "user": "roma_logminer",
      "password": "oracle",
      "service_name": "XE"
    },
    "replicate_do_db": [
      {
        "table_schema": "ACTION_DB",
        "tables": [
          {
            "table_name": "CHAR_255_COLUMNS"
          }
        ]
      }
    ]
  },
  "dest_task": {
    "task_name": "dest",
    "node_id": "2205ffbc-6b16-7208-99c0-eb15e6d24370",
    "mysql_dest_task_config": {},
    "connection_config": {
      "database_type": "MySQL",
      "host": "172.100.9.2",
      "port": 3306,
      "user": "test_dest",
      "password": "test_dest"
    }
  }
}

2. load data on src Oracle

CREATE TABLE ACTION_DB.CHAR_255_COLUMNS(col1 INT, col2 CHAR(255));
INSERT INTO ACTION_DB.CHAR_255_COLUMNS VALUES (1, 'abcdefghijklmnopqrstuvwxyz');
UPDATE ACTION_DB.CHAR_255_COLUMNS SET COL2='ABCDEFGHIJKLMNOPQRSTUVWXYZ' WHERE COL1=1;

3. dest MySQL check data, the update statement not synchronized

mysql> select * from CHAR_255_COLUMNS where COL1=1;
+------+----------------------------+
| COL1 | COL2                       |
+------+----------------------------+
|    1 | abcdefghijklmnopqrstuvwxyz |
+------+----------------------------+
1 row in set (0.00 sec)

4. check dest general log

2022-05-12T06:55:20.442375Z        17 Execute   update `ACTION_DB`.`CHAR_255_COLUMNS` set
`COL1`='1', `COL2`='ABCDEFGHIJKLMNOPQRSTUVWXYZ                                                                                                                                                                                                                                     '
where
((`COL1` = '1') and (`COL2` = 'abcdefghijklmnopqrstuvwxyz                                                                                                                                                                                                                                     ')) limit 1

5. verify where condition, string with space

# MySQL 8
mysql> select * from CHAR_255_COLUMNS where COL2='abcdefghijklmnopqrstuvwxyz    ';
Empty set (0.00 sec)

# MySQL 5
mysql> select * from CHAR_255_COLUMNS where COL2='abcdefghijklmnopqrstuvwxyz   ';
+------+----------------------------+
| COL1 | COL2                       |
+------+----------------------------+
|    1 | abcdefghijklmnopqrstuvwxyz |
+------+----------------------------+
1 rows in set (0.00 sec)

Output of ./dtle version:**

9.9.9.9-master-352256e