After upgrading to version 6.5.3, JSON column type appears as base64:type253 encoding

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 升级至6.5.3版本之后,JSON列类型出现base64:type253编码

| username: 饭光小团

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]
After upgrading the TiDB version from 5.3.1 to 6.5.3, we found that some JSON columns in the database would start with base64:type253 content.
We also referred to other posts DM 同步json类型字段出现base64加密(syncer) - #2,来自 Billmay表妹 - TiDB 的问答社区. It was mentioned that it is due to special characters, but after decoding the base64 content ourselves, we found that there were no special characters inside, and the types were very simple.
Request:

  1. Can this feature be disabled?
| username: 饭光小团 | Original post link

I couldn’t find the enable-table-column-encoding parameter in the documentation.

| username: dba-kit | Original post link

It shouldn’t be. DM shouldn’t convert the JSON type by itself. Can you provide an example of a row?

| username: 饭光小团 | Original post link

For example, this one, the data in the picture is after being base64 encoded. Normally, it should be a JSON string like this.

| username: 饭光小团 | Original post link

This should be a DM issue, not a TiDB cluster configuration issue.

| username: Billmay表妹 | Original post link

Have you done any compiling?

| username: 饭光小团 | Original post link

No, they are all based on the official version.

| username: TiDB_C罗 | Original post link

Is this data written by DM? There was no problem writing to the lower version, but there is a problem writing to the upgraded higher version?

| username: TiDB_C罗 | Original post link

Has DM been upgraded?

| username: Billmay表妹 | Original post link

Check the versions of all your components to see if some of them have not been fully upgraded to 6.5.3.

| username: 有猫万事足 | Original post link

It may not necessarily be a DM issue.

MySQL 5.7:

mysql> select CAST(CAST('1234abcd' AS BINARY) AS JSON);
+------------------------------------------+
| CAST(CAST('1234abcd' AS BINARY) AS JSON) |
+------------------------------------------+
| "base64:type253:MTIzNGFiY2Q="            |
+------------------------------------------+
1 row in set (0.01 sec)

In MySQL, this type of conversion results in a string starting with base64:type253:. Check if there is such type conversion for the column type?

| username: 饭光小团 | Original post link

I will do some testing to reproduce the scenario. Then I’ll post it here.

| username: 饭光小团 | Original post link

I suspect it might be caused by the upstream and downstream character sets. I’ll run some tests to check.

| username: redgame | Original post link

This is to improve query performance and will not affect data correctness. I couldn’t find where to turn it off.

| username: Fly-bird | Original post link

Is it resolved?

| username: aytrack | Original post link

Is this data written directly or synchronized via DM? Did you perform any DM synchronization operations after the upgrade?

| username: aytrack | Original post link

TiDB will store binary string JSON as Opaque Json, with the format ase64:typeXX. For more details, see this PR. You can check if you have a similar JSON writing method based on this issue. You can query the specific JSON type using JSON_TYPE. For more information on JSON types, refer to https://dev.mysql.com/doc/refman/8.0/en/json.html#json-comparison.

| username: lazzyfu | Original post link

How was this issue resolved?

After we upgraded to TiDB 7.1.0, all JSON type data synchronized through DM turned into base64:type253:eyJmcGRt… format. This has affected our business.

| username: zhanggame1 | Original post link

I don’t understand how it was resolved.

| username: ajin0514 | Original post link

How did you solve it?