apache / apache/doris-flink-connector

[Bug] MySQL blob type should not be converted to base64 encoded string in Doris

Open
#231 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
385
Forks
279
Avg merge
2d 2h
Merged PRs (30d)
6

Description

### Search before asking

- [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues.

### Version

1.4.0

### What's Wrong?

After syncing mysql data to doris, the blob type is converted to doris text type because doris doesn't support blob type. But the data should not be converted to base64 encoded.

### What You Expected?

The data should be hex encoded in doris text type or a config should be added to specify the encoder of blob/binary data from source database(e.g. MySQL).

### How to Reproduce?

suppose there is a table tt in MySQL which contains blob data type:

```
CREATE TABLE `tt` (
`id` int NOT NULL AUTO_INCREMENT primary key,
`name` varchar(20),
`bb` blob,
`tt` text);
```

start flink and run the following flink job, you need the below two jars to run the job:

flink-doris-connector-1.17-1.4.0.jar
flink-sql-connector-mysql-cdc-2.4.2.jar

```
bin/flink run -d \
-Dexecution.checkpointing.interval=10s \
-Dparallelism.default=1 \
-c org.apache.doris.flink.tools.cdc.CdcTools \
lib/flink-doris-connector-1.17-1.4.0.jar \
mysql-sync-database \
--database testdb \
--job-name flink_sync_mysql_to_doris_testdb \
--mysql-conf hostname=xxxx \
--mysql-conf port=3360 \
--mysql-conf username=flink \
--mysql-conf password=flink \
--mysql-conf database-name=testdb \
--including-tables ".*" \
--sink-conf fenodes=xxxx:8030 \
--sink-conf username=flink \
--sink-conf password=flink \
--sink-conf jdbc-url=jdbc:mysql://xxxx:9030 \
--sink-conf sink.label-prefix=labeltest1898 \
--table-conf replication_num=1
```

insert a record into MySQL:
```
mysql> insert into tt values(1, 'aaa', 'abcd中国', 'abcd中国');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
+----+------+------------------------+------------+
| id | name | bb | tt |
+----+------+------------------------+------------+
| 1 | aaa | 0x61626364E4B8ADE59BBD | abcd中国 |
+----+------+------------------------+------------+
1 row in set (0.00 sec)

```

The data in doris:
```
mysql> select * from tt;
+------+------+------------------+------------+
| id | name | bb | tt |
+------+------+------------------+------------+
| 1 | aaa | YWJjZOS4reWbvQ== | abcd中国 |
+------+------+------------------+------------+
1 row in set (0.03 sec)
```

The data "YWJjZOS4reWbvQ==" is base64 encoded.

```
mysql> select id, name, from_base64(bb), tt from tt;
+------+------+-----------------+------------+
| id | name | from_base64(bb) | tt |
+------+------+-----------------+------------+
| 1 | aaa | abcd中国 | abcd中国 |
+------+------+-----------------+------------+
1 row in set (0.03 sec)

```

### Anything Else?

It is strange and I haven't found the problem in the code. I searched "base64" in the code and don't find anything suspicious. Maybe the code is in its dependency?

### Are you willing to submit PR?

- [X] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)

Contributor guide

Open the contributing guide

Research direction

Start with the org.apache.doris.flink.tools.cdc.CdcTools entry point and trace how MySQL blob values are converted while syncing into Doris, including the connector dependency if the conversion is not in this repository. Reproduce the case with the provided table, Flink command, and sample data; done means blob data is hex encoded or the requested encoder configuration is supported without unwanted base64 conversion.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, mysql
Domain
data-engineering, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
39/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.