debezium / debezium/dbz

MySQL Source Connector: option to serialize streamed JSON values in the same format as snapshotted ones

Open
#2,376 1 comment 0 reactions 0 assignees View on GitHub
component/mariadb-connector component/mysql-connector type/enhancement
Dominant language
HTML
Stars
6
Forks
8
Avg merge
2d 19h
Merged PRs (30d)
1

Description

## Feature request or enhancement

_For feature requests or enhancements, please provide the following information:_

**Which use case/requirement will be addressed by the proposed feature?**

The MySQL connector emits a different string for the same `JSON` value depending on how it was captured.
A snapshot delivers the value as the server returns it over JDBC; streaming parses the binary JSON from the binlog with the binlog client's `JsonStringFormatter` (`JsonBinary.parseAsString(byte[])`, called from `BinlogValueConverters#convertJson`), which formats it differently:

| Column value | Snapshot (JDBC) | Streaming (binlog) |
|---|---|---|
| `{"a": 2}` | `{"a": 2}` | `{"a":2}` |
| `[1, 2]` | `[1, 2]` | `[1,2]` |
| `CAST('2015-01-15 23:24:25' AS DATETIME)` | `"2015-01-15 23:24:25.000000"` | `"2015-01-15 23:24:25"` |
| `CAST('23:24:25.12' AS TIME(3))` | `"23:24:25.120000"` | `"23:24:25.12"` |
| `CAST(x'cafe' AS JSON)` | `"base64:type15:yv4="` | `"yv4="` |

The values are equivalent as JSON but not byte-identical, so any consumer that compares the raw strings — checksum reconciliation, deduplication, audit and diff tooling — sees a spurious difference for every row that is captured both ways. We hit this on a MySQL → Debezium → Iceberg pipeline, where a checksum comparison flagged every JSON column of a row that had been re-read after the initial snapshot. Working around it downstream means re-parsing and re-serializing every JSON value.

The divergence is long-standing and already visible inside the project: `debezium-connector-binlog/src/test/resources/ddl/json_test.sql` carries separate `expectedJdbcStr` and `expectedBinlogStr` columns that differ in exactly these ways. Reproduced on `main` (3.7.0-SNAPSHOT) against MySQL 8.0; 3.6.1.Final contains the identical call.

MariaDB stores `JSON` as `LONGTEXT` and replicates it as text, which the binlog client passes through unchanged, so it is not affected.

To reproduce with the [tutorial](https://github.com/debezium/debezium-examples/tree/main/tutorial) deployment:

```sql
ALTER TABLE inventory.customers ADD COLUMN meta JSON;
UPDATE inventory.customers SET meta = '{"a": 2}' WHERE id = 1001; -- before the snapshot
```

Register the connector and read `dbserver1.inventory.customers`: the snapshot record for `id = 1001` has `{"a": 2}`. Then, while streaming:

```sql
UPDATE inventory.customers SET meta = '{"a": 2}' WHERE id = 1002;
```

The streamed record for `id = 1002` has `{"a":2}`.

---

**Implementation ideas (optional)**

`JsonBinary.parse(byte[], JsonFormatter)` already accepts a custom formatter, so a small `JsonStringFormatter` subclass can reproduce the server's output while inheriting all escaping and value-conversion rules.

Since consumers may depend on the current output, the behavior would be gated behind a new option on the shared binlog connector config:

```
json.string.formatting.mode = legacy (default) | database
```

- `legacy` — current output, unchanged
- `database` — match the server's textual format, so streamed values are byte-identical to snapshotted ones for the differences above

Apache Flink CDC, which vendored this code from Debezium, addressed the whitespace part the same way (custom formatter plus `use.legacy.json.format`, defaulting to legacy): apache/flink-cdc#3658 / FLINK-36578.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.