Error in binlog replication: Field 'user_id' doesn't have a default value

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

Original topic: binlog复制中断报错Field ‘user_id’ doesn’t have a default value

| username: mydb

【TiDB Usage Environment】Production\Test Environment\POC
Production Environment

【TiDB Version】

【Encountered Problem】
Cluster A synchronizes to Cluster B through drainer
Synchronization interrupted, drainer log error as follows:
[2022/09/05 14:52:50.640 +08:00] [ERROR] [executor.go:111] [“Exec fail, will rollback”] [error=“Error 1364: Field ‘user_id’ doesn’t have a default value”]

Then manually check this data in Cluster B, and it cannot be found:
select user_id from xxx_tb where row_id=9223521735016135040;
ERROR 1364 (HY000): Field ‘user_id’ doesn’t have a default value

If querying other columns, such as name, there will be no error, as shown in the following SQL:
select opt from xxx_tb where row_id=9223521735016135040;

There is a unique index on this table, which is (xxx_id, user_id)

The following SQL shows that this data is already corrupted.
select * from xxx_tb where user_id=‘82946666’;
ERROR 1105 (HY000): [components/tidb_query_executors/src/]: Data is corrupted, missing data for NOT NULL column (offset = 1)

Checked some posts on askgtug, it should be caused by a bug.

Now the questions are:

  1. What caused this bug, and in which version was it fixed?
  2. Is there a way to delete this one corrupted data, so as to avoid redoing the downstream Cluster B?

【Reproduction Path】What operations were performed to cause the problem
【Problem Phenomenon and Impact】

  • Relevant logs, configuration files, Grafana monitoring (
  • TiUP Cluster Display information
  • TiUP Cluster Edit config information
  • TiDB-Overview monitoring
  • Corresponding module Grafana monitoring (such as BR, TiDB-binlog, TiCDC, etc.)
  • Corresponding module logs (including logs one hour before and after the problem)

If the question is about performance optimization or fault troubleshooting, please download the script and run it. Please select all and copy and paste the terminal output.

| username: Kongdom | Original post link

According to the description on Git, it was already fixed in version 4.0.

| username: xfworld | Original post link

5.X no longer recommends using binlog, it is recommended to use tiCDC…

For incorrect data, try this and check if there are any issues with the index and data:

Is it not possible to delete it now?

| username: xiaohetao | Original post link

| username: xiaohetao | Original post link

| username: mydb | Original post link

Thank you, yes, there’s no way to delete this data.

| username: mydb | Original post link

Version 5.1 has come out again, :smile:

| username: mydb | Original post link

Okay, okay.

| username: tiancaiamao | Original post link

Is the data on cluster A normal?

| username: mydb | Original post link

The data on cluster A is normal. There should be only one problematic piece of data on cluster B.

| username: system | Original post link

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