DM 2.0 Version Error 3140: Invalid JSON text: The document is empty Error

Translated
1

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

Original topic: DM 2.0 版本 Error 3140: Invalid JSON text: The document is empty 报错

| username: TiDBer_yyy

[TiDB Usage Environment] Production Environment
[TiDB Version] tidb=5.0.4, dm=v2.0.7
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Issue Phenomenon and Impact]
DM data synchronization encountered Error 3140: Invalid JSON text: The document is empty, causing synchronization to be abnormally interrupted.

After the interruption, a large number of retry errors were found in the logs:

content:[2023/01/03 11:07:28.825 +08:00] [ERROR] [baseconn.go:184] ["execute statement failed"] [task=prod_datacenter-v5_rd_stattax] [unit="binlog replication"] [query="UPDATE `rttax2`.`taxpayer_task` SET `I_ID` = ?, ..., `I_STATUS` = ?,...., `CH_BACK_DATA` = ? WHERE `I_ID` = ? LIMIT 1"] [argument="[...,[],...]"] [error="Error 3140: Invalid JSON text: The document is empty"]
  1. CH_BACK_DATA is a json field, and the update parameter is parsed as [] (empty brackets).
  2. Checked the upstream MySQL data for this field CH_BACK_DATA= null
    image
2
| username: WalterWj | Original post link

Judging by the error, it seems that after updating the JSON field, the content is empty, which causes an issue.

I recommend manually executing the update SQL in TiDB to test and see the error.

  1. It could be that TiDB and MySQL behave differently.
  2. It might be related to sql_mode.

Test it first. If possible, provide the table structure and update statement. I would like to study it as well. :thinking:

3
| username: TiDBer_yyy | Original post link

The issue has been reproduced, and the reason is:
The data field is defined as not null, but the data written is null, causing the DM synchronization to fail.
image

4
| username: lance6716 | Original post link

Could you provide the table structure and DML so we can reproduce it on our end?

5
| username: TiDBer_yyy | Original post link

The following SQL statements are executed on the upstream MySQL of DM:

CREATE TABLE `dm_json_test` (
    ->   `I_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
    ->   `DATA` json NOT NULL COMMENT 'xxx',
    ->   PRIMARY KEY (`I_ID`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Test table';

insert into dm_json_test(I_ID, DATA) values(1, '{"name":"name1"}');

update dm_json_test set DATA=null where I_ID=1;
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

show warnings;
+---------+------+------------------------------+
| Level   | Code | Message                      |
+---------+------+------------------------------+
| Warning | 1048 | Column 'DATA' cannot be null |
+---------+------+------------------------------+

select * from dm_json_test;
+------+------+
| I_ID | DATA |
+------+------+
|    1 | null |
+------+------+
6
| username: TiDBer_yyy | Original post link

MySQL related bug:
https://bugs.mysql.com/bug.php?id=98496

7
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.